distance between UK postcodes for a find my nearest...

[Mike Dodd]

Dear All

I wonder if you can help me or perhaps point me in the right direction. I
am trying to set-up a simple standalone database using MS Access or
OpenOffice Base made up of a table of supplier outlets with each service
providers' name address and UK postcode. A second table with UK postcodes
and the respective Lat/Long figure and some means of calculating the
distance between postcodes for a find my nearest... supplier, service agent,
sales representative and the like.

What I have in mind is for the position of the prospective customers
postcode to be determined and then the system to look for matching outlets
or service people that are say within 2 or 3 miles of the prospective
customer. What I'd like is the system to count outlets or service people.
If there are fewer than 10 matches, the system to expand the area and go back
to try once again. Once at least 10 outlets or service people have been
found, the arrangement somehow calculates the distance from the prospective
customer to the position of each of the outlets or service person and these
then sorted by distance from the customer.

I'm struggling with the basics of MS Access and although I see masses of
website doing this sort of thing I'm not thinking of website use, but purely
set up on a one-off desktop computer.

Any assistance gratefully received

 

[Wayne-I-M]

Hi Mike

I'm not sure is the is any direct corelation between postcode and distance.
As an example the PH33 post code - North West Scotland - (where my sister now
lives cover an area the size of London X 3) whereas the SE postcode is only a
few miles accross. I think I read somewhere that it it has more to do with
how many households there are within the area, allthough I may be wrong about
this.

There may be a way to do what you want but I'm not sure if it will be at
all easy. The best place to start would be the post office website and then
maybe try calling them - but I think they would charge you quite a lot for
the information.

 

[John Nurick]

Possibly the simplest way to proceed would be to acquire Microsoft MapPoint
Europe. This knows the locations of most of the addresses and postcodes in
the UK and can be controlled via VBA from Access to do the sort of thing you
describe.

There's a lot of information about MapPoint here http://www.mp2kmag.com/ and
it may be possible to download a time-limited trial copy from somewhere on
http://www.microsoft.com.

 

[Mike Dodd]

Hi Wayne and John

Many thanks to both for the reply to my question. What I’m trying to
replica is something like the ‘find my nearest’ example shown on
http://www.brainboxweb.co.uk/capabilities/postcodes. I cannot unfortunately
justify cost of a programme such as MapPoint at work which would almost
certainly do all that I’m trying to achieve. I'm need to allow more that one
user access to the information and this likely to make MapPoint too
expensive.

What I am hoping is to get my hands on a postcode list and create a database
that contains latitude and longitude coordinates, apply some Pythagoras and
in some mysterious way the job is done. Unfortunately it’s all Greek to me!

I've seen lots of websites where they give methods of achieving function for
website applications of ‘nearest dealer’ or ‘closest store’ function using
ASP/Pearl/PHP and the like but I don’t know how to convert this into a
straightforward MS Access application.

But thanks for the suggestion

 

[Mike Dodd]

Thanks for your suggestion John. Microsoft MapPoint would certainly do the
job but for my purpose I'm looking for something simple.

 

[John Nurick]

Hi Mike,

I doubt whether it will be hard to find a VBA function that takes two
lat/lon coordinates or OS grid references and calculates a good
approximation to the straight line distance between them (or failing
that a formula from which you'll be able to write your own function).
Over distances of a few km you can even ignore the curvature of the
earth and treat the grid references as Cartesian coordinates.

When I started to write this I thought that the hard part would be to
find a comprehensive and affordable list of postcodes and their
locations. One reason I suggested MapPoint is that it is dirt cheap by
the standards of GIS software and datasets. But I checked the Royal Mail
website and they've been revising their pricing to make it less
unattractive. It looks as their Postzon product has the data you'll need
for £100 p.a. or less. See
http://www.royalmail.com/portal/rm/jump2?mediaId=400088&catId=400084 and
be sure to click through to the "full details".

 

[Mike Dodd]

Hi John

Many thanks for the info I'll give it a look

Mike