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

Hans Forbrich <forbrich_at_yahoo.net> writes:

>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.

>Oracle9i does support most of the geodetic (curved earth) math you
>require.

You don't need to take into account the curvature of the Earth when deciding which bus stop someone used! If you convert from silly Long Lat to a square grid co-ordinate system (which the GPS units will supply if you ask them, so you can avoid the conversion), then the problem of distance reduces to simple Pythagorean triangles (square root of sum of squares of co-ordinate differences).

You could solve this problem in a good spreadsheet, if that's easier for you than writing a program.

--
Chris Malcolm cam_at_infirmatics.ed.ac.uk +44 (0)131 651 3445 DoD #205
IPAB,  Informatics,  JCMB, King's Buildings, Edinburgh, EH9 3JZ, UK
[http://www.dai.ed.ac.uk/homes/cam/]