Forum Stats

  • 3,838,237 Users
  • 2,262,343 Discussions
  • 7,900,552 Comments

Discussions

Custom aggregate function: Collapse vertex rows to polyline

User_1871
User_1871 Member Posts: 244 Red Ribbon
edited May 2, 2022 2:47PM in SQL & PL/SQL

I have multi-part polyline vertices stored as individual rows (Oracle 18c).

with vertices as (
select '001' line_id,1 part_num,1 vertex_num,0 x,5 y,0 m from dual union all
select '001',1,2,10,10,11.18 from dual union all
select '001',1,3,30,0,33.54 from dual union all
select '001',2,1,50,10,33.54 from dual union all
select '001',2,2,60,10,43.54 from dual
)
select * from vertices

   LINE_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 (collapsed into a single row).

I've tried a few different ways of doing that (i.e. listagg and a PL/SQL block). Additionally, as a learning exercise, I would also like to explore creating a custom aggregate function as a solution.

It might look like this:

select
    line_id,
    sdo_geometry(partition by id, part num, vertex order, x, y, m, gtype, srid) as sdo_geom
from
    vertices
group by
    line_id 

Output:
LINE_ID: 001
SDO_GEOM: SDO_GEOMETRY(3306, 26917, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1, 10, 2, 1), MDSYS.SDO_ORDINATE_ARRAY(0, 5, 0, 10, 10, 11.18, 30, 0, 33.54, 50, 10, 33.54, 60, 10, 43.54))

Question:

Is there a way to create a custom aggregate function in Oracle 18c that would collapse the vertices into an SDO_GEOMETRY value?

Tagged:
«1

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    edited May 1, 2022 12:37PM

    Custom Aggregate functions are implemented via Oracle Data Cartridge Interface (ODCI).

    They're UDTs that have a specific set of functions. (ODCIAggregate)


    User_1871
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,586 Red Diamond

    Not exactly sure what you need:

    with vertices as (
                      select '001' line_id,1 part_num,1 vertex_num,0 x,5 y,0 m from dual union all
                      select '001',1,2,10,10,11.18 from dual union all
                      select '001',1,3,30,0,33.54 from dual union all
                      select '001',2,1,50,10,33.54 from dual union all
                      select '001',2,2,60,10,43.54 from dual
                     ),
               t1 as (
                      select  line_id,
                              part_num,
                              vertex_num,
                              x x_from,
                              y y_from,
                              count(*) over(partition by line_id,part_num) cnt,
                              lead(x) over(partition by line_id,part_num order by vertex_num) x_to,
                              lead(y) over(partition by line_id,part_num order by vertex_num) y_to
                        from  vertices
                     ),
               t2 as (
                      select  line_id,
                              part_num,
                              vertex_num,
                              sdo_geometry(
                                           2002,
                                           null,
                                           null,
                                           sdo_elem_info_array(1,2,1),
                                           sdo_ordinate_array(x_from,y_from,x_to,y_to)
                                          ) section
                        from  t1
                        where vertex_num < cnt
                     )
    select  line_id,
            part_num,
            sdo_aggr_concat_lines(section) line
      from  t2
      group by line_id,
               part_num
      order by line_id,
               part_num
    /
    
    LINE_ID   PART_NUM LINE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
    ------- ---------- -----------------------------------------------------------------------------------------------------
    001              1 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0, 5, 10, 10, 30, 0))
    001              2 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(50, 10, 60, 10))
    
    SQL>
    
    

    SY.

    User_1871
  • _jum
    _jum Member Posts: 543 Bronze Trophy
    edited May 2, 2022 2:05PM

    Another approach:

    WITH vertices (line_id,part_num,vertex_num,x,y,m) AS 
     (SELECT '001',1,1, 0, 5,   0 FROM dual UNION ALL
      SELECT '001',1,2,10,10,11.18 FROM dual UNION ALL
      SELECT '001',1,3,30, 0,33.54 FROM dual UNION ALL
      SELECT '001',2,1,50,10,33.54 FROM dual UNION ALL
      SELECT '001',2,2,60,10,43.54 FROM dual),
     t1 AS                     
     (SELECT x coord, line_id, part_num, vertex_num FROM vertices
        UNION ALL          
      SELECT y     , line_id, part_num, vertex_num FROM vertices)
     SELECT line_id, part_num
          , sdo_geometry(2002, NULL, NULL
            , sdo_elem_info_array(1,2,1)
            , CAST(collect (coord ORDER BY line_id, part_num, vertex_num) AS sdo_ordinate_array)) line
      FROM t1            
     GROUP BY line_id, part_num;
    
    
    LINE_ID   PART_NUM LINE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
    ------- ---------- -----------------------------------------------------------------------------------------------------
    001              1 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0, 5, 10, 10, 30, 0))
    001              2 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(50, 10, 60, 10))
    


    User_1871
  • User_1871
    User_1871 Member Posts: 244 Red Ribbon

    @_jum

    Thanks! Does that approach assume that we'd need to do an additional step afterwards: merge the two single-part geometries into a multi-part geometry?

  • _jum
    _jum Member Posts: 543 Bronze Trophy
    edited May 2, 2022 3:11PM

    Yes, with an additional merge step, based on an adapted and extended (for M) approach of @SY or mine. The present draft was ready 😉

    This collapse is certainly possible in one very smart step...

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,586 Red Diamond

    @_jum: Another approach:

    Careful, you need to weigh X & Y, othersise you get non-deterministic results and Y can end up first:

    WITH vertices (line_id,part_num,vertex_num,x,y,m) AS 
     (SELECT '001',1,1, 0, 5,   0 FROM dual UNION ALL
      SELECT '001',1,2,10,10,11.18 FROM dual UNION ALL
      SELECT '001',1,3,30, 0,33.54 FROM dual UNION ALL
      SELECT '001',2,1,50,10,33.54 FROM dual UNION ALL
      SELECT '001',2,2,60,10,43.54 FROM dual),
     t1 AS                     
     (SELECT x coord, line_id, part_num, vertex_num, 1 weight FROM vertices
        UNION ALL          
      SELECT y     , line_id, part_num, vertex_num,2 weight FROM vertices)
     SELECT line_id, part_num
          , sdo_geometry(2002, NULL, NULL
            , sdo_elem_info_array(1,2,1)
            , CAST(collect (coord ORDER BY line_id, part_num, vertex_num,weight) AS sdo_ordinate_array)) line
      FROM t1            
     GROUP BY line_id, part_num;
    

    SY.

    User_1871
  • _jum
    _jum Member Posts: 543 Bronze Trophy

    Of course, I need a break - Thanks @Solomon Yakobson!

  • User_1871
    User_1871 Member Posts: 244 Red Ribbon

    @_jum

    This collapse is certainly possible in one very smart step...

    What's the smart step for merging?

  • _jum
    _jum Member Posts: 543 Bronze Trophy

    I honestly have no idea currently!

  • _jum
    _jum Member Posts: 543 Bronze Trophy
    edited May 3, 2022 1:00PM

    Last step could be:

    SELECT sdo_geom.sdo_union(
           SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0, 5, 0, 10, 10, 11.18, 30, 0, 33.54))
         , SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(50, 10, 33.54, 60, 10, 43.54))
         , 0.01) ugeom
     FROM dual; 
    
    UGEOM
    ---------------------------------
    MDSYS.SDO_GEOMETRY(3006, NULL, NULL
          , MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1, 10, 2, 1)
          , MDSYS.SDO_ORDINATE_ARRAY(0, 5, 0, 10, 10, 11.18, 30, 0, 33.54, 50, 10, 33.54, 60, 10, 43.54))
    
    


    User_1871