Querying distances between two coordinates

#1

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

#2

On Fri, 10 Jun 2011 17:15:05 +0100, Jeremy wrote:

Quote:

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.

dist=sqrt(power(x2-x1,2)+power(y2-y1,2));

Quote:

Q1) How would the latitude and longitude be stored in an Oracle table
(what data types)?

As ordered pairs of coordinates.

Quote:

Q2) What would an example query look like?

See above.

Quote:

Q3) Is this even possible with 10gR2 Standard Edition?

Yes.

Quote:

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!

The first pointers were given by certain guy named Pitagoras. For quite
some time, he has been a bane and a terror of many high school children
around the world. If it was up to me, I'd send the seal team 6 to
dispatch him. Unfortunately, he's not on any terror lists, a grave
omission if you ask me.

--
http://mgogala.byethost5.com

#3

On Jun 10, 12:15*pm, Jeremy <jeremy0... (AT) gmail (DOT) com> wrote:

Quote:

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

This sounds suspiciously like homework but if I were you I would do
some research on Oracle Spatial perhaps.

If lat and long are numbers then ... geez ... wonder what data type
would be used?

When in doubt try http://asktom.oracle.com ...

#4

On Jun 10, 12:15*pm, Jeremy <jeremy0... (AT) gmail (DOT) com> wrote:

Quote:

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.

#5

On Fri, 10 Jun 2011 11:09:40 -0700, Charles Hooper wrote:

Quote:

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

The easiest way is to assume that the Earth is flat. In that case, you
have Pythagorean theorem and linear algebra. If anyone tries to
contradict, they should expect the Spanish inquisition. I know that
nobody expects Spanish inquisition whose primary weapons are....

--
http://mgogala.byethost5.com

#6

On Jun 10, 6:00*pm, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:

Quote:

The easiest way is to assume that the Earth is flat. In that case, you
have Pythagorean theorem and linear algebra. If anyone tries to
contradict, they should expect the Spanish inquisition. I know that
nobody expects Spanish inquisition whose primary weapons are....

I found the lesson plan. I see that part of the point of the lesson
was to destroy Euclid's Elements, which were introduced 23 centuries
ago. :-)

Even though the lesson plan is 3 pages long, the details are a little
sketchy. Distance in miles:
c0 = cos^-1*(cos(90-alpha1)*cos(90-alpha2) + sin(90-alpha1)*sin(90-
alpha2)*cos(theta1-theta2))
distance = c0 * (2pi/360) * 3960 miles

The above appears to be similar to one of the Excel formulas found on
the webpage that I referenced:
=ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS( lon2-lon1))*6371 KM

My notes state that it is important to use radian measurements because
those measurements have a direct correspondence with arc distances.

For verification:
Prague (14 degrees 26 minutes east, 50 degrees 5 minutes north)
Rio de Janeiro (43 degrees 12 minutes west, 22 degrees 57 minutes
south)
= 6152 miles

(119 degrees 48 minutes west, 36 degrees 44 minutes north)
(88 degrees 30 minutes west, 42 degrees south)
= 5789.38 miles

Wow, I sure have forgotten a lot of math!

---
The proof of 1=2 is on my blog.

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.

#7

On Fri, 10 Jun 2011 16:42:42 -0700, Charles Hooper wrote:

Quote:

I found the lesson plan. I see that part of the point of the lesson was
to destroy Euclid's Elements, which were introduced 23 centuries ago.

Stable version, still used in profusion today, at least in the form of
linear algebra who was introduced by Grassman, Hamilton and Cayley.
However, it's incredible what Google can turn out these days:

http://www.movable-type.co.uk/scripts/latlong.html

There is a calculator, implemented in JavaScript, along with the formula.
I have to confess not knowing the formula. My formula collection, called
"Bronstein", is long forgotten since I graduated in 1984.

--
http://mgogala.byethost5.com

#8

In article <b603279c-b4df-479f-ad9e-
5c063f34b8ce (AT) z37g2000vbl (DOT) googlegroups.com>, hurleyjohnb (AT) yahoo (DOT) com
says...

Quote:

On Jun 10, 12:15*pm, Jeremy <jeremy0... (AT) gmail (DOT) 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

This sounds suspiciously like homework but if I were you I would do
some research on Oracle Spatial perhaps.

Not sure why it sounds like homework, I am aware of "spatial" otpions
and "locator" but havent used them before.

Quote:

If lat and long are numbers then ... geez ... wonder what data type
would be used?

Well actually the question is because I have usually seen lat/long
expressed in degrees minutes and seconds but as you can see these are
numbers (and I kinda figured that I might just be able to store those in
a number column)...

Quote:

When in doubt try http://asktom.oracle.com ...

Have done many a time. But I also find this an excellent ng where people
are happy to share their knowledge.

--
jeremy

#9

In article <ba7de41c-7ce8-465a-bc20-67c4e2484871
@f2g2000yqh.googlegroups.com>, hooperc2001 (AT) gmail (DOT) com says...

Quote:

On Jun 10, 12:15*pm, Jeremy <jeremy0... (AT) gmail (DOT) 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

#10

In article <6ff6734c-6844-439b-a65e-48ebb4a54056
@f2g2000yqh.googlegroups.com>, hooperc2001 (AT) gmail (DOT) com says...

Quote:

On Jun 10, 6:00*pm, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:
The easiest way is to assume that the Earth is flat. In that case, you
have Pythagorean theorem and linear algebra. If anyone tries to
contradict, they should expect the Spanish inquisition. I know that
nobody expects Spanish inquisition whose primary weapons are....

I found the lesson plan. I see that part of the point of the lesson
was to destroy Euclid's Elements, which were introduced 23 centuries
ago. :-)

Even though the lesson plan is 3 pages long, the details are a little
sketchy. Distance in miles:
c0 = cos^-1*(cos(90-alpha1)*cos(90-alpha2) + sin(90-alpha1)*sin(90-
alpha2)*cos(theta1-theta2))
distance = c0 * (2pi/360) * 3960 miles

The above appears to be similar to one of the Excel formulas found on
the webpage that I referenced:
=ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS( lon2-lon1))*6371 KM

My notes state that it is important to use radian measurements because
those measurements have a direct correspondence with arc distances.

For verification:
Prague (14 degrees 26 minutes east, 50 degrees 5 minutes north)
Rio de Janeiro (43 degrees 12 minutes west, 22 degrees 57 minutes
south)
= 6152 miles

(119 degrees 48 minutes west, 36 degrees 44 minutes north)
(88 degrees 30 minutes west, 42 degrees south)
= 5789.38 miles

Wow, I sure have forgotten a lot of math!

Many thanks. The "real" question is more about how one actually queries
the db ("radius search") efficiently - this was step 1 in understanding
some basics

--
jeremy