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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialThe idea is that instead of looking for a bus stop inside a circle of a given diameter with its center at the coordinates the passenger informs (diameter=max distance between alleged boarding point and actual bus stop location), you look for the bus stop within a square with its center in the same spot, and which extends max distance to each cardinal point.

As this square is bigger than the circle, you may loose precision. Indeed, the circle is inscribed in the square.

The advantage is that you can find the bus stops with something like:

select [PertinentFields] from [TableOfBusStopCoordinates

which involves less mathematical operations and might (or might not) be quite faster, specially on big tables. (I'm not good at all at SQL so just in case it doesn't exist, or isn't called like that: Abs is meant to be the number without its sign).

However, notice that with both solutions you might get multiple bus stops unless you can warranty that the minimum distance between any two bus stops is greater than

a) the double of the max distance between reported and actual location of bus stops (for the circle, cookres method), or

b) greater than sqrt(8* sqr(MaxDist)) where sqr means square, and sqrt means square root (for this second method).

Hope it helps:

Jack

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

Programming

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

As such, the simplest, although I'm not sure the most efficient, thing to do is calculate the distance from each boarding point to each bus stop to find the nearest one.

The distance between any two points is:

( ((lat1 - lat2)**2) + ((long1-long2)**2) )**.5