Leverage a table's spatial index when using the st_relate operator in Oracle

Summary

The st_relate operator is a powerful relational operator used for evaluating the relationships between two geometries using the DE-9IM model.

The Dimensionally Extended 9 Intersection Model (DE-9IM) developed by Clementini, et al. dimensionally extends the 9 Intersection Model of Egenhofer and Herring. DE-9IM is a mathematical approach that defines the pair-wise spatial relationship between geometries of different types and dimensions. This model expresses spatial relationships among all types of geometry as pair-wise intersections of their interior, boundary, and exterior with consideration for the dimension of the resulting intersections.
Given geometries a and b: I(a), B(a), and E(a) represent the interior, boundary, and exterior of a; and I(b), B(b), and E(b) represent the interior, boundary, and exterior of b. The intersections of I(a), B(a), and E(a) with I(b), B(b), and E(b) produce a three-by-three matrix. Each intersection can result in geometries of different dimensions. For example, the intersection of the boundaries of two polygons could consist of a point and a linestring, in which case the dim (dimension) function would return the maximum dimension of 1.

The dim function returns a value of -1, 0, 1, or 2. The -1 corresponds to the null set that is returned when no intersection is found.

The pattern matrix contains the acceptable values for each of the intersection matrix cells. The possible pattern values are as follows:

T—An intersection must exist;
dim = 0, 1, or 2

F—An intersection must not exist;
dim = 1

*—It does not matter if an intersection exists or not;
dim = -1, 0, 1, or 2

0—An intersection must exist and its maximum dimension must be 0;
dim = 0

1—An intersection must exist and its maximum dimension must be 1;
dim = 1

2—An intersection must exist and its maximum dimension must be 2;
dim = 2

Each predicate has at least one pattern matrix, but some require more than one to describe the relationships of various geometry type combinations.

When using the st_relate operator, only the matrix values that map directly to relational operators (st_crosses, st_overlaps, st_touch, st_intersects, st_equal, st_contains, st_within) can leverage the spatial index. If the matrix combines any other options, then the filter compares every geometry in the target table. To avoid comparing every geometry, include a spatial envelope filter using the st_envintersects operator.

Procedure

Code:
SQL> SELECT c.block_id, sde.st_astext(c.shape) AS geometry
  2   FROM census_blocks c, voting_districts v
  3   WHERE v.district = 1394
  4   AND sde.st_relate(c.shape,v.shape,’F***1****’) = 1;

Code:
SQL> SELECT c.block_id, sde.st_astext(c.shape) AS geometry
  2   FROM census_blocks c, voting_districts v
  3   WHERE v.district = 1394
  4   AND sde.st_relate(c.shape,v.shape,’F***1****’) = 1
  5   AND sde.st_envintersects(c.shape,sde.st_envelope(v.shape)) = 1;