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.

Advertisements