SQL 2008 Geo-location – an easy beginning

I was just playing around with SQL Server 2008’s, converting an old waypoint table with latitude/longitudes as columns. It was a lot easier than I thought, though the syntax seems a bit strange.

Here is my sample where I convert the old values to a new geography column, query it and calculate the distance between two points (in metres). The really weird thing is the magic number 4326, mening WGS84 – but more about that on Wikipedia.

-- SRID: 4326 = WGS84

UPDATE WPGEO
SET GEO =
geography::STPointFromText(
    'POINT('
    + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

SELECT geo.STAsText() FROM wpgeo WHERE Id =2166
SELECT geo.STAsText() FROM wpgeo WHERE Id =2167

DECLARE @geog1 GEOGRAPHY;
DECLARE @geog2 GEOGRAPHY;
DECLARE @result FLOAT;

SELECT @geog1 = Geo FROM wpgeo WHERE id = 2166;
SELECT @geog2 = Geo FROM wpgeo WHERE id = 2167;
SELECT @result = @geog1.STDistance(@geog2);
SELECT @result

The results look like this:

POINT (11.0802 55.5276)

POINT (11.0343 55.5578)

4438,54421068124

This is – in my opinion – pretty cool stuff…

Advertisements
Posted in SQL