OpenGeoDB - Umkreissuche
Die Verwendung der OpenGeoDB für eine Umkreissuche stellt wohl die häufigste Anwendungsform der Daten des Projekts dar. Da in diesem Zusammenhang die meisten Fragen und Probleme auftauchen, soll im folgenden exemplarisch erläutert werden, wie eine für diesen Zweck geeignete Datenbasis geschaffen werden kann.
Die bestehende Datenstruktur für die Implementierung einer Umkreissuche auf Postleitzahl-Basis zu verwenden ist aus Performance- und Effizienz-Gründen nicht anzuraten. Etliche der in der OpenGeoDb enthaltenen Daten sind dafür schlicht überflüssig. Im Prinzip wird lediglich eine Tabelle benötigt, die die Zuordnung von Postleitzahlen zu den entsprechenden Geokoordinaten enthält. Eine sinnvolle Ergänzung stellt die Erfassung der Namen der Städte und Ortschaften dar, um zum einen eine Auswahl über den Wohnort, zum anderen eine exakte Zuordnung der Koordinaten zur Postleitzahl zu ermöglichen.
Inhaltsverzeichnis
Allgemeine Überlegungen
Am Beispiel Berlin kann man leicht sehen, dass eine Stadt (pol. Gliederung) mehrere Postleitzahlen haben kann, umgekehrt kann aber auch eine Postleitzahl für mehrere Ortschaften gelten, wie folgende Abfrage zeigt:
SELECT text_val, count( * ) AS plz_count FROM geodb_textdata WHERE text_type = 500300000 GROUP BY text_val HAVING plz_count >1
So haben beispielsweise folgende Ortschaften die PLZ 24616, woraus sich ergibt, dass für eine Postleitzahl mehrere Koordinaten vorliegen können. In diesem Fall ist dann eine Erfassung der zugeordneten Stadt / Ortschaft zwingend notwendig, damit genauer spezifiziert werden kann welche Location gemeint ist:
SELECT plz.text_val AS plz, ort.text_val AS ortsname FROM geodb_textdata plz LEFT JOIN geodb_textdata ort ON plz.loc_id = ort.loc_id WHERE plz.text_type = 500300000 AND plz.text_val = '24616' AND ort.text_type = 500100000; +-------+-------------------------+ | plz | ortsname | +-------+-------------------------+ | 24616 | Armstedt | | 24616 | Borstel bei Neumünster | | 24616 | Hardebek | | 24616 | Hasenkrug bei Brokstedt | | 24616 | Brokstedt | | 24616 | Sarlhusen | | 24616 | Willenscharen | +-------+-------------------------+ 7 rows in set (0.70 sec)
Postleitzahlen sind in der OpenGeoDB als Textinformation zu einer Location hinterlegt, haben selbst also keine Geokoordinaten. Aktuell finden sich in der Datenbank zwar Locations vom Typ 100800000 (=Postleitzahlgebiet) denen dann auch Koordinaten zugeordnet werden können. Da diese sich gegenwärtig aber in der Entwicklung befinden, werden sie hier (noch) nicht berücksichtigt.
Erstellen der Basis-Daten
Um in der OpenGeoDB einer Postleitzahl eine Koordinate zuzuweisen, ist es nun also notwendig, zu ermitteln welcher Location die Postleitzahl zugeordnet wird und diese Daten entsprechend auszulesen. Wir erstellen also eine Tabelle die diese Daten aufnehmen kann.
CREATE TABLE `zip_coordinates` ( zc_id INT NOT NULL auto_increment PRIMARY KEY, zc_loc_id INT NOT NULL , zc_zip VARCHAR( 10 ) NOT NULL , zc_location_name VARCHAR( 255 ) NOT NULL , zc_lat DOUBLE NOT NULL , zc_lon DOUBLE NOT NULL )
In diese Tabelle importieren wir dann die benötigten Daten:
INSERT INTO zip_coordinates (zc_loc_id, zc_zip, zc_location_name, zc_lat, zc_lon) SELECT gl.loc_id, plz.text_val, name.text_val, coord.lat, coord.lon FROM geodb_textdata plz LEFT JOIN geodb_textdata name ON plz.loc_id = name.loc_id LEFT JOIN geodb_locations gl ON plz.loc_id = gl.loc_id LEFT JOIN geodb_hierarchies tier ON plz.loc_id = tier.loc_id LEFT JOIN geodb_coordinates coord ON plz.loc_id = coord.loc_id WHERE plz.text_type = 500300000 /* Postleitzahl */ AND name.text_type = 500100000 /* Name */ AND tier.id_lvl1 = 104 AND tier.id_lvl2 = 105 /* Bundesrepublik Deutschland */ AND name.text_locale = "de" /* deutschsprachige Version */ AND gl.loc_type IN ( 100600000 /* pol. Gliederung */, 100700000 /* Ortschaft */ );
Da die Tabelle für unsere Zwecke nur lesend verwendet wird und keine Datenpflege darin stattfindet wird (auch aus Performance-Gründen) auf Normalisierung verzichtet. Um die Abfragen zu beschleunigen werden noch folgende Indizes erstellt:
ALTER TABLE zip_coordinates ADD INDEX IDX_ZIP;
Damit ist unsere Daten-Basis als Grundlage für die Umkreissuche geschaffen.
Berechnung durchführen
Um diese nun durchzuführen sind folgende Schritte notwendig:
Auswählen der Geokoordinaten des Startpunktes
Dies kann über eine Suche in der Datenbank nach Postleitzahl oder Ort erfolgen.
SELECT zc_id, zc_location_name, zc_lat, zc_lon FROM zip_coordinates WHERE zc_zip = '[PLZ]';
oder
SELECT zc_id, zc_location_name, zc_lat, zc_lon FROM zip_coordinates WHERE `zc_location_name` like '%[Ortsname]%'
Erhält man als Ergebnis dieser Abfrage mehr als einen Datensatz muss eine weitere Auswahl über den Ortsnamen erfolgen, um eine eindeutige zc_id zu erhalten. Sind dann die Geokoordinaten des Ausgangspunktes bekannt, kann die eigentliche Umkreissuche anhand einer Entfernungsberechnung erfolgen.
Orte im Umkreis ermitteln
Für dieses Beispiel wird ein fester Suchradius von 10 Kilometer und ein Erdradius von 6380 Kilometer angenommen. Die Formel für die Berechnung zwischen zwei Orten A und B lautet:
- arccos( sin(latB)·sin(latA) + cos(latB)·cos(latA)·cos(lonB-lonA) ) · rErde
Möchten wir also alle Orte im Umkreis von 10 Kilometer um Gunzenhausen ermitteln, verwenden wir zur Ermittlung der Koordinaten:
SELECT * FROM zip_coordinates WHERE zc_location_name LIKE '%gunzenhausen%'; +-------+-----------+--------+-----------------------------+--------+---------+ | zc_id | zc_loc_id | zc_zip | zc_location_name | zc_lat | zc_lon | +-------+-----------+--------+-----------------------------+--------+---------+ | 9024 | 17697 | 91710 | Gunzenhausen am Altmühlsee | 49.1 | 10.75 | | 9041 | 26054 | 91747 | Westheim bei Gunzenhausen | 49 | 10.6667 | +-------+-----------+--------+-----------------------------+--------+---------+
Für die Berechnung der Orte im Umkreis verwenden wir dann die Daten der zc_id 9024 und schließen diesen Datensatz explizit aus, um nur umgebende Orte zu erhalten:
SELECT dest.zc_zip, dest.zc_location_name, ACOS( SIN(RADIANS(src.zc_lat)) * SIN(RADIANS(dest.zc_lat)) + COS(RADIANS(src.zc_lat)) * COS(RADIANS(dest.zc_lat)) * COS(RADIANS(src.zc_lon) - RADIANS(dest.zc_lon)) ) * 6380 AS distance FROM zip_coordinates dest CROSS JOIN zip_coordinates src WHERE src.zc_id = 9024 AND dest.zc_id <> src.zc_id HAVING distance < 10 ORDER BY distance; +--------+----------------------------+-----------------+ | zc_zip | zc_location_name | distance | +--------+----------------------------+-----------------+ | 91728 | Gnotzheim | 4.4325567776496 | | 91738 | Pfofeld | 6.3504728414845 | | 91735 | Muhr am See | 6.6538147409469 | | 91723 | Dittenheim | 6.6558263990773 | | 91741 | Theilenhofen | 7.5252762108334 | | 91743 | Unterschwaningen | 8.7104563387936 | | 91802 | Meinheim | 8.8793201892 | | 91719 | Heidenheim (Mittelfranken) | 9.275622122936 | | 91729 | Haundorf am Brombachsee | 9.3550566573581 | +--------+----------------------------+-----------------+ 9 rows in set (0.04 sec)
Da die Abfrage durch die berechneten Felder stets einen full table walk verursacht, ist es eine Überlegung wert, die Entfernungen aller Postleitzahl-Koordinaten zueinander zu berechnen und in einer eigenen Tabelle zu speichern. Andererseits hat die Tabelle nur knapp 18.000 Zeilen; eine Tabelle, die naiv die Entfernungen aller Datensätze zueinander speicherte, beliefe sich auf knapp 324 Millionen Zeilen.
[...]