Oracle 18c
I'm experimenting with different techniques for working with SDO_GEOMETRY vertices in queries.
For example, get a specific ordinate from a geometry, such as the startpoint Y.
---------------------------------------------------------------------------
We 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
) -- 🡅The startpoint Y ordinate.
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 the correct result:
START_POINT_Y
-------------
2
6
12
---------------------------------------------------------------------------
However, other techniques haven't been successful.
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 instead, they output:
STARTPOINT_Y
------------
2
2
2
---------------------------------------------------------------------------
Question:
Why don't the inline views in the last two options produce the correct results?
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'm trying to learn about how ordinates behave as a table in a column subquery.
Source: @MT0 on Stack Overflow - https://stackoverflow.com/a/72583361/5576771