"Find my nearest" problem

[jaydeebe]

I have a table that contains a storelist and another that contains postal sectors and their longitude and latitude coordinates.

I have made an Excel spreadsheet that calculates the distance between two postal sectors based on their longitude and latitude coordinates.

I would like to convert my Excel spreadsheet into an Access query, if possible.

What I would ideally like is for a user to be able to run a find nearest query themselves from a form in the DB. I'm trying to find a way to run this query quickly. At the moment I see the problem as this -

I have 3000 postal sectors and 10000 stores. Those 10000 stores fall within the 3000 sectors. To find the nearest store i would match a store to postal sector and have to run my distance calculation on one sector to all 2999 other sectors before i can get the nearest five. I see two ways of doing this -

1. I run all the distance calculation for all 3000 sectors and then add the data to the DB. This would be very time consuming and produce enormous amounts of data.

2. Do the 2999 distance calculations when processing a query from a user. I'm not sure if this is possible or if it would be too slow for everyday use.

I think calling excel functions would definately be to slow for what i ultimately want.

Any pointers tips greatly appreciated!

Jaydeebe BSC (hons) MCP

 

[MichaelRed]

There are existing threads on this topic, so the first foray might well be into the search engine. I do not recall the specifics of the posts / threads soyour answer may not be found.

I think I would set the process up to look more at the Postal (Zip) code. At least in the U.S. they are laid out in some reasonable order. So searching by the various "digits" of the postal code would probably narrow the number of actual calculations which are required rather dramatically. If, as you state, all of the locations fall within the 3K zip codes, a hit on one of those should reduce the number of calcs required to an almost trivial number.

Sepoeratly, I would think that a postal code correlation "map" would be useful. In effect, each postal code would have some number of "neighbors" which might have store locations closer than one within the postal code. Having the map of neighbors would enable you to expand the searce to "close by" areas from a given postal code.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[grnzbra]

Join the tables in a query to get the name (or store number or ID) and its lat and long. Then create another query with this query being brought in twice AND NOT JOINED. This will give you a recordset where each store has a record for every other store. Create a calculate field that calculates the hypotinuse of a triangle A squared = (B squared) + (C squared) (you may have to convert Deg, Min, Sec to Min or Sec first.) Sort on the calculated field in descending order and select the top 1.

I haven't tried it, but it seems like it would work. Don't know if it is efficient.

 

[grnzbra]

Oh, yeah, in the second query, you would use the original store as the criteria, so you would only get a set 10,000 records. You could also calculate the difference in zip codes and set the criteria to less than a certain amount. You'd have to play with this so that you always ended up with at least one record - perhaps the difference criteria could also be controled by the user.

 

[jaydeebe]

Ok, i'll have another look around the old threads. I see your point with the neighbouring postal codes. I had been thinking about creating an index that identifies these. Obviously codes that start with the same letters are relatively close to each other, but it could take some time to work out the rest for the 3000 sectors a currently have (and potentially the 1.7 millon UK postcodes). I don't know how it is in the US, but here we have to pay for that kind of data, whereas, i've seen other countries postal code information freely available on the web. I have seen some public projects that seem to be addressing some of the issues (i got my 3000 postal sector list from them, thanks). I may start my own public web project to get some help with all the data processing.

Jaydeebe BSC (hons) MCP

 

[jaydeebe]

grnzbra - thanks for your reply.

I'm just trying to match up my postal areas to postal sectors.

Sorry, i don't understand what you mean by bringing the query in twice.

I currently use a more accurate distance calculator than plain pythag with some trig functions and would like to incorporate this.

Jaydeebe BSC (hons) MCP

 

[jaydeebe]

I have two unrelated tables that i need to join. The postal area in the locator table (Postal Area, Long, lat, radians)(in this format CM11)is related to the postal sector (CM11 1, CM11 2 ect...) in a sector table (postal sector, region) and this would be a one to many relationship. I can't run a query without the tables being reltated so how could I do this?

Jaydeebe BSC (hons) MCP

 

[grnzbra]

I just created this in the query designer mode in access by bringing in the sales table and then bringing it in again and selecting the Sale_No from each. This is the resultant SQL

SELECT Sales.Sale_No, Sales_1.Sale_No
FROM Sales, Sales AS Sales_1
WITH OWNERACCESS OPTION;

If you have a better formula (function) than Pythag, use that instead and sort by that calculation descending.

I should have realized you weren't here in the US when you said Postal instead of Zip. Not sure how you would limit things with the code field. However if you used the office number in your query where I used Sale_No, you could feed that in as a criteria and you would get 10,000 records back, each with the calculation. Or perhaps you should just use the unjoined query to select the 10k records you want and then do the calc in a third query.

 

[grnzbra]

OOps. You join them in the first query so that each store has its location figures. Then you run a second query with the first brought in twice with the initial store entered as a selection criteria. That way you have each store matched up to that one store. Otherwise you will have every store matched to every other store (10,000 vs 100,000,000 records) Feed this into a third query which does the calculation (either in the query or setting the field equal to the function) and sort on the result in descending order. Select the top 1.