OpenGeoDB - Umkreissuche: Unterschied zwischen den Versionen

OpenGeoDB & GISWiki - Das freie Portal für Geoinformatik (GIS)
Wechseln zu: Navigation, Suche
(noch etwas mehr Dopplung im SQL entfernt)
 
(8 dazwischenliegende Versionen von einem anderen Benutzer werden nicht angezeigt)
Zeile 56: Zeile 56:
 
  SELECT gl.loc_id, plz.text_val, name.text_val, coord.lat, coord.lon
 
  SELECT gl.loc_id, plz.text_val, name.text_val, coord.lat, coord.lon
 
  FROM geodb_textdata plz
 
  FROM geodb_textdata plz
  LEFT JOIN geodb_textdata name ON plz.loc_id = name.loc_id
+
  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_locations gl     ON plz.loc_id = gl.loc_id
  LEFT JOIN geodb_hierarchies as tier ON plz.loc_id = tier.loc_id /* localisierung */
+
  LEFT JOIN geodb_hierarchies tier ON plz.loc_id = tier.loc_id
 
  LEFT JOIN geodb_coordinates coord ON plz.loc_id = coord.loc_id
 
  LEFT JOIN geodb_coordinates coord ON plz.loc_id = coord.loc_id
  WHERE plz.text_type =500300000/* ID für Postleitzahl */
+
  WHERE plz.text_type = 500300000 /* Postleitzahl */
  AND name.text_type =500100000/* ID für name */
+
AND   name.text_type = 500100000 /* Name */
  AND tier.id_lvl1 = 104
+
AND   tier.id_lvl1 = 104
  AND tier.id_lvl2 = 105 /* Bundesrepublik Deutschland*/
+
AND   tier.id_lvl2 = 105 /* Bundesrepublik Deutschland */
  AND name.text_locale = "de" /* deutschsprachige Version*/
+
AND   name.text_locale = "de" /* deutschsprachige Version */
  AND gl.loc_type IN ( 100600000 /* pol. Gliederung */, 100700000 /* Ortschaft */ );
+
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:
+
<strike>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;
 
  ALTER TABLE zip_coordinates ADD INDEX IDX_ZIP;
 
+
</strike>
 
Damit ist unsere Daten-Basis als Grundlage für die Umkreissuche geschaffen.
 
Damit ist unsere Daten-Basis als Grundlage für die Umkreissuche geschaffen.
  
Zeile 84: Zeile 84:
 
  FROM zip_coordinates
 
  FROM zip_coordinates
 
  WHERE zc_zip = '[PLZ]';
 
  WHERE zc_zip = '[PLZ]';
 
+
 
'''oder'''
+
oder
 
    
 
    
 
  SELECT zc_id, zc_location_name, zc_lat, zc_lon  
 
  SELECT zc_id, zc_location_name, zc_lat, zc_lon  
Zeile 95: Zeile 95:
 
=== Orte im Umkreis ermitteln ===
 
=== 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 lautet:
+
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(B_lat)*sin(A_lat)+cos(B_lat)*cos(A_lat)*cos(B_lon - A_lon)) * Erdradius
+
: arccos( sin(lat<sub>B</sub>)·sin(lat<sub>A</sub>) + cos(lat<sub>B</sub>)·cos(lat<sub>A</sub>)·cos(lon<sub>B</sub>-lon<sub>A</sub>) ) · r<sub>Erde</sub>
  
 
Möchten wir also alle Orte im Umkreis von 10 Kilometer um Gunzenhausen ermitteln, verwenden wir zur Ermittlung der Koordinaten:
 
Möchten wir also alle Orte im Umkreis von 10 Kilometer um Gunzenhausen ermitteln, verwenden wir zur Ermittlung der Koordinaten:
Zeile 115: Zeile 115:
  
 
  SELECT  
 
  SELECT  
     zc_zip,  
+
     dest.zc_zip,  
     zc_location_name,
+
     dest.zc_location_name,
 
     ACOS(
 
     ACOS(
           SIN(RADIANS(zc_lat)) * SIN(RADIANS(49.1))  
+
           SIN(RADIANS(src.zc_lat)) * SIN(RADIANS(dest.zc_lat))  
           + COS(RADIANS(zc_lat)) * COS(RADIANS(49.1)) * COS(RADIANS(zc_lon)
+
           + COS(RADIANS(src.zc_lat)) * COS(RADIANS(dest.zc_lat))
          - RADIANS(10.75))
+
          * COS(RADIANS(src.zc_lon) - RADIANS(dest.zc_lon))
          ) * 6380 AS distance
+
    ) * 6380 AS distance
  FROM zip_coordinates
+
  FROM zip_coordinates dest
  WHERE zc_id <> 9024
+
CROSS JOIN zip_coordinates src
 +
  WHERE src.zc_id = 9024
 +
AND dest.zc_id <> src.zc_id
 
  HAVING distance < 10
 
  HAVING distance < 10
 
  ORDER BY distance;
 
  ORDER BY distance;
Zeile 142: Zeile 144:
 
  9 rows in set (0.04 sec)
 
  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.
+
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.
  
 
[...]
 
[...]

Aktuelle Version vom 28. April 2015, 23:31 Uhr

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.

[...]