SDO_AGGR_SET

Format (as Function in SDO_AGGR Package)

SDO_AGGR.SDO_AGGR_SET_UNION(
     geometry SDO_GEOMETRY_ARRAY, 
     tol      NUMBER 
     ) RETURN SDO_GEOMETRY;

or

SDO_AGGR.SDO_AGGR_SET_UNION(
     cur  SYS_REFCURSOR,
     tol  NUMBER      
     ) RETURN SDO_GEOMETRY;

Format (as Spatial Aggregate Function [Deprecated])

SDO_AGGR_SET_UNION(
     geometry  SDO_GEOMETRY_ARRAY, 
     tol       NUMBER 
     ) RETURN SDO_GEOMETRY;

Description

Returns the aggregate union of all the geometry objects from an input VARRAY of SDO_GEOMETRY objects or a REF cursor.

Parameters

geometry: An array of geometry objects of object type SDO_GEOMETRY_ARRAY, which is defined as VARRAY OF SDO_GEOMETRY.
cur: A REF cursor that returns a set of the geometry objects for which to return the aggregate union of all the geometry objects.
tol: Tolerance value (see Tolerance).

Usage Notes

Note:

Effective with Oracle Database Release 12c (12.1), the format as a spatial aggregate function is deprecated, and will be removed in an upcoming major release.

You should instead use one of the formats for the SDO_AGGR.SDO_AGGR_SET_UNION function.

SDO_AGGR_SET_UNION provides faster performance than SDO_AGGR_UNION but less flexibility, and SDO_AGGR_SET_UNION should be considered when performance is especially important and when it satisfies your functional needs.

SDO_AGGR_UNION is a SQL aggregate function, and therefore it is very flexible and can be used with complex SQL GROUP BY clauses. However, SDO_AGGR_SET_UNION can be much faster than SDO_AGGR_UNION. SDO_AGGR_SET_UNION is useful when the geometries to be grouped can easily be gathered into a collection, such as a VARRAY of SDO_GEOMETRY objects).

SDO_AGGR_SET_UNION:

Cannot aggregate a set of overlapping polygons. For overlapping polygons, use SDO_AGGR_UNION.
Can effectively aggregate a set of non-overlapping polygons, including polygons that touch.
Can aggregate sets of lines and points, even if they overlap.

Examples

The following example creates a generic routine to build a geometry set to pass to SDO_AGGR_SET_UNION. It takes as input a table name, column name, and optional predicate to apply, and returns an SDO_GEOMETRY_ARRAY ready to use with SDO_AGGR_SET_UNION. The first SELECT statement after the get_geom_set function creation calls the SDO_AGGR.SDO_AGGR_UNION PL/SQL function, the second SELECT statement calls the deprecated spatial aggregate function, and the third SELECT statement calls the SDO_AGGR.SDO_AGGR_UNION PL/SQL function using the format that specifies a cursor. All the SELECT statements return the same result. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

CREATE OR REPLACE FUNCTION get_geom_set (table_name  VARCHAR2,
                                         column_name VARCHAR2,
                                         predicate   VARCHAR2 := NULL)
  RETURN SDO_GEOMETRY_ARRAY DETERMINISTIC AS
 
  type            cursor_type is REF CURSOR;
  query_crs       cursor_type ;
  g               SDO_GEOMETRY;
  GeometryArr     SDO_GEOMETRY_ARRAY;
  where_clause    VARCHAR2(2000);
BEGIN
  IF predicate IS NULL
  THEN
    where_clause := NULL;
  ELSE
    where_clause := ' WHERE ';
  END IF;
 
  GeometryArr := SDO_GEOMETRY_ARRAY();
  OPEN query_crs FOR ' SELECT ' || column_name ||
                     ' FROM ' || table_name ||
                       where_clause || predicate;
   LOOP
    FETCH query_crs into g;
     EXIT when query_crs%NOTFOUND ;
     GeometryArr.extend;
     GeometryArr(GeometryArr.count) := g;
   END LOOP;
   RETURN GeometryArr;
END;
/
 
-- Call SDO_AGGR_SET_UNION function in SDO_AGGR PL/SQL package.
SELECT sdo_aggr.sdo_aggr_set_union (get_geom_set ('COLA_MARKETS', 'SHAPE',
  'name <> ''cola_c'''), .0005 ) FROM dual;

SDO_AGGR.SDO_AGGR_SET_UNION(GET_GEOM_SET('COLA_MARKETS','SHAPE','NAME<>''COLA_C'
--------------------------------------------------------------------------------
SDO_GEOMETRY(2007, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 2, 11, 1003, 1), SDO
_ORDINATE_ARRAY(8, 11, 6, 9, 8, 7, 10, 9, 8, 11, 1, 7, 1, 1, 5, 1, 8, 1, 8, 6, 5
, 7, 1, 7))

-- CALL SDO_AGGR_SET_UNION spatial aggregate function (deprecated format).
SELECT sdo_aggr_set_union (get_geom_set ('COLA_MARKETS', 'SHAPE',
  'name <> ''cola_c'''), .0005 ) FROM dual;

SDO_AGGR.SDO_AGGR_SET_UNION(GET_GEOM_SET('COLA_MARKETS','SHAPE','NAME<>''COLA_C'
--------------------------------------------------------------------------------
SDO_GEOMETRY(2007, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 2, 11, 1003, 1), SDO
_ORDINATE_ARRAY(8, 11, 6, 9, 8, 7, 10, 9, 8, 11, 1, 7, 1, 1, 5, 1, 8, 1, 8, 6, 5
, 7, 1, 7))

-- Call SDO_AGGR_SET_UNION function in SDO_AGGR PL/SQL package; specify
-- a cursor as input.
SELECT sdo_aggr.sdo_aggr_set_union 
  (CURSOR(SELECT shape FROM COLA_MARKETS WHERE name <> 'cola_c'), .0005 )
  FROM dual;

SDO_AGGR.SDO_AGGR_SET_UNION(CURSOR(SELECTSHAPEFROMCOLA_MARKETSWHERENAME<>'COLA_C
--------------------------------------------------------------------------------
SDO_GEOMETRY(2007, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 2, 11, 1003, 1), SDO
_ORDINATE_ARRAY(8, 11, 6, 9, 8, 7, 10, 9, 8, 11, 1, 7, 1, 1, 5, 1, 8, 1, 8, 6, 5
, 7, 1, 7))