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

Operator	Description
SDO_FILTER	Specifies which geometries may interact with a given geometry.
SDO_NN	Determines the nearest neighbor geometries to a geometry.
SDO_RELATE	Determines whether or not two geometries interact in a specified way.
SDO_WITHIN_DISTANCE	Determines if two geometries are within a specified Euclidean distance from one another.

---

SDO_FILTER

Purpose

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 geometry2 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 have only 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 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 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.

• If geometry2 is a transient SDO_GEOMETRY object, it is indexed in memory.

Examples

1. SELECT A.gid
   FROM Polygons A, query_polys B
   WHERE B.gid = 1
   AND SDO_FILTER(A.Geometry, B.Geometry, 'querytype = WINDOW') = 'TRUE';

2. SELECT A.gid
   FROM Polygons A, query_polys B
   WHERE SDO_FILTER(A.Geometry, B.Geometry, 'querytype = JOIN') = 'TRUE';

3. Select A.Gid
   FROM Polygons A
   WHERE SDO_FILTER(A.Geometry, :aGeom, 'querytype=WINDOW') = 'TRUE';

4. Select A.Gid
   FROM Polygons A
   WHERE SDO_FILTER(A.Geometry, mdsys.sdo_geometry(2003,NULL,NULL,
   mdsys.sdo_elem_info_array(1,1003,3),
   mdsys.sdo_ordinate_array(x1,y1,x2,y2)),
   'querytype=WINDOW') = 'TRUE';

Related Topics

• SDO_RELATE

---

SDO_NN

Purpose

Uses the spatial index to identify the nearest neighbors for a geometry.

Syntax

SDO_NN(geometry1, geometry2, param) ;

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. The nearest neighbor or neighbors to geometry2 will be returned from geometry1. (geometry2 is specified using a bind variable or SDO_GEOMETRY constructor.) Data type is MDSYS.SDO_GEOMETRY.
PARAM	Determines the behavior of the operator. Data type is VARCHAR2.
Keyword	Description
sdo_num_res	Specifies the number of results (nearest neighbors). If not specified, the default is 1. For example: 'sdo_num_res=10'

Returns

This operator returns the sdo_num_res number of objects from geometry1 that are closest to geometry2 in the query. In determining how close two geometry objects are, the shortest possible distance between any two points on the surface of each object is used.

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_NN(arg1, arg2, `sdo_num_res=<some_val>') = `TRUE'.

You should not make any assumptions about the order of the returned results. For example, the first of several returned objects is not guaranteed to be the one closest to geometry2.

If two or more objects from geometry1 are an equal distance from geometry2, any of the objects can be returned on any call to the function. For example, if item_a, item_b, and item_c are closest to and equally distant from geometry2, and if 'SDO_NUM_RES=2', two of those three objects are returned, but they can be any two of the three.

SDO_NN is not supported for spatial joins.

Example

The following example finds the two objects from the shape column in the cola_markets table that are closest to a specified point (10,7). (The example uses the definitions and data from Section 2.1.)

SELECT c.mkt_id, c.name  FROM cola_markets c  WHERE SDO_NN(c.shape,
   mdsys.sdo_geometry(2001, NULL, mdsys.sdo_point_type(10,7,NULL), NULL,
   NULL),  'sdo_num_res=2') = 'TRUE';

    MKT_ID NAME
---------- --------------------------------
         4 cola_d
         2 cola_b

Related Topics

None.

---

SDO_RELATE

Purpose

Uses the spatial index to identify either the 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 the following in the 9-intersection pattern: TOUCH, OVERLAPBDYDISJOINT, OVERLAPBDYINTERSECT, EQUAL, INSIDE, COVEREDBY, CONTAINS, COVERS, ANYINTERACT. Multiple masks are combined with a the logical Boolean operator OR, for example, 'mask=(inside+touch)'. See Section 1.8 for an explanation of the 9-intersection relationship pattern.
querytype	Valid query types are: JOIN or WINDOW. This is a required parameter if geometry2 is from another table, but it is not a required parameter if geometry2 is a literal or a host variable. 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 geometry2 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 have only 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 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.

• If geometry2 is a transient SDO_GEOMETRY object, it is indexed in memory.

Unlike with 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

1. 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';

2. SELECT A.gid
   FROM Polygons A, query_polys B
   WHERE SDO_RELATE(A.Geometry, B.Geometry,
   'mask=ANYINTERACT querytype = JOIN') = 'TRUE';

3. Select A.Gid
   FROM Polygons A
   WHERE SDO_RELATE(A.Geometry, :aGeom, 'mask=ANYINTERACT querytype=WINDOW')
   = 'TRUE';

4. Select A.Gid
   FROM Polygons A
   WHERE SDO_RELATE(A.Geometry, mdsys.sdo_geometry(2003,NULL,NULL,
   mdsys.sdo_elem_info_array(1,1003,3),
   mdsys.sdo_ordinate_array(x1,y1,x2,y2)),
   'mask=ANYINTERACT querytype=WINDOW') = 'TRUE';

Related Topics

• SDO_FILTER

• SDO_WITHIN_DISTANCE

• SDO_GEOM.RELATE function

---

SDO_WITHIN_DISTANCE

Purpose

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-of-interest 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 has the set of geometry objects that will be operated on to determine if they are within the specified distance of the given object (aGeom). The column must be spatially indexed. Data type is MDSYS.SDO_GEOMETRY.
aGeom	Specifies the object to be checked for distance against the geometry objects in T.column. Specify either a geometry from a table (using a bind variable) or a transient instance of a geometry (using the 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 T.column.
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	Allows special processing for point data. If the objects in T.column have only point data, set this parameter to 'POINT' for optimal performance. Do not set this parameter to 'POINT' if T.column contains any n on-point objects. 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

1. SELECT A.GID
   FROM POLYGONS A
   WHERE
   SDO_WITHIN_DISTANCE(A.Geometry, :aGeom, 'distance = 10') = 'TRUE' ;

2. SELECT A.GID
   FROM POLYGONS A
   WHERE
   SDO_WITHIN_DISTANCE(A.Geometry, mdsys.sdo_geometry(2003,NULL,NULL,
   mdsys.sdo_elem_info_array(1,1003,3),
   mdsys.sdo_ordinate_array(x1,y1,x2,y2)),
   'distance = 10') = 'TRUE' ;

3. 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

• SDO_FILTER

• SDO_RELATE