Calculate distances from given UK postcode

Question

Post reply

Calculate distances from given UK postcode

John M-160555

SSC Veteran

Points: 281
More actions
October 31, 2004 at 7:02 am

#61356

Hi,

I wonder if anyone can help here?

Objective:

1) Produce an SQL statement that can calculate the distance in miles between a given record in the table and all the other records in the table

What i have so far:

1) DB of all the postcodes in the UK

Fields

postCode '// postcode e.g. AB1
postCodeTown '// postcode town e.g. Milton Park
postCodeCounty '// postcode county/area e.g. Aberdeenshire
postCodeLat '// postcode latitude in degrees e.g. 57.187
postCodeLong '// postcode longitude in degress e.g. -2.119

What i need is a column that contains the calculated distance in miles from the given points (lat, long)

After that its just a case of filtering by that column.

Any help would be greatly appretiated

Best Regards

Technicweb

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

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

Vaxman SSC Veteran Points: 211 More actions · Answer 1

Here is an example of calculating distances based on lat/long in SQL

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33753

EdVassie SSC Guru Points: 60472 More actions · Answer 2

Don't forget that your calculations will just give you the distance as the crow flies, not the surface travel distance. The two can be significantly different where a river estuary falls between the two codes.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

I didn't read every linked response, but standard spherical geometry seems to be a popular approach. Unfortunately, this can be inaccurate for short distances (around 10 miles or less) as the SIN function approaches zero.

I researched this a while back for the same problem (distance between addresses) and found the "haversine" formula (short for "half of the versed sine"). I never heard of the "versed sine" either, but evidently it was popular for marine navigation.

Even if you don't understand it, this function is very accurate. If you really want a research project, try to figure out how to correct for the earth's deviation from a perfect sphere. Adjust radius based on latitude maybe?

CREATE FUNCTION system_function_schema.fn_latlondistance (@lat1 float, @lon1 float, @lat2 float, @lon2 float)

RETURNS float

AS

BEGIN

DECLARE @rlat1 float, @rlon1 float, @rlat2 float, @rlon2 float

DECLARE @a float, @C float, @d float

SELECT @rlat1 = RADIANS(@lat1), @rlon1 = RADIANS(@lon1), @rlat2 = RADIANS(@lat2), @rlon2 = RADIANS(@lon2)

SET @C = SQUARE(SIN((@rlat2 - @rlat1) / 2.0)) + COS(@rlat1) * COS(@rlat2) * SQUARE(SIN((@rlon2 - @rlon1) / 2.0))

SET @a = 2.0 * ATN2(SQRT(@c), SQRT(1.0 - @C))

SET @d = 3956.088331329 * @a

RETURN @d

END

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

We did this at a company to calculate fixed expenses etc. for sales visits. We applied a multiplication of just over 1.25 to the straight line distance to allow for the road distance.

Note that the co-ordinates for a postcode relate to a level of accuracy based upon the first postal address for that postcode - depending upon how accurate you need things you might want to think about that. None of these methods allow for rivers etc. But tend to work fairly well in practice. allowing for the spherical stuff is probably counteracted by the inaccuracy of the grid refs for the postcodes < grin > I just did a pythagoras calc !!!

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/