2 Replies Latest reply: Jan 28, 2014 6:17 AM by user9221319 RSS

Extract X,Y from SDO_GEOMETRY / SDO POINT TYPE

81355c9b-6b55-46c5-ac58-027b332478a9 Newbie
Currently Being Moderated

Hi,

 

I have a table showing this data;

 

SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(186858,487, 481875,513, NULL), NULL, NULL)

 

I need to know the X and Y values.. Whats the easiest way to do this? I did quite a bit of googling, but the whole geometry field is new for me..

 

Cheers

Alex

  • 1. Re: Extract X,Y from SDO_GEOMETRY / SDO POINT TYPE
    yhu Journeyer
    Currently Being Moderated

    If you have such point data in tab1(col1), you may have

    select a.col1.sdo_point.x, a.col1.sdo_point.y  from tab1 a;

  • 2. Re: Extract X,Y from SDO_GEOMETRY / SDO POINT TYPE
    user9221319 Newbie
    Currently Being Moderated

    Assuming you have a table (geometry_table) with a geometry column (geometry) and an id column (id) the easiest way to get the x and y components is as follows:

     

    select a.id, t.x, t.y from geometry_table a, table(sdo_util.getvertices(a.geometry)) t;

     

    You want to make sure you use some unique identifier (or rowid) in your query to ensure that you can associate the correct components with each geometry in those cases where you have geometries with multiple points.

     

    In your case, let's assume your geometry id = 1 and you only want the components for that id - your query would look like the following:

     

    select a.id, t.x, t.y from geometry_table a, table(sdo_util.getvertices(a.geometry)) t where a.id = 1;

     

    Your results would be as follows:

     

    ID     |     X                    |     Y

    --------------------------------------------------------

    1          186858.487          481875.513

     

    I also assumed that your geometry was actually:

    SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(186858.487, 481875.513, NULL), NULL, NULL)

     

    and NOT as you typed above:

    SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(186858,487, 481875,513, NULL), NULL, NULL)

     

    The second geometry is invalid (it would either have to be a 4D point - 4001, or a 2D polyline - 2002).  See the following link for more details about the sdo_geometry type.

    Spatial Data Types and Metadata

    Enjoy!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points