Distance and Bearing Query...I'm stumped

[The_Doc_Man]

There are approximation methods using projections that aren't any more terrible than Great Circle computations. Trig is trig no matter what you are subjecting to sines and cosines.

My more important point was that there is no way to eliminate records without comparing them in SOME way, and the OP has 240 million comparisons to make. Even if you compare Lat/Lon in degrees without any other math, you STILL need to form the Cartesian JOIN set. I don't know of any way to filter without looking at something about each point.

My comments also made it clear that the distance function could be a simple TRUE or FALSE result based on estimated distances. But there STILL is no fast way to do the comparison math without doing some form of distance computation. Or at least I don't see it if there is.

 

[sneuberg]

If the locations are randomly distributed maybe some gain could be achieved by dividing the problem up let's say in quarters. You could find the borders of the region by getting the maximums and minimums of the longitudes and latitudes add them and divide by 2 to get these new borders. You could add a small amount, e.g., 3 km, to make them overlap so that nothing was missed.

Generalizing this let's say

• Station Tower Data= m Records
• Cell Tower Data= n Records

and let's say you really lucked out and the sites were perfectly distributed. In that case the total computations would be:

4 * (m/4 * n/4) = m*n/4

or in this case the computations would be reduce from 240 million to 60 million. If the total area is large compared to 3 km it might pay to divide the problem into more areas.

Missing from this is the computation needed to divide them up. I think that might be

2*m + 2*n = 2*(m + n) or 166,000, i.e., small compared to the gain.

 

[jdraw]

Here's a link to more info than you thought you would ever read on distances on surfaces. A great reference, even if for a very focused issue.
I don't see a simple means of reducing the number of comparisons. Steve's suggestion may be the answer.
It seems to me that the Station Tower/CellTower is much like the problem I described earlier, but repeated X times.
If you had a loop where you took each Station lat/long and boxed it, and recorded those cells within D distance, you could identify those Cells (StationCellDistance), then eliminate them from the next iteration of that cycle. I haven't pursued this, and my guess is there may be minimal processing savings (even if the approach has some merit). At some point all Cells will have been tested/eliminated.
The other issue is that the box size could differ as you change latitude, but some testing with a few different values should give an approximation that could be used throughout.

Gooooood luck.

 

[jdraw]

I am returning the database supplied by sneuberg with a routine called GetDistance in moduleBoxing. Uses some sample lat/longs, and uses the boxing approach.

'This is an approach to determine which Station and Cell towers are separated by no more than X miles.
'It uses some Lat/Longs from the internet to create some sample Station and Cell data
'
' The approach uses (adds and subtracts) small values (parts of degrees) to a Station's Lat/Long
' to create a box. For each Station it compares all cell towers to see if the GreatCircleDistance
'is within the radius determined by the "box".
'This is a test to mock up the logic to do the comparison of 5000 Stations and 800 Cell towers.
'
'You can adjust the box dimensions by changing the BoxLat and/or BoxLong values
'A sample box size is produced for Harrisburg, PA and you can cancel the execution at that point.

I hope it's useful.

 

[dd92251]

Hi All,

I finally got it to work correctly by applying a combination of everyone's suggestions. The biggest issue was the Acos function that had to be created in Access VBA. After that was incorporated into the function, it started working.

Adding the SELECT DISTINCT to the query removed duplicates that were in the one table. The output is now, exactly what I needed.

A big "Thank You" to everyone who came to my rescue.

 

[MarkK]

Thanks for posting back with your success!

 

[jdraw]

dd92251,

Glad you resolved your issue.
It may benefit someone else in future if you posted your solution.