# 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:

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