A SQL Function to calculate the distance between two geolocations (longitude and latitude)

Every once in a while a programming task will turn up that pushes all your nerd buttons at once, recently I was treated to one such occasion. In planning a development it became clear that one element that was needed was a SQL function to calculate the distance between two geolocations. My gasp of joy and excitement at the thought of combining geometry and SQL seemed not to be shared by the team so I thought I'd reward myself with a bit of programming (a rare treat these days).

What a joy it was!
Here's how you calculate the distance between two geolocations in a SQL function:


To calculate the distance between two points on the globe we need to use the Haversine formula: http://en.wikipedia.org/wiki/Haversine_formula
This fellow has an excellent page dedicated to implementing the formula in Javascript: http://www.movable-type.co.uk/scripts/latlong.html - it is on this explanation I based my code

Here is the Haversine formula from the moveable-type.co.uk site:

R = earth’s radius (mean radius = 6,371km)
Δlat = lat2− lat1
Δlong = long2− long1
a = sin²(Δlat/2) + cos(lat1).cos(lat2).sin²(Δlong/2)
c = 2.atan2(√a, √(1−a))
d = R.c

It basically calculates the distance by looking at the angles between the points in relation to the Earth's core and then in relation to each other and then uses a bit of simple Pythagoras to figure out the resulting distance.

In my version, I wanted to use miles rather than kilometres (simple task of switching the constant used for the Earth's radius) and I wanted to use a more accurate figure for the Earth's radius.

Here's what I came up with:

CREATE FUNCTION [dbo].[GetDistanceBetweenGeoLocations]
(
@lat1 float,
@long1 float,
@lat2 float,
@long2 float
)
RETURNS float
AS
BEGIN
-- Declare the return variable here
DECLARE @distance float
SET @distance = -1
DECLARE @earthRadius float
SET @earthRadius = 3958.761 --miles
--6371.009 --km
DECLARE @axis float

if ((@lat1 IS NOT NULL) AND (@long1 IS NOT NULL) AND (@lat2 IS NOT NULL) AND (@long2 IS NOT NULL)) -- bit of protection against bad data
BEGIN
SELECT @axis = (SIN(RADIANS(@lat2-@lat1)/2) * SIN(RADIANS(@lat2-@lat1)/2) +
COS(RADIANS(@lat1)) * COS(RADIANS(@lat2)) *
SIN(RADIANS(@long2-@long1)/2) * SIN(RADIANS(@long2-@long1)/2));
-- Multiply root of the axis by the earth's diameter
SELECT @distance = @earthRadius * (2 * ATN2(SQRT(@axis), SQRT(1-@axis)))
END


-- Return the result of the function, rounded down
RETURN ROUND(@distance, 3)

END

As a test, we will work out the distance between Big Ben and the Eiffel Tower - it should be 211.47 miles according to this site: http://www.trueknowledge.com/q/distance_from_big_ben_to_eiffel_tower

Big Ben geolocation: 51.5005, -0.1245 http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=51.5005,+-0.1245+&sll=53.21768,-103.452893&sspn=93.469044,270.527344&ie=UTF8&z=16
Eiffel Tower geolocation: 48.8582, 2.2945 http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=48.8582,+2.2945&sll=51.500874,-0.12447&sspn=0.011661,0.033023&ie=UTF8&z=16

Our test code:

SELECT
[dbo].[GetDistanceBetweenGeoLocations] (51.5005, -0.1245, 48.8582, 2.2945)

Our result:






Close enough, lets blame them for the difference.