Tag: distance

Calculate the distance between two coordinates in SQL Server

SQL

google-distance-calculator-1390807649If you ever had to quickly retrieve a distance between two points (identified with a latitude and longitude each), you might have had to rely on the Google geocoding service / distance calculator.

For a lot of points, this might prove difficult, so we have found a good function that will do all the processing instantly inside SQL.

CREATE function [dbo].[F_GREAT_CIRCLE_DISTANCE]
(
    @Latitude1  float,
    @Longitude1 float,
    @Latitude2  float,
    @Longitude2 float
)
returns float as
begin
    declare @radius float

    declare @lon1  float
    declare @lon2  float
    declare @lat1  float
    declare @lat2  float

    declare @a float
    declare @distance float

    -- Sets average radius of Earth in Miles
    set @radius = 6366.7

    -- Convert degrees to radians
    set @lon1 = radians( @Longitude1 )
    set @lon2 = radians( @Longitude2 )
    set @lat1 = radians( @Latitude1 )
    set @lat2 = radians( @Latitude2 )

    set @a = sqrt(square(sin((@lat2-@lat1)/2.0E)) + (cos(@lat1) * cos(@lat2) * square(sin((@lon2-@lon1)/2.0E))) )
    set @distance = @radius * ( 2.0E *asin(case when 1.0E < @a then 1.0E else @a end ))

    return @distance
end
GO

To use this function, call it with a pair of coordinates.

Example: Chicago 41.8819° N, 87.6278° W
New York 40.7127° N, 74.0059° W

Result: 1,143.38 km

print dbo.[F_GREAT_CIRCLE_DISTANCE] (41.8819, 87.6278, 40.7127, 74.0059)

 

If you would like to see the distance in miles rather than kilometers, change
set @radius = 6366.7
to
set @radius = 3956

The above result will be 710miles.

Advertisements