Olaf Helper

Abgrenzung manuell als Polygone zu definieren, wie ich es in Teil 3 - Polygone gemacht habe, ist in der Praxis zu aufwendig. Nur wie kommt an zumindest an Standard-Daten, wie Ländergrenze und ähnliches?

Die einfachste Lösung ist zugleich die teuerste. Es gibt diverse kommerzielle Anbieter von geografischen Daten, bei denen man umfangreiches Kartenmaterial in den verschiedensten Formaten beziehen kann.

Kostenlose Alternativen gibt es auch, die muss man aber erst einmal suchen & finden und sie sinnvoll zusammen zu tragen, das ist wiederum ein nicht gerade geringer manueller Aufwand.

Im Bereich GIS (Geographic Information Systems) hat sich mittlerweile das Shape-File (.shp) Format der Firma ESRI als de facto Standard durch gesetzt.

Im Internet findet man einige frei zugängliche Shape-Files unterschiedlichen Inhaltes. Für den amerikanischen Raum gibt es die sehr bekannten TIGER Lines (Topologically Integrated Geographic Encoding and Referencing system) von Cenus. In USA ist man auf dem Standpunkt, das Daten, die mit Steuergelder erhoben wurden, auch dem Steuerzahler frei zur Verfügung gestellt werden (sofern nicht geheim). Deshalb gibt es auch reichlich Shape-Files für die USA. Bei „uns“ ist man nicht dieser Meinung, wir bekommen nichts für lau. Entsprechend gibt es den deutschen (und auch europäischen) Raum nur wenige Shapes. Aber es gibt sie, man muss eben suchen.

Eine gute Quelle ist z.B. DIVA-GIS, hier gibt es z.B. die „administrative Lines“ von Deutschland. Im Download-File „DEU_adm.zip“ sind 4 Shape-Files, mit der Landesgrenze (Admin00.shp), den Bundesländern (Admin01.shp), den Kommunen und den Gemeinden.

Zum Import dieser Shape-Files gibt es wiederum das kleine, feine Tool namens Shape2Sql von SharpGIS, den Download Link findet etwas versteckt hier, unten auf der Seite. Es ist eine .NET Applikation mit WPF, installieren muss man nicht, wenn .NET 3.5 vorhanden ist, läuft es auch. Das Tool ist sehr simple, man gibt eine Connection zu einen Microsoft SQL Server mit Version >= 2008 an, wählt das Shape-File aus, kann dann noch Tabellennamen und die aus dem Shp zu übernehmen Felder festlegen und schon kann man die Daten importieren lasse; optional kann man auch gleich einen Geo-Index anlegen lassen.

Wie man Geography Daten wie Punkte, Linie und Polygone definiert, haben wir nun schon kennen gelernt, ebenso wie man sie in der Datenbank speichert.

Jetzt haben wir Geo-Daten (nun, ja, eine Hand voll), was können wir damit mit T-SQL anfangen?

Einfache Berechnungen wie Flächengröße (STArea()) oder Umfang (STLength()) gehören zu den „OGC Static Geography Methods“ und sind im Standard des Microsoft Sql Server Spatial Data Engine bereits implementiert.

Bei diesen Funktionen ist nicht viel dazu zu sagen, deshalb lass ich einfach mal das T-SQL Script „für sich sprechen“.

Zu zwei Funktionen sei aber etwas erwähnt:

Das erste ist die Ermittlung, ob sich ein Punkt innerhalb einer Fläche befindet, hier: Welche Stadt liegt in welchem Bundesland. In der Praxis kommt das z.B. bei Versicherungen bei der Risikobewertung vor, ob sich ein Gebäude in einem Überflutungsgebietes eines Flusses befindet. Diese Funktionalität wird wohl am häufigsten verwendet.

Das zweite ist die Berechnung der „Bounding Box“. Eine Bounding Box ist einfach das Rechteck, horizontal & vertikal ausgerichtet, dass ein Polygon vollständig umschließt. Es wird in Navigation- und GIS Systemen verwendet um zu ermitteln, ob sich ein Objekt innerhalb es gerade betrachteten Bereiches befindet. Statt also die tausende/millionen einzelne Punkte aller Polygon zu prüfen, wird in der ersten Stufe nur die „Bounding Box“ geprüft, ob sich Teile im sichtbarem Bereich befindet; erst dann wird das Objekt mit deren Einzelpunkte weiter geprüft.

Eigentlich ist die Definition recht einfach, es sind die Minimum / Maximum Werte der Längen- und Breitengrade. Da aber das Objekt als ein Polygon vorliegt, muss man dazu alle Punkte einzeln ermitteln und die Werte dazu aggregieren. Dazu verwende ich hier eine CTE, um eine Liste der Nummer der Punkte von 1 bis zur Anzahl der Punkte = STNumPoints() zu generieren. Diese werden wiederum in der Folge verwendet, um die einzelnen Punkte über anzusprechen und die Werte Long und Lat zu ermitteln. Also im Grunde nichts anderes wie bei Datenreihen erstellen.

Hier nun das T-SQL Script für die Berechnungen.

-- In Teil 1 angelegte Datenbank verwenden

USE [GeoTest];

GO

-- Wieviele Dimensionen hat das Geo-Datum?

-- 0 = Punkt, 1 = Line, 2 = Polygon

SELECT SUB.name

      ,SUB.geo.STDimension()

FROM (SELECT A.AreaName AS name

            ,A.Area AS geo

      FROM dbo.Area AS A

      UNION ALL

      SELECT 'Line ' + CONVERT(varchar, L.ID)

            ,L.Line

      FROM dbo.Lines AS L

      UNION ALL

      SELECT L.City

            ,L.Coordinate

      FROM dbo.Locations AS L

      ) AS SUB

-- Wie groß sind die Bundesländer; Fläche und Umfang?

SELECT A.AreaName

      ,A.Area.STArea() / 1000000 AS FlaecheKM2

      ,A.Area.STLength() / 1000  AS UmfangKM

FROM dbo.Area AS A

-- Welche Stadt liegt in dem Bundesland?

SELECT A.AreaName

      ,L.City

FROM dbo.Area AS A

     INNER JOIN dbo.Locations AS L

         ON A.Area.STIntersects(L.Coordinate) = 1

-- Mit wievielen Punkten und Teilflächen

-- ist das Polygon definiert?

-- Niedersachsen ist nur eine Fläche mit Ausschnitt!

SELECT A.Area.STNumPoints() AS AnzPunkte

      ,A.Area.STNumGeometries() AS AnzFlaechen

FROM dbo.Area AS A

-- "Bounding Box" der Flächen ermitteln.

-- dazu Min+Max der einzelnen Punkte aggregieren.

;WITH areaCte (areaId, PointNum)

 AS (SELECT [ID] AS areaId, 1 as PointNum

     FROM dbo.Area AS A

     UNION ALL

     SELECT [ID] AS areaId, PointNum + 1

     FROM dbo.Area AS A

          INNER JOIN areaCte AS C

              ON A.ID = C.areaId

     WHERE PointNum < A.Area.STNumPoints())

SELECT A.ID

      ,A.AreaName

      ,MIN(A.Area.STPointN(C.PointNum).Long) AS MinLong

      ,MIN(A.Area.STPointN(C.PointNum).Lat)  AS MinLat

      ,MAX(A.Area.STPointN(C.PointNum).Long) AS MaxLong

      ,MAX(A.Area.STPointN(C.PointNum).Lat)  AS MaxLat

FROM areaCte AS C

     INNER JOIN

     dbo.Area AS A

         ON C.areaId = A.ID

GROUP BY A.ID

        ,A.AreaName

Nach den Punkten kommen nun die Linien für den Datentypen Geography des Microsoft SQL Server 2008 dran. Das ist nichts weiter kompliziertes, es sind lediglich Auflistungen von Punkten, um eine Linie zu erhalten; da muss man auch nichts weiter beachten, jedenfalls sind mir hier keine Stolperfallen bekannt.

Es gibt dabei aber auch eine interessante Funktion Reduce(), um die Anzahl der Punkte abhängig von der Entfernung untereinander zu verringern.

Einfaches wie praktisches Beispiel: Man hat einen GPS Tracker und zeichnet damit ein Weg (z.B. zur Arbeit oder zum Kunden) auf, das ist eine Anreihung von einzelnen Punkten zu einem bestimmten Zeitpunkt, was wiederum eine Linie ergibt; die Route eben.

Werden die Punkte zeitgesteuert gespeichert, bekommt man bei Ampelstopps oder anderen Pause viele Messpunkte für einen einzelnen Standort; das sind redundante Daten, die unnötig auf die Performance von der Auswertung her bis hin zur Anzeige gehen.

Über die Reduce(tolerance) Funktion kann man nun diese redundante Punkte entfernen lassen, über den Parameter tolerance gibt man dabei die Mindest-Entfernung in Metern an.

Beispiel:

-- In Teil 1 angelegte Datenbank verwenden

USE [GeoTest];

GO

-- Tabelle für einfache Linie

CREATE TABLE Lines

   (ID int IDENTITY(1, 1) NOT NULL Primary Key,

    Line geography);

GO

-- Eine einfache, horizontal & halbwegs gerade Linie

INSERT INTO Lines (Line)

VALUES (geography::STGeomFromText(

        'LINESTRING( 9.73610  52.374401

                   , 9.73611  52.374400

                   , 9.73612  52.374401

                   , 9.73613  52.374400

                   , 9.73614  52.374401)', 4326));

-- Die gleiche Linie parallel dazu, nur etwas "krösselig"

-- und mit weiteren kurzen Zwischenpunkten

INSERT INTO Lines (Line)

VALUES (geography::STGeomFromText(

        'LINESTRING( 9.73610  52.374402

                   , 9.73611  52.374402

                   , 9.73612  52.374402

                   , 9.736121 52.374403

                   , 9.736122 52.374402

                   , 9.736123 52.374403

                   , 9.736124 52.374402

                   , 9.736125 52.374403

                   , 9.736126 52.374403

                   , 9.73613  52.374402

                   , 9.73614  52.374402)', 4326));

-- Selektion der Linie, zusätzlich mit Reduktion der

-- Punkte, die näher als 80 Meter sind.

SELECT Line                          -- Zur Anzeige in Spatial Result

      ,Line.Reduce(0.08) AS LineRed  -- Zur Anzeige in Spatial Result

      ,Line.ToString()

      ,Line.STNumPoints() AS AnzPunkte

      ,Line.Reduce(0.08).ToString()

      ,Line.Reduce(0.08).STNumPoints() AS AnzReduz

FROM Lines;

Jetzt wird die zusätzliche Anzeige von „Spatial result“ auch etwas interessanter; mit Betonung auf etwas.

Leider gibt es (meines Wissens) keine Möglichkeit Einfluss auf die Darstellung / Farben zu nehmen, die dünnen Linien kann man bestenfalls so gerade eben noch erkennen.