Aggregate APPEND Islands and XOR polygons
For those not on 12cR1, or even perhaps if you are, the following two requirements came about at a customer site recently.
Aggregate Append
At that site (running 11gR2), there arose a need to aggregate all the islands of Australia into a single multi-polygon sdo_geometry.
The first approach was to use SDO_AGGR_UNION (the customer is licensed for Spatial). Of course, the processing took a long time (not in 12cR1).
Too long.
So, the following approach, using SDO_AGGR_SET_UNION was implemented based on the realisation that the islands never touched each other and so could be appended without the need for any computations. SDO_UTIL.APPEND is the right function but it can’t be used as it is as an aggregate operator. However it can be used in this way as follows.
DROP FUNCTION aggr_append; CREATE FUNCTION aggr_append(p_cursor IN SYS_REFCURSOR) RETURN SDO_GEOMETRY_ARRAY DETERMINISTIC AS v_geom sdo_geometry; v_rgeom sdo_geometry; BEGIN LOOP FETCH p_cursor INTO v_geom; EXIT WHEN p_cursor%NOTFOUND ; IF (p_cursor%ROWCOUNT = 1) THEN v_rgeom := v_geom; ELSE v_rgeom := sdo_util.append(v_rgeom,v_geom); END IF; END LOOP; -- Don't want the kernel throwing too many open cursors exception CLOSE p_cursor; RETURN SDO_GEOMETRY_ARRAY(v_rgeom); END aggr_append; / SHOW errors
Then it can be used to aggregate the islands based on a state_code attrbute as follows.
SELECT f.state_code, SUM(f.AREA_HA) AS area_ha, sdo_aggr_set_union( aggr_append(CURSOR(SELECT b.geom FROM AU_ISLAND b WHERE b.state_code = f.state_code)), 0.005) AS geom FROM AU_ISLAND f GROUP BY state_code;
I don’t have the speed improvement numbers here but hours became minutes. Try it and see.
Aggregate XOR
At the customer site, a multi-polygon with holes was actually implemented as separate polygons – including the holes! The need was to create the actual polygon from these component parts. The best operator for this is SDO_GEOM.SDO_XOR. But how to do this using SQL SELECT GROUP BY? By using the same approach but with a different function?
First construct a new function for use with SDO_AGGR_SET_UNION.
DROP FUNCTION aggr_xor;
CREATE FUNCTION aggr_xor(p_cursor IN SYS_REFCURSOR)
RETURN SDO_GEOMETRY_ARRAY
DETERMINISTIC
AS
/* input sys_refcoursor should be sorted by polygon area descending */
v_geom sdo_geometry;
v_xgeom sdo_geometry;
BEGIN
LOOP
FETCH p_cursor INTO v_geom;
EXIT WHEN p_cursor%NOTFOUND ;
IF (p_cursor%ROWCOUNT = 1) THEN
v_xgeom := v_geom;
ELSE
v_xgeom := sdo_geom.sdo_xor(v_xgeom, v_geom,0.05);
END IF;
END LOOP;
CLOSE p_cursor;
RETURN SDO_GEOMETRY_ARRAY(v_xgeom);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('AGGR_XOR Failed with: ' || SQLERRM);
CLOSE p_cursor;
RETURN NULL;
END aggr_xor;
/
SHOW errors
Now, when calling it, let’s feed the polygons in in descending order of size to try and give the function the main outer shell (etype 1003) first in the hope that this might improve performance.
SELECT ROW_NUMBER() OVER (ORDER BY 1) AS GID, F.BOUNDARY_TYPE_CODE, F.BOUNDARY_NAME, ST_Extract( sdo_aggr_set_union( aggr_xor(CURSOR(SELECT b.coordinates FROM boundary_live B WHERE B.BOUNDARY_TYPE_CODE = F.BOUNDARY_TYPE_CODE AND b.BOUNDARY_NAME = f.BOUNDARY_NAME ORDER BY sdo_geom.sdo_area(B.coordinates,0.005) DESC ) ), 0.05),'POLYGON') AS GEOM FROM boundary_live F GROUP BY f.BOUNDARY_TYPE_CODE, F.BOUNDARY_NAME;
My ST_Extract function is used as the polygons produced included a collection (sdo_gtype=2004) from which we only wanted the polygons.
I hope these ideas are of use to someone.