甲骨文 18c：
可以使用以下sdo_util.getvertices()函式將 SDO_GEOMETRY 線頂點坐標作為行獲取：
with cte as (
select 100 as asset_id, sdo_geometry('linestring (10 20, 30 40)') shape from dual union all
select 200 as asset_id, sdo_geometry('linestring (50 60, 70 80, 90 100)') shape from dual union all
select 300 as asset_id, sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') shape from dual)
select
cte.asset_id,
id as vertex_id,
v.x,
v.y
from
cte, sdo_util.getvertices(shape) v
ASSET_ID VERTEX_ID X Y
---------- ---------- ---------- ----------
100 1 10 20
100 2 30 40
200 1 50 60
200 2 70 80
200 3 90 100
300 1 110 120
300 2 130 140
300 3 150 160
300 4 170 180
結果行具有縱坐標為數字的列。
我想做類似的事情，但我想將點幾何圖形作為行中每個頂點的行，而不是數字。
結果將如下所示：
ASSET_ID VERTEX_ID SHAPE
---------- ---------- ----------------
100 1 [SDO_GEOMETRY]
100 2 [SDO_GEOMETRY]
200 1 [SDO_GEOMETRY]
200 2 [SDO_GEOMETRY]
200 3 [SDO_GEOMETRY]
300 1 [SDO_GEOMETRY]
300 2 [SDO_GEOMETRY]
300 3 [SDO_GEOMETRY]
300 4 [SDO_GEOMETRY]
主意：
有一個未記錄的函式稱為SDO_UTIL.GET_COORDINATE(geometry, point_number).
（該函式的名稱似乎具有誤導性：它回傳的是點幾何，而不是坐標。）
select
cte.asset_id,
sdo_util.get_coordinate(shape,1) as first_point
from
cte
ASSET_ID FIRST_POINT
---------- ---------------------
100 [MDSYS.SDO_GEOMETRY]
200 [MDSYS.SDO_GEOMETRY]
300 [MDSYS.SDO_GEOMETRY]
該函式可用于將頂點作為點幾何圖形。
問題：
有沒有辦法將點幾何圖形作為SDO_GEOMETRY行中每個頂點的行？
uj5u.com熱心網友回復：
如果要將輸出作為MDSYS.ST_POINT資料型別，則將型別轉換為MDSYS.SDO_GEOMETRY型別并在連接的分層子查詢中MDSYS.ST_LINESTRING使用ST_NumPoints()和ST_PointN(index)成員函式（來自MDSYS.ST_CURVE超型別） ：LATERAL
with cte (asset_id, shape) as (
select 100, sdo_geometry('linestring (10 20, 30 40)') from dual union all
select 200, sdo_geometry('linestring (50 60, 70 80, 90 100)') from dual union all
select 300, sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') from dual
)
select c.asset_id,
p.point
from cte c
CROSS JOIN LATERAL (
SELECT ST_LINESTRING(c.shape).ST_PointN(LEVEL) AS point
FROM DUAL
CONNECT BY LEVEL <= ST_LINESTRING(c.shape).ST_NumPoints()
) p;
db<>在這里擺弄
uj5u.com熱心網友回復：
嘗試...
cte 為 ( select 100 as assets_id, sdo_geometry('linestring (10 20, 30 40)') shape from dual union all select 200 as assets_id, sdo_geometry('linestring (50 60, 70 80, 90 100)') 形狀來自dual union all select 300 as assets_id, sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') shape from dual ) select c.asset_id, id as vertex_id, sdo_geometry(c.shape.sdo_gtype/10 * 10 1, c.shape.sdo_srid, sdo_point_type(vx, vy, vz), null,null) as point from cte c, sdo_util.getvertices(shape) v
uj5u.com熱心網友回復：
我想出了一個似乎可行cross join的connect by level解決方案。
雖然，可能有更簡潔的方法來做到這一點。
with
data as (
select 100 as asset_id, sdo_geometry('linestring (10 20, 30 40)') shape from dual union all
select 200 as asset_id, sdo_geometry('linestring (50 60, 70 80, 90 100)') shape from dual union all
select 300 as asset_id, sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') shape from dual),
vertices as (
select level as vertex_index from dual connect by level <= (select max(sdo_util.getnumvertices(shape)) from data))
select
d.asset_id,
v.vertex_index,
sdo_util.get_coordinate(d.shape,v.vertex_index) as sdo_geom_point, --the ordinates are stored in the SDO_GEOMETRY's SDO_POINT attribute. Example: MDSYS.SDO_POINT_TYPE(10, 20, NULL)
sdo_util.get_coordinate(d.shape,v.vertex_index).sdo_point.x as x,
sdo_util.get_coordinate(d.shape,v.vertex_index).sdo_point.y as y
from
data d
cross join
vertices v
where
v.vertex_index <= sdo_util.getnumvertices(shape)
order by
asset_id,
vertex_index
結果：
ASSET_ID VERTEX_INDEX SDO_GEOM_POINT X Y
---------- ------------ -------------------- ---------- ----------
100 1 [MDSYS.SDO_GEOMETRY] 10 20
100 2 [MDSYS.SDO_GEOMETRY] 30 40
200 1 [MDSYS.SDO_GEOMETRY] 50 60
200 2 [MDSYS.SDO_GEOMETRY] 70 80
200 3 [MDSYS.SDO_GEOMETRY] 90 100
300 1 [MDSYS.SDO_GEOMETRY] 110 120
300 2 [MDSYS.SDO_GEOMETRY] 130 140
300 3 [MDSYS.SDO_GEOMETRY] 150 160
300 4 [MDSYS.SDO_GEOMETRY] 170 180
我將 X 和 Y 列添加到查詢中以顯示[MDSYS.SDO_GEOMETRY]值代表的內容。我實際上不需要查詢中的 X&Y 列。
編輯：
我借用了@MT0 的cross join lateral技術并將其改編SDO_GEOMETRY為MDSYS.ST_POINT.
它比我原來的cross join / connect by level方法更干凈。
with cte (asset_id, shape) as (
select 100, sdo_geometry('linestring (10 20, 30 40)') from dual union all
select 200, sdo_geometry('linestring (50 60, 70 80, 90 100)') from dual union all
select 300, sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') from dual
)
select c.asset_id,
vertex_index,
p.point,
sdo_util.get_coordinate(c.shape,p.vertex_index).sdo_point.x as x,
sdo_util.get_coordinate(c.shape,p.vertex_index).sdo_point.y as y
from cte c
cross join lateral (
select sdo_util.get_coordinate(c.shape,level) as point, level as vertex_index
from dual
connect by level <= sdo_util.getnumvertices(c.shape)
) p;
結果是一樣的：
ASSET_ID VERTEX_INDEX SDO_GEOM_POINT X Y
---------- ------------ -------------------- ---------- ----------
100 1 [MDSYS.SDO_GEOMETRY] 10 20
100 2 [MDSYS.SDO_GEOMETRY] 30 40
200 1 [MDSYS.SDO_GEOMETRY] 50 60
200 2 [MDSYS.SDO_GEOMETRY] 70 80
200 3 [MDSYS.SDO_GEOMETRY] 90 100
300 1 [MDSYS.SDO_GEOMETRY] 110 120
300 2 [MDSYS.SDO_GEOMETRY] 130 140
300 3 [MDSYS.SDO_GEOMETRY] 150 160
300 4 [MDSYS.SDO_GEOMETRY] 170 180
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/494504.html
上一篇：重復條目的更新列
下一篇：獲取可變陣列值作為連接文本串列