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