## Calculate distance between locations using latitude and longitude

A couple months ago I had to calculate distance between locations using latitude and longitude... at that time I found a T-SQL sample that did the job but I forgot the site... I was impressed and I translated the code to C# and put in .NET Code library class and then, to make thigs complicated ( and usable in MS SQL 2005 Analisys Services- SSAS ) I've put in SSAS and call it from MDX query..

So far so good... but now I had some doubts about data accuracy and I searched the web again :). I havent; found same piece of code but I found better - and even article that describes the whole thing :)

In order not to forget again ( and to benefit you ) and I osting the code here. I recommend reading the article - Using SQL Server Yukon's .NET CLR Features Practically by Kent Tegels MCDBA, MCSE+I, MCP+SB

T-SQL user function:

CREATE FUNCTION dbo.udfComputeDistance

(

@lat1 float,

@lon1 float,

@lat2 float,

@lon2 float

)

RETURNS float

AS

begin

-- dLong represents the differences in longitudes

-- while dLat is the difference in latitudes

declare @dLong float

declare @dLat float

-- To keep the calculation easier to understand,

-- we have simplified it by computing it by parts.

-- This value temporarily holds the value of the

-- first calculation.

declare @temp float

-- Convert the decimal degrees to radians

set @lat2 = radians(@lat2)

set @lon1 = radians(@lon1)

set @lat1 = radians(@lat1)

set @lon2 = radians(@lon2)

-- Compute the degree differences

set @dLong = @lon2 - @lon1

set @dLat = @lat1 - @lat2

-- Compute the first part of the equation

set @temp = (square(sin(@dLat/2.0))) + cos(@lat2) * cos(@lat1) * (square(sin(@dLong/2.0)))

-- Return the approximate distance in miles

-- Note that 3956 is the approximate median radius of the Earth.

return (2.0 * atn2(sqrt(@temp), sqrt(1.0-@temp)))*3956.0

end

And C# function

private const double PI_OVER_180 = 0.0174532925;

private static double radians(double DecimalDegrees)

{

return DecimalDegrees * PI_OVER_180;

}

public static SqlDouble ComputeDistance(SqlDouble FromLat,

SqlDouble FromLong, SqlDouble ToLat, SqlDouble ToLong)

{

double lat1, lat2, lon1, lon2,

dLong = 0.0, dLat = 0.0, subCalc = 0.0;

lat1 = radians((double)(FromLat));

lon1 = radians((double)(FromLong));

lat2 = radians((double)(ToLat));

lon2 = radians((double)(ToLong));

dLong = (double)(lon2 - lon1);

dLat = (double)(lat2 - lat1);

subCalc = (Math.Pow(Math.Sin(dLat / 2.0), 2.0))

+ Math.Cos(lat2) * Math.Cos(lat1)

* (Math.Pow(Math.Sin(dLong / 2.0), 2));

return ((2.0 * Math.Atan2(Math.Sqrt(subCalc),

Math.Sqrt(1.0 - subCalc))) * 3956.0);

}

[STAThread]

public static void Main(string[] args)

{

Console.WriteLine(

ComputeDistance(40.7539,-96.6428, 41.28692,-96.07023));

Console.ReadLine();

}

## bob

4/18/2010 7:52:02 AM |"return ((2.0 * Math.Atan2(Math.Sqrt(subCalc), Math.Sqrt(1.0 - subCalc))) * 3956.0);" can be simplified to "return 2.0 * Math.Asin(Math.Sqrt(subCalc)) * 3956.0;"

## Andre Decio

6/7/2010 4:51:06 AM |Anyone have a version of this in VB.net?

Cheers