1562 views

Skip to first unread message

Jun 10, 2011, 12:15:05 PM6/10/11

to

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

Jun 10, 2011, 12:37:16 PM6/10/11

to

On Fri, 10 Jun 2011 17:15:05 +0100, Jeremy 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.

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

>

> Q1) How would the latitude and longitude be stored in an Oracle table

> (what data types)?

As ordered pairs of coordinates.

>

> Q2) What would an example query look like?

See above.

>

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

Yes.

>

> 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.

Jun 10, 2011, 1:28:09 PM6/10/11

to

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 ...

Jun 10, 2011, 2:09:40 PM6/10/11

to

On Jun 10, 12:15 pm, Jeremy <jeremy0...@gmail.com> wrote:

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.

Jun 10, 2011, 6:00:46 PM6/10/11

to

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

> 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....

Jun 10, 2011, 7:42:42 PM6/10/11

to

On Jun 10, 6:00 pm, Mladen Gogala <n...@email.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....

> 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.

Jun 10, 2011, 9:11:04 PM6/10/11

to

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

> 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.

Jun 11, 2011, 10:21:12 AM6/11/11

to

In article <b603279c-b4df-479f-ad9e-

5c063f...@z37g2000vbl.googlegroups.com>, hurle...@yahoo.com

says...

>

> On Jun 10, 12:15 pm, Jeremy <jeremy0...@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

>

> This sounds suspiciously like homework but if I were you I would do

> some research on Oracle Spatial perhaps.

5c063f...@z37g2000vbl.googlegroups.com>, hurle...@yahoo.com

says...

>

> On Jun 10, 12:15 pm, Jeremy <jeremy0...@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

>

> 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.

>

> 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)...

>

> 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

Jun 11, 2011, 12:37:31 PM6/11/11

to

In article <ba7de41c-7ce8-465a-bc20-67c4e2484871

@f2g2000yqh.googlegroups.com>, hoope...@gmail.com says...

>

> On Jun 10, 12:15 pm, Jeremy <jeremy0...@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.

> >

@f2g2000yqh.googlegroups.com>, hoope...@gmail.com says...

>

> On Jun 10, 12:15 pm, Jeremy <jeremy0...@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

Jun 11, 2011, 1:44:59 PM6/11/11

to

In article <6ff6734c-6844-439b-a65e-48ebb4a54056

@f2g2000yqh.googlegroups.com>, hoope...@gmail.com says...

@f2g2000yqh.googlegroups.com>, hoope...@gmail.com says...

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

Jun 11, 2011, 8:21:48 PM6/11/11

to

On Sat, 11 Jun 2011 01:11:04 +0000, Mladen Gogala wrote:

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

Hmmm, I noticed that you gave the same link. I apologize. Sorry Charles,

I'll have to read more carefully.

Jun 13, 2011, 3:48:33 PM6/13/11

to

On Jun 11, 12:37 pm, Jeremy <jeremy0...@gmail.com> wrote:

> In article <ba7de41c-7ce8-465a-bc20-67c4e2484871

> @f2g2000yqh.googlegroups.com>, hooperc2...@gmail.com says...> In article <ba7de41c-7ce8-465a-bc20-67c4e2484871

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.

Jun 14, 2011, 5:53:58 AM6/14/11

to

In article <aff6098e-78f9-45e7-88a1-

70fafa...@b1g2000yql.googlegroups.com>, hoope...@gmail.com says...

70fafa...@b1g2000yql.googlegroups.com>, hoope...@gmail.com says...

> 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.

>

Blimey, you really went to town on this one!!

With regard to the "real question" I was trying to answer (radius-

search) one of my team came up with a solution storing the lat/long in a

table column of type "mdsys.sdo_geometry" - I believe this does not

require any Oracle options - available in SE.

Then insert data into the column using a call such as:

mdsys.sdo_geometry

(2001, -- This is the SDO_GTYPE attribute and it is set to

-- 2001 when storing a two-dimensional single point

-- such as a customer's location.

8307, -- This is the spatial reference system ID

-- (SRID), 8307 corresponds to "Longitude /

--Latitude (WGS 84)

mdsys.sdo_point_type (-0.771618, 51.579601, null),

null,

null)

Then query using the function

"sdo_within_distance"

I hope this information is useful to others; if a fuller example is

required we can provide.

Further comments / advice gratefully received.

--

jeremy

Jun 14, 2011, 3:06:49 PM6/14/11

to

Jeremy:

... I was trying to answer (radius-

search) one of my team came up with a solution storing the lat/long

in a

table column of type "mdsys.sdo_geometry" - I believe this does not

require any Oracle options - available in SE.

Then insert data into the column using a call such as:

mdsys.sdo_geometry

(2001, -- This is the SDO_GTYPE attribute and it is set

to

-- 2001 when storing a two-dimensional single

point

-- such as a customer's location.

8307, -- This is the spatial reference system ID

-- (SRID), 8307 corresponds to "Longitude /

--Latitude (WGS 84)

mdsys.sdo_point_type (-0.771618, 51.579601, null),

null,

null)

... Then query using the function "sdo_within_distance"

JBH notes:

***********

Sounds to me as if you are using Oracle spatial features and will need

to be licensed appropriately.

Oracle makes it easy to have features/options appear in database

instances that you may not have appropriately licensed. Up to the DBA

and IT management to carefully know how to install and select ( and

deselect ) options that you are not licensed for.

The Oracle spatial stuff comes in the mdsys schema ... check with

Oracle and do your homework but I think if you start using this stuff

you better be prepared to pay for it.

Jun 15, 2011, 3:34:41 AM6/15/11

to

In article <e0eb3577-194b-4cf9-8b42-

51e8e3...@f2g2000yqh.googlegroups.com>, hurle...@yahoo.com says...

51e8e3...@f2g2000yqh.googlegroups.com>, hurle...@yahoo.com says...

Thanks for your comments John.

http://download.oracle.com/docs/html/B10826_01/sdo_locator.htm

This states:

"Oracle Locator (also referred to as Locator) is a feature of Oracle

Database 10g Standard Edition. Locator provides core features and

services available in Oracle Spatial. It provides significant

capabilities typically required to support Internet and wireless

service-based applications and partner-based GIS solutions. Locator is

not designed to be a solution for geographic information system (GIS)

applications requiring complex spatial data management. If you need

capabilities such as linear referencing, spatial functions, or

coordinate system transformations, use Oracle Spatial instead of

Locator."

So it looks as though there is a subset of spatial features which do NOT

require additional licensing.

--

jeremy

Jun 15, 2011, 8:34:33 AM6/15/11

to

On Jun 15, 3:34 am, Jeremy <jeremy0...@gmail.com> wrote:

> In article <e0eb3577-194b-4cf9-8b42-

> 51e8e3f4e...@f2g2000yqh.googlegroups.com>, hurleyjo...@yahoo.com says...

> In article <e0eb3577-194b-4cf9-8b42-

> 51e8e3f4e...@f2g2000yqh.googlegroups.com>, hurleyjo...@yahoo.com says...

That's correct; the locator subset of Spatial was something they added

in 10gR2, if I recall correctly.

For some reason, they didn't shout from the highest mountaintops that

you didn't have to buy Spatial (or EE for that matter) for a lot of

common use cases.

Reply all

Reply to author

Forward

0 new messages

Search

Clear search

Close search

Google apps

Main menu