#techie Unless your interested in maps and calculating distances..... ignore this one!

Bit of a techie one this but I thought I'd add it in case somebody found it useful!

I've been doing a bit of work with Google Maps and http://www.openstreetmap.org/ an open data source project which I have a lot of respect for.

I won't go into details but I've needed to calculate distances between two points based on there coordinates and some of this has to be done on a database server...... So I've put together this very quick SQL function which returns the distance between two coordinates in God's own measurement, no not cubits, miles!

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- =============================================

-- Author:        <Christian Miles>

-- =============================================

ALTER FUNCTION [dbo].[calculateDistanceBetweenCoordinates]

(

    @lat1  float,

    @lon1  float,

    @lat2  float,

    @lon2  float

)

RETURNS float

AS

BEGIN

 -- Assumes the Radius of the planet (distance from the hot core to the crust) is   

 -- around 3,959.6 miles

   return (ACOS(SIN(RADIANS( @lat1 )) * SIN(RADIANS( @lat2 )) + COS(RADIANS( @lat1)) * COS(RADIANS( @lat2 )) * COS(RADIANS( @lon2 ) - RADIANS( @lon1 )))) * 3959.6

END

Now unfortunately, or at least from a mathematical puritan perspective the plant Earth is not a perfect sphere.... in fact the radius ranges from around  3,947–3,968 miles and in my opinion all the better for it! but it does mean that the distance it produces will be reasonably accurate but it's tolerance will vary depending where on the Earth your measuring!

Google does do an excellent API for calculating distances.... but if your using it behind a Firewall or restricting access to your site from outside you'll have to buy a licence..... and they also restrict what you do with the data.

They also restrict the number of hits per second and per day you can perform without a licence (with a licence there's a charging model in place once you hit a limit) either way the API call does not work if you have to search across thousands or even millions of records!

Written by 

Christian Miles