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 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.
CodePudding user response：
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
CodePudding user response：
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