|
|
Posted by Steve on 12/01/06 17:43
"NC" <nc@iname.com> wrote in message
news:1164942233.834933.104720@h54g2000cwb.googlegroups.com...
| 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. ;^)
or another quick and fairly accurate caculation:
distance = sqrt(
(
69.1 *
(lat2 - lat1)
) *
2 +
(
69.1 *
(lon2 -lon1) *
cos(lat1 / 57.2958)
) *
2
)
much quicker but there is an accuracy issue of about 10% or slightly more.
for the record...here's the full formula using the more accurate, great
circle formula as before. broken down specifically this time.
r = 3437.74677 (nautical miles)
r = 6378.7 (kilometers)
r = 3963.0 (statute miles)
x/57.2958 = 180 / Pi (degrees to radians)
distance = 3963.0 *
arccos(
sin(lat1 / 57.2958) *
sin(lat2 / 57.2958) +
cos(lat1 / 57.2958) *
cos(lat2 / 57.2958) *
cos(
lon2 / 57.2958 -
lon1 / 57.2958
)
)
i am tempted to try the quadratic formula as it is supposed to be even more
accurate.
for the op though, here's a very good reference that not only contains
working javascript for doing many different formulas, it also has javascript
to display the points using google maps (including drawing a line on the map
between the two points and displaying the distance).
http://www.movable-type.co.uk/scripts/LatLong.html
hth,
me
[Back to original message]
|