For those not on 12cR1, or even perhaps if you are, the following two requirements came about at a customer site recently.
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).
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.
CREATEFUNCTION aggr_append(p_cursor IN SYS_REFCURSOR)
FETCH p_cursor INTO v_geom;
EXIT WHEN p_cursor%NOTFOUND ;
v_rgeom := v_geom;
v_rgeom := sdo_util.append(v_rgeom,v_geom);
-- Don't want the kernel throwing too many open cursors exception
Then it can be used to aggregate the islands based on a state_code attrbute as follows.
FROM AU_ISLAND b
WHERE b.state_code = f.state_code)),
FROM AU_ISLAND f
I don’t have the speed improvement numbers here but hours became minutes. Try it and see.
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 SQLSELECTGROUP BY? By using the same approach but with a different function?
First construct a new function for use with SDO_AGGR_SET_UNION.
CREATEFUNCTION aggr_xor(p_cursor IN SYS_REFCURSOR)
/* input sys_refcoursor should be sorted by polygon area descending */