Geocodieren von Postleitzahlen mit der OpenGeoDB

27. Oktober 2009 Ein Kommentar Google Maps / Geocoding

Im vorherigen Teil des Tutorials haben wir die OpenGeoDB installiert, und nun ist es an der Zeit das System sinnvoll einzusetzen. Ich verwende explizit keine Programmiersprache sondern bleibe beim SQL, damit ihr in allen Sprachen die entsprechenden Abfragen durchführen könnt.

Geocodierung von Postleitzahlen ist eine sehr sinnvolle Sache. Dass tolle ist, das die OpenGeoDB die Koordinaten direkt liefert, und man nicht zusätzlich noch Google Maps fragen muss. Das spart jede Menge API-Aufrufe, die bei Google Maps limitiert sind. Man könnte mit den Koordinaten der OpenGeoDB direkt einen Marker setzen oder auch die Karte entsprechend zentrieren.

Natürlich wäre es praktisch, wenn man jetzt einfach einen SELECT absetzt und man bekommt, was man braucht, aber meistens kommt es im Leben anders, und zweitens als man denkt :) OpenGeoDB ist da natürlich etwas komplexer, und man muss einen JOIN durchführen, da die Daten auf zwei Tabellen verteilt sind.

Deshalb erst einmal etwas zur Struktur der OpenGeoDB: In der Tabelle geodb_coordinates sind Längen- und Breitengrad der Orte gespeichert. Jeder dieser Orte besitzt eine eindeutige ID, die loc_id. In der Tabelle geodb_textdata sind die Postleitzahlen eingetragen. Die Zuordnung zu einem Ort erfolgt über die loc_id. Ferner besitzt ein Eintrag in der Tabelle geodb_textdata einen bestimmten Typ, dieser ist in der Spalte text_type festgehalten. Postleitzahlen sind vom Typ 500300000 (was man in der Tabelle geodb_type_names nachschlagen kann). Die Spalte text_type muss also diesen Wert aufweisen.

Geht man von meiner Postleitzahl – der 48477 – aus, erhält man die folgende Query:

SELECT
lat,lon
FROM geodb_coordinates
INNER JOIN geodb_textdata AS textdata
ON textdata.loc_id = geodb_coordinates.loc_id
WHERE
textdata.text_val = "48477"
AND textdata.text_type = "500300000"

Was wiederum zu den folgenden Koordinaten führt:

52.3167, 7.58333

Kopiert man entsprechend den String in Google Maps, befindet man sich ungefähr an der korrekten Position :) Ungefähr deshalb, weil obige Query mehrere Ergebnisse mit mehreren Koordinaten liefert! Um genau zu sein 6 Stück.

Führt man sich Folgendes zu Gemüte weiß man sehr schnell warum: OpenGeoDB findet nicht nur den eigentlich Ort, sondern auch alle Ortschaften innerhalb dieser Postleitzahl. Um genau zu sein, findet sie den Stadtteil sogar zweimal. Einmal als Ortschaft und einmal als offizielle Ortsbezeichnung hinter der Postleitzahl.

Wir müssen dem Ganzen mit folgender Query etwas mehr zu Leibe rücken:

SELECT gl.loc_id, plz.text_val, name.text_val, coord.lat, coord.lon
FROM geodb_textdata plz
LEFT JOIN geodb_textdata name ON name.loc_id = plz.loc_id
LEFT JOIN geodb_locations gl ON gl.loc_id = plz.loc_id
LEFT JOIN geodb_coordinates coord ON plz.loc_id = coord.loc_id
WHERE plz.text_type =500300000
AND plz.text_val = '48477'
AND name.text_type =500100000
AND  gl.loc_type =100600000

Das bringt genau EIN Ergebnis mit den Koordinaten: 52.3167, 7.58333. Aber auch hier Achtung! Man bekommt die Geokoordinaten des Stadtzentrums! Dies ist insbesondere in Großstädten sub-optimal :) Wenn man in obiger Query die gl.loc_type von 100600000 auf 100800000 ändert wird es genauer. Man bekommt dann mehrere Einträge mit allen Ortschaften in diesem Postleitzahlengebiet zurück. Man kann dann selbst entscheiden, welche Koordinaten man nutzt.

Auf jeden Fall ist es eine nette Möglichkeit Postleitzahlen zu Geocodieren. OpenGeoDB ist bzgl. Verknüpfung der Daten anstrengend, wer eine bessere Idee hat Postleitzahlen zu Geocodieren, ihr könnt jederzeit einen Kommentar schreiben :)