Oracle: extract data from MDSYS.SDO_GEOMETRY column

Question

I have a table form which I need to extract some information. This table has an oracle spatial (MDSYS.SDO_GEOMETRY) column, from which I also need some data.

I started out with a simple query like this:

select id, field1, field2
FROM my_table;

After that, I was able to loop over the result to extract the data that was in the spatial column:

SELECT *
        FROM TABLE  (SELECT a.POSITIONMAP.sdo_ordinates
                          FROM my_table
                          WHERE ID = 18742084);

The POSITIONMAP.sdo_ordinates seems to usually hold 4 values, like these:

100050,887
407294,948
0,577464740471056
-0,816415625470689

I need the last 2 values. I can achieve that by changing the query into this:

SELECT * FROM
    (SELECT rownum AS num,
            column_value AS orientatie
        FROM TABLE  (SELECT a.POSITIONMAP.sdo_ordinates
                          FROM my_table
                          WHERE ID = 18742084))
    WHERE num IN (3,4)

Looping over every row from my first query to extract the data from the POSITIONMAP column is of course not very performance friendly, so my query becomes slow very quickly.

I would like to retrieve all information in one query, but there are a few things that prevent me from doing so.

• Not every row in the table has data in POSITIONMAP
• Some rows do have data in POSITIONMAP, but they only contain 2 values (so not the 3rd and 4th value that I am looking for.
• I need the data in one row for every row in the table (using the previous query would result in duplicate rows

The closest I got is:

select
  id,
  field1, 
  field2
  t.*
  FROM my_table v,
  table (v.POSITIONMAP.sdo_ordinates) t

This gives my 4 rows for every row in my_table. As soon as I try to put the rownum condition into this query, I get an error: "invalid user.table.column, table.column, or column specification"

Is there any way to combine what I want to do into 1 query?

Answer 1

You can use sdo_util.getvertices as follows:

select t.x,t.y
  from my_table mt
      ,table(sdo_util.getvertices(mt.positionmap)) t
 where t.id = 2

I'm assuming that your geometries are lines (gtype=2002) and points (gtype= 2001). If you want X,Y values for lines and empty values for point you can filter on the sdo_gtype property of the geometry object.

select t.x,t.y
  from my_table mt
      ,table(sdo_util.getvertices(mt.positionmap)) t
 where t.id = 2
 and mt.positionmap.sdo_gtype=2002
 union all
 select null as X,
        null as Y
 from my_table mt
 where mt.positionmap.sdo_gtype=2001