Oracle Spatial User's Guide and Reference Release 8.1.6 A7713201 

This chapter describes the operators used when working with the spatial object data type. The operators are listed in Table 91.
Table 91 Spatial Usage OperatorsUses the spatial index to identify either the set of spatial objects that may spatially interact with a given object (such as an areaofinterest), 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.
SDO_FILTER(geometry1, geometry2, params) ;
The expression SDO_FILTER(arg1, arg2, arg3) = `TRUE' evaluates to TRUE for object pairs that are nondisjoint, and FALSE otherwise.
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).
Uses the spatial index to identify the nearest neighbors for a geometry.
SDO_NN(geometry1, geometry2, param) ;
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.
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.
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
None.
Uses the spatial index to identify either the spatial objects that have a particular spatial interaction with a given object such as an areaofinterest, or pairs of spatial objects that have a particular spatial interaction.
This operator performs both primary and secondary filter operations.
SDO_RELATE(geometry1, geometry2, params) ;
geometry1 
Specifies a geometry column in a table. The column must be spatially indexed. 
geometry2 
Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) 
PARAMS 
Determines the behavior of the operator. 
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 9intersection 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 9intersection 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) areaofinterest. 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. 
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.
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).
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.
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 areaofinterest or pointofinterest).
SDO_WITHIN_DISTANCE(T.column, aGeom, params) ;
The expression SDO_WITHIN_DISTANCE(arg1, arg2, arg3) = `TRUE' evaluates to TRUE for object pairs that are within the specified distance, and FALSE otherwise.
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 withindistance operation.

Copyright © 1999 Oracle Corporation. All Rights Reserved. 
