Geeks With Blogs
The Ninja's Blog The Tao begets the one, the one begets the two......

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

Posted on Thursday, January 17, 2008 10:24 PM SQL | Back to top


Comments on this post: How to calculate the distance in calculate the distance in miles between 2 latitude/longitude points.

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Dave "The Ninja" Lawton | Powered by: GeeksWithBlogs.net