Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Convert vertices-as-rows to SDO_GEOMETRY (multi-part, M-enabled)

User_1871Apr 14 2022

I have multi-part polyline vertices stored as individual rows in an 18c table. The vertices have M-values:

ASSET_ID     PART_NUM VERTEX_NUM          X          Y          M
---------- ---------- ---------- ---------- ---------- ----------
001                 1          1          0          5          0
001                 1          2         10         10      11.18
001                 1          3         30          0      33.54
001                 2          1         50         10      33.54
001                 2          2         60         10      43.54

image.pngI want to convert the vertices to a multi-part SDO_GEOMETRY polyline, with the M-values included.
Is it possible to do that in Oracle?
Thanks.

Comments

User_1871

Here's the DDL for creating the sample data:

create table vertices (
    asset_id    varchar2(10),
    part_num    number,
    vertex_num  number,
    x           number,
    y           number,
    m           number);

insert into vertices (asset_id,part_num,vertex_num,x,y,m) values ('001',1, 1, 0,   5, 0);
insert into vertices (asset_id,part_num,vertex_num,x,y,m) values ('001',1, 2, 10, 10, 11.18);
insert into vertices (asset_id,part_num,vertex_num,x,y,m) values ('001',1, 3, 30,  0, 33.54);
insert into vertices (asset_id,part_num,vertex_num,x,y,m) values ('001',2, 1, 50, 10, 33.54);
insert into vertices (asset_id,part_num,vertex_num,x,y,m) values ('001',2, 2, 60, 10, 43.54);

select * from vertices

ASSET_ID     PART_NUM VERTEX_NUM          X          Y          M
---------- ---------- ---------- ---------- ---------- ----------
001                 1          1          0          5          0
001                 1          2         10         10      11.18
001                 1          3         30          0      33.54
001                 2          1         50         10      33.54
001                 2          2         60         10      43.54
David Lapp-Oracle

Do you require the result geometry for each asset to be multiline even if there is no gap between parts? Or can result for an asset be line when parts are contiguous?

User_1871

@david-lapp-oracle I think either of the two options you mentioned would be fine.
Note that the sample MULTILINESTRING does have a gap between parts (is not contiguous).
Thanks!

David Lapp-Oracle

There are definitely more optimized and elegant ways to do this but here's a straightforward way.

create table lrs_test (asset_id varchar2(10), geometry sdo_geometry);

declare
geomPart sdo_geometry;
geomConcat sdo_geometry;
begin
for assetCur in (select distinct asset_id from vertices) loop
 geomConcat := null;
 for partCur in (select distinct part_num from vertices
                 where asset_id=assetCur.asset_id
                 order by part_num) loop
  geomPart   := SDO_GEOMETRY(3302,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY());
  for vertexCur in (select * from vertices
                    where asset_id=assetCur.asset_id and part_num=partCur.part_num
                    order by vertex_num) loop
    geomPart.sdo_ordinates.extend(3);
    geomPart.sdo_ordinates(geomPart.sdo_ordinates.count-2) := vertexCur.x;
    geomPart.sdo_ordinates(geomPart.sdo_ordinates.count-1) := vertexCur.y;  
    geomPart.sdo_ordinates(geomPart.sdo_ordinates.count)   := vertexCur.m;
  end loop;
 geomConcat := sdo_lrs.concatenate_geom_segments(geomConcat,geomPart, 0.005);
 end loop;
 insert into lrs_test values (assetCur.asset_id, geomConcat);
end loop;
end;
1 person found this helpful
1 - 4

Post Details

Added on Apr 14 2022
#database-key-features, #spatial
4 comments
116 views
1 mention