I have just had the requirement to calculate the distance between 2 locations for a client project.

At first I was going down the route of rolling my own within the C# domain model and then I found this amazing piece of code in the form of an SQL function (sql server 2000/20005) courtesy of Troy DeMonbreun (http://blog.troyd.net/PermaLink,guid,847b0f1f-498c-43d4-80de-d29902fbd2eb.aspx).

Thanks Troy, you just saved me!

The Ninja

CREATE FUNCTION [dbo].[LatLonRadiusDistance]

(

@lat1Degrees decimal(15,12),

@lon1Degrees decimal(15,12),

@lat2Degrees decimal(15,12),

@lon2Degrees decimal(15,12)

)

RETURNS decimal(9,4)

AS

BEGIN

DECLARE @earthSphereRadiusNauticalMiles as decimal(10,6)

DECLARE @nauticalMileConversionToMilesFactor as decimal(7,6)

SELECT @earthSphereRadiusNauticalMiles = 6366.707019

SELECT @nauticalMileConversionToMilesFactor = .621371

-- convert degrees to radians

DECLARE @lat1Radians decimal(15,12)

DECLARE @lon1Radians decimal(15,12)

DECLARE @lat2Radians decimal(15,12)

DECLARE @lon2Radians decimal(15,12)

SELECT @lat1Radians = (@lat1Degrees / 180) * PI()

SELECT @lon1Radians = (@lon1Degrees / 180) * PI()

SELECT @lat2Radians = (@lat2Degrees / 180) * PI()

SELECT @lon2Radians = (@lon2Degrees / 180) * PI()

-- formula for distance from [lat1,lon1] to [lat2,lon2]

RETURN ROUND(2 * ASIN(SQRT(POWER(SIN((@lat1Radians - @lat2Radians) / 2) ,2)

+ COS(@lat1Radians) * COS(@lat2Radians) * POWER(SIN((@lon1Radians - @lon2Radians) / 2), 2)))

* (@earthSphereRadiusNauticalMiles * @nauticalMileConversionToMilesFactor), 4)

END

Your comment:
Title:
Name:
Comment: *Allowed tags: blockquote, a, strong, em, p, u, strike, super, sub, code*
Verification:
var RecaptchaOptions = {
theme : 'white',
tabindex : 0
};

No comments posted yet.