Re: Querying distances between two coordinates
From: Charles Hooper <hooperc2001_at_gmail.com>
Date: Mon, 13 Jun 2011 12:48:33 -0700 (PDT)
Message-ID: <aff6098e-78f9-45e7-88a1-70fafa76aa1e_at_b1g2000yql.googlegroups.com>
On Jun 11, 12:37 pm, Jeremy <jeremy0..._at_gmail.com> wrote:
> In article <ba7de41c-7ce8-465a-bc20-67c4e2484871
> _at_f2g2000yqh.googlegroups.com>, hooperc2..._at_gmail.com says...
> > On Jun 10, 12:15 pm, Jeremy <jeremy0..._at_gmail.com> wrote:
> > > Hi this is a 10gR2 Standard Edition question.
>
> > > The requirement is to be able to calculate the distance between two co-
> > > oordinates expressed as latitude and longitude.
>
> > I vaguely remember teaching a class how to do this calculation of
> > distance around a spherical object in the early 1990s (with only a
> > calculator) - I could probably search and find the lesson plan which
> > is probably right next to the proof that 1=2. While I am searching
> > for that lesson plan, see if the following article is helpful:
> >http://www.movable-type.co.uk/scripts/latlong.html
>
> Thanks. Of course what I have failed to do is actually to ask the real
> question I wanted to answer - ultimately we need to be able to query a
> table to find the rows where the lat/long defined on those rows is
> within a user-specified radius. Are there already functions witin Oracle
> 10gR2 SE that provide this capability efficiently?
>
> --
> jeremy
Date: Mon, 13 Jun 2011 12:48:33 -0700 (PDT)
Message-ID: <aff6098e-78f9-45e7-88a1-70fafa76aa1e_at_b1g2000yql.googlegroups.com>
On Jun 11, 12:37 pm, Jeremy <jeremy0..._at_gmail.com> wrote:
> In article <ba7de41c-7ce8-465a-bc20-67c4e2484871
> _at_f2g2000yqh.googlegroups.com>, hooperc2..._at_gmail.com says...
> > On Jun 10, 12:15 pm, Jeremy <jeremy0..._at_gmail.com> wrote:
> > > Hi this is a 10gR2 Standard Edition question.
>
> > > The requirement is to be able to calculate the distance between two co-
> > > oordinates expressed as latitude and longitude.
>
> > I vaguely remember teaching a class how to do this calculation of
> > distance around a spherical object in the early 1990s (with only a
> > calculator) - I could probably search and find the lesson plan which
> > is probably right next to the proof that 1=2. While I am searching
> > for that lesson plan, see if the following article is helpful:
> >http://www.movable-type.co.uk/scripts/latlong.html
>
> Thanks. Of course what I have failed to do is actually to ask the real
> question I wanted to answer - ultimately we need to be able to query a
> table to find the rows where the lat/long defined on those rows is
> within a user-specified radius. Are there already functions witin Oracle
> 10gR2 SE that provide this capability efficiently?
>
> --
> jeremy
Have a look at the following article:
http://hoopercharles.wordpress.com/2011/06/13/calculate-the-distance-between-two-latitudelongitude-points-using-plain-sql/
An up-front warning - the method is CPU intensive if there are many rows in the database table.
Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Mon Jun 13 2011 - 14:48:33 CDT