Finding Distance Using Post Codes

[Dannyboy11]

I have searched the forums and the internet and the only replies that I find use VBA and terminology that goes right over my head, such that I don't even know if it's answering what I want it to!! Something I found was this, which the OP seems to want the same as me except that he uses longitude and latitude.

What I want is to run a query to find which venues are within an X mile radius of a post code.

I have a table of people, which includes post code as a field, and a table of venues, which has a post code as a field.

Is it possible to run a query to select a person and then it will use the post codes to say which venues are within an X mile radius.

Thank you for reading and I appreciate your replies

 

[John Big Booty]

First up you are going to need the latitude and longitude for the centres of each of your postcodes, without that information, the conversation ends pretty much right here.

Now assuming that you do have that information you can use the principals demonstrated in the attached sample to calculate the distance between any two of your postcodes.

 

[gemma-the-husky]

out of interest, i bough MS mapppoint to do this.

You cannot do this for free with google maps, because the UK PO does not give free access to its post code database.

ie say you have a nottingham postcode NN3 4GL

you can only get precision to NN3 on google


-------------
mappoint is then a bit like a programmable autoroute. you can find straightl ine distances, or distances using road routes.

 

[John Big Booty]

If you are after Australian post code data.
The list of postcodes can be downloaded here in CSV format. Lat/Lomg details can be downloaded from here. A very cursory cross refrence would suggest that most of the post code centers are available in the second download file.

 

[Dannyboy11]

Hi

I have the post codes. Is there any way that I can quickly convert these to lat / long co-ordinates? Or will I have to do each one separately, which would be a pain!

The post codes are for England.

 

[John Big Booty]

Postcodes represent a geographical area, so there is no way to extract Lat/Long data from them directly, what you need (given that you are dealing with UK postcodes), base on gemma-the-husky's comments, is to purchase that data from Royal Mail (assuming all along that that data includes Lat/Long details for each postcodes).

 

[gemma-the-husky]

Postcodes represent a geographical area, so there is no way to extract Lat/Long data from them directly, what you need (given that you are dealing with UK postcodes), base on gemma-the-husky's comments, is to purchase that data from Royal Mail (assuming all along that that data includes Lat/Long details for each postcodes).

and note that you can get a sample from Royal Mail - but it's a LOT more complex than you would expect - and therefore is very hard to use. And costs a lot.

from memory, i think the Royal Mail data includes OS grid refs, which are not the same as lats and longs, and is based on a different world-mapping scheme (I may easily be wrong here)

There are numerous on line services that will turn a post code into a lat/long at so much a read ie 2p or so. (you will get a free trial, and I think they send you back a KML file)

Or you can use mappoint, which has a one off cost, and I dont think can be licensed for server (multiple) use.

Not sure if google has a paid service for accurate readings.

 

[Dannyboy11]

Ok I will download the trial of MS MapPoint and then get back to you

 

[gemma-the-husky]

this is the ms mappoint tutorial i used

http://msdn.microsoft.com/en-us/library/aa493431.aspx


there is also a very useful mappoint forums, like this one.

http://www.mapforums.com

 

[Dannyboy11]

I have it now. I don't understand the tutorial at all, if I'm frankly honest.

How can I convert the post codes to lat / long using that? Or how do I incoroporate that into Access to calculate distances etc?

Sorry but I really do need a 'dummy' guide for it!!

 

[Dannyboy11]

Hi

I have a table of post codes and corresponding lat / long distances. It is only rough (using the above example the post code may be NN3 4GL but I just have NN3. This is perfectly ok for my use though).

Something perhaps a bit more simpler to start with...

I want to have a form that will see the full post code. I want to do a lookup through the relevant table using only the first part of the post code, which then returns long and lat into boxes.

I will then have the start long and end long and start lat and end lat values.

I can then try and use the example in the second post to try to figure out the maths required to calculate the distance between the two points.

Thanks

 

[John Big Booty]

You should be able to do it with a query and use the following as your criteria;

Like Left(Forms!FRM_FormName!StartPCode,3) & "*"

That should bring up all post codes that share the same 3 character prefix.