SDO_SAM.TILED_AGGREGATES

Format

SDO_SAM.TILED_AGGREGATES(
     theme_name       IN VARCHAR2, 
     theme_colname    IN VARCHAR2, 
     aggr_type_string IN VARCHAR2, 
     aggr_col_string  IN VARCHAR2, 
     tiling_level     IN NUMBER DEFAULT NULL, 
     tiling_domain    IN SDO_DIM_ARRAY DEFAULT NULL, 
     zero_agg_tiles   IN NUMBER DEFAULT 0, 
     xdivs            IN NUMBER DEFAULT NULL, 
     ydivs            IN NUMBER DEFAULT NULL 
     ) RETURN SDO_REGAGGRSET;

Description

Tiles aggregates for a domain. For each tile, computes the intersecting geometries from the theme table; the values in the aggr_col_string column are weighted proportionally to the area of the intersection, and aggregated according to aggr_col_string.

Parameters

theme_name: Table containing theme information (for example, demographic information).
theme_colname: Name of the column in the theme_name table that contains geometry objects.
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 the theme_name table on which to compute aggregate values. An example might be a POPULATION column.
tiling_level: Level to be used to create tiles. If you specify this parameter, the extent of each dimension is divided into 2^tiling_level parts, resulting in at most 4*tiling_level tiles. (Specify either this parameter or the combination of the xdivs and ydivs parameters.)
tiling_domain: Domain for the tiling level. The parameter is not required, and if you do not specify it, the extent associated with the theme_name table is used.
zero_agg_tiles: Specify 0 to exclude tiles that have a value of 0 for the computed aggregate, or specify 1 to return all tiles. The default value is 0, which ensures that only tiles with a nonzero aggregate value are returned.
xdivs: The number of times that the extent in the first dimension is divided, such that the total number of parts is xdivs + 1. For example, if you specify 10 for xdivs, the extent of the first dimension is divided into 11 parts.
ydivs: The number of times that the extent in the second dimension is divided, such that the total number of parts is ydivs + 1. For example, if you specify 10 for ydivs, the extent of the second dimension is divided into 11 parts.

Usage Notes

This function is similar to SDO_SAM.AGGREGATES_FOR_LAYER, but the results are dynamically generated using tiling information. Given a theme_name table, the tiling domain is determined. Based on the tiling_level value or the xdivs and ydivs values, the necessary tiles are generated. For each tile geometry, thematic aggregates are computed as described in the Usage Notes for SDO_SAM.AGGREGATES_FOR_LAYER.

You must specify either the tiling_level parameter or both the xdivs and ydivs parameters. If you specify all three of these parameters, the tiling_level parameter is ignored and the xdivs and ydivs parameters are used.

If you specify the xdivs and ydivs parameters, the total number of grids (tiles) returned is (xdivs+1)*(ydivs+1).

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

Examples

The following example computes the sum of the population rows of POLY_4PT_TEMP table intersecting with each tile. The extent of the POLY_4PT_TEMP table stored in the USER_SDO_GEOM_METADATA view is used as the domain, and a tiling level of 2 is used (that is, the domain is divided into 16 tiles).

SELECT a.geometry, a.aggregate_value
  from TABLE(sdo_sam.tiled_aggregates('POLY_4PT_TEMP',
                        'GEOMETRY', 'SUM', 'POPULATION', 2)) a;
 
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
AGGREGATE_VALUE
---------------
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR
AY(-180, -90, -90, -45))
     .007150754
 
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR
AY(-180, -45, -90, 0))
     .034831005
 
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR
AY(-180, 0, -90, 45))
     7.73307783
 
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR
AY(-90, -90, 0, -45))
     .019498368
 
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR
AY(-90, -45, 0, 0))
     .939061456
 
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR
AY(-90, 0, 0, 45))
     1.26691592
 
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR
AY(0, 0, 90, 45))
             40
 
7 rows selected.