Forum Stats

  • 3,839,092 Users
  • 2,262,450 Discussions


Custom aggregate function: Collapse vertex rows to polyline



  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy

    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