# SQL Server DISTANCE function

DISTANCE

Updated: 30 April 2011

Use the DISTANCE function to calculate the distance between 2 points on the globe, using the great circle formula.
Syntax
SELECT [wctMath].[wct].[DISTANCE](
<@SourceLat, float,>
,<@SourceLong, float,>
,<@DestLat, float,>
,<@DestLong, float,>
,<@Units, nvarchar(4000),>)

Arguments
@SourceLat
the decimal representation of the latitude of the point of origin. @SourceLat is an expression of type float or of a type that can be implicitly converted to float
@SourceLong
the decimal representation of the longitude of the point of origin. @SourceLong is an expression of type float or of a type that can be implicitly converted to float
@DestLat
the decimal representation of the latitude of the destination. @DestLat is an expression of type float or of a type that can be implicitly converted to float
@DestLong
the decimal representation of the longitude of the destination. @DestLong is an expression of type float or of a type that can be implicitly converted to float
@Units
identifies the units of the result as being returned in miles (MI), kilometers (KM), or nautical miles (NMI).
Return Types
float
Remarks
·         @SourceLat and @DestLat must be between -90 and 90.
·         @SourceLong and @DestLong must be between -180 and 180.
·         If @Units is NULL, then the result is returned in miles (MI).
·         South Latitude is indicated by using a minus sign.
·         West Longitude is indicated by using a minus sign.
Examples
In this example we calculate the distance from the Statue of Liberty to the Santa Monica Pier, in miles.
SELECT wct.DISTANCE(
40.69001          --@SourceLat
,-74.044976       --@SourceLong
,35.7456512       --@DestLat
,-118.652344      --@DestLong
,NULL             --@Units
) as DISTANCE
This produces the following result.
DISTANCE
----------------------
2422.01313678052

(1 row(s) affected)

In this example, we calculate the distance from the Eiffel Tower to St. Peter’s Basilica in Vatican City, in kilometers.
SELECT wct.DISTANCE(
48.858786         --@SourceLat
,2.294512         --@SourceLong
,41.902676        --@DestLat
,12.456522        --@DestLong
,'km'             --@Units
) as DISTANCE
This produces the following result.
DISTANCE
----------------------
1107.67772634464

(1 row(s) affected)

In this example, we enter an invalid latitude and generate an error.
SELECT wct.DISTANCE(
148.858786        --@SourceLat
,2.294512         --@SourceLong
,41.902676        --@DestLat
,12.456522        --@DestLong
,'km'             --@Units
) as DISTANCE
This produces the following result.
DISTANCE
----------------------
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "DISTANCE":
XLeratorDB_math.Core.Exceptions+Function_Parameter_Exception:
[Product version 1.5 Build: 0406.232 Server: PC02]
*** Error information:

Latitudes must be between -90 and 90

Invalid 'SourceLat' parameter value passed to function 'DISTANCE'
Function: DISTANCE
Passed Parameters:
'SourceLat' = System.Double:148.858786
'SouceLong' = System.Double:2.294512
'DestLat' = System.Double:41.902676
'DestLong' = System.Double:12.456522
'Units' = System.String [2chars] :km

*** Error information:

Latitudes must be between -90 and 90

XLeratorDB_math.Core.Exceptions+Function_Parameter_Exception:
at XLeratorDB_math.Core_Functions_Math.do_DISTANCE(Double SourceLat, Double SourceLong, Double DestLat, Double DestLong, String Units)
at XLeratorDB_math.UserDefinedFunctions.DISTANCE(SqlDouble SourceLat, SqlDouble SourceLong, SqlDouble DestLat, SqlDouble DestLong, SqlString Units)
.

In this example, we use a derived table containing a store number and the GPS co-ordinates for the store, and then we return all the stores within 25 miles of a supplied location, sorted by distance.
SELECT storenum, wct.DISTANCE(41.031274,-73.869404,storeLat,storeLong,NULL) as DISTANCE
FROM (VALUES
(101,40.9,-73.9),
(102,40.9,-74.1),
(103,41.1,-73.9),
(104,41.1,-74.1),
(105,40.8,-73.8),
(106,40.8,-74.2),
(107,41.2,-73.8),
(108,41.2,-74.2),
(109,40.7,-73.7),
(110,40.7,-74.2),
(111,41.3,-73.7),
(112,41.3,-74.2)
) n(storenum, storeLat, storeLong)
WHERE wct.DISTANCE(41.031274,-73.869404,storeLat,storeLong,NULL) < 25
ORDER BY 2 ASC
This produces the following result.
storenum               DISTANCE
----------- ----------------------
103       5.01447173856632
101       9.21986780639179
107       12.2184709276458
104       12.9314831119514
102       15.0836387349025
105         16.40357490829
111       20.5749136571593
108       20.8090069276438
106       23.5484282589781
109       24.5682460204703