A SQL Function for the Distance between Two Geo-Locations

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.

Advertisements
This entry was posted in Software Test Automation. Bookmark the permalink.