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

Dave,

You could run a query to find the bus stop closest to the user's GPS. Since latitude and longitude are perpendicular to each other, you can use the pythagorean theorem to solve for the distance between any two points (a user and a stop). Take the min() of the distance between the user and all stops and you have the shortest distance. Here is some pseudo-code (assumes stops table contains all the stops with latitude, longitude and that the variables &user_latitude and &user_longitude are passed in):

• Select any stop which is the minimum distance from the user SELECT s1.bus_stop_info FROM stops s1 WHERE sqrt(
  (s1.latitude - &user_latitude)^2 +
  (s1.longitude - &user_longitude)^2
  ) = (SELECT min(sqrt( (s2.latitude - &user_latitude)^2 + (s2.longitude - &user_longitude)^2 ) ) FROM stops s2 )

This solution doesn't solve a few problems such as what to do if two bus stops are equi-distant from the passenger, it doesn't take depth into account, and it doesn't account for no stop being within the GPS' precision, but this solution may be a close enough approximation to what you are looking for.

-Peter

Dave Patton <dpatton_at_remove-for-nospam.confluence.org> wrote in message news:<Xns9435B15C0D18Fmrzaphoddirectca_at_24.71.223.159>...

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