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

### Like this:

Like Loading...

*Related*