10
Spatial Operators

This chapter describes the operators used when working with the spatial object data type. The operators are listed in Table 10-1.

Table 10-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_NN_DISTANCE	Returns the distance of an object returned by the SDO_NN operator.
SDO_RELATE	Determines whether or not two geometries interact in a specified way.
SDO_WITHIN_DISTANCE	Determines if two geometries are within a specified distance from one another.

SDO_FILTER

Format

SDO_FILTER(geometry1, geometry2, params);

Description

Uses the spatial index to identify either the set of spatial objects that are likely to interact spatially with a given object (such as an area of interest), or pairs of spatial objects that are likely to interact spatially. Objects interact spatially if they are not disjoint.

This operator performs only a primary filter operation. The secondary filtering operation, performed by the SDO_RELATE operator, can be used to determine with certainty if objects interact spatially.

Keywords and Parameters

Value	Description
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.
querytype	Specifies valid query types: WINDOW or JOIN. This is a required parameter. WINDOW is recommended in almost all cases. WINDOW implies that a query is performed for every geometry1 candidate geometry to be compared with geometry2. WINDOW can be used to compare a single geometry (geometry2) to all the geometries in a column (geometry1). JOIN is rarely used. Use JOIN when you want to compare all the geometries of a column to all the geometries of another column. JOIN implies that geometry2 refers to a table column that must have a spatial index built on it. (See the Usage Notes for additional requirements.)
idxtab1	Specifies the name of the index table, if there are multiple spatial indexes, for geometry1.
idxtab2	Specifies the name of the index table, if there are multiple spatial indexes, for geometry2. Valid only if querytype is JOIN.

Returns

The expression SDO_FILTER(arg1, arg2, arg3) = 'TRUE' evaluates to TRUE for object pairs that are non-disjoint, and FALSE otherwise.

Usage Notes

SDO_FILTER is the only operator that can be used with data that is indexed using more than two dimensions. The operator considers all dimensions specified in the spatial index.

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 the geometry2 column is not spatially indexed, the operator indexes the query window in memory and performance is very good.
If the geometry2 column is spatially indexed with the same SDO_LEVEL value as the geometry1 column, the operator reuses the existing index, and performance is very good or better.
If the geometry2 column is spatially indexed with a different SDO_LEVEL value than the geometry1 column, the operator reindexes geometry2 in the same way as if there were no index on the column originally, and then performance is very good.
If two or more geometries from geometry2 are passed to the operator, the ORDERED optimizer hint must be specified, and the table in geometry2 must be specified first in the FROM clause.

If querytype is JOIN:

geometry2 must be a column in a table.
For best performance, both geometry1 and geometry2 should have the same type of index (R-tree or quadtree); and if the geometries have quadtree indexes, the indexes should have the same sdo_level value. If the geometries do not have the same index type (and for quadtree indexes the same sdo_level value), geometry2 is reindexed to be indexed as geometry1 (with the considerations listed for querytype = WINDOW), and performance is less efficient.

If geometry1 and geometry2 are based on different coordinate systems, geometry2 is temporarily transformed to the coordinate system of geometry1 for the operation to be performed, as described in Section 5.7.1.

The layer_gtype keyword for PARAMS has been deprecated, and it is ignored if specified. The operator automatically optimizes its behavior based on the SDO_GTYPE value (explained in Section 2.2.1) of the geometries, which can be specified using the layer_gtype keyword in the parameters for the CREATE INDEX or ALTER INDEX REBUILD statement.

Examples

The following example selects the GID values from the POLYGONS table where the GEOMETRY column objects are likely to interact spatially with the GEOMETRY column object in the QUERY_POLYS table that has a GID value of 1.

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

The following example selects the GID values from the POLYGONS table where the GEOMETRY column object is likely to interact spatially with the geometry stored in the aGeom variable.

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

The following example selects the GID values from the POLYGONS table where the GEOMETRY column object is likely to interact spatially with the specified rectangle having the lower-left coordinates (x1,y1) and the upper-right coordinates (x2, y2).

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

The following example selects the GID values from the POLYGONS table where the GEOMETRY column object is likely to interact spatially with any GEOMETRY column object in the QUERY_POLYS table. In this example, the ORDERED optimizer hint is used and QUERY_POLYS (geometry2) table is specified first in the FROM clause, because multiple geometries from geometry2 are involved (see the Usage Notes)

SELECT /*+ ORDERED */
  A.gid 
  FROM query_polys B, polygons A 
  WHERE 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';

Related Topics

SDO_RELATE

SDO_NN

Format

SDO_NN(geometry1, geometry2, param [, number]);

Description

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

Keywords and Parameters

Value	Description
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. The available keywords are listed in Table 10-2. Data type is VARCHAR2.
number	If the SDO_NN_DISTANCE ancillary operator is included in the call to SDO_NN, specifies the same number used in the call to SDO_NN_DISTANCE. Data type is NUMBER.

Table 10-2 lists the keywords for the param parameter.

Table 10-2 Keywords for SDO_NN Parameter

Keyword	Description
sdo_batch_size	Specifies the number of rows to be evaluated at a time when the SDO_NN expression may need to be evaluated multiple times in order to return the desired number of results that satisfy the WHERE clause. Available only when an R-tree index is used. See the Usage Notes for more information. Data type is NUMBER. For example: 'sdo_batch_size=10'
sdo_num_res	If sdo_batch_size is not specified, specifies the number of results (nearest neighbors) to be returned. If sdo_batch_size is specified, this keyword is ignored; instead, use the ROWNUM pseudocolumn to limit the number of results. See the Usage Notes and Examples for more information. Data type is NUMBER. Default = 1. For example: 'sdo_num_res=5'
unit	If the SDO_NN_DISTANCE ancillary operator is included in the call to SDO_NN, specifies the unit of measurement: a quoted string with unit= and an SDO_UNIT value from the MDSYS.SDO_DIST_UNITS table. See Section 2.6 for more information about unit of measurement specification. Data type is NUMBER. Default = unit of measurement associated with the data. For geodetic data, the default is meters. For example: 'unit=KM'

Returns

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

Usage Notes

The operator is disabled if the table does not have a spatial index or if the index has been built on more than two dimensions.

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, '<some_parameter>') = 'TRUE'.

The operator can be used in two ways:

If all geometries in the layer are candidates, use the sdo_num_res keyword to specify the number of geometries returned.
If any geometries in the table might be nearer than the geometries specified in the WHERE clause, use the sdo_batch_size keyword and use the WHERE clause (including the ROWNUM pseudocolumn) to limit the number of geometries returned.

Specify the sdo_batch_size keyword if any geometries in the table might be nearer than the geometries specified in the WHERE clause. For example, assume that a RESTAURANTS table contained different types of restaurants, and you wanted to find the two nearest Italian restaurants to your hotel. The query might look like the following:

SELECT r.name FROM restaurants r WHERE 
   SDO_NN(r.geometry, :my_hotel, 'sdo_batch_size=10') = 'TRUE' 
   AND r.cuisine = 'Italian' AND ROWNUM <=2;

If the sdo_batch_size keyword is not specified in this example, only the two nearest restaurants are returned, regardless of their CUISINE value; and if the CUISINE value of these two rows is not Italian, the query may return no rows. The ROWNUM <=2 clause is necessary to limit the number of results returned to no more than 2 where CUISINE is Italian.

The sdo_batch_size keyword can be used only when SDO_NN will be using an R-tree index to perform the operation. This keyword cannot be used with a quadtree index.

The sdo_batch_size value can affect the performance of nearest neighbor queries. A good general guideline is to specify the number of candidate rows likely to satisfy the WHERE clause. Using the preceding example of a query for Italian restaurants, if approximately 20 percent of the restaurants nearest to the hotel are Italian and if you want 2 restaurants, an sdo_batch_size value of 10 will probably result in the best performance. On the other hand, if only approximately 5 percent of the restaurants nearest to the hotel are Italian and if you want 2 restaurants, an sdo_batch_size value of 40 would be better.

If the sdo_batch_size keyword is specified, any sdo_num_res value is ignored. Do not specify both keywords.

Specify the number parameter only if you are using the SDO_NN_DISTANCE ancillary operator in the call to SDO_NN. See the information about SDO_NN_DISTANCE.

If this operator is used with geodetic data, the data must be indexed with an R-tree spatial index. If this operator is used with geodetic data and if the R-tree spatial index is created with 'geodetic=false' specified, you cannot use the unit parameter.

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

If the SDO_NN operator uses a partitioned spatial index (see Section 4.1.7), the requested number of geometries is returned for each partition that contains candidate rows based on the query criteria. For example, if you request the 5 nearest restaurants to a point and the spatial index has 4 partitions, the operator returns up to 20 (5*4) geometries. In this case, you must use the ROWNUM pseudocolumn (here, WHERE ROWNUM <=5) to return the 5 nearest restaurants.

SDO_NN is not supported for spatial joins.

In some situations the SDO_NN operator will not use the spatial index unless an optimizer hint forces the index to be used. This can occur when a query involves a join; and if the optimizer hint is not used in such situations, an internal error occurs. To prevent such errors, you should always specify an optimizer hint to use the spatial index with the SDO_NN operator, regardless of how simple or complex the query is. For example, the following excerpt from a query specifies to use the COLA_SPATIAL_IDX index that is defined on the COLA_MARKETS table:

SELECT /*+ INDEX(cola_markets cola_spatial_idx) */ 
  c.mkt_id, c.name, ... FROM cola_markets c, ...;

For detailed information about using optimizer hints, see Oracle9i Database Performance Guide and Reference.

Examples

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

SELECT /*+ INDEX(cola_markets cola_spatial_idx) */
 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
---------- --------------------------------
         2 cola_b
         4 cola_d

The following example uses the sdo_batch_size keyword to find the two objects (ROWNUM <=2), with a NAME value less than 'cola_d', from the SHAPE column in the COLA_MARKETS table that are nearest to a specified point (10,7). The value of 3 for sdo_batch_size represents a best guess at the number of nearest geometries that need to be evaluated before the WHERE clause condition is satisfied. (The example uses the definitions and data from Section 2.1.)

SELECT /*+ INDEX(cola_markets cola_spatial_idx) */ 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_batch_size=3') = 'TRUE'
   AND c.name < 'cola_d' AND ROWNUM <= 2; 

    MKT_ID NAME                                                                 
---------- --------------------------------                                     
         2 cola_b                                                               
         3 cola_c

Related Topics

SDO_NN_DISTANCE

SDO_NN_DISTANCE

Format

SDO_NN_DISTANCE(number);

Description

Returns the distance of an object returned by the SDO_NN operator. Valid only within a call to the SDO_NN operator.

Keywords and Parameters

Value	Description
number	Specifies a number that must be the same as the last parameter passed to the SDO_NN operator. Data type is NUMBER.

Returns

This operator returns the distance of an object returned by the SDO_NN operator. In determining how near two geometry objects are, the shortest possible distance between any two points on the surface of each object is used.

Usage Notes

SDO_NN_DISTANCE is an ancillary operator to the SDO_NN operator. It returns the distance between the specified geometry and a nearest neighbor object. This distance is passed as ancillary data to the SDO_NN operator. (For an explanation of how operators can use ancillary data, see the section on ancillary data in the chapter on domain indexes in the Oracle9i Data Cartridge Developer's Guide.)

You can choose any arbitrary number for the number parameter. The only requirement is that it must match the last parameter in the call to the SDO_NN operator.

Use a bind variable to store and operate on the distance value.

Examples

The following example finds the two objects from the SHAPE column in the COLA_MARKETS table that are nearest to a specified point (10,7), and it finds the distance between each object and the point. (The example uses the definitions and data from Section 2.1.)

SELECT   /*+ INDEX(cola_markets cola_spatial_idx) */ 
   c.mkt_id, c.name, mdsys.SDO_NN_DISTANCE(1) dist
   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', 1) = 'TRUE' ORDER BY dist; 

    MKT_ID NAME                                   DIST                          
---------- -------------------------------- ----------                          
         4 cola_d                           .828427125                          
         2 cola_b                           2.23606798

Note the following about this example:

1 is used as the number parameter for SDO_NN_DISTANCE, and 1 is also specified as the last parameter to SDO_NN (after 'sdo_num_res=2').
The column alias dist holds the distance between the object and the point. (For geodetic data, the distance unit is meters; for non-geodetic data, the distance unit is the unit associated with the data.)

Related Topics

SDO_NN

SDO_RELATE

Format

SDO_RELATE(geometry1, geometry2, params);

Description

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.

Keywords and Parameters

Value	Description
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, ON. Multiple masks are combined with the logical Boolean operator OR, for example, 'mask=inside+touch'; however, see the Usage Notes for an alternative syntax using UNION ALL that may result in better performance. See Section 1.8 for an explanation of the 9-intersection relationship pattern.
querytype	Valid query types are: WINDOW or JOIN. 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 is recommended in almost all cases. WINDOW implies that a query is performed for every geometry1 candidate geometry to be compared with geometry2. WINDOW can be used to compare a single geometry (geometry2) to all the geometries in a column (geometry1). JOIN is rarely used. Use JOIN when you want to compare all the geometries of a column to all the geometries of another column. JOIN implies that geometry2 refers to a table column that must have a spatial index built on it. (See the Usage Notes for additional requirements.)
idxtab1	Specifies the name of the index table, if there are multiple spatial indexes, for geometry1.
idxtab2	Specifies the name of the index table, if there are multiple spatial indexes, for geometry2. Only valid for 'querytype = JOIN'.

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 is disabled if the table does not have a spatial index or if the index has been built on more than two dimensions.

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 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 the geometry2 column is not spatially indexed, the operator indexes the query window in memory and performance is very good.
If the geometry2 column is spatially indexed with the same SDO_LEVEL value as the geometry1 column, the operator reuses the existing index, and performance is very good or better.
If the geometry2 column is spatially indexed with a different SDO_LEVEL value than the geometry1 column, the operator reindexes geometry2 in the same way as if there were no index on the column originally, and then performance is very good.
If two or more geometries from geometry2 are passed to the operator, the ORDERED optimizer hint must be specified, and the table in geometry2 must be specified first in the FROM clause.

If querytype is JOIN:

geometry2 must be a column in a table.
For best performance, both geometry1 and geometry2 should have the same type of index (R-tree or quadtree); and if the geometries have quadtree indexes, the indexes should have the same sdo_level value. If the geometries do not have the same index type (and for quadtree indexes the same sdo_level value), geometry2 is reindexed to be indexed as geometry1 (with the considerations listed for querytype = WINDOW), and performance is less efficient.

Unlike with the SDO_GEOM.RELATE function, DISJOINT and DETERMINE masks are not allowed in the relationship mask with the SDO_RELATE operator. 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.

Although multiple masks can be combined using the logical Boolean operator OR, for example, 'mask=inside+coveredby', better performance may result if the spatial query specifies each mask individually and uses the UNION ALL syntax to combine the results. This is due to internal optimizations that Spatial can apply under certain conditions when masks are specified singly rather than grouped within the same SDO_RELATE operator call. For example, the following query using the logical Boolean operator OR to group multiple masks:

SELECT a.gid
  FROM polygons a, query_polys B
  WHERE B.gid = 1
  AND SDO_RELATE(A.Geometry, B.Geometry,
                   'mask=inside+coveredby querytype=WINDOW') = 'TRUE';

may result in better performance if it is expressed thus, using UNION ALL to combine results of multiple SDO_RELATE operator calls, each with a single mask:

SELECT a.gid
      FROM polygons a, query_polys B
      WHERE B.gid = 1
      AND SDO_RELATE(A.Geometry, B.Geometry,
                   'mask=inside querytype=WINDOW') = 'TRUE'
UNION ALL
SELECT a.gid
      FROM polygons a, query_polys B
      WHERE B.gid = 1
      AND SDO_RELATE(A.Geometry, B.Geometry,
                   'mask=coveredby querytype=WINDOW') = 'TRUE';

Examples

The following examples are similar to those for the SDO_FILTER operator; however, they identify a specific type of interaction (using the mask parameter), and they determine with certainty (not mere likelihood) if the spatial interaction occurs.

The following example selects the GID values from the POLYGONS table where the GEOMETRY column objects have any spatial interaction with the GEOMETRY column object in the QUERY_POLYS table that has a GID value of 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';

The following example selects the GID values from the POLYGONS table where a GEOMETRY column object has any spatial interaction with the geometry stored in the aGeom variable.

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

The following example selects the GID values from the POLYGONS table where a GEOMETRY column object has any spatial interaction with the specified rectangle having the lower-left coordinates (x1,y1) and the upper-right coordinates (x2, y2).

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

The following example selects the GID values from the POLYGONS table where the GEOMETRY column object has any spatial interaction with any GEOMETRY column object in the QUERY_POLYS table. In this example, the ORDERED optimizer hint is used and QUERY_POLYS (geometry2) table is specified first in the FROM clause, because multiple geometries from geometry2 are involved (see the Usage Notes).

SELECT /*+ ORDERED */
  A.gid 
  FROM query_polys B, polygons A 
  WHERE SDO_RELATE(A.Geometry, B.Geometry, 'querytype = WINDOW') = 'TRUE';

The following example selects the GID values from the POLYGONS table where a GEOMETRY column object has any spatial interaction with any GEOMETRY column object in the QUERY_POLYS table. In this example, the QUERY_POLYS.GEOMETRY column must be spatially indexed.

SELECT A.gid

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

Related Topics

SDO_FILTER
SDO_WITHIN_DISTANCE
SDO_GEOM.RELATE function

SDO_WITHIN_DISTANCE

Format

SDO_WITHIN_DISTANCE(geometry1, aGeom, params);

Description

Uses the spatial index to identify the set of spatial objects that are within some specified distance of a given object (such as an area of interest or point of interest).

Keywords and Parameters

Value	Description
geometry1	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 geometry1. 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.
distance	Specifies the distance value. If a coordinate system is associated with the geometry, the distance unit is assumed to be the unit associated with the coordinate system. This is a required parameter. Data type is NUMBER.
idxtab1	Specifies the name of the index table 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.
unit	Specifies the unit of measurement: a quoted string with unit= and an SDO_UNIT value from the MDSYS.SDO_DIST_UNITS table (for example, 'unit=KM'). See Section 2.6 for more information about unit of measurement specification. Data type is NUMBER. Default = unit of measurement associated with the data. For geodetic data, the default is meters.

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 is disabled if the table does not have a spatial index or if the index has been built on more than two dimensions.

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'

The geometry column must have a spatial index built on it. If the data is geodetic, the spatial index must be an R-tree index.

SDO_WITHIN_DISTANCE is not supported for spatial joins. See Section 4.2.1.3 for a discussion on how to perform a spatial join within-distance operation.

Examples

The following example selects the GID values from the POLYGONS table where the GEOMETRY column object is within 10 distance units of the geometry stored in the aGeom variable.

SELECT A.GID

  FROM POLYGONS A

  WHERE

    SDO_WITHIN_DISTANCE(A.Geometry, :aGeom, 'distance = 10') = 'TRUE';

The following example selects the GID values from the POLYGONS table where the GEOMETRY column object is within 10 distance units of the specified rectangle having the lower-left coordinates (x1,y1) and the upper-right coordinates (x2, y2).

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

The following example selects the GID values from the POLYGONS table where the GID value in the QUERY_POINTS table is 1 and a POLYGONS.GEOMETRY object is within 10 distance units of the QUERY_POINTS.GEOMETRY object.

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

10 Spatial Operators

SDO_FILTER

SDO_NN

SDO_NN_DISTANCE

SDO_RELATE

SDO_WITHIN_DISTANCE

10
Spatial Operators