Overblog Folge diesem Blog
Edit post Administration Create my blog
Blog von Olaf Helper

T-SQL Geografie-Unterricht Teil 4 – Berechnungen

12. April 2010 , Geschrieben von Olaf Helper Veröffentlicht in #Geo - GIS

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 

Diesen Post teilen

Repost 0

Kommentiere diesen Post