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

Parameters:
  • 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)
RETURNS FLOAT
AS
BEGIN   
    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
    BEGIN
        RETURN ( @Miles )
    END
    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 )
END

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.

http://maps.googleapis.com/maps/api/geocode/json?address=delhi&sensor=false

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
RESULT

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

    ReplyDelete