提问人:User1974 提问时间:5/7/2022 更新时间:5/7/2022 访问量:194
多零件几何形状:提取零件时获取行号和零件号(通过SDO_UTIL。EXTRACT_ALL)
Multi-part geometries: Get line & part number when extracting parts (via SDO_UTIL.EXTRACT_ALL)
问:
我在 Oracle 18c 中有多部分SDO_GEOMETRIES。
我可以使用SDO_UTIL将部分提取为单独的行。EXTRACT_ALL() 函数:
with cte as ( --Each part is wrapped in brackets and separated by commas: (p1),(p2) select sdo_geometry('MULTILINESTRING((1 1 1, 2 2 2),(3 3 3, 4 4 4))') as shape from dual union all --3D: (X,Y,Z) select sdo_geometry('MULTILINESTRING((5 5, 6 6),(7 7, 8 8),(9 9, 0 0))') as shape from dual union all --2D: (X,Y) select sdo_geometry('MULTILINESTRING((1 1, 2 2))') as shape from dual --2D: (X,Y) ) select a.object_value, sdo_util.to_wktgeometry(a.object_value) from cte, table(sdo_util.extract_all(shape)) a OBJECT_VALUE SDO_UTIL.TO_WKTGEOMETRY(A.OBJECT_VALUE) -------------------- --------------------------------------- [MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0 1.0, 2.0 2.0 2.0) [MDSYS.SDO_GEOMETRY] LINESTRING (3.0 3.0 3.0, 4.0 4.0 4.0) [MDSYS.SDO_GEOMETRY] LINESTRING (5.0 5.0, 6.0 6.0) [MDSYS.SDO_GEOMETRY] LINESTRING (7.0 7.0, 8.0 8.0) [MDSYS.SDO_GEOMETRY] LINESTRING (9.0 9.0, 0.0 0.0) [MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0, 2.0 2.0)
db<>fiddle 在这里
我想将以下列添加到查询中:
- MULTILINE_NUM(原始多部分行号)
- PART_NUM
MULTILINE_NUM PART_NUM OBJECT_VALUE SDO_UTIL.TO_WKTGEOMETRY(A.OBJECT_VALUE) ------------- -------- -------------------- --------------------------------------- 1 1 [MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0 1.0, 2.0 2.0 2.0) 1 2 [MDSYS.SDO_GEOMETRY] LINESTRING (3.0 3.0 3.0, 4.0 4.0 4.0) 2 1 [MDSYS.SDO_GEOMETRY] LINESTRING (5.0 5.0, 6.0 6.0) 2 2 [MDSYS.SDO_GEOMETRY] LINESTRING (7.0 7.0, 8.0 8.0) 2 3 [MDSYS.SDO_GEOMETRY] LINESTRING (9.0 9.0, 0.0 0.0) 3 1 [MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0, 2.0 2.0)
添加这些列时,我不想在事后使用窗口函数等“假装”。
我想直接从几何图形中提取该信息,以确保数字正确(即,零件编号不应是任意的;它应该反映原始几何图形中的零件编号顺序)。
有没有办法从查询中获取MULTILINE_NUM和PART_NUM?table(sdo_util.extract_all(shape))
答:
2赞
MT0
5/7/2022
#1
根据我对你上一个问题的回答,你可以使用:
with cte (shape) as (
--Each part is wrapped in brackets and separated by commas: (p1),(p2)
select sdo_geometry('MULTILINESTRING((1 1 1, 2 2 2),(3 3 3, 4 4 4))') from dual union all --3D: (X,Y,Z)
select sdo_geometry('MULTILINESTRING((5 5, 6 6),(7 7, 8 8),(9 9, 0 0))') from dual union all --2D: (X,Y)
select sdo_geometry('MULTILINESTRING((1 1, 2 2))') from dual --2D: (X,Y)
)
select c.multiline_num,
e.part_no,
sdo_util.to_wktgeometry(e.elem)
from ( SELECT ROWNUM AS multiline_num,
shape
FROM cte
) c
CROSS JOIN LATERAL (
SELECT LEVEL AS part_no,
SDO_UTIL.EXTRACT(c.shape, LEVEL) AS elem
FROM DUAL
CONNECT BY LEVEL <= SDO_UTIL.GETNUMELEM(c.shape)
) e;
输出:
MULTILINE_NUM PART_NO SDO_UTIL。TO_WKTGEOMETRY(E.ELEM) 1 1 线串 (1.0, 1.0, 1.0, 2.0, 2.0, 2.0) 1 2 线串 (3.0, 3.0, 3.0, 4.0, 4.0, 4.0) 2 1 线串(5.0、5.0、6.0、6.0) 2 2 线串(7.0、7.0、8.0、8.0) 2 3 线串 (9.0, 9.0, 0.0, 0.0) 3 1 线串 (1.0, 1.0, 2.0, 2.0)
或者,使用:SDO_UTIL.EXTRACT_ALL
with cte (shape) as (
--Each part is wrapped in brackets and separated by commas: (p1),(p2)
select sdo_geometry('MULTILINESTRING((1 1 1, 2 2 2),(3 3 3, 4 4 4))') from dual union all --3D: (X,Y,Z)
select sdo_geometry('MULTILINESTRING((5 5, 6 6),(7 7, 8 8),(9 9, 0 0))') from dual union all --2D: (X,Y)
select sdo_geometry('MULTILINESTRING((1 1, 2 2))') from dual --2D: (X,Y)
)
select c.multiline_num,
e.part_no,
sdo_util.to_wktgeometry(e.elem)
from ( SELECT ROWNUM AS multiline_num,
shape
FROM cte
) c
CROSS JOIN LATERAL (
SELECT ROWNUM AS part_no,
a.object_value AS elem
FROM TABLE(SDO_UTIL.EXTRACT_ALL(c.shape)) a
) e;
输出相同。
db<>fiddle 在这里
评论