9
Spatial Operators
This chapter describes the operators used when working with the spatial object data type. The operators are listed in Table 9-1.
Table 9-1 Spatial Usage Operators
SDO_FILTER
Purpose
This operator uses the spatial index to identify either the set of spatial objects that may spatially interact with a given object (such as an area-of-interest,) or pairs of spatial objects that might spatially interact. Objects spatially interact if they are not disjoint. This operator performs only a primary filter operation.
Syntax
SDO_FILTER(geometry1, geometry2, params) ;
Keywords and Parameters
|
geometry1 |
Specifies a geometry column in a table. The column must be spatially indexed.
Data type is MDSYS.SDO_GEOMETRY. |
|
geometry2 |
Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.)
Data type is MDSYS.SDO_GEOMETRY. |
|
PARAMS |
Determines the behavior of the operator. Data type is VARCHAR2. |
|
Keyword |
Description |
|
querytype |
Specifies valid query types: JOIN or WINDOW. This is a required parameter.
WINDOW implies that geometry2 should be considered a dynamic (transient) area-of-interest. Use WINDOW when you want to compare a single geometry (geometry2) to all the geometries in a column (geometry1).
JOIN implies that the second argument refers to a table column that must have a spatial index built on it. Use JOIN when you want to compare all the geometries of a column to all the geometries of another column. |
|
idxtab1 |
Not supported in this release. Specifies the name of the index, if there are multiple spatial indexes, for geometry1. |
|
idxtab2 |
Not supported in this release. Specifies the name of the index table (if there are multiple spatial indexes) for geometry2. Only valid for 'querytype = JOIN.' |
|
layer_gtype |
Specifies special processing for point data.
If the columns you are comparing are comprised soley of point data, set this parameter to 'POINT' for optimal performance.
Data type is VARCHAR2.
Default is 'NOTPOINT'. |
Returns
The expression SDO_FILTER(arg1, arg2, arg3) = `TRUE' evaluates to TRUE for object pairs that are non-disjoint and FALSE otherwise.
Usage Notes
- The operator must always be used in a WHERE clause and the condition that includes the operator should be an expression of the form SDO_FILTER(arg1, arg2, arg3) = `TRUE'.
- If the querytype is 'WINDOW', geometry2 can come from a table or be a transient SDO_GEOMETRY object (such as a bind variable or SDO_GEOMETRY constructor). If geometry2 is transient, it is indexed in memory.
If geometry2 comes from a table column that is not spatially indexed, geometry2 is indexed in memory.
If geometry2 comes from a table column that is spatially indexed, geometry2 will reuse its index. Performance will degrade if geometry2 is not indexed with the same sdo_level parameter as geometry1.
Examples
- SELECT A.gid
FROM Polygons A, query_polys B
WHERE B.gid = 1
AND SDO_FILTER(A.Geometry, B.Geometry, 'querytype = WINDOW') = 'TRUE';
- SELECT A.gid
FROM Polygons A, query_polys B
WHERE SDO_FILTER(A.Geometry, B.Geometry, 'querytype = JOIN') = 'TRUE';
- Select A.Gid
FROM Polygons A
WHERE SDO_FILTER(A.Geometry, :aGeom, 'querytype=WINDOW') = 'TRUE';
- Select A.Gid
FROM Polygons A
WHERE SDO_FILTER(A.Geometry, mdsys.sdo_geometry(3,NULL,NULL,
mdsys.sdo_elem_info(1,3,3),
mdsys.sdo_ordinates(x1,y1,x2,y2)),
'querytype=WINDOW') = 'TRUE';
Related Topics
SDO_RELATE
SDO_RELATE
Purpose
This operator uses the spatial index to identify either the set of spatial objects that have a particular spatial interaction with a given object such as an area-of-interest, or pairs of spatial objects that have a particular spatial interaction.
This operator performs both primary and secondary filter operations.
Syntax
SDO_RELATE(geometry1, geometry2, params) ;
Keywords and Parameters
|
geometry1 |
Specifies a geometry column in a table. The column must be spatially indexed.
Data type is MDSYS.SDO_GEOMETRY. |
|
geometry2 |
Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.)
Data type is MDSYS.SDO_GEOMETRY. |
|
PARAMS |
Determines the behavior of the operator.
Data type is VARCHAR2. |
|
Keyword |
Description |
|
mask |
Specifies the topological relation of interest. This is a required parameter.
Valid values are one or more of {TOUCH, OVERLAP, EQUAL, INSIDE, COVEREDBY, CONTAINS, COVERS, ANYINTERACT} in the 9-intersection pattern. Multiple masks are combined with a the logical Boolean operator OR as follows: 'mask=(inside+touch)'. See Section 1.7 for an explanation of the 9-intersection relationship pattern. |
|
querytype |
Valid query types are: JOIN or WINDOW. This is a required parameter.
WINDOW implies that geometry2 should be considered a dynamic (transient) area-of-interest. Use WINDOW when you want to compare a single geometry (geometry2) to all the geometries in a column (geometry1).
JOIN implies that the second argument refers to a table column that must have a spatial index built on it. Use JOIN when you want to compare all the geometries of a column to all the geometries of another column. |
|
idxtab1 |
Not supported in this release. Specifies the name of the index, if there are multiple spatial indexes, for geometry1. |
|
idxtab2 |
Not supported in this release. Specifies the name of the index, if there are multiple spatial indexes, for geometry2. Only valid for 'querytype = JOIN'. |
|
layer_gtype |
Specifies special processing for point data.
If the columns you are comparing are composed soley of point data, set this parameter to 'POINT' for optimal performance.
Data type is VARCHAR2.
Default is 'NOTPOINT'. |
Returns
The expression SDO_RELATE(geometry1,geometry2, `mask = <some_mask_val> querytype = <some_querytype>') = `TRUE' evaluates to TRUE for object pairs that have the topological relationship specified by <some_mask_val> and FALSE otherwise.
Usage Notes
- The operator must always be used in a WHERE clause, and the condition that includes the operator should be an expression of the form SDO_RELATE(arg1, arg2, `mask = <some_mask_val> querytype = <some_querytype>') = `TRUE'.
- If the query type is 'WINDOW', geometry2 can come from a table or be a transient SDO_GEOMETRY object (such as a bind variable or SDO_GEOMETRY constructor). If geometry2 is transient, it is indexed in memory.
If geometry2 comes from a table column that is not spatially indexed, geometry2 is indexed in memory.
If geometry2 comes from a table column that is spatially indexed, geometry2 will re-use its index. Performance will degrade if geometry2 is not indexed with the same sdo_level parameter as geometry1.
- Unlike the SDO_GEOM.RELATE function, DISJOINT and DETERMINE masks are not allowed in the relationship mask. This is because SDO_RELATE uses the spatial index to find candidates that may interact, and the information to satisfy DISJOINT or DETERMINE is not present in the index.
Examples
- SELECT A.gid
FROM Polygons A, query_polys B
WHERE B.gid = 1
AND SDO_RELATE(A.Geometry, B.Geometry,
'mask=ANYINTERACT querytype = WINDOW') = 'TRUE';
- SELECT A.gid
FROM Polygons A, query_polys B
WHERE SDO_RELATE(A.Geometry, B.Geometry,
'mask=ANYINTERACT querytype = JOIN') = 'TRUE';
- Select A.Gid
FROM Polygons A
WHERE SDO_RELATE(A.Geometry, :aGeom, 'mask=ANYINTERACT querytype=WINDOW')
= 'TRUE';
- Select A.Gid
FROM Polygons A
WHERE SDO_RELATE(A.Geometry, mdsys.sdo_geometry(3,NULL,NULL,
mdsys.sdo_elem_info(1,3,3),
mdsys.sdo_ordinates(x1,y1,x2,y2)),
'mask=ANYINTERACT querytype=WINDOW') = 'TRUE';
Related Topics
- SDO_FILTER
- SDO_WITHIN_DISTANCE
- SDO_GEOM.RELATE( )
SDO_WITHIN_DISTANCE
Purpose
This operator uses the spatial index to identify the set of spatial objects that are within some specified Euclidean distance of a given object (such as an area or point-of-interest.)
Syntax
SDO_WITHIN_DISTANCE(T.column, aGeom, params) ;
Keywords and Parameters
|
T.column |
Specifies a geometry column in a table. The column must be spatially indexed.
Data type is MDSYS.SDO_GEOMETRY. |
|
aGeom |
Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.)
Data type is MDSYS.SDO_GEOMETRY. |
|
PARAMS |
Determines the behavior of the operator.
Data type is VARCHAR2. |
|
Keyword |
Description |
|
distance |
Specifies the Euclidean distance value. This is a required parameter.
Data type is NUMBER. |
|
idxtab1 |
Not supported in this release. Specifies the name of the index if there are multiple spatial index tables for geometry1. |
|
querytype |
Set 'querytype=FILTER' to perform only a primary filter operation. If querytype is not specified, both primary and secondary filter operations are performed (default).
Data type is VARCHAR2. |
|
layer_gtype |
Specifies special processing for point data.
If the columns you are comparing are composed soley of point data, set this parameter to 'POINT' for optimal performance.
Data type is VARCHAR2.
Default is 'NOTPOINT'. |
Returns
The expression SDO_WITHIN_DISTANCE(arg1, arg2, arg3) = `TRUE' evaluates to TRUE for object pairs that are within the specified distance, and FALSE otherwise.
Usage Notes
- Distance between two extended objects (nonpoint objects such as lines and polygons) is defined as the minimum distance between these two objects. The distance between two adjacent polygons is zero.
- The operator must always be used in a WHERE clause and the condition that includes the operator should be an expression of the form:
SDO_WITHIN_DISTANCE(arg1, arg2, 'distance = <some_dist_val>') = `TRUE'
- T.column must have a spatial index built on it.
- SDO_WITHIN_DISTANCE( ) is not supported for spatial joins. See Section 4.2.3 for a discussion on how to perform a spatial join within-distance operation.
Examples
- SELECT A.GID
FROM POLYGONS A
WHERE
SDO_WITHIN_DISTANCE(A.Geometry, :aGeom, 'distance = 10') = 'TRUE' ;
- SELECT A.GID
FROM POLYGONS A
WHERE
SDO_WITHIN_DISTANCE(A.Geometry, mdsys.sdo_geometry(3,NULL,NULL,
mdsys.sdo_elem_info(1,3,3),
mdsys.sdo_ordinates(x1,y1,x2,y2)),
'distance = 10') = 'TRUE' ;
- SELECT A.GID
FROM POLYGONS A, Query_Points B
WHERE B.GID = 1 AND
SDO_WITHIN_DISTANCE(A.Geometry, B.Geometry, 'distance = 10') = 'TRUE' ;
Related Topics
