Free Aggregate Method for Concatenating 2D Lines in Oracle Locator 10g

The SDO_AGGR_CONCAT_LINES function in Oracle 10g and above allows for linestrings to be concatenated via GROUP BY SQL clauses.

However, this function is only available for Spatial users on Enterprise Edition of the Oracle database 10g (it can be used on 11g).

Locator users can, however, use the mdsys.sdo_util.concat_lines function to append two linestrings together.

This function can be used as the basis for a custom, license free, aggregation function as the following shows.

 -- First we need to create an collection type
 --
 Create Or Replace Type GeometrySet Is Table Of mdsys.SDO_GEOMETRY;
 /
 Grant execute on GeometrySet to public;
  
 -- Now we create a function that uses that type along with the mdsys.sdo_util.concat_lines function
 --
 create or replace
 Function concatLines(p_lines IN geometrySet) 
   Return mdsys.sdo_geometry 
 Is
   v_geometry mdsys.sdo_geometry;
 Begin
   IF ( p_lines is null ) THEN
       Return NULL;
   END IF;
   IF ( p_lines.COUNT = 1 ) THEN
       Return p_lines(1);
   Else
     v_geometry := p_lines(1);
     FOR i IN (p_lines.FIRST+1) .. p_lines.LAST LOOP
       v_geometry := mdsys.sdo_util.concat_lines(v_geometry,p_lines(i));
     END LOOP;
     Return v_geometry;
   End If;
 End concatLines;
 /
 SHOW ERRORS
 
 Grant execute on concatLines to public;

Now, let’s test our function….

 select id, concatLines(CAST(COLLECT(a.GEOM) as geometrySet)) as aggregatedLines
   from (select 1 as id, MDSYS.SDO_GEOMETRY(2002,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(127.8819,-39.9566,143.774,-39.1098)) as geom from dual
         union all 
         select 1 as id, MDSYS.SDO_GEOMETRY(2002,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(143.774,-39.1098,128.8819,-38.9566)) as geom from dual
         union all
         select 2 as id, MDSYS.SDO_GEOMETRY(2002,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(27.8819,-3.9566,28.774,-3.1098)) as geom from dual
         union all 
         select 2 as id, MDSYS.SDO_GEOMETRY(2002,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(28.774,-3.1098,27.8819,-3.9566)) as geom from dual
         ) a
 group by id;
 
 -- mdsys.sdo_util.concat_lines doesn't support 3D linestrings. These must be degrated to 2D linestrings to use this function
 --
 ID AGGREGATEDLINES
 -- -----------------------------------------------------------------------------------------------------------------------------------------------------------
 1  MDSYS.SDO_GEOMETRY(2002, 4326, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.SDO_ORDINATE_ARRAY(127.8819, -39.9566, 143.774, -39.1098, 128.8819, -38.9566))
 2  MDSYS.SDO_GEOMETRY(2002, 4326, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.SDO_ORDINATE_ARRAY(27.8819, -3.9566, 28.774, -3.1098, 27.8819, -3.9566))
 
 
 select id, concatLines(CAST(COLLECT(geom.to_2d(a.GEOM)) as geometrySet)) as aggregatedLines
   from (select 1 as id, MDSYS.SDO_GEOMETRY(3002,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(127.8819,-39.9566,1,143.774,-39.1098,2)) as geom from dual
         union all 
         select 1 as id, MDSYS.SDO_GEOMETRY(3002,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(143.774,-39.1098,3,128.8819,-38.9566,4)) as geom from dual
         union all
         select 2 as id, MDSYS.SDO_GEOMETRY(3002,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(27.8819,-3.9566,5,28.774,-3.1098,6)) as geom from dual
         union all 
         select 2 as id, MDSYS.SDO_GEOMETRY(3002,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(28.774,-3.1098,7,27.8819,-3.9566,8)) as geom from dual
         ) a
 group by id;
 
 ID AGGREGATEDLINES
 -- -----------------------------------------------------------------------------------------------------------------------------------------------------------
 1  MDSYS.SDO_GEOMETRY(2002, 4326, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.SDO_ORDINATE_ARRAY(127.8819, -39.9566, 143.774, -39.1098, 128.8819, -38.9566))
 2  MDSYS.SDO_GEOMETRY(2002, 4326, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.SDO_ORDINATE_ARRAY(27.8819, -3.9566, 28.774, -3.1098, 27.8819, -3.9566))

I hope this helps someone.