Results 1 to 12 of 12

010903, 12:45 #1Registered User
 Join Date
 Dec 2002
 Location
 Vienna
 Posts
 9
Unanswered: Oracle Spatial  X and Y coordinates
I've got Oracle 8i and I use the Oracle Spatial objectrelational model.
Does anyone know, how to write a SQL statement having the X and Y coordinates of PointGeometries in two independent columns?
This result would be nice: PointID,X,Y
Thanks in advance,
Robert

010903, 13:12 #2Moderator.
 Join Date
 Sep 2002
 Location
 UK
 Posts
 5,171
Provided Answers: 1Re: Oracle Spatial  X and Y coordinates
Originally posted by Thalion
I've got Oracle 8i and I use the Oracle Spatial objectrelational model.
Does anyone know, how to write a SQL statement having the X and Y coordinates of PointGeometries in two independent columns?
This result would be nice: PointID,X,Y
Thanks in advance,
Robert
SELECT l.point1.id, l.point1.x, l.point1.y
FROM line l
...Tony Andrews
http://tinyurl.com/tonyandrews

010903, 13:25 #3Registered User
 Join Date
 Dec 2002
 Location
 Vienna
 Posts
 9
You can execute, for example, following statement:
select p.sdogeometry.sdo_ordinates from points p where id = 1;
This would yield:
SDOGEOMETRY.SDO_ORDINATES

SDO_ORDINATE_ARRAY(4375,45, 319508,921)
This is not a datatype which I could use with ADO objects.
The statement
select p.sdogeometry.sdo_ordinates.x, p.sdogeometry.sdo_ordinates.y
from points p where id = 1;
is unfortunately not possible.
There is another trick:
The statement
select column_value from
table(select p.sdogeometry.sdo_ordinates from points p where id=1);
results
COLUMN_VALUE

4375,45
319508,921
But I don't like the resulting coordinates among each other. Better are resulting columns for each ordinate.

010903, 13:29 #4Registered User
 Join Date
 Dec 2002
 Location
 Vienna
 Posts
 9
Is it possible to convert
SDO_ORDINATE_ARRAY(4375,45, 319508,921)
to a string?
Then I can use 'substr' to cut out the disturbing string SDO_ORDINATE_ARRAY.

010903, 13:35 #5Moderator.
 Join Date
 Sep 2002
 Location
 UK
 Posts
 5,171
Provided Answers: 1I just had a look at the Spatial manual. It has this example (Example 2.7) in it:
SELECT * from cola_markets c WHERE c.shape.SDO_POINT.X = 12;
I would have thought that if that works, so should this:
SELECT c.shape.SDO_POINT.X from cola_markets c WHERE c.shape.SDO_POINT.X = 12;Tony Andrews
http://tinyurl.com/tonyandrews

010903, 13:46 #6Registered User
 Join Date
 Dec 2002
 Location
 Vienna
 Posts
 9
That's a nice example ;)
But:

SDO_POINT  Is an object type with attributes X, Y, and Z, all of type NUMBER. If the SDO_ELEM_INFO and SDO_ORDINATES arrays are both null, and the SDO_POINT attribute is nonnull, then the X and Y values are considered to be the coordinates for a point geometry. Otherwise the SDO_POINT attribute is ignored by Spatial. You should store points in the SDO_POINT attribute for optimal storage.

This object is unfortunately not used. I'm relied on SDO_ORDINATES.

010903, 14:25 #7Moderator.
 Join Date
 Sep 2002
 Location
 UK
 Posts
 5,171
Provided Answers: 1Originally posted by Thalion
That's a nice example ;)
But:

SDO_POINT  Is an object type with attributes X, Y, and Z, all of type NUMBER. If the SDO_ELEM_INFO and SDO_ORDINATES arrays are both null, and the SDO_POINT attribute is nonnull, then the X and Y values are considered to be the coordinates for a point geometry. Otherwise the SDO_POINT attribute is ignored by Spatial. You should store points in the SDO_POINT attribute for optimal storage.

This object is unfortunately not used. I'm relied on SDO_ORDINATES.
v(1) = point 1 X value
v(2) = point 1 Y value
v(3) = point 2 X value
v(4) = point 2 Y value
... etc.
So how about somthing like this:
 Creating my own type and table for testing
create type t as varray(100) of number;
create table p( id number, v t);
 test data
insert into p values (1, t(1,2,3,4,5,6) );
 The select
select x.id, x.column_value, y.column_value from
(
select id, column_value, rownum rn
from p, table(v)
) x,
(
select id, column_value, rownum rn
from p, table(v)
) y
where x.id = 1
and y.id = 1
and x.rn = y.rn1
and mod(x.rn,2) = 1
/
ID X Y
  
1 1 2
1 3 4
1 5 6
Explanation: The two subqueries x and y both return all the VARRAY values and a ROWNUM (rn):
ID COLUMN_VALUE RN
  
1 1 1
1 2 2
1 3 3
1 4 4
1 5 5
1 6 6
For X we then restrict rn to odd values (1,3,5,...) using MOD(rn,2) = 1
For matching Y value we join x.rn = y.rn1
Now if you can get that to work using SDO_ORDINATES...Tony Andrews
http://tinyurl.com/tonyandrews

011003, 04:19 #8Registered User
 Join Date
 Dec 2002
 Location
 Vienna
 Posts
 9
Thank you for that approach to retrieve coordinates.
I'm going to do some tests with the SDO_ORDINATES.

011003, 07:00 #9Registered User
 Join Date
 Dec 2002
 Location
 Vienna
 Posts
 9
Does anyone know how to convert SDO_ORDINATES of point objects to SDO_POINT?

010904, 08:16 #10Registered User
 Join Date
 Jan 2004
 Location
 hyderabad,ap,India
 Posts
 1
Re: Oracle Spatial  X and Y coordinates
select c.geom.SDO_GTYPE
from COUNTIES c
select x.* from
COUNTIES c,table(c.GEOM.sdo_ordinates) x
select c.geom.SDO_GTYPE
from COUNTIES c
select c.geom.SDO_SRID
from COUNTIES c
select c.geom.SDO_POINT
from COUNTIES c
element Info
select x.* from
COUNTIES c,table(c.GEOM.SDO_ELEM_INFO) x
ordinates
select x.* from
COUNTIES c,table(c.GEOM.sdo_ordinates) x

033004, 03:05 #11Registered User
 Join Date
 Mar 2004
 Posts
 15
Check wether u have a function SDO_UTIL_GETVERTICES in ur 8i, b'cos this is he simplest way of extracting coordinates in 9i.
To convert SDO_ORDINATES of point objects to SDO_POINT, probably u can do it by converting the standard SDO_ORDINATE data to LRS data and again converting the LRS data to standard data. B'cos with conversion of LRS point data to standard data, the information in the SDO_ORDINATES in the input geometry (LRS) is used to set the SDO_POINT attributes in the resulting geometry. Not sure wether it works, but u can try.
Uday

100410, 19:42 #12Registered User
 Join Date
 Oct 2010
 Posts
 1
convert latitude and longitude to UTMcoordinates in Oracle
Hello, I want to convert geodesic coordinates into UTM coordinates in Oracle, for example, I have two fields:
X : 0°45’78.69’’
Y: 79°28’12.78’’
I want to convert these and insert the result in other fields, Latitude and Longitude.
Can I do this ?