RMJCS Logo
 

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.

Related Links;

 
     
 

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 @decLat1Rad DECIMAL(9,6)       -- Store first latitude as radians.
DECLARE @decLon1Rad DECIMAL(9,6)       -- Store first longitude as radians.
DECLARE @decLat2Rad DECIMAL(9,6)       -- Store second latitude as radians.
DECLARE @decLon2Rad DECIMAL(9,6)       -- Store second longitude as radians.
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 @decCosLat1Rad DECIMAL(9,6)    -- Store the cosine of the first latitude (radians) values in radians.
DECLARE @decCosLat2Rad DECIMAL(9,6)    -- Store the cosine of the second latitude (radians) values in radians.
DECLARE @decSinLat1Rad DECIMAL(9,6)    -- Store the sine of the first latitude (radians) values in radians.
DECLARE @decSinLat2Rad DECIMAL(9,6)    -- Store the sine of the second latitude (radians) values in radians.

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

   SELECT
      @decLat1Rad = RADIANS(@decLat1Deg), -- Convert latitudes and longitudes from degrees to radians.
      @decLon1Rad = RADIANS(@decLon1Deg),
      @decLat2Rad = RADIANS(@decLat2Deg),
      @decLon2Rad = RADIANS(@decLon2Deg)
   SELECT
      @decLonDiffRad = @decLon2Rad - @decLon1Rad
   SELECT
      @decCosLonDiffRad = COS(@decLonDiffRad), -- only compute trig functions once for those values that are used more than once.
      @decCosLat1Rad = COS(@decLat1Rad),
      @decCosLat2Rad = COS(@decLat2Rad),
      @decSinLat1Rad = SIN(@decLat1Rad),
      @decSinLat2Rad = SIN(@decLat2Rad)

   SET @r = ATN2(
                 SQRT( POWER(@decCosLat2Rad * SIN(@decLonDiffRad), 2) + POWER((@decCosLat1Rad * @decSinLat2Rad) - (@decSinLat1Rad * @decCosLat2Rad * @decCosLonDiffRad), 2) ) ,
                 ((@decSinLat1Rad * @decSinLat2Rad) + (@decCosLat1Rad * @decCosLat2Rad * @decCosLonDiffRad))
                ) * @decEarthRadiusKM

   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
 
     
Copyright 2007-2015 RMJCS Ltd