Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

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


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service