Teodor Sigaev wrote:
>> also, i'd be happy to listen opinions from people who have experience
>> of usage of such things like soundex.
I'm using metaphone() together with levenshtein() to search a place name
gazetteer database and order the results. That works reasonably well and
gives interesting results ("places with similar names"). However, it does
not cover "partial" matches (it does just compare the whole string, and does
not find multi-word names when just a single word is entered, eg. it would
not find "santa cruz" when you just enter "cruz").
Regarding db structure: I've specifically added a column which contains the
metaphone string (loaded with "UPDATE places set pname_metaphone =
metaphone(pname, 11)") - this row is obviously indexed (and, with functional
indices, actuall redundant ;). i'm then using "SELECT * from places where
pname_metaphone = metaphone('searchstring', 11)" to retrieve similar names.
levenshtein is used to order those rows by string distance.
try it at http://nona.net/features/map/
I haven't attemted yet to combine tsearch2 and metaphone results - that
would probably be the PerfectSolution(tm).
hope that helps
Alex