SDO_RELATE

Format

SDO_RELATE(geometry1, geometry2,  param);

Description

Identifies 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

Value	Description
geometry1	Specifies a geometry column in a table. A spatial index on this column is recommended. Data type is 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 SDO_GEOMETRY.
param	Specifies the `mask` keyword, and optionally either or both of the `min_resolution` and `max_resolution` keywords. The data type for this parameter is VARCHAR2. The `mask` keyword specifies the topological relationship of interest. This is a required parameter. Valid `mask` keyword values are one or more of the following in the nine-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'`. See Spatial Relationships and Filtering for an explanation of the nine-intersection relationship pattern. The `min_resolution` keyword 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.) The `max_resolution` keyword 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.) For backward compatibility, any additional keywords for the `param` parameter that were supported before release 10.1 will still work; however, the use of those keywords is discouraged and is not supported for new uses of the operator.

geometry1

Specifies a geometry column in a table. A spatial index on this column is recommended. Data type is 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 SDO_GEOMETRY.

param

Specifies the mask keyword, and optionally either or both of the min_resolution and max_resolution keywords. The data type for this parameter is VARCHAR2.

The mask keyword specifies the topological relationship of interest. This is a required parameter. Valid mask keyword values are one or more of the following in the nine-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'. See Spatial Relationships and Filtering for an explanation of the nine-intersection relationship pattern.

The min_resolution keyword 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.)

The max_resolution keyword 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.)

For backward compatibility, any additional keywords for the param parameter that were supported before release 10.1 will still work; however, the use of those keywords is discouraged and is not supported for new uses of the operator.

Returns

The expression SDO_RELATE(geometry1,geometry2, 'mask = <some_mask_val>') = '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 number of dimensions for the query window does not match the number of dimensions specified when the index 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_RELATE(arg1, arg2, 'mask = <some_mask_val>') = 'TRUE'. (The expression must not equate to any value other than 'TRUE'.)

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 geometry2 passed to the operator originates from a table or view, the LEADING optimizer hint is not required, but may be helpful to inform the optimizer the driving table or view in the query should be the one that contains geometry2. The table or view specified in the LEADING hint should correspond to the table name or view name that contains geometry2. If the table or view is aliased in the FROM clause, the alias should be specified in the LEADING hint.

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 Different Coordinate Systems for Geometries with Operators and Functions.

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 (if one is available) to find candidates that may interact, and the information to satisfy DISJOINT or DETERMINE is not present in the index.

Multiple masks can be combined using the logical Boolean operator OR, for example, 'mask=touch+coveredby'. Effective with Release 12.1, if you set the SPATIAL_VECTOR_ACCELERATION system parameter to TRUE (highly recommended, as explained in SPATIAL_VECTOR_ACCELERATION System Parameter), you do not need to use UNION ALL with such masks. However, if you are not setting SPATIAL_VECTOR_ACCELERATION to TRUE, 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. (There are two exceptions, inside+coveredby and contains+covers, where the combination performs better than the UNION ALL alternative.)

For example, consider 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=touch+coveredby') = 'TRUE';

If SPATIAL_VECTOR_ACCELERATION is TRUE, then preceding query is simplest and has the best performance. However, if SPATIAL_VECTOR_ACCELEERATION is FALSE, the preceding query may result in better performance if it is expressed as follows, 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=touch') = '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') = 'TRUE';

The following considerations apply to relationships between lines and a multipoint geometry (points in a point cluster). Assume the example of a line and a multipoint geometry (for example, SDO_GTYPE = 2005) consisting of three points.

If none of the points has any interaction with the line, the relationship between the line and the point cluster is DISJOINT.
If one of the points is on the interior of the line and the other two points are disjoint, the relationship between the line and the point cluster is OVERLAPBDYDISJOINT.
If one of the points is on the boundary of the line (that is, if it is on the start point or end point of the line) and the other two points are disjoint, the relationship between the line and the point cluster is TOUCH.
If one of the points is on the boundary of the line (that is, if it is on the start point or end point of the line), another point is on the interior of the line, and the third point is disjoint, the relationship between the line and the point cluster is OVERLAPBDYDISJOINT (not OVERLAPBDYINTERSECT).

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 examples are similar to those for the SDO_FILTER operator; however, they identify a specific type of interaction (using the mask keyword), and they determine with certainty (not mere likelihood) if the spatial interaction occurs.

The following example selects the geometries that have any interaction with 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.)

SELECT c.mkt_id, c.name
  FROM cola_markets c
  WHERE SDO_RELATE(c.shape,
    SDO_GEOMETRY(2003, NULL, NULL,
      SDO_ELEM_INFO_ARRAY(1,1003,3),
      SDO_ORDINATE_ARRAY(4,6, 8,8)),
    'mask=anyinteract') = 'TRUE';

    MKT_ID NAME       
---------- --------------------------------                                     
         2 cola_b                                                               
         1 cola_a                                                               
         4 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 circle cola_d is excluded, because its MBR is a square whose sides have a length of 4.

SELECT c.mkt_id, c.name
  FROM cola_markets c
  WHERE SDO_RELATE(c.shape,
    SDO_GEOMETRY(2003, NULL, NULL,
      SDO_ELEM_INFO_ARRAY(1,1003,3),
      SDO_ORDINATE_ARRAY(4,6, 8,8)),
    'mask=anyinteract min_resolution=4.1') = 'TRUE';

    MKT_ID NAME       
---------- --------------------------------                                     
         2 cola_b                                                               
         1 cola_a

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') = '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') = '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, sdo_geometry(2003,NULL,NULL,
                                   sdo_elem_info_array(1,1003,3),
                                   sdo_ordinate_array(x1,y1,x2,y2)),
                     'mask=ANYINTERACT') = '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 LEADING 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 /*+ LEADING(B) */
  A.gid 
  FROM query_polys B, polygons A 
  WHERE SDO_RELATE(A.Geometry, B.Geometry, 'mask=ANYINTERACT') = 'TRUE';