選擇 SDO_GEOMETRY 線頂點作為行——使用遞歸 WITH 子句

我有一個具有 SDO_GEOMETRY 列（行）的 Oracle 18c 表：

create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry);

insert into a_sdo_geometry_tbl (line_id, shape) 
values (1, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
   sdo_ordinate_array (671539.6852734378,4863324.181436138, 671595.0500703361,4863343.166556185, 671614.013553706,4863350.343483042, 671622.2044153381,4863353.525396131))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (2, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
   sdo_ordinate_array (71534.5567096211,4863119.991809748, 671640.7384688659,4863157.132745253, 671684.8621150404,4863172.022995591))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (3, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
   sdo_ordinate_array (671622.2044153381,4863353.525396131, 671633.3267164109,4863357.846229106, 671904.0614077691,4863451.286166754))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (4, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
   sdo_ordinate_array (671684.8620521119,4863172.022995591, 671892.1496144319,4863244.141440067, 671951.2156571196,4863264.824310392, 671957.4471461186,4863266.847617676, 671966.8243856924,4863269.146632658))  )

---

select
   line_id,
   sdo_util.to_wktgeometry(shape) as well_known_text
from
   a_sdo_geometry_tbl;

  LINE_ID    WELL_KNOWN_TEXT
--------------------------------------------------------------------------------
        1    LINESTRING (671539.685273438 4863324.18143614, 671595.050070336 4863343.16655619, 671614.013553706 4863350.34348304, 671622.204415338 4863353.52539613)         
                                                                               
        2    LINESTRING (71534.5567096211 4863119.99180975, 671640.738468866 4863157.13274525, 671684.86211504 4863172.02299559)                                             
                                                                               
        3    LINESTRING (671622.204415338 4863353.52539613, 671633.326716411 4863357.84622911, 671904.061407769 4863451.28616675)                                            
                                                                               
        4    LINESTRING (671684.862052112 4863172.02299559, 671892.149614432 4863244.14144007, 671951.21565712 4863264.82431039, 671957.447146119 4863266.84761768, 671966.824385692 4863269.14663266)                                                       
                                                                               
4 rows selected.

---

對於每一行，我想在查詢/結果集中選擇每個頂點作為單獨的行。

我想通過遞歸 WITH 子句來做到這一點——不使用 GetVertices() 函式或自定義類型。

有沒有辦法做到這一點？

---

結果集如下所示：

  LINE_ID  VERTEX_ID          X          Y
---------- ---------- ---------- ----------
        1          1 671539.685 4863324.18
        1          2 671595.050 4863343.17
        1          3 671614.014 4863350.34
        1          4 671622.204 4863353.53

        2          1 71534.5567 4863119.99
        2          2 671640.738 4863157.13
        2          3 671684.862 4863172.02

        3          1 671622.204 4863353.53
        3          2 671633.327 4863357.85
        3          3 671904.061 4863451.29

        4          1 671684.862 4863172.02
        4          2 671892.150 4863244.14
        4          3 671951.216 4863264.82
        4          4 671957.447 4863266.85
        4          5 671966.824 4863269.15

提示：

以下功能可能有用：

PointN：返回一個點，它是頂點集合中的第 n 個頂點

GetNumVertices：返回輸入幾何中的頂點數。

---

選項1：

使用 WELL_KNOWN_TEXT 函式：

SELECT DISTINCT
 LINE_ID,
 ROUND(TO_NUMBER(REGEXP_SUBSTR (VALUE, '[^ - ]+', 1, 1 )),10) AS X,
 ROUND(TO_NUMBER(REGEXP_SUBSTR (VALUE, '[^ - ]+', 2, 1 )),10) AS Y
FROM
 (
   SELECT
     LINE_ID,
     TRIM(REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL)) AS VALUE
   FROM
     (
       SELECT
         LINE_ID,
         REPLACE(REPLACE(SUBSTR(WELL_KNOWN_TEXT,12),'(',''),')','') STR
       FROM
         (
           SELECT
             LINE_ID,
             SDO_UTIL.TO_WKTGEOMETRY(SHAPE) AS WELL_KNOWN_TEXT
           FROM
             A_SDO_GEOMETRY_TBL
         )
     )
     CONNECT BY INSTR(STR, ',', 1, LEVEL - 1) > 0
 )
ORDER BY  LINE_ID; 

選項 2：

使用空間函式，包括自定義 PointN 函式，但不包括 Get_Vertices 函式：

--Custom PointN fucntion: https://gis.stackexchange.com/a/412038/135445
create or replace function cust_pointn (g sdo_geometry, n number)
return sdo_geometry
as
begin
 return (
   sdo_geometry (
     2001,
     g.sdo_srid,
     sdo_point_type (
       g.sdo_ordinates((n-1) * 2 + 1),
       g.sdo_ordinates((n-1) * 2 + 2),
       null
     ),
     null,
     null
   )
 );
end;
/

--Generate rows for each vertex using recursive WITH: https://stackoverflow.com/a/65993625/10936066
with  rcte (line_id, vertex_id, num_vertices) as (
     select line_id, 1 as vertex_id, sdo_util.getnumvertices(shape) as num_vertices
     from a_sdo_geometry_tbl
     where sdo_util.getnumvertices(shape) > 0
     union all
     select line_id, vertex_id + 1, num_vertices
     from rcte
     where vertex_id < num_vertices
    )

--1. Select from the recursive WITH; has rows for each vertex
--2. Join to the original table to get the shape/line column
--3. Use the custom function to get the vertex shape/point (since I couldn't figure out how to include the shape column in the recursive WITH query)
--4. get the x and y from the vertex shape/point
select 
   a.line_id, 
   a.vertex_id,
   cust_pointn(shape,a.vertex_id) as vertex_point_shape,
   cust_pointn(shape,a.vertex_id).sdo_point.x as x,
   cust_pointn(shape,a.vertex_id).sdo_point.y as y
from 
   rcte a
left join
   a_sdo_geometry_tbl b
   on a.line_id = b.line_id
order by
   a.line_id,
   a.vertex_id

引用自：https://dba.stackexchange.com/questions/300417