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
-- 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
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)))

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


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:

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

Our result:

Close enough, lets blame them for the difference.


madin said...

Dal 1848, Omega è leadereconomici orologi mondiale nella progettazione di orologi di fascia alta. Nel corso degli anni, il marchio è stato ampiamente elogiato per replica omega 1957 trilogy orologi replica omega specialties orologila sua durata e precisione. Omega (OMEGA) è stato il timer ufficiale dei Giochi Olimpici per quasi un secolo. Hanno anche creato l'orologio ufficiale del programma spaziale e sono atterrati sulla luna per la prima volta. L'acquisto di orologi OMEGA è un modo meraviglioso di investire in un pezzo di storia.

midn said...

replica watches uk, combining elegant style and cutting-edge technology, a variety of styles of replica rolex watches, the pointer walks between your exclusive taste style.

Post a Comment