|
Free Aggregate Method for Concatenating 2D Lines in Oracle Locator 10g
Sunday June 26 2011 at 15:38
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.
        
|
SpatialDB Advisor
Home
Comment