| Sarita Moodie 2004-12-26, 9:01 pm |
| Hi Ian,
I know this is an old thread, but I just found it and want to add what
I know.
Its not a big deal to do great circle distances using SQL. Access
should be able to handle it fine. The query looks big, but thats not
important if you dont let it bother you.
The trick is to encode the great circle formula using SQL syntax. Look
here for the formulas:
http://williams.best.vwh.net/avform.htm
You may have to change the syntax to make it work with whatever
database you use.
Now that you can compute the distance, you can sort on this distance
using ORDER BY, and then take the top X values. Or you can easily put
it in a WHERE clause to show only the records within Z kilometers.
Now the problem with this approach is that the query will start to slow
down when you get into large record sets (your 32,000 is already fairly
large). It will be a lot faster if you use a database that allows
spatial indexing. When using such databases your queries can scale
much larger. New versions of MySQL, PostgreSQL, and Oracle all support
spatial indexes. Good luck!
-Robin Chauhan
Ian Pattison wrote:
> Hi,
>
> I'm trying to put together what I think is a fairly complex database
query.
>
> I have a table of about 32,000 latitiude and longitude pairs and I
need to
> be able to query for two different things:
>
> 1. All points that are within X kilometers (or miles for that matter)
of
> coordinate Y (both are supplied from an HTML form).
> 2. The nearest X points to coordinate Y (again, supplied by a form).
>
> The first should be simple enough... I already know how to measure
the
> distance between two points (once I translate all that trig into
perl) so
> all I need to do is run the query and discard any result that's
outside the
> circle. The second is tougher. Does anyone know how to sort a list
like
> that?
>
> Thanks,
>
> Ian
|