Link to home
Start Free TrialLog in
Avatar of luke_airig
luke_airig

asked on

Find a location given approximate latitude and longitude near the location


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 would be welcome. In any case, ANY help or advice on how to proceed is much appreciated.

TIA

ASKER CERTIFIED SOLUTION
Avatar of cookre
cookre
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Callandor
I think you will need to create an additional table riderBusStopDistance that has riderID, busStopID, and distance (using cookre or JackNaif's calculation).  Then you create one more temp table with SELECT distinct(riderID), min(distance) into minRiderDistance from riderBusStopDistance group by riderID.  

Your answer comes from
SELECT a.riderID, a.busStopID, a.distance from riderBusStopDistance a, minRiderDistance b
WHERE a.riderID = b.riderID AND a.distance = b.distance
How's it going?