Solved

Find a location given approximate latitude and longitude near the location

Posted on 2003-11-16
4
1,239 Views
Last Modified: 2008-03-04

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

0
Comment
Question by:luke_airig
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
cookre earned 250 total points
ID: 9759906
I don't know PL/SQL, but can make the observation that given the relative small scale, you can consider lat/long as rectangular coordinates.  

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


0
 
LVL 1

Assisted Solution

by:JackNaif
JackNaif earned 250 total points
ID: 9760574
An alternative algorithm to cookre's (ith which there is nothing wrong) but that might be better suited to use with databases is to use, instead of a distance, a longitude delta, and latitude delta.

The 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] where (Abs(LatOfBusStop - LatOfPassenger) < MaxDist) _and_ (Abs(LongOfBusStop - LongOfPassenger) < MaxDist)

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
0
 
LVL 69

Expert Comment

by:Callandor
ID: 9765839
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
0
 
LVL 22

Expert Comment

by:cookre
ID: 9889082
How's it going?
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
word0 challenge 4 68
Please explain: Aspect Oriented Programming 2 92
detecting  the added row index in a datagridview 3 70
Full Screen problem when auto scale IFRAME 2 79
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This is about my first experience with programming Arduino.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question