Forum Stats

  • 3,838,954 Users
  • 2,262,428 Discussions
  • 7,900,816 Comments

Discussions

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

User_1871
User_1871 Member Posts: 244 Red Ribbon

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

I 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.

Answers

  • User_1871
    User_1871 Member Posts: 244 Red Ribbon
    edited Apr 14, 2022 3:40PM

    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
    David Lapp-Oracle Member Posts: 59 Employee

    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
    User_1871 Member Posts: 244 Red Ribbon

    @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
    David Lapp-Oracle Member Posts: 59 Employee

    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;
    
    User_1871