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

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.

Spatial_Result_BoundingBox.jpg

Diesen Post teilen

Repost 0
Um über die neuesten Artikel informiert zu werden, abonnieren:

Kommentiere diesen Post