Skip to Main Content
Forums

SQL & PL/SQL

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!

Custom aggregate function: Collapse vertex rows to polyline

User_1871Apr 30 2022 — edited May 2 2022

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.pngI 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?

Comments

Mike Kutz

Custom Aggregate functions are implemented via Oracle Data Cartridge Interface (ODCI).
They're UDTs that have a specific set of functions. (ODCIAggregate)
User-Defined Aggregate Functions Interface (0 Bytes)

1 person found this helpful
Solomon Yakobson

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.

1 person found this helpful
_jum

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))
1 person found this helpful
User_1871

@jum3
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

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

@_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.

1 person found this helpful
_jum

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

User_1871

@jum3
This collapse is certainly possible in one very smart step...
What's the smart step for merging?

_jum

I honestly have no idea currently!

_jum

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))

1 person found this helpful
odie_63

Isn't it just a matter of CONCAT them together?

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
 UNION ALL      
 SELECT m   , line_id, part_num, vertex_num,3 weight FROM vertices
 )
, t2 as (
 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
 )
 select line_id
   , sdo_aggr_concat_lines(line) 
 from t2
 group by line_id
 ;
1 - 11

Post Details

Added on Apr 30 2022
#spatial
11 comments
249 views
1 mention