SQL Server 2008 GEOGRAPHY STDistance() value

Question

I am using geography.STDistance() to return the distance between two single point locations. I'm curious as to which measurement is used for the return value? Is it in KM's, miles or perhaps some other?

I'm getting results back upwards of 250k but i've no idea if im doing something wrong with my TSQL as these are historical locations(i.e. they no longer exist) so I can't just do a quick lookup.

declare @p1 geography

declare @p2 geography

SELECT @p1 = Location from tblLocations where Id = 1
SELECT @p2 = Location from tblLocations where Id = 2

select @p1.STDistance(@p2)

Cacophonous

Answer 1

I think the return measurement depends upon the Spatial Reference Identifiers (SRIDs) of your geography data type. The default is 4326 which is in meters. There' a table in the DB you can check Select * from sys.spatial_reference_systems

Abdul

Answer 2

Just to cover people arriving here looking for the answer when using STDistance with GEOMETRY types, the result is "expressed in the same unit of measurement as the coordinate values themselves' (from 'Beginning Spatial with SQL Server 2008') which for WGS84 / SRID 4326 data is in Degrees.

The following SQL should run on SQL Server 2008 R2 and above. (Source of location data for Edinburgh Waverley and London Charing Cross stations bing maps):

DECLARE @edinGeom GEOMETRY = GEOMETRY::STGeomFromText('POINT(-3.1917 55.9517)', 4326)
DECLARE @cxGeom GEOMETRY = GEOMETRY::STGeomFromText('POINT(-0.1252 51.5083)', 4326)
SELECT @edinGeom.STDistance(@cxGeom), sqrt(square(3.1917-0.1252) + square(55.9517-51.5083)) AS 'Distance from Pythagoras';

DECLARE @MetersPerMile FLOAT = 1609.344;
DECLARE @edinGeog GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT(-3.1917 55.9517)', 4326)
DECLARE @cxGeog GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT(-0.1252 51.5083)', 4326)
SELECT @edinGeog.STDistance(@cxGeog), @edinGeog.STDistance(@cxGeog)/@MetersPerMile;

The results for the first 3 lines using GEOMETRY types are:

STDistance Geom: 5.39881707506376, Distance From Pythagoras: 5.39881707506376

The results for the GEOGRAPHY types are:

STDistance Geog: 534226.761544321, Converted to Miles: 331.953119745885

The '331 miles' or so from the GEOGRAPHY calculation with conversion ties in nicely with that shown on Bing maps as the distance between two points (clearly this is not a proof of anything, but it suggests similar underlying calculations).

The numbers output by the GEOMETRY calculation hopefully demonstrate that the result is very clearly in degrees, with the value apparently being calculated using pythagoras (the underlying calculations would be more complex if we were getting the distance between points and polygons).

Karankaras