SQL Server 2008 und GeoDaten bzw. Spatials

Heute musste ich mich mal ganz unerwartet mit den „neuen“ spatial Datentypen und Funktionen des SQL Servers auseinandersetzen, nachdem ein Kollege die Mitbewerberstandorte im Umkreis von 10 Kilometern einer bestimmten Filiale haben wollte.

Längen- und Breitengrad hatten wir schon länger in einzelnden decimal Spalten in unseren Tabellen
Das Ergebnis sah dann so aus:

SELECT
 geography::STGeomFromText( 'POINT(' + CONVERT(VARCHAR(100),fil.laengenGrad) + ' ' +  CONVERT(VARCHAR(100),fil.breitenGrad) +')',4326) AS FilGeo
 ,geography::STGeomFromText( 'POINT(' + CONVERT(VARCHAR(100),mfil.laengenGrad) + ' ' +  CONVERT(VARCHAR(100),mfil.breitenGrad) +')',4326) AS MitGeo
 ,((geography::STGeomFromText( 'POINT(' + CONVERT(VARCHAR(100),fil.laengenGrad) + ' ' +  CONVERT(VARCHAR(100),fil.breitenGrad) +')',4326)).STDistance(
 geography::STGeomFromText( 'POINT(' + CONVERT(VARCHAR(100),mfil.laengenGrad) + ' ' +  CONVERT(VARCHAR(100),mfil.breitenGrad) +')',4326))/1000) AS DistanzKilometer
 --,fil.ortsId, mfil.ortsid
 , fil.filialName, fil.filialNummer, filialName2, ort.postLeitZahl AS FilialPlz, ort.ortsName AS FilialOrt
 , mfir.mitbewerberFirmenBez, m.mitbewerberName, mfil.strasse, mort.postLeitZahl AS MitbewerberPlz, mort.ortsName AS MitbewerberOrt
 FROM Filialen AS fil
 INNER JOIN Orte AS ort ON ort.ortsId = fil.ortsId
 INNER JOIN MitbewerberFilialen AS mfil ON
 ((geography::STGeomFromText( 'POINT(' + CONVERT(VARCHAR(100),fil.laengenGrad) + ' ' +  CONVERT(VARCHAR(100),fil.breitenGrad) +')',4326)).STDistance(
 geography::STGeomFromText( 'POINT(' + CONVERT(VARCHAR(100),mfil.laengenGrad) + ' ' +  CONVERT(VARCHAR(100),mfil.breitenGrad) +')',4326))/1000)<10
 INNER JOIN MitbewerberFirmen AS mfir ON mfil.mitbewerberFirmenId = mfir.mitbewerberFirmenId
 INNER JOIN Mitbewerber AS m ON m.mitbewerberId = mfil.mitbewerberFirmenId
 INNER JOIN Orte AS mort ON mort.ortsId = mfil.ortsId
 WHERE fil.filialNummer=1173

Hier noch ein paar Erläuterungen und Links, die mich zur Lösung führten:

  • Denis‘ Post SQL Server 2008 Proximity Search With The Geography Data Type war ein guter Einstieg, um mal grundsätzlich herauszufinden, wie man aus zwei Decimal-Typen einen Geographie-Typ erzeugt
    -> geography::STGeomFromText
    und wie man im definierten Umkreis sucht
    -> .STDistance
  • Die Funktion STGeoFromText ist kurz hier in der MSDN erläutert.
  • Antwort auf die Frage, was der Ausdruck 4326 zu bedeuten hat, fand ich auf stackoverflow.
  • Mit der Abfrage
    select * from sys.spatial_reference_systems where spatial_reference_id=4326
    kriegt man dann auch die passende Info aus der Datenbank und erfährt auch gleich, in welcher Maßeinheit die Distanz geliefert wird (in diesem Fall sind es Meter).
  • Dieses Problem ist mir zum Glück erspart geblieben 😉
Advertisements

Schlagwörter: ,

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s


%d Bloggern gefällt das: