English

FAQ: Is it necessary to include the st_envintersects operator when executing a SQL statement with additional spatial relational operators in Oracle?

Question

Is it necessary to include the st_envintersects operator when executing a SQL statement with additional spatial relational operators in Oracle?

Answer

It is not necessary to include the additional spatial operator st_envintersects as a filter to identify candidate geometries for the spatial relational operator when composing an SQL statement with a spatial relational operator (st_intersects, st_within, st_crosses, etc.. By including the st_envintersects operator in the query, the st_envintersects operator adds unnecessary computing cost to the query and the filter is redundant with the spatial relational operator. Spatial relational operators when using a spatial index first filters candidate shapes by applying the input geometries envelope as the primary filter prior to performing the spatial relational comparison.

The following SQL examples demonstrate how the st_envintersects operator is applied as a predicate filter and in a subquery, but in both cases is not required. The SQL statement's objective is to identify all parcel centroids in a neighborhood.

The first example adds the st_envintersects opertor as a predicate filter. The predicate filter is redundant to the st_within operator because the st_within operator uses the input neighborhoods shape's envelope to first identify all the candidate parcel_cent shapes prior to performing the relational comparison.

Code:
SQL> SELECT a.owner, a.address
2 FROM parcel_cent a, neighborhoods b
3 WHERE b.name = 'SOUTHGATE'
4 AND sde.st_within(a.shape,b.shape) = 1
5 AND sde.st_envintersects(a.shape,b.shape) = 1;


The second example applies the st_envintersects filter in a subquery to return a set of candidate geometries and ObjectID values as the filter to the parent query that contains the spatial relational operator.

Code:
SQL> SELECT owner, address
2 FROM parcel_cent
3 WHERE objectid IN
4 (SELECT objectid
5 FROM parcel_cent a, neighborhoods b
6 WHERE b.name = 'SOUTHGATE'
7 AND sde.st_envintersects(a.shape,b.shape) = 1);


Both examples result in the queries consuming additional computing resources with no benefit of increasing the queries performance. The proper SQL syntax for executing a query of this nature is to specify the predicate filter and spatial relational operator.

Code:
SQL> SELECT a.owner, a.address
2 FROM parcel_cent a, neighborhoods b
3 WHERE b.name = 'SOUTHGATE'
4 AND sde.st_within(a.shape,b.shape) = 1;