"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 find the nearest five stores to a given store upon an adhoc request from a user via a form.

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

Can anyone give me any ideas how to get started. Is it possible to incorporate my Excel formulas in an Access 97 db? I use the RADIANS function and some trig (sine, cosine ect...) in my distance calculator.

 

[dcx693]

I've never calculated distances the way you are, but you can certainly continue to use those Excel functions if you want to move the data to Access. See my post here for an example: Call an Excel function from Access Of course, try to find native Access equivalent functions first, since they'll run must faster.

To find the five nearest stores, run a TOP VALUES query. Do a quick search of the Access online help and this forum for info on that.

 

[theprez]

All the trigonometric functions are available in Access. Use the store and postal codes tables like you are doing. You can then import your spreadsheet as a table or use the same formulas you used in Excel to give you the distances and then you probably want the bottom 5, being the shortest distances. To get bottom values or shortest, use the TopValue command on your distance, but make sure distance is your left most field in a query and that it is sorted in ascending order.

 

[Jaydeebe]

Thanks for your replies. I'm sorry, i don't think i explained myself properly. 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 it 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.

Is there a Access native function equivelent to RADIANS in Excel. I think calling excel functions would definately be to slow for what i ultimately want.

 

[neileg]

But the radians function only converts degrees to radians. Since there are 2*pi radians in a circle, the conversion is only radians=degrees*pi/180

In any event, why do you need to do the maths in radians? Won't degrees do?

 

[Jaydeebe]

But the radians function only converts degrees to radians. Since there are 2*pi radians in a circle, the conversion is only radians=degrees*pi/180

In any event, why do you need to do the maths in radians? Won't degrees do?

Ok fair point. I could do the radians calculation as you have stated. However, I am using radians because I don't think Excel's trig functions work with degrees, only radians.

 

[neileg]

Access also works in radians. Still not sure why that matters, though?

But can't you simplify the calculation anyway? Why not approximate the difference in lat and long in degrees to a distance and calculate the hypotenuse using Pythagorus' theorem. I know this won't return the exact distance, but the discrepancies will be of the same order of magnitude for geographically similar locations, and accurate enough for a 'nearest store to my home' calculation.

 

[Jaydeebe]

Access also works in radians. Still not sure why that matters, though?

But can't you simplify the calculation anyway? Why not approximate the difference in lat and long in degrees to a distance and calculate the hypotenuse using Pythagorus' theorem. I know this won't return the exact distance, but the discrepancies will be of the same order of magnitude for geographically similar locations, and accurate enough for a 'nearest store to my home' calculation.

Ok, that may be true for short distances, but for my own satisfaction I would like to use the more accurate equation so that it can be reapplied to future projects where the level of inaccuaracy will make a difference.

My spreadsheet works on "Great circle theory" accounting for the curvature of the earth. Eventually I would like to use more accurate locators than postal sectors as these sectors cover large areas. I would like to distinguish between a store being next door to a competitor and one on the other side of town or other end of the country.

Lets forget the radians, i have a new column in my table that gives this information. I think my problem is more to do with using trig in queries and tables. Do you have a link to any good resources for this kind of info?

 

[neileg]

Well I'm impressed that you're using great circle, but if you have performance issues you might later decide that this is a bit ambitious!

Basically, any formula that you have in Excel can be used in a calculated field in a query with almost no changes to the sytax. I think you'll find that Access has the same trig functions as Excel, at least the one's you're likely to need.

You may find that your performace is helped by creating a compiled function that you call in your queries, rather than explicitly including the calculation. However, as a prototype, I'd start with the basic query.

If you're really stuck, post the calculation and some sample data and I'll knock up a query for you.