Querying distances between two coordinates

#11

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

Quote:

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.

--
http://mgogala.byethost5.com

#12

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

Quote:

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

Have a look at the following article:
http://hoopercharles.wordpress.com/2...ing-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.

#13

In article <aff6098e-78f9-45e7-88a1-
70fafa76aa1e (AT) b1g2000yql (DOT) googlegroups.com>, hooperc2001 (AT) gmail (DOT) com says...

Quote:

Have a look at the following article:
http://hoopercharles.wordpress.com/2...ing-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

#14

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.

#15

In article <e0eb3577-194b-4cf9-8b42-
51e8e3f4e96e (AT) f2g2000yqh (DOT) googlegroups.com>, hurleyjohnb (AT) yahoo (DOT) com says...

Quote:

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.

Thanks for your comments John.

http://download.oracle.com/docs/html...do_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

#16

On Jun 15, 3:34*am, Jeremy <jeremy0... (AT) gmail (DOT) com> wrote:

Quote:

In article <e0eb3577-194b-4cf9-8b42-
51e8e3f4e... (AT) f2g2000yqh (DOT) googlegroups.com>, hurleyjo... (AT) yahoo (DOT) com says...

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.

Thanks for your comments John.

http://download.oracle.com/docs/html...do_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

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Querying distances between two coordinates

comp.databases.oracle.server comp.databases.oracle.server

Re: Querying distances between two coordinates - 06-11-2011 , 07:21 PM

Re: Querying distances between two coordinates - 06-13-2011 , 02:48 PM

Re: Querying distances between two coordinates - 06-14-2011 , 04:53 AM

Re: Querying distances between two coordinates - 06-14-2011 , 02:06 PM

Re: Querying distances between two coordinates - 06-15-2011 , 02:34 AM

Re: Querying distances between two coordinates - 06-15-2011 , 07:34 AM