Working with SDO_GEOMETRY ordinates as a table in a column subquery

Question

Oracle 18c:

I'm experimenting with different techniques for working with SDO_GEOMETRY vertices in queries.

For example, working with ordinates as a table in a column subquery:

with cte as (
select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 1, 2,  3, 4              )) shape from dual union all
select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 5, 6,  7, 8,  9,10       )) shape from dual union all
select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(11,12, 13,14, 15,16, 17,18)) shape from dual)

select 
    (select 
        column_value
    from
        table((shape).sdo_ordinates) 
    where 
        rownum = 1       --Side note: "FETCH FIRST ROW ONLY" doesn't work the way I expected. It selects 1 for each startpoint X, which is incorrect. I'm not sure why it works that way.
    ) startpoint_x
from 
    cte

STARTPOINT_X
------------
           1
           5
          11

That query works as expected. It gets the startpoint X for each geometry.

---

Similarly, I had wondered if I could use the same technique to get the startpoint Y for each geometry. I would do this by changing rownum = 1 to rownum = 2 to get the second ordinate from the list of ordinates:

with cte as (
select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 1, 2,  3, 4              )) shape from dual union all
select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 5, 6,  7, 8,  9,10       )) shape from dual union all
select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(11,12, 13,14, 15,16, 17,18)) shape from dual)

select 
    (select 
        column_value
    from
        table((shape).sdo_ordinates) 
    where 
        rownum = 2
    ) startpoint_y
from 
    cte

STARTPOINT_Y
------------
      (null)
      (null)
      (null)

But that didn't work the way I thought it would. It returned nulls, whereas I wanted it to return:

STARTPOINT_Y
------------
           2
           6
          12

Question:

Why did that query work for startpoint X rownum = 1, but not startpoint Y rownum = 2?

---

I'm aware that there are other ways to interact with vertices, such as cross join table(sdo_util.getvertices(shape)). That works, but I want to learn about how ordinates behave as a table in a column subquery.

Answer 1

You can use a function:

with FUNCTION get_ordinate(
  shape SDO_GEOMETRY,
  idx   PLS_INTEGER
) RETURN NUMBER
IS
BEGIN
  RETURN shape.sdo_ordinates(idx);
END;
cte as (
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 1, 2,  3, 4              )) shape from dual union all
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 5, 6,  7, 8,  9,10       )) shape from dual union all
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(11,12, 13,14, 15,16, 17,18)) shape from dual
)
select get_ordinate(c.shape, 2) AS start_point_y
from   cte c

or, CROSS JOIN LATERAL (or CROSS APPLY):

with cte as (
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 1, 2,  3, 4              )) shape from dual union all
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 5, 6,  7, 8,  9,10       )) shape from dual union all
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(11,12, 13,14, 15,16, 17,18)) shape from dual
)
select sp.start_point AS start_point_y
from   cte c
       CROSS JOIN LATERAL (
         SELECT column_value AS start_point, ROWNUM AS start_point_index
         FROM   table(c.shape.sdo_ordinates)
       ) sp
WHERE  sp.start_point_index = 2

Which both output:

START_POINT_Y
2
6
12

---

This should work but does not:

with cte as (
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 1, 2,  3, 4              )) shape from dual union all
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 5, 6,  7, 8,  9,10       )) shape from dual union all
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(11,12, 13,14, 15,16, 17,18)) shape from dual
)
select ( SELECT column_value
         FROM   (
           select column_value, ROWNUM AS rn
           from   table(c.shape.sdo_ordinates) 
           WHERE  ROWNUM <= 2
         )
         WHERE rn = 2
       ) AS startpoint_y
from   cte c

and

with cte as (
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 1, 2,  3, 4              )) shape from dual union all
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 5, 6,  7, 8,  9,10       )) shape from dual union all
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(11,12, 13,14, 15,16, 17,18)) shape from dual
)
select ( select column_value
         from   table(c.shape.sdo_ordinates) 
         OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
       ) AS startpoint_y
from   cte c

Both should work but output:

STARTPOINT_Y
2
2
2

db<>fiddle here

Answer 2

You did get help for your issue, and it was the best to get rid of the rownum-filter in here, but I guess you still miss the answer, why rownum = 1 did the job whereas rownum = 2 didn't.

Just be aware: with Oracle you can never ask for rownum = 2 or something like rownum > 2. I've read an explanation for this long time ago and won't be able to give you that explanation again, but it's got something to do with the evaluation time of "rownum", as far as I remember. If you don't want to have rownum = 1 and filter it away, then you will never ever have a rownum = 2, that's the short form, that I kept in my mind.

If you really have to work with something like that in the future, you can make use of the row_number() function instead.

Answer 3

Here's a generic version of @MTO's second query. It gets all ordinates as rows, instead of just the startpoint_y:

or, CROSS JOIN LATERAL (or CROSS APPLY):

(I modified the sample data from the original question for clarity.)

with cte as (
  select 'A' as line_id, sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(101,102, 103,104                  )) shape from dual union all
  select 'B' as line_id, sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(105,106, 107,108, 109,110         )) shape from dual union all
  select 'C' as line_id, sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(111,112, 113,114, 115,116, 117,118)) shape from dual
)
select 
    cte.line_id,
    v.ordinate_index,
    v.ordinate_val
from   
    cte
cross join lateral (         --CROSS APPLY works too
                    select 
                        rownum as ordinate_index,
                        column_value as ordinate_val 
                    from   
                        table((shape).sdo_ordinates)
                    ) v
--where
    --ordinate_index = 1 --startpoint_x    
    --ordinate_index = 2 --startpoint_y
    --ordinate_index = (sdo_util.getnumvertices(cte.shape)*2) -1 --endpoint x; assumes each vertex only has 2 dimensions (is 2d and not LRS)
    --ordinate_index = (sdo_util.getnumvertices(cte.shape)*2)    --endpoint y; assumes each vertex only has 2 dimensions (is 2d and not LRS)

---

LINE_ID ORDINATE_INDEX ORDINATE_VAL
------- -------------- ------------
      A              1          101
      A              2          102
      A              3          103
      A              4          104

      B              1          105
      B              2          106
      B              3          107
      B              4          108
      B              5          109
      B              6          110

      C              1          111
      C              2          112
      C              3          113
      C              4          114
      C              5          115
      C              6          116
      C              7          117
      C              8          118