Forum Stats

  • 3,839,017 Users
  • 2,262,438 Discussions
  • 7,900,836 Comments

Discussions

Categories

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
image.png

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:
· Share on TwitterShare on Facebook
«1

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 6,198 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)

    https://docs.oracle.com/database/121/ADDCI/ext_agg_ref.htm#ADDCI4220


    User_1871
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,608 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
    · Share on TwitterShare on Facebook
  • _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
    · Share on TwitterShare on Facebook
  • 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?

    · Share on TwitterShare on Facebook
  • _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...

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,608 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
    · Share on TwitterShare on Facebook
  • _jum
    _jum Member Posts: 543 Bronze Trophy

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

    · Share on TwitterShare on Facebook
  • 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?

    · Share on TwitterShare on Facebook
  • _jum
    _jum Member Posts: 543 Bronze Trophy

    I honestly have no idea currently!

    · Share on TwitterShare on Facebook
  • _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
    · Share on TwitterShare on Facebook