主页 / dba / 问题 / 299815
Accepted
User1974
Asked: 2021-09-19 15:14:54 +0800 CST

选择 SDO_GEOMETRY 线顶点作为行

  • 772

我有一个具有 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.

对于每一行，我想在查询/结果集中选择每个顶点作为单独的行。

我怎样才能做到这一点？

oracle spatial

1 个回答

  1. Best Answer

    看起来我可以使用getvertices()函数和table()函数：

    select
    a.line_id,
    --sdo_util.getvertices(a.shape),
    b.id as vertex_id,
    b.x, b.y
from
    a_sdo_geometry_tbl a
cross join
    table(sdo_util.getvertices(a.shape)) b
order by 
    a.line_id, b.id;


   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

15 rows selected.
    • 0

相关问题