Overblog
Folge diesem Blog Administration + Create my blog
Blog von Olaf Helper

geo - gis

Shape-Files als geografische SQL Daten importieren

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

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.

Weiterlesen

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

Weiterlesen

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 
Weiterlesen

T-SQL Geografie-Unterricht Teil 3 – Polygon

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

Kommen wir zur nächsten geografischen Form im Microsoft SQL Server Spatial Data (hier wieder Geography), den Polygonen. Auch hier gibt es ein paar einfache Punkte zu beachten.

 

-       Ein Polygon darf nicht größer als eine Hemisphäre sein, wobei es nicht auf eine bekannte (östlich / westliche oder nördliche / südliche) Hemisphäre beschränkte .Oder vereinfacht gesagt: Betrachtet man einen Globus aus einiger Entfernung, muss die definierte Fläche vollständig im sichtbaren Bereich sein. Das ist in erster Linie eine technische Einschränkung, praktisch dürfte es kein Problem sein; selbst der gesamte amerikanische Kontinent passt da rein.

-       Ein Polygon muss in sich geschlossen sein; das ist einfach, der Endpunkt muss mit dem Startpunkt über identisch sein.

-       Besteht ein Polygon aus mehreren separaten Flächen, dürfen die sich nicht überlappen, auch nicht teilweise.

-       Bei der Reihenfolge der einzelnen Eckpunkten muss man die „Links-Hand Regel“ (Left-Hand Rule) beachten, d.h. geht man die Punkte (in Gedanken) nacheinander ab, befindet sich die definierte Fläche zur Linken Seite hin. Die Punkte müssen also entgegen dem Uhrzeigersinn nach definiert werden.

Das muss man also im Hinterkopf behalten.

 

Bei dem ersten Beispiel mit den Punkten aus Teil 1 habe ich Städte mit deren realen Koordinaten verwendet, da bietet sich für dies hier Flächen wie Bundesländer o.ä als Beispiele an. Leider habe ich bisher keine frei verfügbare Koordinaten-Listen / Shape-Files oder vergleichbare Daten gefunden.

Deswegen habe ich mal selbst ein paar Eckpunkte heraus gesucht; aber eben nur ein paar groblegende, es soll ja nur ein Beispiel werden.

Ich habe hier Niedersachen genommen, denn es bietet gleich noch einen interessanten Aspekt: Bremen als „Insel“ innerhalb der Fläche. Wie man unten im T-SQL Script sehen kann, wird diese Insel durch eine weitere Fläche innerhalb des Polygons festgelegt. Die Punkte sind hier im Uhrzeiger-Sinn angeordnet, weil sich die eigentliche Fläche außerhalb befindet; so schneidet man die Fläche aus der anderen raus.

 

 -- In Teil 1 angelegte Datenbank verwenden 
 USE [GeoTest]; 
 GO 
   
 -- Tabelle für Bereiche anlegen 
 CREATE TABLE Area 
  (ID int IDENTITY(1, 1) NOT NULL Primary Key, 
  AreaName nvarchar(50), 
  Area geography); 
 GO 
   
 -- Niedersachen; na ja, so irgendwie 
 -- Wichtig bei Geometry-Polygon: Die Links-Hand Regel beachten. 
 -- Die zweite Fläche in dem Polygon stellt Bremen dar, 
 -- das aus Niedersachsen ausgeschnitten wird, weil es innen liegt. 
 INSERT INTO Area 
 VALUES (N'Niedersachen' 
  ,geography::STGeomFromText( 
  'POLYGON(( 10.7080 51.6387  
  , 10.5596 51.9899 
  , 10.9734 52.0562 
  , 10.7655 52.8280 
  , 11.6011 53.0335 
  , 10.3189 53.4344 
  , 9.8703 53.4308 
  , 9.7805 53.5456 
  , 9.2144 53.8694 
  , 8.6661 53.8665 
  , 8.0329 53.7027 
  , 7.0975 53.5748 
   , 7.0556 52.6282 
  , 6.7154 52.6183 
  , 6.7284 52.4718 
  , 7.0424 52.2062 
  , 7.6176 52.4459 
  , 8.0267 52.1244 
  , 8.5208 52.1644 
   , 8.3500 52.4635 
  , 9.1354 52.4687 
  , 9.7164 51.2969 
  , 10.7080 51.6387 
  )'  
  -- Ab hier der Ausschnitt "Bremen"  
  + ', 
  ( 8.5009 53.2254 
  , 8.9835 53.1127 
   , 8.9287 52.9992 
  , 8.7099 53.0355 
  , 8.6237 53.1599 
  , 8.5009 53.2254 
  ) 
  )' 
  ,4326) 
  ); 
   
 -- Bremen; genau wie die Lücke in Niedersachen 
 -- nur die Punkte in umgedrehter Reihenfolge, 
 -- wegen der Links-Hand Regel!  
 INSERT INTO Area 
 VALUES (N'Bremen' 
  ,geography::STGeomFromText( 
  'POLYGON(( 8.5009 53.2254 
  , 8.6237 53.1599 
  , 8.7099 53.0355 
  , 8.9287 52.9992 
  , 8.9835 53.1127 
  , 8.5009 53.2254 
  ) 
  )' 
  ,4326) 
  ); 
 GO 
   
 -- Zur Anzeige selektieren: 
 SELECT * 
 FROM Area; 
 GO 

 

Jetzt wird auch mal wirklich was im „Spatial results“ angezeigt, farbig ausgefüllte Flächen. Wenn man als „Projection“ dann „Mercator“, eine Landkarte daneben hält, ein Auge zukneift und nicht ganz so scharf hinsieht, kann man fast erkennen, dass es sich um den Umriss von Niedersachen handelt. J

Wenn man dann noch bei „Label column“ das „AreaName“ angibt, wird auch der Name des Landes angezeigt; schon fast eine kleine Geo-Visualisierung.

 

Spatial_Result_Polygon.jpg

Weiterlesen

T-SQL Geografie-Unterricht Teil 2 – Linie

25. März 2010 , Geschrieben von Olaf Helper Veröffentlicht in #Geo - GIS

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.

 
Spatial_Result_Line.jpg

Weiterlesen

T-SQL Geografie-Unterricht Teil 1 - Punkte

24. März 2010 , Geschrieben von Olaf Helper Veröffentlicht in #Geo - GIS

Mit dem Microsoft Sql Server 2008 wurden die neue Geo-Spatial Datentypen Geometry und Geography eingeführt.

Das ist nichts neues, aber wie es (bei mir) halt immer so ist, man beschäftigt sich damit erst, wenn man es brauchen könnte. Auf dem TechNet Seminar: Management und Analyse von Geodaten mit SQL Server 2008, das gestern bei Microsoft in Bad Homburg stattfand, wurde auf wirklich sehr interesssante Weise demonstriert, wie man Geodaten mit dem MS Sql Server nutzen und mit z.B. Bing Maps individuell visualisieren kann. Hinzu kommt, das mit MS SQL Server 2008 R2 und dem Report Builder 3.0 auch Kartendarstellungen unterstützt werden; Zeit also sich mal mit den Geodaten zu beschäftigen.

Als erster, einfacher Start wollte ich mit einfachen Punkten (Point) und den Funktionen dazu anfangen. Für die Testdaten hierzu habe ich mir bei Wikipedia für ein paar Großstädte die Koordinaten heraus gesucht, die dann per T-SQL in eine Tabelle eingefügt werden.

Hier gibt es schon den ersten kleinen Stolperstein und das ist die Reihenfolge der Koordinatendaten. Von allen GIS, Navigationssystemen, GPS Empfänger und Karten kennt man es her, das erst den Breitengrad (Latitude, Y-Koordinate) und dann den Längengrad (Longitude, X-Koordinate) angibt; im geografischen Bereich ist das auch so üblich.

Beim Datentyp Geography sind die Vorgaben der OGC (Open Geospatial Consortium, Inc) umgesetzt worden und das folgt den mathematischen Gebräuchen; erst die X-Koordinate = Länge und dann die Y = Breite.

Wenn man also Geodaten aus diesen Quellen verwendet, muss man also die Werte umdrehen, um korrekte Ergebnisse zu erhalten.

 

 -- Neue Datenbank für Test (Daten) anlegen 
 CREATE DATABASE [GeoTest]; 
 GO 
 -- Und verwenden 
 USE [GeoTest]; 
 GO 
   
 -- Geography Unterricht Teil 1 
 -- Punkte von ein paar Großstädten anlegen 
 CREATE TABLE Locations 
   (ID int IDENTITY(1, 1) NOT NULL Primary Key, 
   Ctry char(2), 
   City varchar(40), 
   Coordinate geography); 
   
 INSERT INTO Locations VALUES 
   ('DE', 'Berlin',  'POINT(13.4000 51.5166)') 
   ,('DE', 'Hamburg',  'POINT(10.0000 53.5500)') 
   ,('DE', 'Köln',  'POINT( 6.9572 50.9413)') 
   ,('DE', 'Hannover',  'POINT( 9.7361 52.3744)') 
   ,('DE', 'Frankfurt (M)', 'POINT( 8.6859 50.1118)') 
   ,('DE', 'Bonn',  'POINT( 7.0998 50.7339)') 
   ,('DE', 'Düsseldorf',  'POINT( 6.7827 51.2255)') 
   ,('DE', 'München',  'POINT(11.5744 48.1397)') 
   ,('DE', 'Nürnberg',  'POINT(11.0777 49.4527)') 
   ,('DE', 'Frankfurt (O)', 'POINT(14.5500 52.3500)') 
   ,('DE', 'Dortmund',  'POINT( 7.4652 51.5138)') 
   ,('DE', 'Regensburg',  'POINT(12.0833 49.0166)') 
   ,('DE', 'Dresden',  'POINT(13.7383 51.0492)') 
   ,('DE', 'Leipzig',  'POINT(12.3747 51.3403)') 
   ,('DE', 'Halle (Saale)', 'POINT(11.9700 51.4827)') 
   ,('DE', 'Magdeburg',  'POINT(11.6166 52.1333)') 
   ,('DE', 'Cottbus',  'POINT(14.3341 51.7605)') 
   ,('DE', 'Flensburg',  'POINT( 9.4366 54.7819)') 
   ,('GB', 'London',  'POINT(-0.1183 51.5094)') 
   ,('GB', 'Greenwich',  'POINT(-0.0080 51.4812)') 
   
 GO 
 -- Heimat-Lokation festlegen; hier über die statische Methode 
 -- für WKT = Well Known Text. 
 DECLARE @Langenhagen geography; 
 SET @Langenhagen = geography::STGeomFromText ('POINT( 9.7400 52.4394)', 4326) 
   
 -- Wie weit ist es von mir aus? 
 SELECT Ctry, City 
   ,Coordinate 
   ,Coordinate.ToString()  -- Klartext über ToString() 
   ,Coordinate.STDistance(@Langenhagen) / 1000 AS [Abstand KM] 
 FROM Locations 
 ORDER BY Coordinate.STDistance(@Langenhagen); 
 GO 
   
 -- Abstand zwischen den Städten in km 
 SELECT LOC1.City AS Von 
   ,LOC1.Coordinate.STAsText() -- Klartext über STAsText() 
   ,LOC2.City AS Nach 
   ,LOC2.Coordinate.STAsText() 
   ,LOC1.Coordinate.STDistance(LOC2.Coordinate) / 1000 AS [Abstand KM] 
 FROM Locations AS LOC1 
   INNER JOIN Locations AS LOC2 
   ON LOC1.ID <> LOC2.ID 

 

Ergebnis für die einfache Entfernungs-Ermittlung:

 

Ctry

City

()

Abstand KM

DE

Hannover

POINT (9.7361 52.3744)

7,23774753606767

DE

Hamburg

POINT (10 53.55)

124,821044324537

DE

Magdeburg

POINT (11.6166 52.1333)

132,505176012853

DE

Halle (Saale)

POINT (11.97 51.4827)

186,607932446477

DE

Dortmund

POINT (7.4652 51.5138)

187,174792157204

DE

Leipzig

POINT (12.3747 51.3403)

218,741754386579

DE

Düsseldorf

POINT (6.7827 51.2255)

244,511317946084

DE

Köln

POINT (6.9572 50.9413)

254,550957265859

DE

Flensburg

POINT (9.4366 54.7819)

261,486320493581

DE

Bonn

POINT (7.0998 50.7339)

263,577773933599

DE

Frankfurt (M)

POINT (8.6859 50.1118)

269,189893714565

DE

Berlin

POINT (13.4 51.5166)

271,598467554947

DE

Dresden

POINT (13.7383 51.0492)

316,45980986038

DE

Cottbus

POINT (14.3341 51.7605)

323,685136489577

DE

Frankfurt (O)

POINT (14.55 52.35)

327,518614106162

DE

Nürnberg

POINT (11.0777 49.4527)

345,289654773764

DE

Regensburg

POINT (12.0833 49.0166)

415,100145911616

DE

München

POINT (11.5744 48.1397)

495,77502274763

GB

Greenwich

POINT (-0.008 51.4812)

677,953206853436

GB

London

POINT (-0.1183 51.5094)

684,735673692768

 

Auch interessant:

Lässt man im SSMS bei der Selektion auch eine Spalte vom Typ Spatial mit ausgeben, bekommt man im Result einen weiteren Kartenreiter angezeigt, „Spatial result“, der die Geodaten vereinfacht visualisiert. In dem Beispiel hier mit den Punkten eher witzlos; es sieht mehr wie Staubflecke auf dem Display aus. Optisch interessanter wird es dann bei geometrischen Objekten wie Polygone; das sehen wir dann später mal.
Spatial_Result.jpg

Weiterlesen