Beispiele

OpenGeoDB & GISWiki - Das freie Portal für Geoinformatik (GIS)
Version vom 23. März 2008, 18:45 Uhr von Sts (Diskussion | Beiträge) (Die Seite wurde neu angelegt: Durch die folgenden Beispiele sollen die Möglichkeiten zur Nutzung der OpenGeoDB praktisch veranschaulicht werden. == Informationen zu einer Ortschaft auslesen == W...)

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

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)