English

# How To: 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

The following SQL statement demonstrates a query using the st_relate operator and a matrix for comparing the spatial relationships between two geometries.

The objective of the query is to return all census blocks that share a common linear boundary but do not intersect a specific voting district (1394).

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

The st_relate matrix is defined as Census Blocks being the target geometry and the Voting District as the input geometry. The matrix defines that the interiors of each geometry must not intersect 'F' (in the first position of the matrix a.shape's interior must not intersect b.shape's interior), and the geometries must have a common linear boundary as defined in the fifth position of the matrix (a.shape's boundary with b.shape's boundary returns a geometry that is a dimension of 1 - a line).

In this case, the one input voting district must then be compared with every census block geometry. The time to execute this statement and return a result is dependent on the number of census blocks. If there are just a few shapes to compare, then performance might be acceptable.

But if there are thousands of census blocks, the time to execute the statement may be substantial. To improve performance, a spatial envelope filter can be specified to narrow down the number of census blocks for performing the spatial relation.

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

By including the st_envintersects operator as an additional filter, only the Census Blocks whose envelopes intersect the envelope of the selected Voting district will be compared.