T-SQL Geografie-Unterricht Teil 5 – Daten konvertieren

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

Geo-Daten zu erfassen, wie ich es mit den Polygonen für 2 Bundesländer gemacht habe, ich mühselig, aufwendig und zeitintensiv; großen Spaß macht es auch nicht.

Da ist es bequemer, sich Daten liefern zu lassen. Aber diese Daten müssen dann in der Regel auch erst einmal in den Datentypen Geography konvertiert werden.

Ein häufiges Datenformat bei Geo-Daten ist XML, da fällt es im Microsoft SQL Server mit der XML Unterstützung recht einfach. Per OPENROWSET mit der BULK Option lädt man die Daten und fragt die Daten per XQuery ab, das Ergebnis konvertiert man dann.

Im zweiten Beispiel verwende ich das Ergebnis der CTE Query aus Teil 4, um die Bounding Box als Polygon zu erstellen.

Zum eigentlichen Konvertieren kann man das klassische CONVERT verwenden, das funktioniert wie bei jedem anderen Datentypen auch. Zudem gibt es noch die statische Methode geography::STGeomFromText(), um Werte in den Datentypen Geography zu konvertieren. Diese Methode bietet den Vorteil, dass man die SRID angeben kann, für den Fall, dass sie vom Standard abweicht.

Alles Weitere kann man dem folgenden Script entnehmen:

 -- In Teil 1 angelegte Datenbank verwenden

 USE [GeoTest];

GO

 -- Geo-Daten konvertieren

 -- Einfaches XML in Geography - Point wandeln.

 DECLARE @g AS XML;

 SET @g = '

 <params>

  <param lat="52.4728736" lng="9.7196064"

  vv="2009-01-06T08:02:50Z+01:00&lt;br&gt;Speed: 96.13 km/hr&lt;br&gt;

  Altitude: 52 m&lt;br&gt;Course: 175 deg.&lt;br&gt;"/>

  <param lat="52.4704672" lng="9.7201096"

  vv="2009-01-06T08:03:00Z+01:00&lt;br&gt;Speed: 97.95 km/hr&lt;br&gt;

  Altitude: 54 m&lt;br&gt;Course: 172 deg.&lt;br&gt;"/>

  <param lat="52.4692768" lng="9.7204344"

  vv="2009-01-06T08:03:05Z+01:00&lt;br&gt;Speed: 96.55 km/hr&lt;br&gt;

  Altitude: 52 m&lt;br&gt;Course: 171 deg.&lt;br&gt;"/>

 </params>';

 -- Konvertierung, Anzeige des Ergebnisses mit .ToString()

 SELECT SUB.*

   ,CONVERT(geography

  ,'POINT(' + SUB.Longitude + ' '

  + SUB.Latitude + ')').ToString() AS Pos1

  ,geography::STGeomFromText(

  'POINT(' + SUB.Longitude + ' '

  + SUB.Latitude + ')'

  ,4326).ToString() AS Pos2

 FROM (SELECT res.value('@lat', 'varchar(20)') as Latitude

  ,res.value('@lng', 'varchar(20)') as Longitude

  FROM @g.nodes('params/param') AS qry(res)

  ) AS SUB;

GO

 -- Ergebnis der Bounding Box Selektion

 -- in Polygon wandeln.

 -- Basis ist dabei die CTE aus Teil 5

 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 AreaName

  ,CONVERT(geography

  ,'POLYGON((' + MaxLong + ' ' + MaxLat + ', '

  + MinLong + ' ' + MaxLat + ', '

  + MinLong + ' ' + MinLat + ', '

  + MaxLong + ' ' + MinLat + ', '

  + MaxLong + ' ' + MaxLat

  + '))'

  ) AS BoundingBox

 FROM ( SELECT A.ID

  ,A.AreaName

  ,CONVERT(varchar

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

  ,CONVERT(varchar

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

  ,CONVERT(varchar

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

  ,CONVERT(varchar

  ,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

  ) AS SUB

 -- Zur Gegenüberstellung die Areas

 UNION ALL

 SELECT A.AreaName

  ,A.Area

 FROM Area AS A;

Über „Spatial Result“ kann man kontrollieren, ob die Bounding Box Berechnung stimmt; und es sieht passend aus.

Diesen Post teilen

Repost0

T-SQL Geografie-Unterricht Teil 4 – Berechnungen

Shape-Files als geografische SQL Daten importieren

Zurück zur Home

Kommentiere diesen Post

2016
- April 1
- März 2
2013
- August 1
- Juni 2
2012
- November 1
- Juni 1
- April 2
- März 2
- Januar 1
2011
- Dezember 1
- November 2
- Oktober 1
- August 4
- Juli 1
- Mai 1
- April 5
- März 4
- Februar 5
2010
- Mai 1
- April 11
- März 5
- Februar 2
- Januar 14
2009
- Dezember 3
- November 10
- Oktober 11
- September 9
- August 15
- Juli 11
- Juni 6
- Mai 8
- April 9
- März 12
- Februar 19
- Januar 26
2008
- Dezember 27