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)
This is – in my opinion – pretty cool stuff…