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

Chris Malcolm wrote:
>
> Hans Forbrich <forbrich_at_yahoo.net> writes:
>
> >Luke Airig wrote:
>
> >> 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
> >> will earn you my vote for a spot in heaven. :) In any case, ANY help or advice
> >> on how to proceed is much appreciated.
>
> >Oracle9i does support most of the geodetic (curved earth) math you
> >require.
>
> You don't need to take into account the curvature of the Earth when
> deciding which bus stop someone used! If you convert from silly Long
> Lat to a square grid co-ordinate system (which the GPS units will
> supply if you ask them, so you can avoid the conversion), then the
> problem of distance reduces to simple Pythagorean triangles (square
> root of sum of squares of co-ordinate differences).
>
> You could solve this problem in a good spreadsheet, if that's
> easier for you than writing a program.
>
 

Sheesh! Solve it even easier with Oracle Locator.

A single select statement using the SDO_NN (nearest neighbor) function will resolve this for each/any/all riders depending on Where clause!

He says he's got the data in Lat/Long. Implies that it's already Geodetic (therefore takes curvature into account). Just needs to add a MDSYS.SDO_GEOMETRY col to his table and UPDATE tables SET (coords << point_type/lat/long). Received on Mon Nov 17 2003 - 08:14:52 CST