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

luke_airig_at_hotmail.com (Luke Airig) wrote in news:a066cf23.0311161211.4a93db18_at_posting.google.com:

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

There isn't sufficient information in the above data to answer your question, but you can get close.
You can break the problem down into discrete steps, but there may be features of your version of Oracle that allow for a more consise real-world solution.
1)
See if there are any records where the stop latitude(Slat) matches the boarding latitude(Blat). If not, use a different query to get at least one record, such as using a value to specify all records where the Slat is within "X" of Blat. 2)
Using the recordset from (1), use a similar process to find the record(s) where the stop longitude is closest to the boarding longitude.
3)
You may have to repeat the process with a larger value for "X" until you get at least one record, or with a smaller value if you get too many records. Providing you are working with distances typical of city bus routes(vs cross-country routes) you don't need to calculate actual distances that a difference in latitude or longitude represents - you can just use degrees, or radians.
4)
You now have all the records where the lat/lon of the stop is within a 'bounding box' centered on the boarding lat/lon. 5)
You can't answer your question without additional information. For example, you have to know the direction of travel of both the passenger and for the stop, otherwise you may select a stop that is on the wrong side of the street. There are also issues where bus lines intersect, or run parallel (within "X" distance).

-- 
Dave Patton
Canadian Coordinator, the Degree Confluence Project
http://www.confluence.org  dpatton at confluence dot org
My website: http://members.shaw.ca/davepatton/
Vancouver/Whistler - host of the 2010 Winter Olympics