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
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;
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!