 # Galin Iliev's blog

### Software Architecture & Development

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;
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);
}
public static void Main(string[] args)
{
Console.WriteLine(
ComputeDistance(40.7539,-96.6428, 41.28692,-96.07023));
}

### Comments (2) -

• #### bob

4/18/2010 7:52:02 AM | Reply

"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 | Reply

Anyone have a version of this in VB.net?

Cheers  