/ Open Source

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:

Hauptplatz108010Graz15.437443087547.0713055625
Hauptplatz118010Graz15.43820619090909247.071604572727274
Hauptplatz128010Graz15.43823690000000147.071482185714295
Hauptplatz138010Graz15.43850752999999947.071479919999994
Hauptplatz148010Graz15.43855389999999947.07128896
Hauptplatz158010Graz15.43874962500000247.07117975
Hauptplatz168010Graz15.43888393333333347.071101388888884
Hauptplatz178010Graz15.43910881249999947.071079649999994
Hauptplatz48010Graz15.43774712000000147.07077956
Hauptplatz58010Graz15.43768131666666447.07083186666666
Hauptplatz68010Graz15.43763371428571347.070904385714286
Hauptplatz78010Graz15.437717447.071087212500004
Hauptplatz88010Graz15.437667547.071124579999996
Hauptplatz98010Graz15.43759591666666647.071199916666664
*(Daten in der Tabelle © [OpenStreetMap-Mitwirkende](http://www.openstreetmap.org/copyright))*

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