Olaf Helper

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<br>Speed: 96.13 km/hr<br>

         Altitude: 52 m<br>Course: 175 deg.<br>"/>

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

        vv="2009-01-06T08:03:00Z+01:00<br>Speed: 97.95 km/hr<br>

            Altitude: 54 m<br>Course: 172 deg.<br>"/>

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

        vv="2009-01-06T08:03:05Z+01:00<br>Speed: 96.55 km/hr<br>

            Altitude: 52 m<br>Course: 171 deg.<br>"/>

</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.