Einfaches Geocoding mit Open Street Map
Nach dem gestrigen Tutorial über die Verarbeitung von Openstreetmap Daten geht es heute um ein sehr interessantes Thema: Geocoding.
Es gibt unzählige Geocoder, die entweder ebenfalls auf Openstreetmap oder kommerzielle Daten zugreifen, um das Geocoding durchzuführen. Meistens gibt es dabei Restriktionen bezüglich der Anzahl der Anfragen pro Tag oder pro Sekunde. Openstreetmap bietet mit Nominatim einen eigenen mächtigen Geocoder an. Wer jedoch keine vollständige Volltextsuche und andere fancy Features von Nominatim benötigt und nur "mal eben" ein paar Adressen geocoden will oder eine leichtgewichtige Schnittstelle für eigene Projekte benötigt, steht da meistens im Regen.
Deshalb habe ich das gestrige Tutorial zum Anlass genommen, um nachzuforschen, wie umständlich es ist, mit Hilfe der gestrigen Datenbasis Geocoding Anfragen zu verarbeiten und war erstaunt, wie einfach das geht und wie schnell (relativ) die Anfragen bearbeitet werden, ohne gröbere Optimierungen an der Datenbank vorgenommen zu haben. Zum Beispiel verwende ich die Postgres Volltextsuche, ohne einen Index dafür zu erstellen (deshalb ist es vielleicht nicht die schlauste Idee, das Ding für Produktive Projekte zu verwenden).
Die Query
Zuerst habe ich mich auf die Suche nach den Adressdaten in Openstreetmap gemacht und bin schnell im Wiki fündig geworden. Dabei sind die Adressdaten meistens in der Tabelle ways
gespeichert. In nodes
wiederum findet man die Koordinaten für die Wege und in way_nodes
sind die Relationen der beiden zu finden. Alles in Allem keine große Kunst, wir filtern am Ende noch alle Datensätze heraus, die keine Adresseinträge haben und bekommen folgende Query.
SELECT *
FROM ways w
INNER JOIN way_nodes wn ON w.id = wn.way_id
INNER JOIN nodes n ON n.id = wn.node_id
WHERE exist(w.tags, 'addr:housenumber') AND exist(w.tags, 'addr:street')
Ich filtere absichtlich nur nach den Hausnummern und Straßennamen, da mich vorwiegend diese beiden interessieren und ich auf die Postleitzahl (addr:postcode
) und Stadt- sowie Ländernamen (addr:city
, addr:country
) verzichten kann. Sollte man nur vollständige Datensätze benötigen, müsste man die WHERE
-Klausel noch um AND exist(w.tags,'addr:<tagname>')
für die einzelnen Tags erweitern.
Als Nächstes habe ich die Query um folgende Punkte erweitert:
- Die interessanten Tags wie
addr:housenumber
,... als Spalten anzeigen - Die Koordinaten Gruppieren und den Durchschnitt berechnen, wobei das in manchen Situationen zu Problemen führen könnte, da OSM die Häuser teilweise in ihren Grundrissformen (laut Grundbuch) abbildet und
AVG
nicht unbedingt den Mittelpunkt des Gebäudes präsentieren muss. Für meine (und wahrscheinlich viele andere) Zwecke kann man diese Ungenauigkeit jedoch vernachlässigen. - Ich verbinde den gesamten Eintrag zum Format
Straße Hausnummer PLZ Stadt Land
, um daraus Volltextsuchen zu machen.
Und schon haben wir eine annehmbare Query, die uns Geocoding in beide Richtungen erlaubt.
SELECT
w.tags -> 'addr:street' AS street,
w.tags -> 'addr:housenumber' AS housenumber,
w.tags -> 'addr:postcode' AS postcode,
w.tags -> 'addr:city' AS city,
w.tags -> 'addr:country' AS country,
AVG(ST_X(n.geom)) AS longitude,
AVG(ST_Y(n.geom)) AS latitude,
concat_ws(' ', w.tags -> 'addr:street',
w.tags -> 'addr:housenumber',
w.tags -> 'addr:postcode',
w.tags -> 'addr:city',
w.tags -> 'addr:country'
) AS full_text
FROM ways w
INNER JOIN way_nodes wn ON w.id = wn.way_id
INNER JOIN nodes n ON n.id = wn.node_id
WHERE exist(w.tags, 'addr:housenumber') AND exist(w.tags, 'addr:street')
GROUP BY housenumber, street, postcode, city, country
Dies kann man jetzt als View oder gleich als eigene Tabelle abspeichern und den oben genannten Full-Text-Index anlegen. Oder man baut die Abfrage als indirektes View in die Query ein.
Geocoding
Mit dieser Abfrage können wir jetzt ganz einfach Geocoding durchführen. Angenommen ich suche nach Hauptplatz Graz
muss ich den Suchstring zuerst in einen Postgres Suchstring umwandeln. In dem Fall wäre das Hauptplatz & Graz
geworden, da nach Hauptplatz UND Graz gesucht wurde. Diese Arbeit nimmt uns die Funktion plainto_tsquery('Hauptplatz, Graz')
ab.
Die angesprochene Query sieht also so aus:
SELECT
street,
housenumber,
postcode,
city,
longitude,
latitude
FROM (SELECT
w.tags -> 'addr:street' AS street,
w.tags -> 'addr:housenumber' AS housenumber,
w.tags -> 'addr:postcode' AS postcode,
w.tags -> 'addr:city' AS city,
w.tags -> 'addr:country' AS country,
AVG(ST_X(n.geom)) AS longitude,
AVG(ST_Y(n.geom)) AS latitude,
concat_ws(' ', w.tags -> 'addr:street',
w.tags -> 'addr:housenumber',
w.tags -> 'addr:postcode',
w.tags -> 'addr:city',
w.tags -> 'addr:country'
) AS full_text
FROM ways w
INNER JOIN way_nodes wn ON w.id = wn.way_id
INNER JOIN nodes n ON n.id = wn.node_id
WHERE exist(w.tags, 'addr:housenumber') AND exist(w.tags, 'addr:street')
GROUP BY housenumber, street, postcode, city, country
) geocode
WHERE to_tsvector(full_text) @@ plainto_tsquery('Hauptplatz, Graz')
ORDER BY street, housenumber
Und liefert folgendes Ergebnis mit meinen Graz Daten von dem gestrigen Post:
Hauptplatz | 10 | 8010 | Graz | 15.4374430875 | 47.0713055625 |
Hauptplatz | 11 | 8010 | Graz | 15.438206190909092 | 47.071604572727274 |
Hauptplatz | 12 | 8010 | Graz | 15.438236900000001 | 47.071482185714295 |
Hauptplatz | 13 | 8010 | Graz | 15.438507529999999 | 47.071479919999994 |
Hauptplatz | 14 | 8010 | Graz | 15.438553899999999 | 47.07128896 |
Hauptplatz | 15 | 8010 | Graz | 15.438749625000002 | 47.07117975 |
Hauptplatz | 16 | 8010 | Graz | 15.438883933333333 | 47.071101388888884 |
Hauptplatz | 17 | 8010 | Graz | 15.439108812499999 | 47.071079649999994 |
Hauptplatz | 4 | 8010 | Graz | 15.437747120000001 | 47.07077956 |
Hauptplatz | 5 | 8010 | Graz | 15.437681316666664 | 47.07083186666666 |
Hauptplatz | 6 | 8010 | Graz | 15.437633714285713 | 47.070904385714286 |
Hauptplatz | 7 | 8010 | Graz | 15.4377174 | 47.071087212500004 |
Hauptplatz | 8 | 8010 | Graz | 15.4376675 | 47.071124579999996 |
Hauptplatz | 9 | 8010 | Graz | 15.437595916666666 | 47.071199916666664 |
Reverse Geocoding
Reverse Geocoding sollte mit dem bisherigen Wissen ein leichtes Spiel sein, dennoch möchte ich die Query nicht vorenthalten.
Man beachte, dass ich hier auf die Volltextsuche verzichtet habe und stattdessen mit PostGis den "Nearest Point" 10 Einträge mit der geringsten Distanz suche.
SELECT
street,
housenumber,
postcode,
city,
longitude,
latitude,
CAST (
st_distance_sphere(
st_makepoint(longitude,latitude),
st_makepoint(<lon>,<lat>)
)
AS FLOAT) AS distance
FROM (SELECT
w.tags -> 'addr:street' AS street,
w.tags -> 'addr:housenumber' AS housenumber,
w.tags -> 'addr:postcode' AS postcode,
w.tags -> 'addr:city' AS city,
w.tags -> 'addr:country' AS country,
AVG(ST_X(n.geom)) AS longitude,
AVG(ST_Y(n.geom)) AS latitude
FROM ways w
INNER JOIN way_nodes wn ON w.id = wn.way_id
INNER JOIN nodes n ON n.id = wn.node_id
WHERE exist(w.tags, 'addr:housenumber') AND exist(w.tags, 'addr:street')
GROUP BY housenumber, street, postcode, city, country
) geocode
ORDER BY distance ASC
LIMIT 10
Fazit
Auch wenn man die Queries wegen der Laufzeit nicht unbedingt in einem Produktivsystem verwenden will, hat mir dieses Experiment klar gezeigt, wie ich die Openstreetmap Daten verarbeiten kann.
Auch die Dauer von knapp 4 Stunden inklusive Blogpost, Recherche und Herumprobieren hat mich positiv überrascht - vor 4 Stunden hatte ich noch nicht mal eine Ahnung, wie ich das anstellen könnte.
Ich habe auch ein Github Projekt angelegt und eine kleine API drumherum gebastelt https://github.com/plechi/mini-geocoder.
Außerdem werde ich mich in den kommenden Tage und Wochen sicher noch mehr mit Openstreetmap und den unzähligen Möglichkeiten beschäftigen.
Ressourcen
- Blogpost von gestern: http://blog.plechinger.at/spass-mit-open-street-map-daten/
- Gitub Projekt der API: https://github.com/plechi/mini-geocoder