Calculate Distance between two UK Post Codes

Question

Calculate Distance between two UK Post Codes

SQL_Kills

Hall of Fame

Points: 3154
More actions
November 14, 2014 at 3:28 pm

#312556

Hi,
I want to be able to calculate the Distance between two UK Postcodes.
So there will two parameters being past through
@StartPostcode
@EndPostcode
So when passing in the @StartPostcode = L1 and @EndPostcode = M1 then it will return the distinace in Miles which would be 38.8 based on Distance by Road as per the example from the below website:
http://www.postcode.org.uk/country/uk/_postcode-distance-calculator.asp?SPC=L1&FPC=M1&Submit=Calculate+Distance
Has anyone does something similar? And where would I need to download the table that will hold the postcode longitude/latitude information if required?
Thanks

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234064 More actions · Answer 1

i don't think UK postcodes are an exact measurable science i could be wrong. They are assigned on a street level and one long street will usually have many postcodes

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 2

I only want the short post code not bothered about the full post code, the website is only doing the short post code so it won't be as many lookups to calculate I am presuming.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

If you want to code it yourself, you need to work with spatial data. There might be some way to get all the data instead of collecting it, but I can't tell where to find it.

This can help you to start with spatial data: http://technet.microsoft.com/en-us/library/bb933876(v=sql.105).aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 4

Hi,

So I have found this website that has a user defined function

http://www.hexcentral.com/articles/postcodes-distances.htm

CREATE FUNCTION dbo.GetDistance

(@e1 float, @e2 float, @n1 float, @n2 float)

RETURNS float

AS

BEGIN

RETURN sqrt(square(@e1 - @e2) + square(@n1 - @n2))

END

I am not getting the correct miles when running the function for the postcodes of L1 to M1, this is the longitue latitude i got from the net which i am guessing is what needs to be passed through?

Select dbo.GetDistance(53.402051,-2.979837,53.478484,-2.23557)