Posted by NC on 12/01/06 03:03

Steve wrote:
>
> i had saved this calculation some time ago. NC posted it here.
> this formula will take long/lat and give you distance between
> two points. (acos, cos, and sin are supported functions in most
> db's...i'd do it in a query where it uses your lat/long points as
> input and and the criteria is where distance is <= your max
> desired distance from any given store.

The disadvantage of this approach is that you have to compute
the distance from a given point to EACH LOCATION STORED IN
THE TABLE, and then filter and sort based on that computed
value. This could be CPU-intensive...

There's a better way. First, you use trigonometry to compute
boundaries of a "square" (trapezoid, really) with a center at your
location and side that equals twice the maximum distance sought.
Then you query the database to retrieve all locations within that
"square". Something like this:

SELECT * FROM locations
WHERE
latitude > [southern boundary] AND
latitude < [northern boundary] AND
longitude > [eastern boundary] AND
longitude < [western boundary];

(Some minor changes will be required in cases when your
"square" overlaps a pole or the 180th meridian.)

Finally, if you are so inclined, you can exclude locations that
are in the corners of the square by computing (in your script)
distance between the center location and the location retrieved
from the database and eliminating the locations that are too
far from the center. With this approach, you can take full
advantage of indexing...

> hth...and thanks to NC for the math. ;^)

No problem. :)

Cheers,
NC

[Back to original message]