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


View PDF 
The MDSYS.SDO_SAM package contains subprograms for spatial analysis and data mining.
To use the subprograms in this chapter, you must understand the conceptual information about spatial analysis and data mining in Chapter 8.
Table 211 lists the spatial analysis and mining subprograms.
Table 211 Subprograms for Spatial Analysis and Mining
Function  Description 

SDO_SAM.AGGREGATES_FOR_GEOMETRY 
Computes the thematic aggregate for a geometry. 
SDO_SAM.AGGREGATES_FOR_LAYER 
Computes thematic aggregates for a layer of geometries. 
SDO_SAM.BIN_GEOMETRY 
Computes the mostintersecting tile for a geometry. 
SDO_SAM.BIN_LAYER 
Assigns each location (and the corresponding row) in a data mining table to a spatial bin. 
SDO_SAM.COLOCATED_REFERENCE_FEATURES 
Performs a partial predicatebased join of tables, and materializes the join results into a table. 
SDO_SAM.SIMPLIFY_GEOMETRY 
Simplifies a geometry. 
SDO_SAM.SIMPLIFY_LAYER 
Simplifies a geometry layer. 
SDO_SAM.SPATIAL_CLUSTERS 
Computes clusters using the existing Rtree index, and returns a set of SDO_REGION objects where the geometry column specifies the boundary of each cluster and the geometry_key value is set to null. 
SDO_SAM.TILED_AGGREGATES 
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 . 
SDO_SAM.TILED_BINS 
Tiles a twodimensional space and returns geometries corresponding to those tiles. 
The rest of this chapter provides reference information on the spatial analysis and mining subprograms, listed in alphabetical order.
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
Name of the theme table.
Name of the geometry column in theme_name
.
Any Oracle SQL aggregate function that accepts one or more numeric values and computes a numeric value, such as SUM
, MIN
, MAX
, or AVG
.
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.
Geometry object.
A quoted string containing a distance value and optionally a unit value. 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 the distance
keyword and that may contain the unit
keyword to identify the unit of measurement associated with the distance
value. For example:
'distance=2 unit=km'
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 Section 2.6 for more information about unit of measurement specification.
Examples
The following example computes the thematic aggregate for an area with a 3mile 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 ;
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
Name of the theme table.
Name of the geometry column in theme_name
.
Any Oracle SQL aggregate function that accepts one or more numeric values and computes a numeric value, such as SUM
, MIN
, MAX
, or AVG
.
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.
Name of the data mining table.
Name of the column in tablename
that holds the geometries.
A quoted string containing a distance value and optionally a unit value. See the Usage Notes 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.
The dst_spec
parameter, if specified, is a quoted string that must contain the distance
keyword and that may contain the unit
keyword to identify the unit of measurement associated with the distance
value. For example:
'distance=2 unit=km'
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 Section 2.6 for more information about unit of measurement specification.
Examples
The following example computes the thematic aggregates for all geometries in a table named TEST_TAB for an area with a 3mile 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;
Format
SDO_SAM.BIN_GEOMETRY(
geom IN SDO_GEOMETRY,
tol IN SDO_DIM_ARRAY,
bin_tablename IN VARCHAR2,
bin_colname IN VARCHAR2
) RETURN NUMBER;
or
SDO_SAM.BIN_GEOMETRY(
geom IN SDO_GEOMETRY,
dim IN SDO_DIM_ARRAY,
bin_tablename IN VARCHAR2,
bin_colname IN VARCHAR2
) RETURN NUMBER;
Description
Computes the mostintersecting tile for a geometry.
Parameters
Geometry for which to compute the bin.
Tolerance value (see Section 1.5.5).
Dimensional array for the table that holds the bin geometries.
Name of the table that holds the bin geometries.
Column in bin_tablename
that holds the bin geometries.
Usage Notes
This function bins the geometry to the mostintersecting bin in the specified bin table.
Examples
The following example computes the bin for a specified geometry.
SELECT sdo_sam.bin_geometry(a.geometry, 0.0000005, 'BINTBL', 'GEOMETRY') FROM poly_4pt a, user_sdo_geom_metadata b WHERE b.table_name='POLY_4PT' AND a.gid=1; SDO_SAM.BIN_GEOMETRY(A.GEOMETRY,0.0000005,'BINTBL','GEOMETRY')  43 1 row selected.
Format
SDO_SAM.BIN_LAYER(
tablename IN VARCHAR2,
colname IN VARCHAR2,
bin_tablename IN VARCHAR2,
bin_colname IN VARCHAR2,
bin_id_colname IN VARCHAR2,
commit_interval IN NUMBER DEFAULT 20);
Description
Assigns each location (and the corresponding row) in a data mining table to a spatial bin.
Parameters
Name of the data mining table.
Name of the column in table_name
that holds the location coordinates.
Name of the table that contains information (precomputed for the entire twodimensional space) about the spatial bins.
Column in bin_tablename
that holds the bin geometries.
Name of the column in bin_tablename
that holds the bin ID value of each geometry added to a bin.
Number of bin insert operations to perform before Spatial performs an internal commit operation. If commit_interval
is not specified, a commit is performed after every 20 insert operations.
Usage Notes
This procedure computes the mostintersecting tile for each geometry in a specified layer using the bins in bin_tablename
. The bin ID value for each geometry is added in bin_id_colname
.
Examples
The following example assigns each GEOMETRY column location and corresponding row in the POLY_4PT_TEMP data mining table to a spatial bin, and performs an internal commit operation after each bin table insertion.
CALL SDO_SAM.BIN_LAYER('POLY_4PT_TEMP', 'GEOMETRY', 'BINTBL', 'GEOMETRY', 'BIN_ID', 1);
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 predicatebased join of tables, and materializes the join results into a table.
Parameters
Name of the table with which to join tablename
.
Name of the geometry column in theme_tablename
.
Qualifying WHERE clause predicate to be applied to theme_tablename
.
Name of the data mining table.
Name of the column in tablename
that holds the location coordinates.
Qualifying WHERE clause predicate to be applied to tablename
. Must be a single table predicate, such as 'country_code=10'.
A quoted string containing a distance value and optionally a unit value. See the Usage Notes for an explanation of the format and meaning.
The table in which materialized join results are stored. This table must have the following definition: (tid NUMBER, rid1 VARCHAR2(24), rid2 VARCHAR2(24))
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 predicatebased 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 that must contain the distance
keyword and that may contain the unit
keyword to identify the unit of measurement associated with the distance
value. For example:
'distance=2 unit=km'
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 Section 2.6 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);
Format
SDO_SAM.SIMPLIFY_GEOMETRY(
geom IN SDO_GEOMETRY,
dim IN SDO_DIM_ARRAY,
pct_area_change_limit IN NUMBER DEFAULT 2
) RETURN SDO_GEOMETRY;
or
SDO_SAM.SIMPLIFY_GEOMETRY(
geom IN SDO_GEOMETRY,
tol IN NUMBER,
pct_area_change_limit IN NUMBER DEFAULT 2
) RETURN SDO_GEOMETRY;
Description
Simplifies a geometry.
Parameters
Geometry to be simplified.
Dimensional array for the geometry to be simplified.
Tolerance value (see Section 1.5.5).
The percentage of area changed to be used for each simplification iteration, as explained in the Usage Notes.
Usage Notes
This function reduces the number of vertices in a geometry by internally applying the SDO_UTIL.SIMPLIFY function (documented in Chapter 19) with an appropriate threshold value.
Reducing the number of vertices may result in a change in the area of the geometry. The pct_area_change_limit
parameter specifies how much area change can be tolerated while simplifying the geometry. It is usually a number from 1 to 100. The default value is 2; that is, the area of the geometry can either increase or decrease by at most two percent compared to the original geometry as a result of the geometry simplification.
Examples
The following example simplifies the geometries in the GEOMETRY column of the POLY_4PT_TEMP table.
SELECT sdo_sam.simplify_geometry(a.geometry, 0.00000005) FROM poly_4pt_temp a, user_sdo_geom_metadata b WHERE b.table_name='POLY_4PT_TEMP' ; SDO_SAM.SIMPLIFY_GEOMETRY(A.GEOMETRY,0.00000005)(ORIG_AREA, CUR_AREA, ORIG_LEN,  SDO_SMPL_GEOMETRY(28108.5905, 28108.5905, 758.440118, 758.440118, SDO_GEOMETRY(2 003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(122.4215, 37.7862, 122.422, 37.7869, 122.421, 37.789, 122.42, 37.7866, 122.4215, 37.78 62))) SDO_SMPL_GEOMETRY(4105.33806, 4105.33806, 394.723053, 394.723053, SDO_GEOMETRY(2 003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(122.4019, 37.8052, 122.4027, 37.8055, 122.4031, 37.806, 122.4012, 37.8052, 122.4019, 3 7.8052))) . . . 50 rows selected.
Format
SDO_SAM.SIMPLIFY_LAYER(
theme_tablename IN VARCHAR2,
theme_colname IN VARCHAR2,
smpl_geom_colname IN VARCHAR2,
commit_interval IN NUMBER DEFAULT 10,
pct_area_change_limit IN NUMBER DEFAULT 2);
Description
Simplifies a geometry layer.
Parameters
Name of the table containing the geometry layer to be simplified.
Column in theme_tablename
of type SDO_GEOMETRY containing the geometries to be simplified.
Column in theme_tablename
of type SDO_GEOMETRY into which the simplified geometries are to be placed by this function.
Number of geometries to simplify before Spatial performs an internal commit operation. If commit_interval
is not specified, a commit is performed after every 10 simplification operations.
The percentage of area changed to be used for each simplification iteration, as explained in the Usage Notes for the SDO_SAM.SIMPLIFY_GEOMETRY function.
Usage Notes
This procedure simplifies all geometries in a layer. It is equivalent to calling the SDO_SAM.SIMPLIFY_GEOMETRY function for each geometry in the layer, except that each simplified geometry is put in a separate column in the table instead of being returned to the caller. See also the Usage Notes for the SDO_SAM.SIMPLIFY_GEOMETRY function.
Examples
The following example adds a column named SMPL_GEOM to the POLY_4PT_TEMP table, then simplifies all geometries in the GEOMETRY column of the POLY_4PT_TEMP table, placing each simplified geometry in the SMPL_GEOM column in the same row with its associated original geometry.
ALTER TABLE poly_4pt_temp ADD (smpl_geom mdsys.sdo_geometry); Table altered. EXECUTE sdo_sam.simplify_layer('POLY_4PT_TEMP', 'GEOMETRY', 'SMPL_GEOM'); PL/SQL procedure successfully completed.
Format
SDO_SAM.SPATIAL_CLUSTERS(
tablename IN VARCHAR2,
colname IN VARCHAR2,
max_clusters IN NUMBER,
allow_outliers IN VARCHAR2 DEFAULT 'TRUE',
tablepartition IN VARCHAR2 DEFAULT NULL
) RETURN SDO_REGIONSET;
Description
Computes clusters using the existing Rtree index, and returns a set of SDO_REGION objects where the geometry column specifies the boundary of each cluster and the geometry_key
value is set to null.
Parameters
Name of the data mining table.
Name of the column in tablename
that holds the location coordinates.
Maximum number of clusters to obtain.
TRUE
(the default) causes outlying values (isolated instances) to be included in the spatial clusters; FALSE
causes outlying values not to be included in the spatial clusters. (TRUE
accommodates all data and may result in larger clusters; FALSE
may exclude some data and may result in smaller clusters.)
Name of the partition in tablename
.
Usage Notes
The clusters are computed using the spatial Rtree index on tablename
.
Examples
The following example clusters the locations in cities into at most three clusters, and includes outlying values in the clusters.
SELECT * FROM TABLE(sdo_sam.spatial_clusters('PROJ_CITIES', 'LOCATION', 3, 'TRUE'));
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,
tiling_domain IN SDO_DIM_ARRAY 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
Table containing theme information (for example, demographic information).
Name of the column in the theme_name
table that contains geometry objects.
Any Oracle SQL aggregate function that accepts one or more numeric values and computes a numeric value, such as SUM
, MIN
, MAX
, or AVG
.
Name of a column in the theme_name
table on which to compute aggregate values. An example might be a POPULATION column.
Level to be used to create tiles.
Domain for the tiling level. If the geometry data in the theme_name
table is geodetic, you must specify this parameter. If the geometry data in the theme_name
table is not geodetic and if you do not specify this parameter, the extent associated with the theme_name
table is used.
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, 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.
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.
Format
SDO_SAM.TILED_BINS(
l1 IN NUMBER,
u1 IN NUMBER,
l2 IN NUMBER,
u2 IN NUMBER,
tiling_level IN NUMBER,
srid IN NUMBER DEFAULT NULL
) RETURN SDO_REGIONSET;
Description
Tiles a twodimensional space and returns geometries corresponding to those tiles.
Parameters
Lower bound of the extent in the first dimension.
Upper bound of the extent in the first dimension.
Lower bound of the extent in the second dimension.
Upper bound of the extent in the second dimension.
Level to be used to tile the specified extent.
SRID value to be included for the coordinate system in the returned tile geometries.
Usage Notes
This function returns an object of type SDO_REGIONSET. The SDO_REGIONSET object type is defined as:
TABLE OF SDO_REGION
The SDO_REGION object type is defined as:
Name Null? Type    ID NUMBER GEOMETRY MDSYS.SDO_GEOMETRY
Examples
The following example tiles the entire Earth's surface at the first tiling level, using the standard longitude and latitude coordinate system (SRID 8307). The resulting SDO_REGIONSET object contains four SDO_REGION objects, one for each tile.
SELECT * FROM TABLE(sdo_sam.tiled_bins(180, 180, 90, 90, 1, 8307)) ORDER BY id; ID  GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)  0 SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR AY(180, 90, 0, 0)) 1 SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR AY(180, 0, 0, 90)) 2 SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR AY(0, 90, 180, 0)) 3 SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR AY(0, 0, 180, 90)) 4 rows selected.