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.

## 0 comments:

## Post a Comment