SDO_WITHIN_DISTANCE

Format

SDO_WITHIN_DISTANCE(geometry1, aGeom, params);

Description

Identifies 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`). A spatial index on this column is recommended. Data type is 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 SDO_GEOMETRY.
params	A quoted string containing one or more keywords (with values) that determine the behavior of the operator. The remaining items (`distance`, `ellipsoidal`, `max_resolution`, `min_resolution`, `querytype`, and `unit`) are potential keywords for the `params` parameter. 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 keyword. Data type is NUMBER.
ellipsoidal	Specifies if ellipsoidal distance is always used with geodetic data (`true`), or if spherical distance is used in some cases (`false`, the default). See Distance: Spherical versus Ellipsoidal with Geodetic Data. For example: 'ellipsoidal=true'
max_resolution	Includes only geometries for which at least one side of the geometry's MBR is less than or equal to the specified value. For example, `max_resolution=10` includes only geometries for which the width or the height (or both) of the geometry's MBR is less than or equal to 10. (This keyword can be used to exclude geometries that are too large to be of interest.)
min_resolution	Includes only geometries for which at least one side of the geometry's MBR is equal to or greater than the specified value. For example, `min_resolution=10` includes only geometries for which the width or the height (or both) of the geometry's MBR is at least 10. (This keyword can be used to exclude geometries that are too small to be of interest.)
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 Unit of Measurement Support 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

Note:

SDO_WITHIN_DISTANCE

operator is not supported for 3D data in Oracle Autonomous Database Serverless deployments.

The 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 number of dimensions for the query window does not match the number of dimensions specified when the spatial index (if one is being used) was created.

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 expression must not equate to any value other than '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 Within-Distance Operator for a discussion on how to perform a spatial join within-distance operation.

For information about 3D support with spatial operators (which operators do and do not consider all three dimensions in their computations), see Three-Dimensional Spatial Objects.

Examples

The following example selects the geometries that are within a distance of 10 from a query window (here, a rectangle with lower-left, upper-right coordinates 4,6, 8,8). (The example uses the definitions and data described and illustrated in Simple Example: Inserting_ Indexing_ and Querying Spatial Data. In this case, all geometries shown in that figure are returned.)

SELECT c.name FROM cola_markets c WHERE SDO_WITHIN_DISTANCE(c.shape,
  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(4,6, 8,8)),
  'distance=10') = 'TRUE';
 
NAME                                                                            
--------------------------------                                                
cola_b                                                                          
cola_a                                                                          
cola_c                                                                          
cola_d

The following example is the same as the preceding example, except that it includes only geometries where at least one side of the geometry's MBR is equal to or greater than 4.1. In this case, only cola_a and cola_b are returned, because their MBRs have at least one side with a length greater than or equal to 4.1. The trapezoid cola_c is excluded, because its MBR has sides with lengths of 3 and 2; and the circle cola_d is excluded, because its MBR is a square whose sides have a length of 4.

SELECT c.name FROM cola_markets c WHERE SDO_WITHIN_DISTANCE(c.shape,
  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(4,6, 8,8)),
  'distance=10 min_resolution=4.1') = 'TRUE';
 
NAME                                                                            
--------------------------------                                                
cola_b                                                                          
cola_a

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, sdo_geometry(2003,NULL,NULL,
                             sdo_elem_info_array(1,1003,3),
                             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';

See also the more complex SDO_WITHIN_DISTANCE examples in SDO_WITHIN_DISTANCE Examples.