Here is a user-defined T-SQL function for the distance between two geographical locations. The math comes from http://mathforum.org/library/drmath/view/51879.html. I’m using type real for my parameters; all the calculations are done as float with implicit type conversion.

— ======================

— great circle distance in km between two geo points

— where lat and lon are like (47.65, -122.17)

— uses the haversine (half-versed sine) formula

create function DistanceBetween(@lat1 real, @lon1 real,

@lat2 real, @lon2 real)

returns real

as

begin

declare @result real

declare @lat1InRadians real

declare @lon1InRadians real

declare @lat2InRadians real

declare @lon2InRadians real

declare @deltaLatitudes real

declare @deltaLongitudes real

declare @a real

declare @c real

set @lat1InRadians = @lat1 * ( PI() / 180.0)

set @lon1InRadians = @lon1 * ( PI() / 180.0)

set @lat2InRadians = @lat2 * ( PI() / 180.0)

set @lon2InRadians = @lon2 * ( PI() / 180.0)

set @deltaLatitudes = @lat2InRadians – @lat1InRadians

set @deltaLongitudes = @lon2InRadians – @lon1InRadians

set @a = POWER(SIN(@deltaLatitudes / 2.0), 2.0) + COS(@lat1InRadians) *

COS(@lat2InRadians) * POWER(SIN(@deltaLongitudes / 2.0), 2.0)

set @c = 2.0 * ATN2( SQRT(@a), SQRT(1.0 – @a) )

set @result = 6370.0 * @c — radius of earth approx. 6370.0 km

return @result

end

In SQL Server Management Studio, I right-clicked on the Programmability | Functions | Scalar-valued Functions folder icon in the database I was working with, and selected New Scalar-valued Function from the context menu. I deleted the template code and typed the code above. I hit F5 to create the code and then did a View | Refresh to verify that the new function had been created. (I had to delete several times with the command ‘drop function dbo.DistanceBetween’ during development). To test the DistanceBetween function I went to a new query window and typed:

use myWorkingDatabase

go

declare @lat1 real

declare @lon1 real

declare @lat2 real

declare @lon2 real

set @lat1 = 47.68 — Seattle

set @lon1 = -122.12

set @lat2 = 40.76 — New York

set @lon2 = -73.98

select dbo.DistanceBetween(@lat1, @lon1, @lat2, @lon2) as dist

go

The result was 3847.653 (km). The exact value of the function result depends on the constant used for the radius of the earth in kilometers.