SDO_SAM.COLOCATED_REFERENCE

Format

SDO_SAM.COLOCATED_REFERENCE_FEATURES(
     theme_tablename  IN VARCHAR2, 
     theme_colname    IN VARCHAR2, 
     theme_predicate  IN VARCHAR2, 
     tablename        IN VARCHAR2, 
     colname          IN VARCHAR2, 
     ref_predicate    IN VARCHAR2, 
     dst_spec         IN VARCHAR2, 
     result_tablename IN VARCHAR2, 
     commit_interval  IN NUMBER DEFAULT 100);

Description

Performs a partial predicate-based join of tables, and materializes the join results into a table.

Parameters

theme_tablename: Name of the table with which to join tablename.
theme_colname: Name of the geometry column in theme_tablename.
theme_predicate: Qualifying WHERE clause predicate to be applied to theme_tablename.
tablename: Name of the data mining table.
colname: Name of the column in tablename that holds the location coordinates.
ref_predicate: Qualifying WHERE clause predicate to be applied to tablename. Must a null or non-null value: if null, no predicate is applied; if non-null, it must be a single table predicate, such as 'country_code=10'.
dst_spec: A quoted string containing a distance value and optionally a unit value for a buffer around the geometries. See the Usage Notes for an explanation of the format and meaning.
result_tablename: The table in which materialized join results are stored. This table must exist and must have the following definition: (tid NUMBER, rid1 VARCHAR2(24), rid2 VARCHAR2(24))
commit_interval: Number of internal join operations to perform before Spatial performs an internal commit operation. If commit_interval is not specified, a commit is performed after every 100 internal join operations.

Usage Notes

This procedure materializes each pair of rowids returned from a predicate-based join operation, and stores them in the rid1, rid2 columns of result_tablename. The tid is a unique generated "interaction" number corresponding to each rid1 value.

The dst_spec parameter, if specified, is a quoted string containing 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.

Examples

The following example identifies cities with a 1990 population (POP90 column value) greater than 120,000 that are located within 20 kilometers of interstate highways (GEOM column in the GEOD_INTERSTATES table). It stores the results in a table named COLOCATION_TABLE, and performs an internal commit operation after each 20 internal operations.

EXECUTE SDO_SAM.COLOCATED_REFERENCE_FEATURES(
  'geod_cities', 'location', 'pop90 > 120000',
  'geod_interstates', 'geom', null,
  'distance=20 unit=km', 'colocation_table', 20);