OpenGeoDB - Beschreibung

OpenGeoDB & GISWiki - Das freie Portal für Geoinformatik (GIS)
Version vom 28. März 2008, 22:11 Uhr von HeinzJ (Diskussion | Beiträge) (geodb_coordinates ist Beta:)

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

Im Mittelpunkt des Projektes OpenGeoDB steht der Aufbau einer möglichst vollständigen Datenbank mit Geokoordinaten zu allen Orten und Postleitzahlen im deutschsprachigen Raum (D,A,CH).

Karten können direkt von http://opengeodb.de bezogen werden. Einfach auf OpenGeoDB unter Ortsuche den entsprechenden Ortsnamen eingeben und die dort angezeigte Karte (PNG-Format) abspeichern. Die Genehmigung zur Nutzung dieser Karten unter der GFDL. Auch der Download der Datenbank ist möglich.

OpenGeoDB 0.2.4d Einführung in das Datenbank Layout

Quelle: http://sourceforge.net/docman/display_doc.php?docid=27437&group_id=132421 (Stand 14.11.2005)

ÜBERBLICK ÜBER DIE RELATIONEN:

Die opengeodb Datenbank besteht z.Zt. vor allem aus den Relationen (Tabellen) geodb_locations, geodb_hierarchies, geodb_coordinates und geodb_textdata.

Die restlichen Relationen geodb_floatdata, geodb_intdata, geodb_areas, geodb_polygons und geodb_type_names spielen (noch) keine oder keine wesentliche Rolle.

Hinweis

In den Versionen größer 0.2.4d wird die Tabelle geodb_hierarchies nicht mehr gefüllt (sondern unter geodb_textdata ein Attribut "Teil von" (text_type = 400.100.000) eingetragen). Die Relationen geodb_floatdata, geodb_intdata enthalten jetzt Fläche bzw. Einwohnerzahlen.

DIE RELATIONEN IM DETAIL:

geodb_locations:

      create table geodb_locations (
        loc_id               serial primary key,
        loc_type             integer not null
          check (loc_type = CONTINENT or loc_type = STATE or
                 loc_type = NUTS_I or loc_type = NUTS_II or
                 loc_type = NUTS_III or loc_type = POL_DIVISION or
                 loc_type = POPULATED_AREA or loc_type = LOC_AREA_CODE)
      );
geodb_locations ist der eigentliche Aufhänger, der für jeden Ort einen einzigen Datensatz enthält. Hierin wird eine "loc_id" definiert, die diesen Ort auch in allen anderen Relationen (Tabellen) eindeutig identifiziert.

Weiter gibt es nur noch das Attribut loc_type, das aussagt, um was es hier eigentlich geht: Die Koordinaten / Geschichte / Daten usw. z.B. einer Ortschaft oder einer Postleitzahl usw..

geodb_hierarchies:

    create table geodb_hierarchies (
      loc_id               integer not null references geodb_locations,
      level                integer not null check (level>0 and level<=9),
      id_lvl1              integer not null,
      id_lvl2              integer,
      id_lvl3              integer,
      id_lvl4              integer,
      id_lvl5              integer,
      id_lvl6              integer,
      id_lvl7              integer,
      id_lvl8              integer,
      id_lvl9              integer,
      valid_since          date,
      date_type_since      integer,
      valid_until          date not null,
      date_type_until      integer not null
      check (
        (
          (level = 1 and /* loc_id = id_lvl1 and */
                         id_lvl2 is null and id_lvl3 is null and
                         id_lvl4 is null and id_lvl5 is null and
                         id_lvl6 is null and id_lvl7 is null and
                         id_lvl8 is null and id_lvl9 is null) or
          (level = 2 and /* loc_id = id_lvl2 and */
                         id_lvl1 is not null and id_lvl3 is null and
                         id_lvl4 is null and id_lvl5 is null and
                         id_lvl6 is null and id_lvl7 is null and
                         id_lvl8 is null and id_lvl9 is null) or
          (level = 3 and /* loc_id = id_lvl3 and */
                         id_lvl1 is not null and id_lvl2 is not null and
                         id_lvl4 is null and id_lvl5 is null and
                         id_lvl6 is null and id_lvl7 is null and
                         id_lvl8 is null and id_lvl9 is null) or
          (level = 4 and /* loc_id = id_lvl4 and */
                         id_lvl1 is not null and id_lvl2 is not null and
                         id_lvl3 is not null and id_lvl5 is null and
                         id_lvl6 is null and id_lvl7 is null and
                         id_lvl8 is null and id_lvl9 is null) or
          (level = 5 and /* loc_id = id_lvl5 and */
                         id_lvl1 is not null and id_lvl2 is not null and
                         id_lvl3 is not null and id_lvl4 is not null and
                         id_lvl6 is null and id_lvl7 is null and
                         id_lvl8 is null and id_lvl9 is null) or
          (level = 6 and /* loc_id = id_lvl6 and */
                         id_lvl1 is not null and id_lvl2 is not null and
                         id_lvl3 is not null and id_lvl4 is not null and
                         id_lvl5 is not null and id_lvl7 is null and
                         id_lvl8 is null and id_lvl9 is null) or
          (level = 7 and /* loc_id = id_lvl7 and */
                         id_lvl1 is not null and id_lvl2 is not null and
                         id_lvl3 is not null and id_lvl4 is not null and
                         id_lvl5 is not null and id_lvl6 is not null and
                         id_lvl8 is null and id_lvl9 is null) or
          (level = 8 and /* loc_id = id_lvl8 and */
                         id_lvl1 is not null and id_lvl2 is not null and
                         id_lvl3 is not null and id_lvl4 is not null and
                         id_lvl5 is not null and id_lvl6 is not null and
                         id_lvl7 is not null and id_lvl9 is null) or
          (level = 9 and /* loc_id = id_lvl9 and */
                         id_lvl1 is not null and id_lvl2 is not null and
                         id_lvl3 is not null and id_lvl4 is not null and
                         id_lvl5 is not null and id_lvl6 is not null and
                         id_lvl7 is not null and id_lvl8 is not null)
          ) and
          (
            (valid_since is null and date_type_since is null) or
            (valid_since is not null and date_type_since is not null)
          )
      )
    );
geodb_hierarchies enthält die hierarchischen Strukturen, in der ein Ort wiederzufinden ist. Dafür finden sich in ihr neun Attribute mit den Namen id_lvl1 bis id_lvl9, in der die loc_ids aller neun Ebenen aufzufinden sind. Beispiel für den Ort mit der loc_id 27431 (Karwitz):

104 105 116 176 351 19122 27431 null null

id_lvl1 (104): Europa id_lvl2 (105): Deutschland id_lvl3 (116): Niedersachsen id_lvl4 (176): Regierungsbezirk Lüneburg id_lvl5 (351): Landkreis Lüchow-Dannenberg id_lvl6 (19122): Samtgemeinde Dannenberg (Elbe) id_lvl7 (27431): Karwitz id_lvl8 (null): - id_lvl9 (null): -

Das Attribut mit dem Namen "level" bezeichnet die Ebene, in der dieser Ort steht. Die Ebene 6 bezeichnet normale (eigenständige) Orte, die Ebene 7 Orts- oder Stadtteile. Ein Eintrag mit der Ebene 2 wäre ein ganz normaler Staat.

geodb_coordinates, geodb_textdata, geodb_floatdata, geodb_intdata:

    create table geodb_coordinates (
      loc_id               integer not null references geodb_locations,
      lon                  double precision,
      lat                  double precision,
      sin_lon              double precision,
      sin_lat              double precision,
      cos_lon              double precision,
      cos_lat              double precision,
      coord_type           integer not null check (coord_type=WGS84),
      coord_subtype        integer,
      valid_since          date,
      date_type_since      integer,
      valid_until          date not null,
      date_type_until      integer not null
    );
    create table geodb_textdata (
      loc_id               integer not null references geodb_locations,
      text_val             varchar(255) not null, /* varchar(2000)? */
      text_type            integer not null,
      text_locale          varchar(5), /* ISO 639-1 */
      is_native_lang       smallint(1),
      is_default_name      smallint(1),
      valid_since          date,
      date_type_since      integer,
      valid_until          date not null,
      date_type_until      integer not null,
        check (
          (
            (
              (text_type = NAME    or text_type = NAME_7BITLC    or
               text_type = NAME_VG or text_type = NAME_VG_7BITLC or
               text_type = SOURCE  or text_type = COMMENT
              ) and
              text_locale like '__%' and
              is_native_lang is not null and
              is_default_name is not null
            ) or
            (
              (text_type = ISO_3166_1_ALPHA_2 or text_type = ISO_3166_2 or
               text_type = AREA_CODE          or text_type = CAR_LICENSE_CODE or
               text_type = CAR_LIC_CODE_NAME
              ) and
              text_locale is null and
              is_native_lang is null and
              is_default_name is null
            )
          ) and
            (
              (valid_since is null and date_type_since is null) or
              (valid_since is not null and date_type_since is not null)
            )
        )
    );
    create table geodb_intdata (
      loc_id               integer not null references geodb_locations,
      int_val              bigint not null,
      int_type             integer not null,
      int_subtype          integer,
      valid_since          date,
      date_type_since      integer,
      valid_until          date not null,
      date_type_until      integer not null
    );
    create table geodb_floatdata (
      loc_id               integer not null references geodb_locations,
      float_val            double precision not null,
      float_type           integer not null,
      float_subtype        integer,
      valid_since          date,
      date_type_since      integer,
      valid_until          date not null,
      date_type_until      integer not null
    );
In diesen Relationen finden sich die eigentlichen Daten eines Ortes.

Während geodb_coordinates nicht wesentlich mehr als nur die Längen- und Breitengrade eines Ortes enthält, sind die anderen drei Relationen "Universalbehälter", in die man alle Daten packen kann, die in das jeweilige Format (Text / Fließkommazahl / Ganzzahl) passen.

Der tatsächliche Inhalt eines Datensatzes bestimmt sich über den jeweiligen "Typ" im Attribut text_type bzw. float_type oder int_type.

Wenn man also z.B. den Namen eines Ortes auslesen möchte, dann braucht man eine Abfrage in der Form:

SELECT text_val FROM geodb_textdata WHERE text_type=500100000 /* NAME */ AND loc_id=27431;

geodb_textdata enthält z.Zt. die Typen Name, Name in 7 Bit ASCII und Kleinschrift (zum Sortieren), Name eines Verwaltungszusammenschlusses und das gleiche wieder in 7 Bit usw., dann ISO Codes für die Länder und Provinzen (Bundesländer, Kantone usw. usf.), Postleitzahl, Autokennzeichen, Amtlicher Gemeindeschlüssel und testhalber Datenursprung (Source) und Kommentare.

geodb_floatdata enthält noch keine Daten, könnte aber z.B. die Flächengrößen o.ä. mit aufnehmen.

geodb_intdata enthält bislang nur Testdaten, und zwar ein paar Einwohnerzahlen. Auch hier sind andere Daten wie z.B. Höhenangaben möglich.

geodb_type_names:

    create table geodb_type_names (
      type_id              integer not null,
      type_locale          varchar(5) not null,
      name                 varchar(255) not null,
    unique (type_id, type_locale)
    );
Diese Relation sollte Bezeichner für die Typen in den "Universalrelationen" geodb_textdata etc. enthalten. In wieweit das sinnvoll, ist mir noch nicht ganz klar. Mehr als ein paar (nicht gepflegte) Testdaten enthält sie aber noch nicht.

geodb_areas, geodb_polygons:

Auch diese Relationen enthalten noch keine Daten. Vermutlich werden auch die Attribute dieser Relationen noch geändert bis dort Daten erscheinen werden. Es geht im Endeffekt darum, dort Vektordaten ablegen zu können, also vor allen die Grenzen von Ländern oder Gemeinden oder Ortschaften oder Postleitzahlen: was auch immer.

DIE SQL PRAXIS:

EINFÜHRUNG: Alle Ortsnamen mit Postleitzahl

Das obige SQL Beispiel zeigt eine sehr limitierte Anfrage, die in vielen Fällen nicht ausreichend ist.

Lassen wir uns zum Anfang mal alle Ortsnamen mit ihren Postleitzahlen ausgeben:

    SELECT plz.text_val as "PLZ", name.text_val as "Ort"
    FROM geodb_textdata plz, geodb_textdata name
    WHERE name.loc_id=plz.loc_id AND
          plz.text_type=500300000 /* PLZ */ AND
          name.text_type=500100000 /* NAME */
    ORDER by 2;

Erst einmal benötigen wir ZWEI VERSCHIEDENE Datensätze aus der geodb_textdata, nämlich einmal einen Datensatz, der die Postleitzahl enthält, ein anderes Mal den Datensatz, der den Namen des Ortes enthält. Aus diesem Grunde brauchen wir geodb_textdata ZWEIMAL in der FROM Klausel. Zur (notwendigen) Unterscheidung bennenen wir sie, hier der Übersicht halber mit "plz" und "name".

Aus der ersten geodb_textdata lesen wir die Postleitzahl aus, deshalb:

    SELECT plz.text_val as "plz" [...] FROM geodb_textdata plz

Aus der zweiten geodb_textdata lesen wir den Namen des Ortes aus, also:

    SELECT [...], name.text_val as "name" FROM [...], geodb_textdata name

Natürlich sollen die Postleitzahl und der Name sich auf den gleichen Ort beziehen, der ja - wie oben kurz beschrieben - eindeutig durch eine loc_id gekennzeichnet ist. Also kommt in die WHERE Klausel:

 ... WHERE name.loc_id=plz.loc_id [...]

Dann soll die als "plz" benannte Relation natürlich NUR Postleitzahlen auslesen, die wir über den text_type erkennen. Also geht es weiter mit:

 ... WHERE [...] AND plz.text_type=500300000 /* PLZ */ [...]

In der gleichen Weise soll aus der zweiten geodb_textdata Relation nur der NAME des Ortes ausgelesen werden, also heißt es:

 ... WHERE [...] AND name.text_type=500100000 /* NAME */

FÜR FORTGESCHRITTENE: Postleitzahlen und Orte von Liechtenstein

Na gut, das waren jetzt vielleicht doch etwas viele Orte, wie wäre es, wenn wir uns auf alle Orte in Liechtenstein beschränken?

Hier müssen wir in die geodb_hierarchies schauen, weil nur dort erkennbar ist, in welcher politischen Hierarchie dieser Ort steht.

Erst einmal benötigen wir die id_lvl2 aus der geodb_hierarchies, denn das bezeichnet die loc_id des Staates aller Orte:

 ... WHERE id_lvl2=???

Ja gut, aber welchen Wert soll id_lvl2 haben? Oder anders gefragt: Was ist die loc_id des Ortes, das z.B. den ISO Code "FL" (== Fürstentum Liechtenstein) hat? Kein Problem:

    SELECT [...] geodb_textdata.loc_id
    FROM geodb_textdata
    WHERE text_val='FL' AND
              text_type=500100001 /* ISO Code */

Oder zusammengebastelt:

    SELECT ...
    FROM geodb_hierarchies hi, geodb_textdata land
    WHERE hi.id_lvl2=land.loc_id AND
          land.text_val='FL' AND
          land.text_type=500100001 /* ISO 3166 */

Jetzt wollen wir aber noch den Namen des Ortes (aus geodb_textdata) auslesen, den wir aber nicht in der geodb_textdata land finden. Also müssen wir nochmal eine geodb_textdata spendieren, diesmal soll der text_type der Name sein und irgendwo in der Hierarchie stehen, die als Bedingung hatte, daß der Staat 'FL' sei:

 ... WHERE [...] ort.loc_id = hi.loc_id AND
          ort.text_type = 500100000 /* NAME */

Und jetzt alles zusammen:

    SELECT ort.text_val
    FROM geodb_hierarchies hi, geodb_textdata land, geodb_textdata ort
    WHERE hi.id_lvl2=land.loc_id AND
          land.text_val='FL' AND
          land.text_type=500100001 /* ISO 3166 */ AND
          ort.loc_id = hi.loc_id AND
          ort.text_type = 500100000 /* NAME */

Jetzt fehlt uns noch die Postleitzahl, die noch einen weiteren geodb_textdata Datensatz erfordert, dann sieht das alles zusammen etwa so aus:

    SELECT plz.text_val, ort.text_val
    FROM geodb_hierarchies hi,
         geodb_textdata land,
         geodb_textdata ort,
         geodb_textdata plz
    WHERE hi.id_lvl2=land.loc_id AND
          land.text_val='FL' AND
          land.text_type=500100001 /* ISO 3166 */ AND
          ort.loc_id = hi.loc_id AND
          ort.text_type = 500100000 /* NAME */ AND
          plz.loc_id = ort.loc_id AND
          plz.text_type = 500300000 /* PLZ */

"Verhübschen" wir es noch etwas:

    SELECT plz.text_val as "PLZ",
           concat(concat(land.text_val,'-'),ort.text_val) as "Name"
    FROM geodb_hierarchies hi,
         geodb_textdata land,
         geodb_textdata ort,
         geodb_textdata plz
    WHERE hi.id_lvl2=land.loc_id AND
          land.text_val='FL' AND
          land.text_type=500100001 /* ISO 3166 */ AND
          ort.loc_id = hi.loc_id AND
          ort.text_type = 500100000 /* NAME */ AND
          plz.loc_id = ort.loc_id AND
          plz.text_type = 500300000 /* PLZ */
    ORDER BY 2

(Postgres Nutzer ersetzen die Vorkommen von concat durch textcat)

Schwierig? Ich tendiere eher zu einem "komplex!"...

VIEL ZU EINFACH: Alle Kantone der Schweiz

Ja, aber??? Das hatten wir doch gerade? -- Stimmt!

Nur diesmal wollen wir das ISO Kürzel anstelle der Postleitzahlen ausgeben, suchen zur Abwechselung nach dem Begriff "Suisse" (also text_type = Name, d.h. 500100000):

    SELECT iso.text_val as "ISO",
           name.text_val as "Name"
    FROM geodb_hierarchies hi,
         geodb_textdata land,
         geodb_textdata name,
         geodb_textdata iso
    WHERE hi.id_lvl2=land.loc_id AND
          land.text_val='Suisse' AND
          land.text_type=500100000 /* NAME */ AND
          name.loc_id = hi.loc_id AND
          name.text_type = 500100000 /* NAME */ AND
          iso.loc_id = name.loc_id AND
          iso.text_type = 500100001 /* ISO 3166 */

Nanu, nanu: wir haben nicht nur die Kantone, sondern auch die Schweiz selber? Das können wir zusätzlich ausschließen, wenn wir sagen, daß die Ebene der von geodb_hierarchies gelieferten Daten ausschließlich drei sein soll (drei == Kantone / Bundesländer etc.), also:

    WHERE ... AND level = 3 ...

Einfacher und direkter geht es aber, wenn wir nicht nach name.loc_id = hi.loc_id schauen (was uns unabhängig von der Ebene ALLE Lokalitäten ausgibt), sondern nach name.loc_id = hi.id_lvl3:

    SELECT iso.text_val as "ISO",
           name.text_val as "Name"
    FROM geodb_hierarchies hi,
         geodb_textdata land,
         geodb_textdata name,
         geodb_textdata iso
    WHERE hi.id_lvl2=land.loc_id AND
          land.text_val='Suisse' AND
          land.text_type=500100000 /* NAME */ AND
          name.loc_id = hi.id_lvl3 AND
          name.text_type = 500100000 /* NAME */ AND
          iso.loc_id = name.loc_id AND
          iso.text_type = 500100001 /* ISO 3166 */

SPEZIALFÄLLE UND ANDERE ANMERKUNGEN:

Datum der Gültigkeit:

Alle Daten sind mit einem Gültigkeitsdatum versehen. Diese sind in den Attributen valid_since, date_type_since, valid_until und date_type_until in fast allen Relationen vorhanden.

date_type_since / date_type_until enthalten die Angabe, wie genau das angegebene Datum zu interpretieren ist, also als exakt oder z.B. als "In dem Jahr ..." usw.. Öfter wird man auf den Typ UNKNOWN_FUTURE_DATE (300500000) treffen, der angibt, daß die Gültigkeit noch nicht abgelaufen ist.

UNKNOWN_FUTURE_DATE geht immer einher mit dem Datum 1.1.3000, so daß man bei Fragen nach aktuellen Daten immer die Frage stellen kann:

 ... WHERE valid_until >= [heute]

Eine Ausnahme bilden die Fragen nach den Einwohnerzahlen, weil sie sich ja auf Zeit-PUNKTE beziehen und nicht auf Zeiträume. Wie die Datumsangaben dort einzutragen sind, ist für mich noch nicht endgültig geklärt, zumal es bislang noch keine wesentlichen Daten dazu gibt.

Besondere Atribute in geodb_textdata:

Um es zu ermöglichen, mehrere Namen für einen Ort angeben zu können, z.B. München / Munich oder België / Belgique, gibt es in geodb_textdata ein Attribut mit dem Namen text_locale.

Da es meistens sinnvoller ist, nicht ALLE möglichen Namen auszugeben, sondern nur die Namen, die an dem Ort auch in Gebrauch sind, wurde ein weiteres Attribut eingeführt: is_native_lang gibt an, ob diese Sprache auch am Ort üblich ist.

Mehrere Namen erzeugen aber noch ganz andere Probleme. Oftmals (meistens) möchte man nur einen einzigen eindeutigen Namen erhalten. Die Rückgabe mehrerer Namen kann Anwendungen extrem verkomplizieren. Deshalb gibt es noch ein Attribut in geodb_textdata mit dem Namen 'is_default_name'. Es ist garantiert, dass dieses Attribut entweder null ist oder aber eindeutig für einen bestimmten Ort und einem bestimmten text_type.

Auf diese Weise kann man mit der Klausel:

 ... AND is_default_name = 1

beim text_type=NAME etc. garantieren, daß man nur einen, und zwar den sinnvollsten Namen zurückbekommt.

Da es ein paar Situationen gibt, bei denen man keinen wirklich perfekten Namen finden kann, z.B. bei Europa, wird im Zweifelsfall vorzugsweise auf den englischen Namen ausgewichen. So würde man bei Europa und der Abfrage auf '... is_default_name = 1' 'Europe' als Antwort bekommen, was möglicherweise nicht gewünscht ist, wenn man z.B. eine deutsche WWW-Seite betreibt.

In diesem Fall müsste man formal gesehen auf eine "Exklusiv-Oder" Bedingungen ausweichen, die es einem ermöglichten, die deutsche Sprache zu wählen, WENN es einen Eintrag in deutscher Sprache gibt UND WENN es eine Sprache an diesem Ort ist (is_native_lang = 1) ODER WENN das nicht der Fall ist, dann den default_name zu nehmen.

Da Exklusiv-Oder Bedingungen aber nicht zu einem frühen SQL-Standard gehören, kann es sein, daß man diese Konstruktion per Anwendungsprogramm nachbilden müßte: das wäre sehr aufwendig.

geodb_coordinates ist Beta:

4.3.1) Diese Relation ist z.Zt. sehr aufgebläht mit abgeleiteten Werten wie sin_lon usw. usf.. Der Sinn war, Anfragen z.B. nach Entfernungen zu anderen Orten zu beschleunigen, indem man diese vorgefertigten Sinus und Cosinus Werte nutzen konnte.

Es sieht allerdings in der Praxis nicht so aus, als würde der Nachteil der stark aufgeblähten Relation den Vorteil möglicherweise etwas schnellerer Anfragen aufwiegen.

Das ist ein Grund dafür, weshalb ich erwäge, dieses vier Attribute aus geodb_coordinates wieder zu entfernen.

4.3.2) Das Attribut coord_type enthält den Typ der Koordinaten, also in unseren Fall WGS84. Das macht nur dann Sinn, wenn man andere Koordinatentypen zulassen wollte, z.B. UTM oder welche Projektionsart auch immer. Auf der anderen Seite wären diese anderen Koordinatentypen auch wieder nur abgeleitete Daten, die vor allem die Datenbank aufblasen, ohne einen wesentlichen Informationszugewinn zu bringen.

Deshalb denke ich darüber nach, coord_type ebenfalls aus dieser Relation zu entfernen (und coord_subtype in coord_type umzubenennen