SQL Server 2008 (6): Importieren von geografischen Daten

Bei vielen Datenbankfeatures, mit denen man sich beschäftigen möchte gibt es ein kleines Problem: Man braucht Daten. Wenn dieses neue Datenbankfeature nun aber die neuen geografischen Datentypen und Funktionen sind ist das kein kleines, sondern ein großes Problem.

Zuerst habe ich nach einem Hinweis von Bob Beauchemin die Datenbank Mondial gefunden. Diese Datenbank wird von der Uni Göttingen gepflegt und wurde von Bob für SQL Server 2008 aufbereitet. Download über Bob's Blogeintrag.

Mondial hat aber einige Nachteile. Zum einen enthält sie nicht allzu viele geografische Objekte, zum anderen haben diese häufig sehr ungenaue Koordinaten. Zum dritten und vor allem enthält Mondial aber nur Punkte, keine Gebiete oder Linienzüge. Daraufhin habe ich etwas gesucht und im SourceForge Projekt Mapbender eien Datei mit den Umrissen aller Postleitzahlen in Deutschland gefunden.

Die Aufgabe ist also, diese Tabelle (post_code_areas) nach Mondial zu importieren. Das Schema war schnell angepasst:

CREATE TABLE post_code_areas (
gid int NOT NULL,
plz99 char(5),
plz99_n int,
plzort99 varchar(50),
the_geom geography
)

Aber jetzt kommen die Probleme: Mapbender verwendet PostgreSQL, dessen GIS-Daten dem Standard OGC 99-049 entsprechen. SQL Server implementiert den neueren Standard OGC 05-126 mit der June 2005/06-135r1 Best Practice. Um den Unterschied zu verstehen muss man sich ansehen, wie die Textrepräsentation (WKT des Open Geospatial Consortium) von geografischen Daten aussieht:

POINT(50 10) beschreibt einen Punkt an den Koordinaten 50, 10

POLYGON((50 10,50 10.1,50.1 10.1,50.1 10,50 10)) beschreibt ein Rechteck mit der Kantenlänge 1/10 Grad. Dabei ist zu beachten, dass der Polygonzug geschlossen sein muss, also der letzte gleich dem ersten Punkt ist.

Analog gibt es LINESTRING sowie die Collections MULTIPOINT, MULTILINESTRING und MULTIPOLYGON.

Soweit sind sich alle in der Implementierung einig, ebenso, dass man ein Koordinatensystem, einen sogenannten Spatial Reference Identifier (SRID) braucht, der die Projektion und die Größenangaben beschreibt, mit der von "runder Erde" auf "flache Erde" und umgekenrt umgerechnet wird. Hier wird meist die SRID 4326 verwendet, der WGS84-Standard, der insbesondere von GPS verwendet wird.

Aber nun kommen die Unterschiede: Reihenfolge der Koordinaten in einem Punkt und Drehrichrichtung ("ring orientation") der Polygone.

Die Reihenfolge der Koordinaten ist die Frage: was kommt zuerst? Längen- oder Breitengrad? Und da unterscheiden sich die Daten: PostgreSQL (und Oracle) haben die Reihenfolge "Längengrad Breitengrad", während neuere Standards wie der von SQL Server CTP5 verwendete (aber auch andere wie GML) "Breitengrad Längengrad" verwenden.

Und natürlich ist auch die Ring Orientation unterschiedlich: SQL Server möchte sie entgegen dem Uhrzeigersinn. Was ist eigentlich eine Ring Orientation? Nun, auf einer (nahezu) kugelförmigen Erde beschreibt ein geschlossener Linienzug zwei Flächen. Nehmen wir als Extremfall einen Linienzug, der rund um den Äquator geht. Welche Fläche ist gemeint, die Nord- oder die Südhalbkugel? Um das zu definieren wird im von SQL Server implementierten Standard festgelegt, dass bei einem Polygon die Fläche gemeint ist, die von den Punkten entgegen dem Uhrzeigersinn eingeschlossen wird (wie in meinem Polygonbeispiel oben). Und natürlich ist auch das bei den Mapbender-Daten genau andersherum.

Um diese beiden Punkte hat sich eine umfassende Diskussion entsponnen, wie bei zwei sich widersprechenden Standards (die das beide auch nur implizit festlegen) nicht anders zu erwarten war. Diese Diskussion findet man hier. Und das SQL Server Team hat das Problem auch erkannt und wird wahrscheinlich bis zur Release von SQL Server 2008 auch Funktionen anbieten, die das "andere" Format lesen und schreiben können. Mehr dazu hier.

Aber alles das half mir jetzt nicht, ich brauchte die Daten mit vertauschten Längen- und Breitengraden und umgekehrter Ring Orientation. Also habe ich Visual Studio angeworfen und in C# ein Konvertierungsprogramm geschrieben. Das ist erst mal nur für die plz.sql-Datei aus dem Download gedacht (die insbesondere nur MULTIPOLYGONe mit eingebetteter SRID verwendet), mag aber auch mit kleinen Anpassungen für andere PostgreSQL-Dumps funktionieren.

Dann waren nur noch ein paar Indizes zu erstellen, insbesondere ein Spatial Index um die geografischen Abfragen zu beschleunigen, und die Daten können verwendet werden.

Nun können die geografischen Daten abgefragt werden. Zum Beispiel: Welche 10 Postleitzahlen haben die größte Fläche?

select TOP 10 post_code_areas.plz99, post_code_areas.plzort99,
CONVERT(numeric(10,2), the_geom.STArea()/1000000)
from post_code_areas
order by the_geom.STArea() DESC

Oder: Welche Postleitzahlen grenzen an (haben also eine Schnittmenge mit) meiner Heimat-PLZ 13089:

DECLARE @heinersdorf geography

select @heinersdorf = the_geom from post_code_areas where plz99 = '13089'

select post_code_areas.plz99, post_code_areas.plzort99, CONVERT( numeric(10,2),the_geom.STIntersection(@heinersdorf).STArea()) Ueberschneidung
from post_code_areas
where the_geom.STIntersects(@heinersdorf) = 1

Und schließlich: Welche Postleitzahlen haben eigentlich die deutschen Berge? Diese Abfrage setzt korrigierte Koordinaten für die Berge in Mondial voraus.

select mountain.Name, mountain.Height, post_code_areas.plz99, post_code_areas.plzort99
from mountain, post_code_areas
where
post_code_areas.the_geom.STIntersects(mountain.geog) = 1

Anbei die beiden Projekte: SQL (Datenbankschema, Indizes und Testabfragen - setzt Mondial voraus) und C# (Konvertierung der PLZ-Daten)

Einen Überblick zu geometrischen und geografischen Daten gibt es in meiner SQL Server 2008 kurz und knapp Webcastreihe am 21.1.

Viel Spaß mit Geodaten wünscht
Steffen