DISTANCE

Updated 2023-10-13 20:01:08.027000

Syntax

SELECT [westclintech].[wct].[DISTANCE](
  <@SourceLat, float,>
 ,<@SourceLong, float,>
 ,<@DestLat, float,>
 ,<@DestLong, float,>
 ,<@Units, nvarchar(4000),>)

Description

Use the scalar function DISTANCE function to calculate the distance between 2 points on the globe, using the great circle formula.

Arguments

@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.

@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.

@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 Type

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.

{"columns":[{"field":"DISTANCE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"DISTANCE":"2422.01313678052"}]}

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.

{"columns":[{"field":"DISTANCE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"DISTANCE":"1107.67772634464"}]}

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.

Msg 6522, Level 16, State 1, Line 1A .NET Framework error occurred during execution of user-defined routine or aggregate "DISTANCE":q+c: Invalid 'SourceLat' parameter value passed to function 'DISTANCE'Latitudes must be between -90 and 90

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.

{"columns":[{"field":"storenum","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DISTANCE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"storenum":"103","DISTANCE":"5.01447173856632"},{"storenum":"101","DISTANCE":"9.21986780639179"},{"storenum":"107","DISTANCE":"12.2184709276458"},{"storenum":"104","DISTANCE":"12.9314831119514"},{"storenum":"102","DISTANCE":"15.0836387349025"},{"storenum":"105","DISTANCE":"16.40357490829"},{"storenum":"111","DISTANCE":"20.5749136571593"},{"storenum":"108","DISTANCE":"20.8090069276438"},{"storenum":"106","DISTANCE":"23.5484282589781"},{"storenum":"109","DISTANCE":"24.5682460204703"}]}