Calculate Distance from Sql Spatial Geography Points

Problem

Given points stored in sql spatial Geography data type how do you find the ground distance in meters, miles or feet using Geography Data Types.

Solution

The Sql Spatial function for calculating distance is STDistance

DECLARE @from geography, @to GEOGRAPHY

SET @from = geography::STGeomFromText( 'POINT(-66.1711278 -17.4125)' , 4326)

SET @to = geography::STGeomFromText( 'POINT(0.0275361 51.5064694)' , 4326)

Show the distance in kilometers:

SELECT @from.STDistance(@to)/1000

Show the distance in miles:

SELECT @from.STDistance(@to)/1609.344

Show the distance in feet:

SELECT (@from.STDistance(@to)/1609.344) * 5280

The query can also be written with the point detail inline:

SELECT (geography:: STGeomFromText( 'POINT(-66.1711278 -17.4125)' , 4326).STDistance(geography::STGeomFromText('POINT(0.0275361 51.5064694)', 4326))/1609.344) * 5280

To use values from a table use the following:

select site_id, Lat_altered, Long_altered, latitude_dec, longitude_dec,

geography::Point(Lat_altered,Long_altered, 4326).STDistance(geography::Point(Lat_altered,Long_altered, 4326))/1609.344 as DistanceInMiles

from tb_site