Oracle® Spatial User's Guide and Reference 10g Release 1 (10.1) Part Number B1082601 


View PDF 
This chapter contains reference and usage information for the spatial aggregate functions, which are listed in Table 141.
Table 141 Spatial Aggregate Functions
Method  Description 

SDO_AGGR_CENTROID 
Returns a geometry object that is the centroid ("center of gravity") of the specified geometry objects. 
SDO_AGGR_CONCAT_LINES 
Returns a geometry that concatenates the specified line or multiline geometries. 
SDO_AGGR_CONVEXHULL 
Returns a geometry object that is the convex hull of the specified geometry objects. 
SDO_AGGR_LRS_CONCAT 
Returns an LRS geometry object that concatenates specified LRS geometry objects. 
SDO_AGGR_MBR 
Returns the minimum bounding rectangle of the specified geometry objects. 
SDO_AGGR_UNION 
Returns a geometry object that is the topological union (OR operation) of the specified geometry objects. 
See the usage information about spatial aggregate functions in Section 1.10.
Most of these aggregate functions accept a parameter of type SDOAGGRTYPE, which is described in Section 1.10.1.
Format
SDO_AGGR_CENTROID(
AggregateGeometry SDOAGGRTYPE
) RETURN SDO_GEOMETRY;
Description
Returns a geometry object that is the centroid ("center of gravity") of the specified geometry objects.
Parameters
An object of type SDOAGGRTYPE (see Section 1.10.1) that specifies the geometry column and dimensional array.
Usage Notes
The behavior of the function depends on whether the geometry objects are all polygons, all points, or a mixture of polygons and points:
If the geometry objects are all polygons, the centroid of all the objects is returned.
If the geometry objects are all points, the centroid of all the objects is returned.
If the geometry objects are a mixture of polygons and points (specifically, if they include at least one polygon and at least one point), any points are ignored, and the centroid of all the polygons is returned.
The result is weighted by the area of each polygon in the geometry objects. If the geometry objects are a mixture of polygons and points, the points are not used in the calculation of the centroid. If the geometry objects are all points, the points have equal weight.
See also the information about the SDO_GEOM.SDO_CENTROID function in Chapter 13.
Examples
The following example returns the centroid of the geometry objects in the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1.)
SELECT SDO_AGGR_CENTROID(SDOAGGRTYPE(shape, 0.005)) FROM cola_markets; SDO_AGGR_CENTROID(SDOAGGRTYPE(SHAPE,0.005))(SDO_GTYPE, SDO_SRID, SDO_POINT  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(5.21295938, 5.00744233, NULL), NULL, NUL L)
Format
SDO_AGGR_CONCAT_LINES(
geom SDO_GEOMETRY
) RETURN SDO_GEOMETRY;
Description
Returns a geometry that concatenates the specified line or multiline geometries.
Parameters
Geometry objects.
Usage Notes
Each input geometry must be a twodimensional line or multiline geometry (that is, the SDO_GTYPE value must be 2002 or 2006). This function is not supported for LRS geometries. To perform an aggregate concatenation of LRS geometric segments, use the SDO_AGGR_LRS_CONCAT spatial aggregate function.
The input geometries must be line strings whose vertices are connected by straight line segments. Circular arcs and compound line strings are not supported.
The topological relationship between the geometries in each pair of geometries to be concatenated must be DISJOINT or TOUCH; and if the relationship is TOUCH, the geometries must intersect only at two end points.
You can use the SDO_UTIL.CONCAT_LINES function (described in Chapter 19) to concatenate two line or multiline geometries.
An exception is raised if any input geometries are not line or multiline geometries, or if not all input geometries are based on the same coordinate system.
Examples
The following example inserts two line string geometries in the COLA_MARKETS table, and then returns the aggregate concatenation of these geometries. (The example uses the data definitions from Section 2.1.)
 First, insert two line geometries. INSERT INTO cola_markets VALUES(1001, 'line_1', SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(1,1, 5,1))); INSERT INTO cola_markets VALUES(1002, 'line_2', SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(5,1, 8,1)));  Perform aggregate concatenation of all line geometries in layer. SELECT SDO_AGGR_CONCAT_LINES(c.shape) FROM cola_markets c WHERE c.mkt_id > 1000; SDO_AGGR_CONCAT_LINES(C.SHAPE)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY( 1, 1, 5, 1, 8, 1))
Format
SDO_AGGR_CONVEXHULL(
AggregateGeometry SDOAGGRTYPE
) RETURN SDO_GEOMETRY;
Description
Returns a geometry object that is the convex hull of the specified geometry objects.
Parameters
An object of type SDOAGGRTYPE (see Section 1.10.1) that specifies the geometry column and dimensional array.
Usage Notes
See also the information about the SDO_GEOM.SDO_CONVEXHULL function in Chapter 13.
Examples
The following example returns the convex hull of the geometry objects in the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1.)
SELECT SDO_AGGR_CONVEXHULL(SDOAGGRTYPE(shape, 0.005)) FROM cola_markets; SDO_AGGR_CONVEXHULL(SDOAGGRTYPE(SHAPE,0.005))(SDO_GTYPE, SDO_SRID, SDO_POI  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(8, 1, 10, 7, 10, 11, 8, 11, 6, 11, 1, 7, 1, 1, 8, 1))
Format
SDO_AGGR_LRS_CONCAT(
AggregateGeometry SDOAGGRTYPE
) RETURN SDO_GEOMETRY;
Description
Returns an LRS geometry that concatenates specified LRS geometries.
Parameters
An object of type SDOAGGRTYPE (see Section 1.10.1) that specifies the geometry column and dimensional array.
Usage Notes
This function performs an aggregate concatenation of any number of LRS geometries. If you want to control the order in which the geometries are concatenated, you must use a subquery with the NO_MERGE optimizer hint and the ORDER BY clause. (See the examples.)
The direction of the resulting segment is the same as the direction of the first geometry in the concatenation.
A 3D format of this function (SDO_AGGR_LRS_CONCAT_3D) is available. For information about 3D formats of LRS functions, see Section 7.4.)
For information about the Spatial linear referencing system, see Chapter 7.
Examples
The following example adds an LRS geometry to the LRS_ROUTES table, and then performs two queries that concatenate the LRS geometries in the table. The first query does not control the order of concatenation, and the second query controls the order of concatenation. Notice the difference in direction of the two segments: the segment resulting from the second query has decreasing measure values because the first segment in the concatenation (Route0
) has decreasing measure values. (This example uses the definitions from the example in Section 7.7.)
 Add a segment with route_id less than 1 (here, zero). INSERT INTO lrs_routes VALUES( 0, 'Route0', SDO_GEOMETRY( 3302,  Line string; 3 dimensions (X,Y,M); 3rd is measure dimension. NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),  One line string, straight segments SDO_ORDINATE_ARRAY( 5,14,5,  Starting point  5 is measure from start. 10,14,0)  Ending point  0 measure (decreasing measure) ) ); 1 row created.  Concatenate all routes (no ordering specified). SELECT SDO_AGGR_LRS_CONCAT(SDOAGGRTYPE(route_geometry, 0.005)) FROM lrs_routes; SDO_AGGR_LRS_CONCAT(SDOAGGRTYPE(ROUTE_GEOMETRY,0.005))(SDO_GTYPE, SDO_SRID  SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY( 2, 2, 0, 2, 4, 2, 8, 4, 8, 12, 4, 12, 12, 10, 18, 8, 10, 22, 5, 14, 27, 10, 14, 32))  Aggregate concatenation using subquery for ordering. SELECT SDO_AGGR_LRS_CONCAT(SDOAGGRTYPE(route_geometry, 0.005)) FROM ( SELECT /*+ NO_MERGE */ route_geometry FROM lrs_routes ORDER BY route_id); SDO_AGGR_LRS_CONCAT(SDOAGGRTYPE(ROUTE_GEOMETRY,0.005))(SDO_GTYPE, SDO_SRID  SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY( 2, 2, 32, 2, 4, 30, 8, 4, 24, 12, 4, 20, 12, 10, 14, 8, 10, 10, 5, 14, 5, 10, 14 , 0))
Format
SDO_AGGR_MBR(
geom SDO_GEOMETRY
) RETURN SDO_GEOMETRY;
Description
Returns the minimum bounding rectangle (MBR) of the specified geometries, that is, a single rectangle that minimally encloses the geometries.
Parameters
Geometry objects.
Usage Notes
Use this function instead of the deprecated SDO_TUNE.EXTENT_OF function to return the MBR of geometries. The SDO_TUNE.EXTENT_OF function is limited to twodimensional geometries, whereas this function is not.
All input geometries must have 4digit SDO_GTYPE values (explained in Section 2.2.1).
This function does not return an MBR geometry if a proper MBR cannot be constructed. Specifically:
If the input geometries are all null, the function returns a null geometry.
If all data in the input geometries is on a single point, the function returns the point.
If all data in the input geometries consists of points on a straight line, the function returns a twopoint line.
Examples
The following example returns the minimum bounding rectangle of the geometry objects in the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1.)
SELECT SDO_AGGR_MBR(shape) FROM cola_markets; SDO_AGGR_MBR(C.SHAPE)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SD  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR AY(1, 1, 10, 11))
Format
SDO_AGGR_UNION(
AggregateGeometry SDOAGGRTYPE
) RETURN SDO_GEOMETRY;
Description
Returns a geometry object that is the topological union (OR operation) of the specified geometry objects.
Parameters
An object of type SDOAGGRTYPE (see Section 1.10.1) that specifies the geometry column and dimensional array.
Usage Notes
See also the information about the SDO_GEOM.SDO_UNION function in Chapter 13.
Examples
The following example returns the union of the first three geometry objects in the COLA_MARKETS table (that is, all except cola_d
). (The example uses the definitions and data from Section 2.1.)
SELECT SDO_AGGR_UNION( SDOAGGRTYPE(c.shape, 0.005)) FROM cola_markets c WHERE c.name < 'cola_d'; SDO_AGGR_UNION(SDOAGGRTYPE(C.SHAPE,0.005))(SDO_GTYPE, SDO_SRID, SDO_POINT(  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))
See also the more complex SDO_AGGR_UNION example in Section C.4.