Phonetische Suche

[lapadula]

Ich bin dabei die Suchfunktion in unserem Programm zu verbessern, damit sollen Namen nach ihrem Sprachklang gefunden und bestmöglich sortiert werden.

In der Personentabelle habe ich jeweils eine generated Spalte für den Namen und Vornamen der Person, in der der Name nach Kölner Phonetik kodiert vorliegt. Zum Beispiel:

name | vorname | soundex_name | soundex_vorname
-------------------------------------------------
Meier, Dieter, 67, 227
Neyer, Theodor, 67, 227
Lehner, Dieter, 567, 227

Angenommen man möchte nach dem Namen und Vornamen Meier, Dieter suchen. Meine Abfrage sieht so aus:

SELECT
    name,
    vorname
FROM
    person
WHERE
   person.soundex_name    like '%67%' AND -- Meier
   person.soundex_vorname like '%227%' --Dieter
ORDER BY
    levenshtein('Meier', person.name),
    levenshtein('Dieter', person.vorname);

Die Suchbegriffe werden nach Kölner Phonetik kodiert und in der Where-Bedinungen angegeben.
Zum Sortieren nutze ich die Levenshtein Distanz, je geringer der Wert, desto eher entspricht es dem Suchbegriff.

Leider befindet sich Neyer, Theodor über Lehner, Dieter, weil die Levenshtein-Distanz für den Namen Lehner zu Meier 3 beträgt, die Distanz von Neyer zu Meier aber 2.
In dem Fall würde ich gerne den direkten Treffer beim Vornamen oben sehen.

Habe bereits versucht, beide Begriffe in der levenshtein-funktion anzugeben:

levenshtein('Dieter Meier', person.name || ' ' || person.vorname);

Dadurch werden aber dennoch namen wie:

Attermeyer,Dieter,02767,227

unterhalb von Neyer, Theodor einsortiert. Eine Idee was ich noch machen kann?

Kölner Phonetik – Wikipedia

de.wikipedia.org

Levenshtein-Distanz – Wikipedia

de.wikipedia.org

 

[dabadepdu]

In dem Fall würde ich gerne den direkten Treffer beim Vornamen oben sehen.

Es macht nicht viel Sinn in DB über Einzelfälle bei einer Phonetischen Suche zu sprechen. Es kennt auch niemand die Dateninhalte, die sonst noch so aufpoppen könnten, wenn man etwas Deiner Suche ändert. Was soll man da raten?

Ich würde sagen, die Suchoptionen wie z.B. Sortierung nach Namensspalten oder Levenshtein Distanz als Frontend Option anbieten. Du würdest gerne den Treffer im Vornamen bevorzugen, andere vielleicht was anderes. Das würde ich allerdings nicht für beliebig große Ergebnismengen anbieten.

Falls die allgemeine Regel ist, in gewissen Fällen eine Sortierung / Priorisierung nach bester Vornamen-Match zu zeigen, dann müssen halt die Distanzen für Name und Vorname separat als sortierbare Ergebnisspalten für den Anwender verfügbar sein.
Soll "das beste" Ergebnis automatisch dargestellt werden, musst Du die Regeln für die Automatik genau definieren.

 

[AndreasM]

Hallo lapadula,

ich habe ein ähnliches Problem und nutze Kölner Phonetik und ggf Phonet.
Aber ich mach keinen Vergleich mit like, sondern nutzt die Volltextsuche mit tsquery und tsvector.
Nachdem ich die Doku gelesen habe schien mir das besser zu sein.
Der Nachteil bei etwas wie " like '%227%' " ist, das man keine Indizierung zur Abfragebeschleunigung nutzen kann, wenn ich die Doku richtig gelesen habe.

Ich erzeuge einen phonetischen Code ( ich nenne die jetzt mal phonet_vorname und phonet_nachname) und speichere diese as Volltextvektor in einem indizierten Vektorfeld ts_vector_name zur Volltextsuche

ts_vector_name = to_tsvector('dh_fts_german_names', concat( phonet_vorname, ' ', phonet_nachname) );

Im Gegenzug kann ich Treffer mit einem tsquery abfragen:

SELECT ....
WHERE plainto_tsquery('dh_fts_german_names', concat(phonet_vorname, ' ', phonet_nachname) ) @@ t1.ts_vector_name

Das funktioniert sehr gut und findet auch "Wolfgang Meyer" gegen "Meyer Wolfgang", falls man z.B. mal Namensdreher hat.

VG
Andreas

 

[castorp]

Der Nachteil bei etwas wie " like '%227%' " ist, das man keine Indizierung zur Abfragebeschleunigung nutzen kann, wenn ich die Doku richtig gelesen habe.

Doch kann man, mit einem trigram index

 

[Walter]

Doch kann man, mit einem trigram index

Sehr interessant zu lesen.

 

[castorp]

Waiting for 9.1 – Faster LIKE/ILIKE – select * from depesz;

 

[AndreasM]

Hallo,
evtl. FYI, auch wenn es an der ursprünglichen Frage vorbeigeht:

Es gibt einen interessanten, älteren Artikel "Doppelgänger gesucht" zur phonetischen Codierung mit Quellcode
c't Magazin

Ausserdem habe da ein Lehrprojekt gefunden, wo der Autor die "Phonet"-Routine aus dem obigen Artikel in eine Postgres-Extension integriert hat:

Index of /beispiele/db/postgresql/volltext/phonet

Ich habe diese Extension kompilieren und installieren können.
Jetzt sieht eine phonetische Codierung eines Vektors zur Volltextsuche so aus

SELECT 1, TO_TSVECTOR('phonet1', 'Meier, Dieter')
UNION
SELECT 2, TO_TSVECTOR('phonet1', 'Mayer, Dieter')
UNION
SELECT 3, TO_TSVECTOR('phonet1', 'Dieter, Meir')
UNION
SELECT 4, TO_TSVECTOR('phonet1', 'Theodor, Neyer')
-------
Total rows: 3 of 3

1 "'dita':2 'meia':1"
2 "'dita':2 'meia':1"
3 "'dita':1 'meia':2"
4 "'neia':2 'teodor':1"

Die einzelnen Lexeme sind alphanumerisch sortiert, die Zahl gibt ihre Position auf dem Vektor an .
Die Phonetik funktioniert schon mal sehr gut

Mit einen plainto_tsquery findet man dann z.B. auch Dreher

SELECT 1, PLAINTO_TSQUERY('phonet1', 'Dieter, Meir') @@ TO_TSVECTOR('phonet1', 'Meier, Dieter')
----
1 true

SELECT 1, PLAINTO_TSQUERY('phonet1', 'Dieter, Meir') @@ TO_TSVECTOR('phonet1', 'Theodor Neyer ')
----
1 false

Man findet auch Treffer wenn der Text mit weniger Worten im Text mit mehr Worten enthalten ist, z.B. "Dieter Meyer" in "Hans-Dieter Meyer"

SELECT 1, PLAINTO_TSQUERY('phonet2', 'Dieter Mayer') @@ TO_TSVECTOR('phonet2', 'Hans-Dieter Meyer ')
----
1 true


Möchte man die Reihenfolge der Worte beachtet wissen nutzt man

SELECT 1, PHRASETO_TSQUERY('phonet1', 'Dieter, Meir') @@ TO_TSVECTOR('phonet1', 'Meier, Dieter')
1 false

Dann habe ich noch einen neueren Ansatz gefunden:

Beider-Morse Phonetic Matching (BMPM) , aber habe das noch nicht weiter ausprobiert weil ich noch nicht weiss wie ich das in eine Postgres-Funktion bringen kann.

VG