View Full Version : 'Find Nearest Store'
01-24-2003, 08:49 AM
What I would like to do is someone able to put their postcode in and see where there nearest 'store' (well airfield) is. This will be done on UK postcodes hopefully. I have got three airfields that I want to list, is there a nice algorithm to compare two postcodes?
Hopefully like http://www.dixons-group-plc.co.uk/webcode/stores/insmap_dix.htm where you enter your postcode bit.
Probably. By comparing the coördinates of the citys with the airports. This implies you have the latitudes and longitudes of citys and airports in a database. You could then compare the length of the vectors (3) from the city to each airport. (of coarse, this is a lineair vector) I don't know if such data is available in db-format, but it'll surely exist.
But i presume there are crostab tables available, that display the distances between each city (postalservices and traincompagnies in belgium used them) All you have to do then is take a section (3 x 'Number of cities') ans store that in a table.
Or check up on some route-planners. They probably can supplie these data. (or could be integrated in your app to display route en distance to airport. for instance: search for an online route-planner that uses the querystring to submit to the searchpage, and then build your own querystring to the page they are submitting to)
01-25-2003, 12:13 AM
I'd also look up "Great Circle Distance" and "Longitude Latitude Distance Calculation" on google, if you can use a "great circle" calculation like for this it should be more accurate, but probably not necessary if the airports are not very far apart, globally speaking. I stink at math, but the formulas didn't even look particularly intimidating to me. Haven't done anything with them yet, though. ;)
01-26-2003, 04:32 AM
I stink at math
You are bad at something? Really?
Join the club though, I rule at physics but when I enter the math room door by brain ejects itself and waits until I come out again.
01-26-2003, 12:16 PM
how far are your airports apart? are they liverpool, london and edinborough? depending on there location i have a plan
01-26-2003, 06:21 PM
No they are Goodwood (Chichester), White Waltham (Maidenhead) and Gloucester.
Why what was the idea?
01-26-2003, 08:35 PM
Your best bet if you would like to do this is to contact a company like Experian or CACI (they are geo-demographic companies).
I've done a project like this with them in the past. You've got two basic options.
1. Get them to supply you the data
2. Purchase the server component from Experian.
If you buy the data to code yourself you're looking at a pretty massive project/server. There are over 1.9million postcodes in the UK. Add to that the fact that the Post Office update the postal address file every 3 months with all the postcode changes it's a real headache.
Buying the server component isn't cheap but then you just use the Experian data which is constantly updated and the coding is pretty simple.
Hope this helps.
01-26-2003, 09:13 PM
if you divide the UK into three zones so the nearest apart is in the same zone as your area.
If you look at post codes there are
if you consider the following:
Acrise Place Kent CT18
East Lavington West Sussex GU28
Edinburgh City of Edinburgh EH9
so as i was saying you could look at the first bit of the postcode e.g. CT18 and compare it with a list of postcodes. So that if it matches on a list it will show the list header which is the airport.
there is a list of all postcodes in the Uk available at http://www.brainstorm.co.uk/utils/post-codes.txt
it only contains the first part though.
So if you compare the first bit with the postcodes in the zones and it mathces your nearest airport is Blah.
its still a mamouth task.
01-26-2003, 09:34 PM
Yep - have to agree with scroots....
Using scroots' method (i.e. postal districts) would certainly reduce the data volume and distance calculation time, but raises all sorts of other problems - mostly to do with size of the postal district.
In some more remote parts of the country a postal district can quite large, whereas in a city like London they can be very small.
Have a look at what solutions are available as hosted solutions - that might be the best bet.
01-26-2003, 09:43 PM
domincall you might have slightly the wrong end of the stick.
so if the postcode(or first part of it) was enterd as TM3 it would show as Airport 3.
How has postcode area size have a problem? there are possibley a few postcode slap bang in the middle which would return two results.
01-26-2003, 09:45 PM
or in some cases only the first two letters of the postcode may be required as the post code PH*****refers to highland and kinross.
01-26-2003, 09:59 PM
Yep - did get the wrond end of the stick
Assumed that we were actually trying to calculate distances... which I guess would be technically the most accurate way to find the nearest...
... but given that there are only 3 airports then probably not a problem... if it was a store in every other town then might be a different proposition.
Good spot scroots
don't lissen to them! Please, do it the hard way (writing a simple algorithm to compute the distance) and publish it here, so we can all use it for whatever app. This sort a thing is highly reusable.
If you can supply the data (Degrees/Minutes/Seconds of all englich towns), then i'll write the algorithm.
01-27-2003, 09:26 AM
I agree with you - it is highly re-usable - but HUGE.
If you want to be able to calculate distance accurately then it needs to be done at full postcode level (i.e. SE4 1PL). There are approximately 1.9 million unit postcodes in the UK.
To calculate the distance, you therefore need to store both the postcodes and the degrees, minutes, seconds in the database. Believe me, it becomes HUGE.
Then to calculate the distance you need to search the database for the postcodes of the starting and finishing positions and the run the calculation algorythm. The calculation itself is the easy bit - LOL.
Taking it out of the context of the 3 airports scenario, if you want to enter 2 postcodes and get the distance between 2 user entered postcodes then the volume of the data store is massive and processing power to complete the data fetch and calculation in a 'user' reasonable time is also massive.
I've been involved in a project before doing just this and in the end we decided to go for the paid-for hosted solution. Partly for time, partly for efficacy but mainly because we knew the continual updating of the Postal Address File (PAF File) was managed remotely by a company that specialises in geographic mapping and processing.
Otherwise, you need to upload a new PAF File of 1.9 million records with co-ordinates at least every quarter (if not more often) and test to make sure everything is OK.
However, if anyone does a quick and easy solution I'd be more than happy to 'borrow' it :D
Not in any way wishing to rain on your parade - I tried the same approach and it just became too huge.
Well. Huge is good. I like huge. Kind a nice to say : “My database has 2 million records and I want to capitalize all cell’s …” :-)
Serious: I’d have a go at it, if you can supply the data.
Of coarse, not on a 2 mil records table. I’d set up a relational databasemodel that breaks down the computing process + create different processes.
For instance: if someone wants to know the exact distance between to citys, then his request will be processed differently then if someone wants to know which is the nearest airport. The second question can be broken into different pieces. Nearest big city in more a less right direction, nearest airport to this big city. You see ? The computing is not completely correct, but the answer will be correct. What’s more: when you drive to that airport, you will probably use the same rout as was used in the computing. (You could even return multiple alternatives …)
It would be an app with a sort of MDDB datacubes (preprocessed information) than can give the desired data really fast + some computing on this data.
If the question can’t be answered by the MDDB model, then there will be used other processes, running on a relational or hierarchical database (the same one that was used to build the MDDB’s). And the computing should still be real fast.
Of coarse, typing in two area codes and press submit, will create problems (like selecting on a 2 mil table).
I wouldn’t take that option (since typo’s and stuff can create serious problems + response time would be to high). I’d break it down into : select state, district and at the lowest level you then select this 1 record out of a table with a few thousand records. Just retrieve the coordinates (possibly precomputed into decimal numbers) + same thing for other city. (with each selection, the cube that needs to be searched, is losing a dimension)
From there on, its easy co compute the distance
This way you can also choose how accurate the result needs to be, by selecting your own scale (postcode or city etc).
To ensure re-usability, all data to the actual searching and computing pages will be in the querystring, so other app’s can link to it with the data in the url, and display the results in a frame on there page. (or send it back to one of there pages with the results in the querystring)
Just some thoughts.
01-27-2003, 06:11 PM
I am aware of it being a mamoth task, who is going to volunteer to input them (j/k) Also if you were to search 1.9 million records how would it take?
01-27-2003, 07:30 PM
We never got that far...
The search took too long and eventually crashed our test SQL server - and that was the only query running on it.
So we just went out and bought the solution instead.
01-27-2003, 07:59 PM
I put the post code prefix into word and it was 495 pages long, using find and replace to elimated duplicate postcode beginings for about 10 postcodes it whitled it down to 488 pages. I might keep going.
01-27-2003, 10:05 PM
Me thinks that it will be better for a paid service! We co-locate the a huge server from a large Telco and they have a huge SQL database of postcodes for checking.
01-27-2003, 11:06 PM
A possible short cut solution is to use postal sectors...
All postcodes are made up of four segments:
Area - e.g. SE
District - e.g. SE4
Sector - e.g. SE4 1
Code - e.g. SE4 1PL (my postcode)
For those that can't remember, there's a simple way to remember the running order:
All Dogs Scare Cats
Just a useful reminder if ever you need to know again.
While there are about 1.9 million postcodes, there are only about 9,800 postal sectors in the UK (exc. N. Ireland). Postal sectors can vary a lot in size - 1 block in the centre of a city to quite a large area in sparse rural communities.
Each postal sector has what's called the postal sector centroid (as defined by Royal Mail).
Despite the size differences, you could calculate a distance +/- 5 miles for example between each postal sector centroid, i.e.
ask for two postcodes, concatenate to remove the space, remove the last two bytes that make the full code, search against the database table of only 9,800(ish) records and then perform the calculation.
It's less accurate but a lot quicker.
Just a thought.