Below is a user defined function to find the distance between two locations. Assume that we have the longitude and latitude of the both locations. There is an option to return either the distance in kilometers or Miles or Feet.
CREATE Function [Dbo].[fnGetDistancebyLongitudeandLatitudeandUnit]
(
@Latitude1 Float(18),
@Longitude1 Float(18),
@Latitude2 Float(18),
@Longitude2 Float(18),
@ReturnType char
)
Returns Float(18)
AS
Begin
Declare @R Float(8),
@dLat Float(18),
@dLon Float(18),
@a Float(18),
@c Float(18);
Set @R =
Case @ReturnType
When 'M' Then 3956.55
When 'K' Then 6367.45
When 'F' Then 20890584
Else 20890584 -- Default feet (Garmin rel elev)
End
Set @dLat = Radians(@Latitude2 - @Latitude1);
Set @dLon = Radians(@Longitude2 - @Longitude1);
Set @a = Sin(@dLat / 2)
* Sin(@dLat / 2)
+ Cos(Radians(@Latitude1))
* Cos(Radians(@Latitude2))
* Sin(@dLon / 2)
* Sin(@dLon / 2);
Set @c = 2 * Asin(Min(Sqrt(@a)));
Return ROUND(@R * @c,2);
End