SDO_SAM.AGGREGATES_FOR

Format

SDO_SAM.AGGREGATES_FOR_GEOMETRY(
     theme_name       IN VARCHAR2, 
     theme_colname    IN VARCHAR2, 
     aggr_type_string IN VARCHAR2, 
     aggr_col_string  IN VARCHAR2, 
     geom             IN SDO_GEOMETRY, 
     dst_spec         IN VARCHAR2 DEFAULT NULL 
     ) RETURN NUMBER;

Description

Computes the thematic aggregate for a geometry.

Parameters

theme_name: Name of the theme table.
theme_colname: Name of the geometry column in theme_name.
aggr_type_string: Any Oracle SQL aggregate function that accepts one or more numeric values and computes a numeric value, such as SUM, MIN, MAX, or AVG.
aggr_col_string: Name of a column in theme_name on which to compute aggregate values, as explained in the Usage Notes. An example might be a POPULATION column.
geom: Geometry object.
dst_spec: A quoted string specifying either a distance buffer or a number of nearest neighbor geometries to consider. See the Usage Notes for an explanation of the format and meaning.

Usage Notes

For a specific geometry, this function identifies the geometries in the theme_name table, finds their intersection ratio, multiplies the specified aggregate using this intersection ratio, and aggregates it for the geometry. Specifically, for all rows of the theme_name table that intersect with the specified geometry, it returns the value from the following function:

aggr_type_string(aggr_col_string * proportional_area_of_intersection(geometry, theme_name.theme_colname))

The theme_colname column must have a spatial index defined on it. For best performance, insert simplified geometries into this column.

The dst_spec parameter, if specified, is a quoted string that must contain either of the following:

The distance keyword and optionally the unit keyword (unit of measurement associated with the distance value), to specify a buffer around the geometry. For example, 'distance=2 unit=km' specifies a 2-kilometer buffer around the input geometry. If dst_spec is not specified, no buffer is used.

If the unit keyword is specified, the value must be an SDO_UNIT value from the MDSYS.SDO_DIST_UNITS table (for example, 'unit=km'). If the unit keyword is not specified, the unit of measurement associated with the geometry is used. See Unit of Measurement Support for more information about unit of measurement specification.
The sdo_num_res keyword, to specify the number of nearest-neighbor geometries to consider, without considering proportional coverage. For example, 'sdo_num_res=5' could be used in a query that asks for the populations of the five cities that are nearest to a specified point.

Examples

The following example computes the thematic aggregate for an area with a 3-mile radius around a specified point geometry. In this case, the total population of the area is computed based on the proportion of the circle's area within different counties, assuming uniform distribution of population within the counties.

SELECT sdo_sam.aggregates_for_geometry(
  'GEOD_COUNTIES', 'GEOM',
  'sum', 'totpop',
  SDO_GEOMETRY(2001, 8307,
    SDO_POINT_TYPE(-73.943849, 40.6698,NULL),
    NULL, NULL),
  'distance=3 unit=mile')
FROM DUAL a ;