Datenbank: Unterschied zwischen den Versionen

OpenGeoDB & GISWiki - Das freie Portal für Geoinformatik (GIS)
Wechseln zu: Navigation, Suche
 
(Tabellen-Übersicht)
 
(13 dazwischenliegende Versionen von 4 Benutzern werden nicht angezeigt)
Zeile 1: Zeile 1:
=Die Datenbank der OpengeoDB (Version 0.2.x)=
+
== Allgemein ==
Hier finden Sie ein Bild der Datenbank-Struktur der OpenGeoDB der Versionen 0.2: [[Datenbank-Modell]]
+
 
==generelles==
+
In der Datenbank OpenGeoDB können Informationen gespeichert werden, die irgendwie einem Ort zugeordnet werden können. Im folgenden wird dabei von einer Location gesprochen. Eine Location kann sowohl eine Ortschaft, ein Postleitzahlgebiet, ein See oder auch ein Kontinent sein.
die OpenGeoDB setzt sich aus mehreren Tabellen zusammen. Die Erklärungen dazu im Einzelnen siehe [[Datenbankstruktur#Die Tabellen|unten]].
+
 
das wahrscheinlich wichtigste Feld in der ganzen Datenbank, das in mehreren Tabellen wieder auftaucht, ist die loc_id.
+
Die zentrale Tabelle ist dabei die Tabelle geodb_locations. Sie enthält zwei Felder, die loc_id als Primärschlüssel, sowie den loc_type, der die Art des Datensatzes beschreibt. Die genaue Bedeutung von loc_type wird in der Tabelle geodb_type_names beschrieben. Folgende Abfrage liefert eine Übersicht über Typ und Anzahl der in geodb_locations enthaltenen Einträge, die für die loc_id in geodb_locations verwendet werden:
Diese kennzeichnet eindeutig ein bestimmtes Objekt - sei es eine Stadt, ein Stadtteil, ein Bundesland, ein Landkreis oder etwas anderes.
+
 
Die Datenbank ist nun im Grunde genommen typ-basiert aufgebaut.
+
SELECT gtn.type_id, gtn.name, COUNT(*) AS amount
geodb_intdata enthält Ganzzahlige Werte, geodb_textdata Zeichenketten, floatdata kannn Fließkomma-Werte aufnehmen und so weiter.
+
FROM geodb_locations AS gl
 +
LEFT JOIN geodb_type_names AS gtn ON gl.loc_type = gtn.type_id
 +
GROUP BY gl.loc_type
 +
 +
+-----------+-----------------------+--------+
 +
| type_id  | name                  | amount |
 +
+-----------+-----------------------+--------+
 +
| 100200000 | Staat/Land            |      1 |
 +
| 100300000 | Kanton                |    32 |
 +
| 100400000 | Regierungsbezirk      |    32 |
 +
| 100500000 | Landkreis            |    440 |
 +
| 100600000 | Politische Gliederung |  12300 |
 +
| 100700000 | Ortschaft            |  3568 |
 +
| 100800000 | Postleitzahlgebiet    |  43988 |
 +
| 100900000 | Ortsteil              |    11 |
 +
+-----------+-----------------------+--------+
 +
 
 +
Die in den Tabellen geodb_coordinates, geodb_floatdata, geodb_intdata und geodb_textdata enthaltenen Datensätze liefern weitere Informationen zu den in geodb_locations eingetragenen Datensätzen in der dem Tabellennamen entsprechenden Form: geodb_textdata enthält Daten, die als String zu interpretieren sind, geodb_intdata enthält Integerwerte etc.
 +
 
 +
Diesen vier Tabellen ist gemeinsam, dass sie ein Feld loc_id als Fremdschlüssel enthalten, über den sie einer Location aus geodb_locations zuzuordnen sind, sowie einen Fremdschlüssel [coord|float|int|text]_type, der mit einer Beschreibung in geodb_type_names verknüpft. Anhand dieses Schlüssels kann ermittelt werden, was der Tabelleneintrag beschreibt. So kennzeichnet ein Eintrag in geodb_textdata mit text_type 500300000 eine Postleitzahl, ein Eintrag in Tabelle geodb_intdata mit int_type 600700000 die Anzahl der Einwohner.
 +
 
 +
Art und Anzahl der verfügbaren Informationen in Textform ermittelt beispielsweise die folgende Abfrage (für die anderen Tabellen ist analog vorzugehen):
 +
 
 +
SELECT gtn.type_id, gtn.name, COUNT(*) AS amount
 +
FROM geodb_textdata AS gt
 +
LEFT JOIN geodb_type_names AS gtn ON gt.text_type = gtn.type_id
 +
GROUP BY gt.text_type
 +
 +
+-----------+------------------------------+--------+
 +
| type_id  | name                        | amount |
 +
+-----------+------------------------------+--------+
 +
| 400100000 | Teil von                    |  60352 |
 +
| 400200000 | Ebene                        |  60354 |
 +
| 400300000 | Typ                          |  14713 |
 +
| 500100000 | Name                        |  60356 |
 +
| 500100002 | Sortiername                  |  60356 |
 +
| 500300000 | Postleitzahl                |  60686 |
 +
| 500400000 | Telefonvorwahl              |  12434 |
 +
| 500500000 | KFZ-Kennzeichen              |  60246 |
 +
| 500600000 | Amtlicher Gemeindeschlüssel  |  60343 |
 +
| 500700000 | Verwaltungszusammenschluss  |  9961 |
 +
+-----------+------------------------------+--------+
 +
 
 +
 
 +
Weiterhin bieten diese Tabellen die Möglichkeit den einzelnen Datensätzen Gültigkeitszeiträume zuzuweisen (Felder valid_since und valid_until), die Genauigkeit dieser Daten wird mittels date_type_since und date_type_until angegeben, deren Bedeutung ebenfalls in geodb_type_names definiert wird.
 +
 
 +
Eine hierarchische Zuordnung der einzelnen Einträge untereinander bietet der text_type 400100000 ("Teil von") über den beispielsweise eine Kette Ortsteil->Stadt->Landkreis->Regierungsbezirk->Bundesland->Staat->Kontinent implementiert werden kann. Für diesen Zweck existierte früher die Tabelle geodb_hierarchies, die aber nicht alle Gebiete abdecken kann und somit nicht befüllt geliefert wird. Gegebenenfalls ist diese aus den Daten selbst zu füllen. Im Unterverzeichnis dump existieren im Moment experimentelle Daten für diese Tabelle. (Ahier.sql, Bhier.sql, CHhier.sql, LIhier.sql)
 +
 
 +
== Hintergrund ==
 +
 
 +
Hintergrund dieses Datenbank-Schemas ist die Notwendigkeit, flexibel Daten zu Locations erfassen zu können, ohne dass bei neu hinzukommenden Informationsarten die bestehende Struktur geändert werden muss. Möchte man beispielsweise zusätzlich zur Einwohnerzahl auch Geburts- und Sterberate erfassen, genügt es, in der Tabelle geodb_type_names zwei neue Einträge anzulegen:
 +
 
 +
INSERT INTO geodb_type_names (type_id, type_locale, name)
 +
VALUES (600700001, 'de', 'Geburtenrate'), (600700002, 'de', 'Sterberate');
 +
 
 +
Danach kann begonnen werden in der Tabelle geodb_intdata die entsprechenden
 +
Daten zu erfassen:
 +
 
 +
INSERT INTO geodb_intdata (loc_id, int_type, int_val)
 +
VALUES ([loc_id], 600700001, [Wert]);
 +
 
 +
Durch die Verwendung von locales bei der Definition der type_id in der Tabelle geodb_type_names ist auch die Möglichkeit der Lokalisierung gegeben.
 +
 
 +
 
 +
Dieses Datenbank-Schema wurde für die Datenhaltung der opengeodb entwickelt, als ersichtlich wurde, dass das vorherige Schema der Version 0.1.3ff die sich mehrfach ändernden Anforderungen nicht erfüllen konnte. Dabei wurde das Hauptaugenmerk auf Flexibilität und Universalität bei der Erfassung und Pflege der Daten gelegt, unabhängig davon, für welchen Einsatzzweck die Daten später verwendet werden sollen.  
 +
 
 +
Aufgrund der Vielzahl der Möglichkeiten, die vorhandenen Daten zu nutzen ist es nicht möglich bzw. sinnvoll, die Struktur an der Ziel-Anwendung auszurichten. Dies gehört ausdrücklich nicht zur Zielsetzung der OpenGeoDB. Vielmehr ist jeder Nutzer gehalten, sich die für ihn relevanten Daten aus der Datenbank zu extrahieren und in eine für seine Anwendung passende Struktur zu überführen, um Performance und Zuordnung entsprechend zu optimieren.
 +
 
 +
Die [[Beispiele]] im nächsten Abschnitt sollen dabei behilflich sein, indem sie mögliche Abfragen illustrieren. Der Punkt [[OpenGeoDB - Umkreissuche|Verwendung der Opengeodb für eine Umkreissuche]] beschreibt exemplarisch die Verwendung der Daten für die Erstellung einer Umkreissuche, dem wohl häufigsten Einsatzgebiet der Daten aus der OpenGeoDB.
 +
 
 +
== Grafische Darstellung ==
 +
 
 +
Hier finden Sie eine schematisierte Darstellung der Datenbank-Struktur der OpenGeoDB der Versionen 0.2
 +
 
 +
[[Image:Opengeodb-db-modell.png|800px]]
 +
 
 +
== Tabellen-Übersicht ==
  
==Die Tabellen==
 
 
Klicken Sie auf den Tabellennamen, um die Datenstruktur der Tabelle und weitere Informationen zu erhalten.
 
Klicken Sie auf den Tabellennamen, um die Datenstruktur der Tabelle und weitere Informationen zu erhalten.
 
;[[geodb_areas]]
 
;[[geodb_areas]]
:diese Tabelle ist bisher leer - geplant ist, hier Flächen zu speichern, also Länder- und Staatsgrenzen genauso, wie Grenzen von Städten oder Stadtteilen.
+
:diese Tabelle ist bisher leer - geplant ist, hier Flächen zu speichern, also Länder- und Staatsgrenzen genauso, wie Grenzen von Städten oder Stadtteilen. Die Punkte, aus denen die Flächen geometrisch gebildet werden, werden dazu in geodb_polygons gespeichert.
 
;[[geodb_changelog]]
 
;[[geodb_changelog]]
 
:hat mit der Datenbank an sich eigentlich nichts zu tun, enthält allerdings Kommentare zu größeren Änderungen an der Datenbank.
 
:hat mit der Datenbank an sich eigentlich nichts zu tun, enthält allerdings Kommentare zu größeren Änderungen an der Datenbank.
 
;[[geodb_coordinates]]
 
;[[geodb_coordinates]]
:Koordinaten-Sammlung mit Verknüpfung über die loc_id zu anderen Tabellen.  
+
:Koordinaten-Sammlung mit Verknüpfung über die loc_id zu anderen Tabellen.
 
;[[geodb_floatdata]]
 
;[[geodb_floatdata]]
 
:Fließkomma-Werte - bisher nicht genutzt
 
:Fließkomma-Werte - bisher nicht genutzt
 
;[[geodb_hierarchies]]
 
;[[geodb_hierarchies]]
:Hier werden den loc_ids Hierarchien zugeordnet. Das Feld "level" definiert für die loc_id eine entsprechende Hierarchie-Ebene, die übergeordneten Level werden in den weiteren Feldern (id_lv1 bis id_lv9) den entsprechenden übergeordneten Einträgen zugeordnet.  
+
:Hier werden den loc_ids Hierarchien zugeordnet. Das Feld "level" definiert für die loc_id eine entsprechende Hierarchie-Ebene, die übergeordneten Level werden in den weiteren Feldern (id_lv1 bis id_lv9) den entsprechenden übergeordneten Einträgen zugeordnet. Diese Tabelle ist im Standard-Dump momentan leer. Die Daten in dieser Tabelle können aus den restlichen Daten erzeugt werden. Ein Entsprechendes SQL-Script zur Erzeugung ist geplant.
 
;[[geodb_intdata]]
 
;[[geodb_intdata]]
:
+
:Hier finden sich Zahlenwerte (integer), die über loc_id zugeordnet werden, u.a. die Einwohnerzahl.
 
;[[geodb_locations]]
 
;[[geodb_locations]]
:Verknüpfung der loc_ids mit einem Typ (siehe auch [[geodb_type_names]])
+
:Verknüpfung der loc_ids mit einem Typ (siehe auch [[geodb_type_names]]). Hier werden also die eigentlichen Locations definiert, die durch alle weiteren Tabellen/Datensätze spezialisiert werden.
 
;[[geodb_polygons]]
 
;[[geodb_polygons]]
:Punkte für Polygone - um damit zum Beispiel Grenzen von Staaten, Bundesländern, Städten etc speichern zu können.
+
:Punkte für Polygone - um damit zum Beispiel Grenzen von Staaten, Bundesländern, Städten etc speichern zu können. Die Polygone selbst werden in geodb_areas definiert.
 
;[[geodb_textdata]]
 
;[[geodb_textdata]]
:Alle als Text gespeicherten Werte, die loc_ids zugeordnet werden können. verknüpft werden diese mit anderen Tabellen wieder über loc_id
+
:Alle als Text gespeicherten Werte, die loc_id zugeordnet werden können. Hier finden sich u.a. Ortsname, Amt, KfZ-Kennzeichen und Postleitzahl.
 
;[[geodb_type_names]]
 
;[[geodb_type_names]]
 
:Diese Tabelle definiert die Typen, die in der Datenbank vorkommen. Da Objekte - ob Stadt, Staat, Bundesland, Regierungsbezirk oder Ortsteil - im Grunde genommen auf die gleiche Art und Weise definiert und gespeichert werden, kriegt jeder Eintrag eine type_id als zusätzliche Information. Dies weist Berlin zum Beispiel im einen Datensatz als Stadt, im zweiten als Bundesland und im dritten als Landkreis aus.
 
:Diese Tabelle definiert die Typen, die in der Datenbank vorkommen. Da Objekte - ob Stadt, Staat, Bundesland, Regierungsbezirk oder Ortsteil - im Grunde genommen auf die gleiche Art und Weise definiert und gespeichert werden, kriegt jeder Eintrag eine type_id als zusätzliche Information. Dies weist Berlin zum Beispiel im einen Datensatz als Stadt, im zweiten als Bundesland und im dritten als Landkreis aus.
  
==Verknüpfungen und Indizes==
+
[[Kategorie:Datenbank]]

Aktuelle Version vom 28. März 2008, 22:09 Uhr

Allgemein

In der Datenbank OpenGeoDB können Informationen gespeichert werden, die irgendwie einem Ort zugeordnet werden können. Im folgenden wird dabei von einer Location gesprochen. Eine Location kann sowohl eine Ortschaft, ein Postleitzahlgebiet, ein See oder auch ein Kontinent sein.

Die zentrale Tabelle ist dabei die Tabelle geodb_locations. Sie enthält zwei Felder, die loc_id als Primärschlüssel, sowie den loc_type, der die Art des Datensatzes beschreibt. Die genaue Bedeutung von loc_type wird in der Tabelle geodb_type_names beschrieben. Folgende Abfrage liefert eine Übersicht über Typ und Anzahl der in geodb_locations enthaltenen Einträge, die für die loc_id in geodb_locations verwendet werden:

SELECT gtn.type_id, gtn.name, COUNT(*) AS amount
FROM geodb_locations AS gl
LEFT JOIN geodb_type_names AS gtn ON gl.loc_type = gtn.type_id
GROUP BY gl.loc_type

+-----------+-----------------------+--------+
| type_id   | name                  | amount |
+-----------+-----------------------+--------+
| 100200000 | Staat/Land            |      1 |
| 100300000 | Kanton                |     32 |
| 100400000 | Regierungsbezirk      |     32 |
| 100500000 | Landkreis             |    440 |
| 100600000 | Politische Gliederung |  12300 |
| 100700000 | Ortschaft             |   3568 |
| 100800000 | Postleitzahlgebiet    |  43988 |
| 100900000 | Ortsteil              |     11 |
+-----------+-----------------------+--------+

Die in den Tabellen geodb_coordinates, geodb_floatdata, geodb_intdata und geodb_textdata enthaltenen Datensätze liefern weitere Informationen zu den in geodb_locations eingetragenen Datensätzen in der dem Tabellennamen entsprechenden Form: geodb_textdata enthält Daten, die als String zu interpretieren sind, geodb_intdata enthält Integerwerte etc.

Diesen vier Tabellen ist gemeinsam, dass sie ein Feld loc_id als Fremdschlüssel enthalten, über den sie einer Location aus geodb_locations zuzuordnen sind, sowie einen Fremdschlüssel [coord|float|int|text]_type, der mit einer Beschreibung in geodb_type_names verknüpft. Anhand dieses Schlüssels kann ermittelt werden, was der Tabelleneintrag beschreibt. So kennzeichnet ein Eintrag in geodb_textdata mit text_type 500300000 eine Postleitzahl, ein Eintrag in Tabelle geodb_intdata mit int_type 600700000 die Anzahl der Einwohner.

Art und Anzahl der verfügbaren Informationen in Textform ermittelt beispielsweise die folgende Abfrage (für die anderen Tabellen ist analog vorzugehen):

SELECT gtn.type_id, gtn.name, COUNT(*) AS amount
FROM geodb_textdata AS gt
LEFT JOIN geodb_type_names AS gtn ON gt.text_type = gtn.type_id
GROUP BY gt.text_type

+-----------+------------------------------+--------+
| type_id   | name                         | amount |
+-----------+------------------------------+--------+
| 400100000 | Teil von                     |  60352 |
| 400200000 | Ebene                        |  60354 |
| 400300000 | Typ                          |  14713 |
| 500100000 | Name                         |  60356 |
| 500100002 | Sortiername                  |  60356 |
| 500300000 | Postleitzahl                 |  60686 |
| 500400000 | Telefonvorwahl               |  12434 |
| 500500000 | KFZ-Kennzeichen              |  60246 |
| 500600000 | Amtlicher Gemeindeschlüssel  |  60343 |
| 500700000 | Verwaltungszusammenschluss   |   9961 |
+-----------+------------------------------+--------+


Weiterhin bieten diese Tabellen die Möglichkeit den einzelnen Datensätzen Gültigkeitszeiträume zuzuweisen (Felder valid_since und valid_until), die Genauigkeit dieser Daten wird mittels date_type_since und date_type_until angegeben, deren Bedeutung ebenfalls in geodb_type_names definiert wird.

Eine hierarchische Zuordnung der einzelnen Einträge untereinander bietet der text_type 400100000 ("Teil von") über den beispielsweise eine Kette Ortsteil->Stadt->Landkreis->Regierungsbezirk->Bundesland->Staat->Kontinent implementiert werden kann. Für diesen Zweck existierte früher die Tabelle geodb_hierarchies, die aber nicht alle Gebiete abdecken kann und somit nicht befüllt geliefert wird. Gegebenenfalls ist diese aus den Daten selbst zu füllen. Im Unterverzeichnis dump existieren im Moment experimentelle Daten für diese Tabelle. (Ahier.sql, Bhier.sql, CHhier.sql, LIhier.sql)

Hintergrund

Hintergrund dieses Datenbank-Schemas ist die Notwendigkeit, flexibel Daten zu Locations erfassen zu können, ohne dass bei neu hinzukommenden Informationsarten die bestehende Struktur geändert werden muss. Möchte man beispielsweise zusätzlich zur Einwohnerzahl auch Geburts- und Sterberate erfassen, genügt es, in der Tabelle geodb_type_names zwei neue Einträge anzulegen:

INSERT INTO geodb_type_names (type_id, type_locale, name)
VALUES (600700001, 'de', 'Geburtenrate'), (600700002, 'de', 'Sterberate');

Danach kann begonnen werden in der Tabelle geodb_intdata die entsprechenden Daten zu erfassen:

INSERT INTO geodb_intdata (loc_id, int_type, int_val) 
VALUES ([loc_id], 600700001, [Wert]);

Durch die Verwendung von locales bei der Definition der type_id in der Tabelle geodb_type_names ist auch die Möglichkeit der Lokalisierung gegeben.


Dieses Datenbank-Schema wurde für die Datenhaltung der opengeodb entwickelt, als ersichtlich wurde, dass das vorherige Schema der Version 0.1.3ff die sich mehrfach ändernden Anforderungen nicht erfüllen konnte. Dabei wurde das Hauptaugenmerk auf Flexibilität und Universalität bei der Erfassung und Pflege der Daten gelegt, unabhängig davon, für welchen Einsatzzweck die Daten später verwendet werden sollen.

Aufgrund der Vielzahl der Möglichkeiten, die vorhandenen Daten zu nutzen ist es nicht möglich bzw. sinnvoll, die Struktur an der Ziel-Anwendung auszurichten. Dies gehört ausdrücklich nicht zur Zielsetzung der OpenGeoDB. Vielmehr ist jeder Nutzer gehalten, sich die für ihn relevanten Daten aus der Datenbank zu extrahieren und in eine für seine Anwendung passende Struktur zu überführen, um Performance und Zuordnung entsprechend zu optimieren.

Die Beispiele im nächsten Abschnitt sollen dabei behilflich sein, indem sie mögliche Abfragen illustrieren. Der Punkt Verwendung der Opengeodb für eine Umkreissuche beschreibt exemplarisch die Verwendung der Daten für die Erstellung einer Umkreissuche, dem wohl häufigsten Einsatzgebiet der Daten aus der OpenGeoDB.

Grafische Darstellung

Hier finden Sie eine schematisierte Darstellung der Datenbank-Struktur der OpenGeoDB der Versionen 0.2

Opengeodb-db-modell.png

Tabellen-Übersicht

Klicken Sie auf den Tabellennamen, um die Datenstruktur der Tabelle und weitere Informationen zu erhalten.

geodb_areas
diese Tabelle ist bisher leer - geplant ist, hier Flächen zu speichern, also Länder- und Staatsgrenzen genauso, wie Grenzen von Städten oder Stadtteilen. Die Punkte, aus denen die Flächen geometrisch gebildet werden, werden dazu in geodb_polygons gespeichert.
geodb_changelog
hat mit der Datenbank an sich eigentlich nichts zu tun, enthält allerdings Kommentare zu größeren Änderungen an der Datenbank.
geodb_coordinates
Koordinaten-Sammlung mit Verknüpfung über die loc_id zu anderen Tabellen.
geodb_floatdata
Fließkomma-Werte - bisher nicht genutzt
geodb_hierarchies
Hier werden den loc_ids Hierarchien zugeordnet. Das Feld "level" definiert für die loc_id eine entsprechende Hierarchie-Ebene, die übergeordneten Level werden in den weiteren Feldern (id_lv1 bis id_lv9) den entsprechenden übergeordneten Einträgen zugeordnet. Diese Tabelle ist im Standard-Dump momentan leer. Die Daten in dieser Tabelle können aus den restlichen Daten erzeugt werden. Ein Entsprechendes SQL-Script zur Erzeugung ist geplant.
geodb_intdata
Hier finden sich Zahlenwerte (integer), die über loc_id zugeordnet werden, u.a. die Einwohnerzahl.
geodb_locations
Verknüpfung der loc_ids mit einem Typ (siehe auch geodb_type_names). Hier werden also die eigentlichen Locations definiert, die durch alle weiteren Tabellen/Datensätze spezialisiert werden.
geodb_polygons
Punkte für Polygone - um damit zum Beispiel Grenzen von Staaten, Bundesländern, Städten etc speichern zu können. Die Polygone selbst werden in geodb_areas definiert.
geodb_textdata
Alle als Text gespeicherten Werte, die loc_id zugeordnet werden können. Hier finden sich u.a. Ortsname, Amt, KfZ-Kennzeichen und Postleitzahl.
geodb_type_names
Diese Tabelle definiert die Typen, die in der Datenbank vorkommen. Da Objekte - ob Stadt, Staat, Bundesland, Regierungsbezirk oder Ortsteil - im Grunde genommen auf die gleiche Art und Weise definiert und gespeichert werden, kriegt jeder Eintrag eine type_id als zusätzliche Information. Dies weist Berlin zum Beispiel im einen Datensatz als Stadt, im zweiten als Bundesland und im dritten als Landkreis aus.