Posts Tagged ‘TSQL’

SQL Server 2008 und GeoDaten bzw. Spatials

23. November 2010

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 😉

LINQ to SQL Debug Visualizer

2. Juni 2009

Leider gibt’s im Visual Studio 2008 keine bereits eingebaute Möglichkeit, das durch LINQ to SQL generiert TSQL anzuschauen.
Wem der Umweg über den SQL Server Profiler zu aufwendig ist, der findet im LINQ to SQL Debug Visualizer vielleicht die Lösung.
Mehr Info gibt in Scott Guthrie’s Blog und dort gibt’s auch den Download.
Irgendwo im ZIP-File gibt’s dann auch die DLL, die man dann ins Verzeichnis c:\Programme\Microsoft Visual Studio 9.0\Common7\Packages\Debugger\Visualizers\ kopiert. Visual Studio neu starten und fertig ist die Laube.