SDO_SAM.AGGREGATES_FOR

Format

SDO_SAM.AGGREGATES_FOR_LAYER(
     theme_name       IN VARCHAR2, 
     theme_colname    IN VARCHAR2, 
     aggr_type_string IN VARCHAR2, 
     aggr_col_string  IN VARCHAR2, 
     tablename        IN VARCHAR2, 
     colname          IN VARCHAR2, 
     dst_spec         IN VARCHAR2 DEFAULT NULL 
     ) RETURN SDO_REGAGGRSET;

Description

Computes thematic aggregates for a layer of geometries.

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.
tablename: Name of the data mining table.
colname: Name of the column in tablename that holds the geometries.
dst_spec: A quoted string specifying either a distance buffer or a number of nearest neighbor geometries to consider. See the Usage Notes for the SDO_SAM.AGGREGATES_FOR_GEOMETRY function in this chapter for an explanation of the format and meaning.

Usage Notes

For each geometry in tablename, 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 each geometry in tablename. Specifically, for all rows of the theme_name table, it returns the value from the following function:

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

This function returns an object of type SDO_REGAGGRSET. The SDO_REGAGGRSET object type is defined as:

TABLE OF SDO_REGAGGR

The SDO_REGAGGR object type is defined as:

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 REGION_ID                                          VARCHAR2(24)
 GEOMETRY                                           MDSYS.SDO_GEOMETRY
 AGGREGATE_VALUE                                    NUMBER

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

Examples

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

SELECT a.aggregate_value FROM TABLE(sdo_sam.aggregates_for_layer(
  'GEOD_COUNTIES', 'GEOM', 'SUM', TOTPOP', TEST_TAB', 'GEOM'
    'distance=3 unit=mile')) a;