Jun 15, 2012

SQL Calculate Distance between Two Points

In this post, I am sharing how to calculate distance between two GEO locations given geographical points (latitude and longitude) of the locations.

Below is a function that takes latitude and longitude of the locations and returns the distance between the locations in miles

  • lat1: Latitude of Source Location
  • long1: Longitude of Destination Locations
  • lat2: Latitude of Source Location
  • long2: Longitude of Destination Locations

CREATE FUNCTION dbo.udf_GetDistance(@lat1 float, @long1 float, @lat2 float, @long2 float)
    DECLARE @DToR as float
    DECLARE @Ans as float
    DECLARE @Miles as float

    SET @DToR= 57.29577951
    SET @Ans = 0
    SET @Miles = 0

    IF @lat1 IS NULL OR @lat1 = 0 OR @long1 IS NULL OR @long1 = 0 OR @lat2 IS NULL OR @lat2 = 0 OR @long2 IS NULL OR @long2 = 0
        RETURN ( @Miles )
    SET @Ans = SIN(@lat1 / @DToR) * SIN(@lat2 / @DToR) + COS(@lat1 / @DToR) * COS( @lat2 / @DToR) * COS(ABS(@long2 - @long1 )/@DToR)
    SET @Miles = 3959 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans)   
    RETURN ( @Miles )

To calculate distance we need geographical points of the locations that we can get from google API's.

For intro refer, https://developers.google.com/maps/documentation/geocoding/intro

Using below link, we would be taking Geo points of North-East and South-West of Delhi and will pass to the function to get the distance between two.


Let's pass the latitude and longitude of both that we get using above link to the function and see the result.

SELECT dbo.udf_GetDistance(28.88225340, 77.34169940, 28.42219750, 76.85382840) as Miles

    Choose :
  • OR
  • To comment
1 comment:
Write Comments
  1. Hi,
    I know it was written long time ago but I do not understand one thing.
    Could anyone explain to me what means @DToR parameter and why it is 57.29577951 ?

    I would be gratefull.