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

Answer

Note:
ArcSDE software, including the application server, command tools, and SDK with C and Java APIs, was deprecated at ArcGIS 10.2.2 and is no longer distributed.

When composing an SQL statement with a spatial relational operator such as st_intersects, st_within, st_crosses, etc., it is not necessary to include the additional spatial operator st_envintersects as a filter to identify candidate geometries for the spatial relational operator. If the st_envintersects operator is included in the query, it is redundant and adds unnecessary computing cost to the query. When using a spatial index, spatial relational operators first filter 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 it is not required in either case. 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 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 primary 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 which consume additional computing resources without 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;