Re: Querying distances between two coordinates
From: Charles Hooper <hooperc2001_at_gmail.com>
Date: Fri, 10 Jun 2011 11:09:40 -0700 (PDT)
Message-ID: <ba7de41c-7ce8-465a-bc20-67c4e2484871_at_f2g2000yqh.googlegroups.com>
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.
>
> Q1) How would the latitude and longitude be stored in an Oracle table
> (what data types)?
>
> Q2) What would an example query look like?
>
> Q3) Is this even possible with 10gR2 Standard Edition?
>
> Just to be clear (!) this is the sort of simplistic representation I
> guess
>
> item lat long
> ---- --- ----
> P1 50.716667 -1.883333
> P2 51.023332 -1.872231
>
> The format of the lat/long are as I have been informed they are recorded
> in a remote system with which we are needing to integrate.
>
> I would want to be able to calculate the straight line distance between
> P1 and P2.
>
> Thanks for any pointers!
>
> --
> jeremy
Date: Fri, 10 Jun 2011 11:09:40 -0700 (PDT)
Message-ID: <ba7de41c-7ce8-465a-bc20-67c4e2484871_at_f2g2000yqh.googlegroups.com>
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.
>
> Q1) How would the latitude and longitude be stored in an Oracle table
> (what data types)?
>
> Q2) What would an example query look like?
>
> Q3) Is this even possible with 10gR2 Standard Edition?
>
> Just to be clear (!) this is the sort of simplistic representation I
> guess
>
> item lat long
> ---- --- ----
> P1 50.716667 -1.883333
> P2 51.023332 -1.872231
>
> The format of the lat/long are as I have been informed they are recorded
> in a remote system with which we are needing to integrate.
>
> I would want to be able to calculate the straight line distance between
> P1 and P2.
>
> Thanks for any pointers!
>
> --
> jeremy
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
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 Fri Jun 10 2011 - 13:09:40 CDT