# Haversine Formula

The Haversine formula can be used to calculate the distance between two points on a sphere. Unfortunately the Earth is not a sphere, it's an ellipsoid, but the Haversine formula may still be suitable depending on your purposes.

This function is suitable for SQL Server 2005 as well as SQL Server 2000 (just remove the RETURNS NULL ON NULL INPUT function option). The function works in SQL 2008 although distance calculations can now be done natively using the new GEOGRAPHY datatype and STDistance method.

# dbo.udf_CalculateDistanceKilometers

```CREATE FUNCTION dbo.udf_CalculateDistanceKilometers(
@decLat1Deg DECIMAL(9,6), -- First latitude in degrees.
@decLon1Deg DECIMAL(9,6), -- First longitude in degrees.
@decLat2Deg DECIMAL(9,6), -- Second latitude in degrees.
@decLon2Deg DECIMAL(9,6)  -- Second longitude in degrees.
) RETURNS DECIMAL(9,4) AS
/*^
* Description:   Return the surface distance between two latitude/longitude points based on the Haversine formula.
*
* Parameters :   @decLat1Deg, mandatory, latitude of first location (DECIMAL degrees).
*                @decLon1Deg, mandatory, longitude of first location (DECIMAL degrees).
*                @decLat2Deg, mandatory, latitude of second location (DECIMAL degrees).
*                @decLon2Deg, mandatory, longitude of second location (DECIMAL degrees).
*
* History:
* Date          Who   Ver   Desc
* 20 Nov 2006   RMJ   0.1   Initial version.
* 05 Mar 2008   RMJ   0.2   Code review, tweaked lat/lon and distance datatypes to decimal(9,x),
*                              implemented Atan2 instead of Atan,
*                              further functional and SQL Server version testing.
*
* References:    http://en.wikipedia.org/wiki/Great_circle_distance - master reference for implementation.
*                http://www.movable-type.co.uk/scripts/LatLong.html - JavaScript utility to compare results with.
*                http://mathforum.org/library/drmath/view/51879.html
*                http://www.tbs-sct.gc.ca/rpm-gbi/guides/Latlong_e.asp - definition and description of lat and long, with discussion on precision/accuracy.
^*/
BEGIN

DECLARE @decEarthRadiusKM DECIMAL(9,4); SET @decEarthRadiusKM = 6372.795 -- Store the average great-circle radius of the Earth in Kilometers.

DECLARE @decLonDiffRad DECIMAL(9,6)    -- Store the difference betwen the two longitude (radians) values.
DECLARE @decCosLonDiffRad DECIMAL(9,6) -- Store the cosine of the difference betwen the two longitude (radians) values in radians.

DECLARE @r DECIMAL(9,4) -- Result.

SELECT
SELECT
SELECT
@decCosLonDiffRad = COS(@decLonDiffRad), -- only compute trig functions once for those values that are used more than once.

SET @r = ATN2(

RETURN @r

END
GO

-- Tests / Examples
SELECT
dbo.udf_CalculateDistanceKilometers(0.0, 0.0, 0.0, 0.0) AS GoingNowhereA,
dbo.udf_CalculateDistanceKilometers(0.0, 0.0, 0.0, +180.0) AS GoingHalfwayA,
dbo.udf_CalculateDistanceKilometers(0.0, 0.0, 0.0, -180.0) AS GoingHalfwayB,
dbo.udf_CalculateDistanceKilometers(0.0, 0.0, +90.0, 0.0) AS GoingPoleA,
dbo.udf_CalculateDistanceKilometers(0.0, 0.0, -90.0, 0.0) AS GoingPoleB,
dbo.udf_CalculateDistanceKilometers(0.0, +180.0, +90.0, 0.0) AS GoingPoleC,
dbo.udf_CalculateDistanceKilometers(0.0, -180.0, -90.0, 0.0) AS GoingPoleD,
dbo.udf_CalculateDistanceKilometers(-90.0, 0.0, +90.0, 0.0) AS GoingPoleE,
dbo.udf_CalculateDistanceKilometers(+90.0, 0.0, -90.0, 0.0) AS GoingPoleF,
dbo.udf_CalculateDistanceKilometers(0.0, 0.0, 1.0, 0.0) AS OneDegreeOfLatitudeA,
dbo.udf_CalculateDistanceKilometers(1.0, 0.0, 0.0, 0.0) AS OneDegreeOfLatitudeB,
dbo.udf_CalculateDistanceKilometers(0.0, +180.0, 1.0, +180.0) AS OneDegreeOfLatitudeC,
dbo.udf_CalculateDistanceKilometers(1.0, -180.0, 0.0, -180.0) AS OneDegreeOfLatitudeD,
dbo.udf_CalculateDistanceKilometers(0.0, 0.0, 0.0, 1.0) AS OneDegreeOfLongitudeA,
dbo.udf_CalculateDistanceKilometers(0.0, 1.0, 0.0, 0.0) AS OneDegreeOfLongitudeB
GO```