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