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

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

Diesen Post teilen

Repost 0

Kommentiere diesen Post