Distance between Postcodes

Question

I am trying to find out the best way of working out distance between locations (we have the postcode and locations).

Basically with each record we have a Location and Postcode, the user on the front-end can enter a postcode and select records ..

- Within 10 Miles
- Within 20 Miles
- Within 40 Miles
- Within 50 Miles

The only way I can see doing this is by getting EACH record from the database and working out the distance between each, entering it into a temporary table and then ordering this table by location and filtering it. This seems like a long-winded and time-consuming way of doing it - does anyone have any suggestions of how this could be done better? Or is this the best way of doing it?

An example would be a customer enters SO40 9AA (Southampton Postcode) and then selects "within 50 miles", this should display and records within 50 miles of that postcode or location.

If you have access to longitude/latitude, gave a look at this: http://stackoverflow.com/questions/4117979/sql-server-geography-point and this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360 — Allov, Aug 29 '12 at 17:17

score 0 · Answer 1 · answered Aug 29 '12 at 18:01

0

Do you have the geo-code in your dataset? If not you can add it for the zipcodes you have with a service like http://geocoder.us/help/city_state_zip.shtml Or you can get a pre-coded database, available at http://www.zipinfo.com/products/z5ll/z5ll.htm Once you add the code to the records, then you can use a standard mathematical distance calculation using the lat / long coordinates to calculate distance.

If you want driving distance, use the Google Maps API, otherwise this distance will be "as the crow flies."

answered Aug 29 '12 at 18:01

macycron

91
3

These two solutions are great for US solutions, but that postcode by OP is UK. – swasheck Aug 29 '12 at 18:04
Great point. Sorry. For UK addresses try this site http://www.freemaptools.com/download-uk-postcode-lat-lng.htm – macycron Aug 29 '12 at 18:47

Tom Collins · Answer 2 · 2012-08-29T21:01:37.127

Once you the geo coords for all your postcodes, here's a VB function to get the strait distance between any two points. This produces results in miles, but easy enough to convert it.

Function distance(lat1 As Single, lon1 As Single, lat2 As Single, lon2 As Single)
  Dim theta As Single, dist As Single
  theta = deg2rad(lon1 - lon2)
  lat1 = deg2rad(lat1)
  lat2 = deg2rad(lat2)
  dist = Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(theta)
  dist = acos(dist)
  dist = rad2deg(dist)
  distance = dist * 60 * 1.1515
End Function

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
':::  This function get the arccos function from arctan function    :::
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Function acos(Rad)
  If Abs(Rad) <> 1 Then
    acos = pi / 2 - Atn(Rad / Sqr(1 - Rad * Rad))
  ElseIf Rad = -1 Then
    acos = pi
  End If
End Function


'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
':::  This function converts decimal degrees to radians             :::
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Function deg2rad(Deg As Single) As Single
    deg2rad = CSng(Deg * pi / 180)
End Function

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
':::  This function converts radians to decimal degrees             :::
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Function rad2deg(Rad As Single) As Single
    rad2deg = CSng(Rad * 180 / pi)
End Function

Distance between Postcodes

2 Answers2