Beispiele - ratopi/opengeodb GitHub Wiki

TOC Durch die folgenden Beispiele sollen die Möglichkeiten zur Nutzung der OpenGeoDB praktisch veranschaulicht werden.

== Informationen zu einer Ortschaft auslesen ==

Wir ermitteln die loc_id zu Oberammergau durch eine einfache Abfrage der Tabelle geodb_textdata:

SELECT loc_id FROM geodb_textdata WHERE text_val = 'oberammergau' AND text_type = 500100000 /* ID von Name */;

+--------+ | loc_id | +--------+ | 21855 | +--------+

Ermitteln aller Daten zu Oberammergau, die in Textform vorliegen (um die Bedeutung des text_type zu verdeutlichen wird ein JOIN auf die Tabelle geodb_type_names durchgeführt):

SELECT gtn.name, gt.text_val FROM geodb_textdata AS gt LEFT JOIN geodb_type_names AS gtn ON gt.text_type = gtn.type_id WHERE loc_id = 21855;

+------------------------------+--------------+ | name | text_val | +------------------------------+--------------+ | Teil von | 207 | | Ebene | 6 | | Typ | Gemeinde | | Name | Oberammergau | | Sortiername | OBERAMMERGAU | | Telefonvorwahl | 08822 | | KFZ-Kennzeichen | GAP | | Amtlicher Gemeindeschlüssel | 09180125 | | Postleitzahl | 82487 | +------------------------------+--------------+

Abfrage aller Daten zu Oberammergau, die als Integer vorliegen:

SELECT gtn.name, gi.int_val FROM geodb_intdata AS gi LEFT JOIN geodb_type_names AS gtn ON gi.int_type = gtn.type_id WHERE loc_id = 21855;

+---------------+---------+ | name | int_val | +---------------+---------+ | Einwohnerzahl | 5379 | +---------------+---------+

Abfrage aller Daten zu Oberammergau, die als Fließkommazahl vorliegen:

SELECT gtn.name, gf.float_val FROM geodb_floatdata AS gf LEFT JOIN geodb_type_names AS gtn ON gf.float_type = gtn.type_id WHERE loc_id = 21855;

+--------+-----------+ | name | float_val | +--------+-----------+ | Fläche | 30 | +--------+-----------+

Abfrage der Geokoordinaten zu Oberammergau

SELECT gtn.name, gc.lon, gc.lat FROM geodb_coordinates AS gc LEFT JOIN geodb_type_names AS gtn ON gc.coord_type = gtn.type_id WHERE loc_id = 21855;

+-------------------+---------+------+ | name | lon | lat | +-------------------+---------+------+ | WGS84 Koordinaten | 11.0667 | 47.6 | +-------------------+---------+------+

Führen wir das ganze für Berlin durch, erhalten wir drei loc_ids, wir prüfen also, von welchem Typ diese Locations jeweils sind:

SELECT gt.loc_id , gtn.name FROM geodb_textdata as gt LEFT JOIN geodb_locations gl ON gl.loc_id = gt.loc_id LEFT JOIN geodb_type_names gtn ON gl.loc_type = gtn.type_id WHERE gt.text_val LIKE 'berlin' AND gt.text_type = 500100000;

+--------+-----------------------+ | loc_id | name | +--------+-----------------------+ | 109 | Bundesland | | 319 | Landkreis | | 14356 | Politische Gliederung | +--------+-----------------------+

Uns interessiert hier also die "Politische Gliederung" sprich die Stadt Berlin (Die Art der politischen Gliederung wird wiederum durch einen Eintrag "Typ" in der Tabelle geodb_textdata (text_type 400300000) näher definiert.

SELECT gtn.name, gt.text_val FROM geodb_textdata AS gt LEFT JOIN geodb_type_names AS gtn ON gt.text_type = gtn.type_id WHERE loc_id = 14356;

+------------------------------+----------+ | name | text_val | +------------------------------+----------+ | Teil von | 319 | | Ebene | 6 | | Typ | Stadt | | Name | Berlin | | Sortiername | BERLIN | | Telefonvorwahl | 030 | | KFZ-Kennzeichen | B | | Amtlicher Gemeindeschlüssel | 11000000 | | Postleitzahl | 10178 | | Postleitzahl | 10115 | | Postleitzahl | 10117 | | [...] | [...] | | Postleitzahl | 14197 | | Postleitzahl | 14199 | +------------------------------+----------+ 199 rows in set (0.00 sec)

== Ermitteln der Landkreise in Deutschland ==

Eine Übersicht über die Landkreise in Deutschland mit der dazugehörigen loc_id und den KFZ-Kennzeichen:

SELECT gl.loc_id, lkrs_name.text_val, kfz_name.text_val AS kfz FROM geodb_locations AS gl LEFT JOIN geodb_textdata AS lkrs_name ON gl.loc_id = lkrs_name.loc_id LEFT JOIN geodb_textdata AS kfz_name ON gl.loc_id = kfz_name.loc_id WHERE gl.loc_type =100500000 /* ID für Landkreis / AND lkrs_name.text_type =500100000 / ID für Name / AND kfz_name.text_type =500500000 / ID für KFZ-Kennzeichen */

+--------+----------------------------+------+ | loc_id | text_val | kfz | +--------+----------------------------+------+ | 194 | Rhein-Sieg-Kreis | SU | | 195 | Rhein-Kreis Neuss | NE | | 196 | Unna | UN | | 197 | Rheinisch-Bergischer Kreis | GL | | 198 | Viersen | VIE | | [...]| [...] |[...] | | 632 | Weißenburg-Gunzenhausen | WUG | | 689 | Heilbronn | HN | | 690 | Bamberg | BA | +--------+----------------------------+------+ 439 rows in set (0.93 sec)

== Detailinformationen zu einem ausgewählten Landkreis ==

Möchten wir nun wissen, welche Ortschaften zum Landkreis Pinneberg gehören, nehmen wir die loc_id 485 (aus der obigen Abfrage ermittelt) und prüfen welche Datensätze den text_type "Teil von" (=400100000) deren Feld text_val dieser loc_id entspricht, und führen einen weitere JOINs durch um die Postleitzahl, den Namen der Ortschaft, den Typ, die Vorwahl und die Einwohnerzahl zu erhalten:

SELECT gtv.loc_id, plz.text_val AS plz, name.text_val AS name, typ.text_val AS typ, telv.text_val AS vorwahl, einw.int_val AS einwohner FROM geodb_textdata gtv LEFT JOIN geodb_textdata name ON gtv.loc_id = name.loc_id LEFT JOIN geodb_textdata typ ON gtv.loc_id = typ.loc_id LEFT JOIN geodb_textdata plz ON gtv.loc_id = plz.loc_id LEFT JOIN geodb_textdata telv ON gtv.loc_id = telv.loc_id LEFT JOIN geodb_intdata einw ON gtv.loc_id = einw.loc_id WHERE name.text_type = 500100000 /* Name / AND plz.text_type = 500300000 / Postleitzahl / AND typ.text_type = 400300000 / Typ / AND telv.text_type = 500400000 / Vorwahl / AND einw.int_type = 600700000 / Einwohner / AND gtv.text_type = 400100000 / Teil von / AND gtv.text_val = '485' / loc_id des Landkreis Pinneberg */;

Diese Abfrage liefert uns folgendes Ergebnis (gekürzt):

+--------+-------+------------------------------+----------+---------+-----------+ | loc_id | plz | name | typ | vorwahl | einwohner | +--------+-------+------------------------------+----------+---------+-----------+ | 13710 | 25482 | Appen, Kreis Pinneberg | Gemeinde | 04101 | 5807 | | 14109 | 25355 | Barmstedt | Stadt | 04123 | 9475 | | 14442 | 25355 | Bevern, Holstein | Gemeinde | 04123 | 583 | | 14498 | 25485 | Bilsen | Gemeinde | 04106 | 713 | | 14692 | 25474 | Bönningstedt | Gemeinde | 040 | 4077 | | 14727 | 25364 | Bokel bei Elmshorn | Gemeinde | 04127 | 634 | | [...] | [...] | [...] | [...] | [...] | [...] | | 25031 | 25436 | Tornesch | Stadt | 04122 | 12876 | | 25191 | 25436 | Uetersen | Stadt | 04122 | 17871 | | 25758 | 22880 | Wedel | Stadt | 04103 | 31875 | | 26035 | 25364 | Westerhorn | Gemeinde | 04127 | 1310 | +--------+-------+------------------------------+----------+---------+-----------+ 49 rows in set (0.03 sec)