Adresssuche in PostgreSQL
Ein interessantes Thema – vorallem, weil die Schreibweisen von Adressen oft nicht trivial sind. Auf die Schnelle habe ich folgende zwei Strategien gefunden:
- pg_trgm: Dieses Modul erlaubt es, die Ähnlichkeit von Wörtern zu messen. Durch das Anlegen eines Index, ist diese Methode extrem performant.
- Phonet: Erlaubt einen String auf einen „Aussprache“ String zu projizieren. Ähnlich klingende Strings mappen somit auf den gleichen String. Anschließend Abgleich mit pg_gtrm. Der Vorteil: Aussprachefehler werden „ausgebessert“ und führen zu einem besseren Match.
Wann funktioniert das gut? Wenn man die Menge an Möglichkeiten voreinschränkt. Dazu bietet sich natürlich die Postleitzahl an. In Österreich 4-stellig – aber es reicht, wenn man auch nur 1-2 Stellen eingibt. Vorangestellt oder nicht – je nach dem. Ich war mit dem Ergebnis sehr zufrieden.
Die Daten habe ich von http://www.statistik.at/verzeichnis/strassenliste/gemplzstr.zip bezogen, habe später aber eine bessere Datenquelle inkl. Hausnummern gefunden: https://www.bev.gv.at/portal/page?_pageid=713,2601271&_dad=portal&_schema=PORTAL
Die größte Herausforderung ist es sicher, die Adresse zu „zerlegen“. Beispiele sind:
Beliebige-Muster-Straße 7 1230 Wien
Bzw. mit Stiege und Block:
Beliebige-Muster-Straße 7/ 4 1230 Wien
Beliebige-Muster-Straße 7/B/3/4 1230 Wien
Mit regulären Ausdrücken prinzipiell möglich – nur kann man bei „named capture groups“ den Hausnummer / Block / Stiege / Türnummer teil nicht mehr zerlegen (ist denk, da nicht mehr context-free). Das Thema Hausnummer / Block / Stiege / Türnummer ist natürlich sehr interessant. Ich würde mir die Fragen stellen: PostgreSQL hat keine SPARSE Columns – Impact da Block und Stiege oft leer ist? Eventuell eine Tabelle mit „Häuser“ mit Spalte Hausnummer und JSOB für den „Rest“? Der „Rest“ muss ja nicht durchsucht werden?
Das Schema für die Straßennamen gestaltet sich relativ einfach (Tabellenstruktur wurde stur übernommen):
CREATE DATABASE playground;
CREATE SCHEMA IF NOT EXISTS playground;
DROP TABLE IF EXISTS playground.streets;
CREATE TABLE playground.streets
(
gemnr TEXT,
gemnam38 TEXT,
okz TEXT,
ortname TEXT,
skz TEXT,
stroffi TEXT,
plznr TEXT,
gemnr2 TEXT,
zustort TEXT,
code TEXT
);
CREATE EXTENSION pg_trgm;
CREATE INDEX streets_stroffi_idx ON playground.streets USING GiST(stroffi GiST_trgm_ops);
CREATE INDEX streets_code_idx ON playground.streets USING GiST(code GiST_trgm_ops);
CREATE INDEX streets_plznr_idx ON playground.streets USING GiST(plznr GiST_trgm_ops);
Die Suche nach Adressen habe ich mit Dappr und einer .NET Core Consolen Applikation getestet:
using Dapper;
using Npgsql;
using phonet4n.Core;
using System;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
namespace Test
{
class Program
{
private static Phonetizer phonet = new Phonetizer(true);
private static Regex addressRegex = new Regex(@"^(?<street>[^\d\,]*[^\d,\s])\s*(?<house>[\/\dA-Za-z]+)?(?:,\s*(?<plz>\d{1,4})\s*(?<city>.*))?$", RegexOptions.Compiled);
private static object syncRoot = new object();
static void Main(string[] args)
{
var timer = new System.Timers.Timer();
var inputBuffer = new StringBuilder();
timer.Elapsed += (sender, e) =>
{
((System.Timers.Timer)sender).Stop();
lock (syncRoot)
{
Console.Clear();
Console.SetCursorPosition(0, 0);
Console.WriteLine(inputBuffer);
Console.SetCursorPosition(0, 2);
Console.WriteLine(GetResult(inputBuffer.ToString()));
Console.SetCursorPosition(inputBuffer.Length, 0);
}
};
timer.Interval = 1000;
timer.Enabled = false;
while (true)
{
var pressedKey = Console.ReadKey();
timer.Stop();
timer.Start();
if (!char.IsControl(pressedKey.KeyChar))
{
inputBuffer.Append(pressedKey.KeyChar);
}
var charWasRemoved = false;
if (pressedKey.Key == ConsoleKey.Backspace && inputBuffer.Length > 0)
{
inputBuffer.Remove(inputBuffer.Length - 1, 1);
charWasRemoved = true;
}
lock (syncRoot)
{
Console.SetCursorPosition(0, 0);
Console.Write(inputBuffer);
if(charWasRemoved)
{
Console.Write(" ");
Console.SetCursorPosition(Console.CursorLeft - 1, Console.CursorTop);
}
}
}
}
private static string GetResult(string userQuery)
{
var match = addressRegex.Match(userQuery);
if (match.Success)
{
var result = new StringBuilder();
using (var connection = new NpgsqlConnection($"Server=localhost;Database=playground;User Id=postgres;Password=postgres;"))
{
connection.Open();
var result_tgrm = Enumerable.Empty<string>();
var result_phonet = Enumerable.Empty<string>();
if (match.Groups["plz"].Success)
{
result_tgrm = connection.Query<string>("SELECT CONCAT(stroffi, ', ', plznr, ' ', zustort, ' ', (stroffi <-> @name)) FROM playground.streets WHERE plznr LIKE @plz AND stroffi <-> @name < 0.9 ORDER BY stroffi <-> @name LIMIT 10;", new { name = match.Groups["street"].Value, plz = $"{match.Groups["plz"].Value }%" });
result_phonet = connection.Query<string>("SELECT CONCAT(stroffi, ', ', plznr, ' ', zustort, ' ', (code <-> @phonet)) FROM playground.streets WHERE plznr LIKE @plz AND code <-> @phonet < 0.9 ORDER BY code <-> @phonet LIMIT 10;", new { phonet = phonet.Phonetize(match.Groups["street"].Value), plz = $"{match.Groups["plz"].Value }%" });
}
else
{
result_tgrm = connection.Query<string>("SELECT CONCAT(stroffi, ', ', plznr, ' ', zustort, ' ', (stroffi <-> @name)) FROM playground.streets WHERE stroffi <-> @name < 0.9 ORDER BY stroffi <-> @name LIMIT 10;", new { name = match.Groups["street"].Value });
result_phonet = connection.Query<string>("SELECT CONCAT(stroffi, ', ', plznr, ' ', zustort, ' ', (code <-> @phonet)) FROM playground.streets WHERE code <-> @phonet < 0.9 ORDER BY code <-> @phonet LIMIT 10;", new { phonet = phonet.Phonetize(match.Groups["street"].Value) });
}
result.AppendLine(string.Join(Environment.NewLine, result_tgrm));
result.AppendLine();
result.AppendLine(string.Join(Environment.NewLine, result_phonet));
return result.ToString();
}
}
else
{
return "Syntax error: <streetname> <optinal doornumber>, <optional plz> <optinal city>";
}
}
}
Ergebnis:
pilgram,1050
Pilgramgasse, 1050 Wien 0.5 <----
U-Bahn Station Pilgramgasse, 1050 Wien 0.7586207
Pilgramgasse, 1050 Wien 0.46153843 <----
U-Bahn Station Pilgramgasse, 1050 Wien 0.7407408
===============
bilgrammgasse,1
Pilgramgasse, 1050 Wien 0.5 <----
Lammgasse, 1080 Wien 0.58823526
Billgasse, 1220 Wien 0.58823526
Stammgasse, 1030 Wien 0.6111111
Billergasse, 1190 Wien 0.6315789
...
Pilgramgasse, 1050 Wien 0.39999998 <----
Billgasse, 1220 Wien 0.46153843
Billergasse, 1190 Wien 0.625
U-Bahn Station Pilgramgasse, 1050 Wien 0.64285713
Gotramgasse, 1220 Wien 0.64705884
...
===============
belvü,1190
Bellevuestraße, 1190 Wien 0.8333333 <----
Beethovengang, 1190 Wien 0.8888889
Beethovensteg, 1190 Wien 0.8888889
Bernatzikgasse, 1190 Wien 0.8947368
Walter-Berry-Weg, 1190 Wien 0.9
Bellevuestraße, 1190 Wien 0.8125 <----
Bernatzikgasse, 1190 Wien 0.875
Beethovengang, 1190 Wien 0.875
Beethovensteg, 1190 Wien 0.875
Walter-Berry-Weg, 1190 Wien 0.8888889
================
dr böringer,1
Dr.-Boehringer-Gasse, 1120 Wien 0.6086956 <----
Döblinger Bad, 1190 Wien 0.7
Sieveringer Bad, 1190 Wien 0.72727275
...
Dr.-Boehringer-Gasse, 1120 Wien 0.3125 <----
Döblinger Bad, 1190 Wien 0.7368421
Börnsteingasse, 1210 Wien 0.75
...
================
kling strom gasse,1
Strohgasse, 1030 Wien 0.61904764
Stefan-Koblinger-Gasse, 1220 Wien 0.6333333
Strohbogasse, 1210 Wien 0.6521739
Strobelgasse, 1010 Wien 0.6521739
Strozzigasse, 1080 Wien 0.6521739
Klingenbachgasse, 1210 Wien 0.65384614
Ing.-Körner-Gasse, 1170 Wien 0.65384614
Stroheckgasse, 1090 Wien 0.6666666
St.-Wendelin-Gasse, 1220 Wien 0.6666666
Strobachgasse, 1050 Wien 0.6666666
Strohgasse, 1030 Wien 0.6315789
Klingerstraße, 1230 Wien 0.6363636
Strohmayergasse, 1060 Wien 0.6363636
Klinkowströmgasse, 1140 Wien 0.64 <----
Ing.-Körner-Gasse, 1170 Wien 0.6666666
Strozzigasse, 1080 Wien 0.6666666
Strobelgasse, 1010 Wien 0.6666666
Stromstraße, 1200 Wien 0.6666666
Strohbogasse, 1210 Wien 0.6666666
Strohberggasse, 1120 Wien 0.6818182
================
gerhartugasse,1
Gerhardusgasse, 1200 Wien 0.4736842 <----
Gerlgasse, 1030 Wien 0.58823526
Gerhard-Fritsch-Gasse, 1170 Wien 0.6
...
Gerhardusgasse, 1200 Wien 0.5294118 <----
Gerhard-Fritsch-Gasse, 1170 Wien 0.55
Gerambgasse, 1220 Wien 0.5625
Gerlgasse, 1030 Wien 0.6
...
================
harteckerstrasse,1
Hartäckerstraße, 1190 Wien 0.5652174 <----
Hartlgasse, 1200 Wien 0.6666666
Hackergasse, 1100 Wien 0.6818182
Hartmanngasse, 1050 Wien 0.7083334
...
Hartäckerstraße, 1190 Wien 0 <----
Bäckerstraße, 1010 Wien 0.5294118
Haugerstraße, 1110 Wien 0.5555556
Hasnerstraße, 1160 Wien 0.5555556
...
================s
badegaweg,8501
Badeggerweg, 8501 Lieboch 0.5333333 <----
Bahnweg, 8501 Lieboch 0.71428573
Bahnweg, 8501 Lieboch 0.71428573
Bachweg, 8501 Lieboch 0.71428573
...
Badeggerweg, 8501 Lieboch 0 <----
Nadeggerweg, 8501 Lieboch 0.46153843
Jägerweg, 8501 Lieboch 0.6153846
Jägerweg, 8501 Lieboch 0.6153846
...
================
pösendorfastrasse,1
Bösendorferstraße, 1010 Wien 0.71428573 <----
Utendorfgasse, 1140 Wien 0.72
Vösendorfer Straße, 1230 Wien 0.7241379
Roggendorfgasse, 1170 Wien 0.7407408
...
Bösendorferstraße, 1010 Wien 0.3 <----
Ebendorferstraße, 1010 Wien 0.47619045
Vösendorfer Straße, 1230 Wien 0.47826087
...
================
kanwogasse,1
Kantgasse, 1010 Wien 0.5
Kannegasse, 1150 Wien 0.5333333
Kandlgasse, 1070 Wien 0.5333333
Kanitzgasse, 1230 Wien 0.5625
Kaniakgasse, 1110 Wien 0.5625
Kantnergasse, 1210 Wien 0.58823526
Kaingasse, 1210 Wien 0.6
Kahlgasse, 1210 Wien 0.6
Kainzgasse, 1170 Wien 0.625
Kauergasse, 1150 Wien 0.625
Kantgasse, 1010 Wien 0.53846157
Carrogasse, 1210 Wien 0.53846157
Kannegasse, 1150 Wien 0.53846157
Canalettogasse, 1120 Wien 0.5625
Canongasse, 1180 Wien 0.57142854
Kanitzgasse, 1230 Wien 0.57142854
Kandlgasse, 1070 Wien 0.57142854
Canovagasse, 1010 Wien 0.6 <----
Kahlgasse, 1210 Wien 0.6153846
Kantnergasse, 1210 Wien 0.62
================
radsekystrasse,80
Radgasse, 8020 Graz 0.6666666
Radetzkystraße, 8010 Graz 0.6956522 <----
Radetzkystraße, 8010 Graz 0.6956522 <----
Stradiotgasse, 8020 Graz 0.73913044
Rasthausgasse, 8054 Seiersberg-Pirka 0.73913044
Strassoldogasse, 8010 Graz 0.75
Hochstrassergasse, 8045 Graz 0.76
Raachgasse, 8051 Graz 0.7619048
Stranzgasse, 8041 Graz 0.77272725
Rankengasse, 8020 Graz 0.77272725
Radetzkystraße, 8010 Graz 0.5 <----
Radetzkystraße, 8010 Graz 0.5 <----
Ragnitzstraße, 8047 Hart b.Graz 0.6111111
Rabnitzstraße, 8063 Eggersdorf b.Graz 0.6111111
Ragnitzstraße, 8047 Graz 0.6111111
Ragnitzstraße, 8047 Kainbach b.Graz 0.6111111
Ragnitzstraße, 8047 Graz 0.6111111
Ragnitzstraße, 8010 Graz 0.6111111
Rabnitzstraße, 8062 Kumberg 0.6111111
Riesstraße, 8010 Graz 0.625
Man sieht, dass bei Rechtschreib- bzw. Aussprachefehler die Phonet Lösung etwas besser ist – allerdings auch nicht in dem Umfang, dass man sich jetzt eine zusätzliche Library ins Projekt holen muss.
Phonet findet man hier:
Zu erwähnen ist noch, das pg_trgm den Wortanfang höher gewichtet:
SELECT '123' <-> '123abcd', '123abcd'
UNION
SELECT '123' <-> 'a123bcd', 'a123bcd'
UNION
SELECT '123' <-> 'ab123cd', 'ab123cd'
UNION
SELECT '123' <-> 'abcd123', 'abcd123'
?column? |?column?|
---------+--------+
0.9090909|a123bcd |
0.8|abcd123 |
0.6666666|123abcd |
0.9090909|ab123cd |