OpenGeoDB - Umkreissuche

OpenGeoDB & GISWiki - Das freie Portal für Geoinformatik (GIS)
Version vom 28. April 2015, 23:31 Uhr von Mwelt (Diskussion | Beiträge)

(Unterschied) ← Nächstältere Version | Aktuelle Version ansehen (Unterschied) | Nächstjüngere Version → (Unterschied)
Wechseln zu: Navigation, Suche

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.


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.

[...]