Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2002
    Location
    Vienna
    Posts
    9

    Question Unanswered: Oracle Spatial - X and Y coordinates

    I've got Oracle 8i and I use the Oracle Spatial object-relational model.

    Does anyone know, how to write a SQL statement having the X and Y coordinates of Point-Geometries in two independent columns?

    This result would be nice: PointID,X,Y

    Thanks in advance,
    Robert

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Oracle Spatial - X and Y coordinates

    Originally posted by Thalion
    I've got Oracle 8i and I use the Oracle Spatial object-relational model.

    Does anyone know, how to write a SQL statement having the X and Y coordinates of Point-Geometries in two independent columns?

    This result would be nice: PointID,X,Y

    Thanks in advance,
    Robert
    I don't use Spacial, but I believe it is all based on Objects, so I would think you should be able to get at the attributes in the usual way, like:

    SELECT l.point1.id, l.point1.x, l.point1.y
    FROM line l
    ...

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

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

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I 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;

  6. #6
    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 non-null, 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.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally 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 non-null, 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.
    Right, and I now see that SDO_ORDINATES is a VARRAY that contains data like:
    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.rn-1
    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.rn-1

    Now if you can get that to work using SDO_ORDINATES...

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

  9. #9
    Join Date
    Dec 2002
    Location
    Vienna
    Posts
    9
    Does anyone know how to convert SDO_ORDINATES of point objects to SDO_POINT?

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

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

  12. #12
    Join Date
    Oct 2010
    Posts
    1

    convert latitude and longitude to UTM-coordinates in Oracle

    Hello, I want to convert geodesic coordinates into UTM coordinates in Oracle, for example, I have two fields:
    X : 04578.69
    Y: 792812.78

    I want to convert these and insert the result in other fields, Latitude and Longitude.
    Can I do this ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •