Re: Find a location given approximate latitude and longitude near the location

Luke Airig wrote:
>
> I have a gps-related Oracle problem that I can use some help with.
>
> Here is the scenario that I am trying address in Oracle 8.1.7.4:
>
> I have the following tables with latitude and longitude stored in decimal
> degrees:
>
> 1. Bus stops along with their exact latitude and longitude.
> 2. Bus riders along with the approximate latitude and longitude of where
> they boarded a bus. I cannot assume that I will have the exact
> coordinates, only a reasonably close approximation.
>
> Can anyone help me with a solution that, given the latitude and longitude
> where a rider boarded, will return the bus stop where they boarded? The
> coordinates where the rider boarded will be guaranteed to be within a specified
> distance of the bus stop latitude and longitude.
>
> This problem is outside the day-to-day programming issues that I normally deal
> with and I am woefully deficient in the math department. I'm totally at a loss
> as to where to begin, so specific calculations, formulas, sql and/or PL/SQL code
> will earn you my vote for a spot in heaven. :) In any case, ANY help or advice
> on how to proceed is much appreciated.
>
> TIA
Oracle's Locator (part of Intermedia, included wit hall 8i/9i licenses) and optional "Spatial" capability handles both Projected or a Geodetic system. (It comes with over 700 Open GIS Consortium projected coordinate systems and supports adding your own.)

Oracle9i does support most of the geodetic (curved earth) math you require. The relevant functions are in the Spatial manuals in the Oracle docs, but I'd strongly advise looking at Oracle's Spatial courses (either Instructor led or online at http://education.oracle.com/oln >> Online Library) as the subject is not easy to follow. (The Online Library is reasonably inexpensive and the Spatial courses in it are very good!)

Your question is resolved very easily using the LRS capability of Spatial. Locator requires some additional hoops (lacking LRS) but is also workable!

Note that there seems to be a major change in the definition of Locator with Oracle9i - according to the tables in the Appendix of the Spatial docco, Locator can handle complex geometries not, instead of single points in 8i!

HTH
/Hans Received on Sun Nov 16 2003 - 18:50:08 CST