Oracle 18c:
It's possible to get SDO_GEOMETRY line vertex ordinates as rows using the sdo_util.getvertices() function:
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
The resulting rows have columns with ordinates as numbers.
I want to do something similar, but I want to get point geometries as rows for each vertex in the lines, instead of numbers.
The result would look like this:
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]
Idea:
There is an undocumented function called SDO_UTIL.GET_COORDINATE(geometry, point_number).
(The name of that function seems misleading: it returns a point geometry, not a coordinate.)
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]
That function could be useful for getting vertices as point geometries.
Question:
Is there a way to get point geometries as rows for each vertex in the SDO_GEOMETRY lines?
CodePudding user response：
If you want the output as an MDSYS.ST_POINT data type then convert the MDSYS.SDO_GEOMETRY type to an MDSYS.ST_LINESTRING type and use the ST_NumPoints() and ST_PointN(index) member functions (from the MDSYS.ST_CURVE super-type) in a LATERAL joined hierarchical sub-query:
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<>fiddle here
CodePudding user response：
Try...
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 c.asset_id, id as vertex_id, sdo_geometry(c.shape.sdo_gtype/10 * 10 1, c.shape.sdo_srid, sdo_point_type(v.x, v.y, v.z), null,null) as point from cte c, sdo_util.getvertices(shape) v
CodePudding user response：
I came up with a cross join and connect by level solution that seems to work.
Although, there might be more succinct ways of doing it.
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
Result:
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
I added the X & Y columns to the query to show what the [MDSYS.SDO_GEOMETRY] values represent. I don't actually need the X&Y columns in my query.
Edit:
I borrowed @MT0's cross join lateral technique and adapted it for SDO_GEOMETRY instead of MDSYS.ST_POINT.
It's cleaner than my original cross join / connect by level approach.
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;
The result is the same:
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