# Wednesday, February 14, 2007

This is a free SQL Server 2000/2005 function that will calculate the distance in miles between 2 latitude/longitude points

The latitude/longitude points are input as decimal degrees, e.g.: 36.098217314. For those that are interested, it uses (a derivation of) the Haversine formula using the Great Circle Sphere earth radius. It can easily be adjusted to return kilometers or any other distance conversion.  Its intended use is to enable geographic (e.g.: zip, address) radius/proximty search by evaluating distance in a query WHERE statement on the table contains latitude and longitude columns.

But, I need code for .NET, not T-SQL!

For the .NETers among you, this can also easily be converted to .NET code -- just remember to use the Math namespace for all the math/trig functions (e.g.: Math.Asin(), Math.Round(), etc.).

CREATE FUNCTION [dbo].[LatLonRadiusDistance] 
	@lat1Degrees decimal(15,12),
	@lon1Degrees decimal(15,12),
	@lat2Degrees decimal(15,12),
	@lon2Degrees decimal(15,12)
RETURNS decimal(9,4)

	DECLARE @earthSphereRadiusKilometers as decimal(10,6)
	DECLARE @kilometerConversionToMilesFactor as decimal(7,6)
	SELECT @earthSphereRadiusKilometers = 6366.707019
	SELECT @kilometerConversionToMilesFactor = .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)))
        * (@earthSphereRadiusKilometers * @kilometerConversionToMilesFactor), 4)