ST_GEOMETRY: spatial column must appear immediately after spatial relationship function for spatial index to be used

Question

The docs for SDE.ST_GEOMETRY have a blurb that says the following:

When are spatial indexes used with ST_Geometry?

...the spatial column must appear immediately after the spatial relationship function in the query for the spatial index to be used.

I'm not clear on what that means or why it's necessary.

• That blurb seems to imply that spatial relationship functions would only be used in the SELECT clause. I would have thought that spatial relationship functions could be used in the FROM and WHERE clauses too.

---

Question:

What does the documentation mean by "must appear immediately after the spatial relationship function" and why is it necessary?

---

Oracle 18c; ArcGIS Enterprise 10.7.1 (the docs seem to apply to PostgreSQL too)

Answer 1

This page from the docs explains it better:

What is the correct order for the st_geometry relational operator input arguments in Oracle?

Specifying the correct argument order for the st_geometry relational operators (st_contains, st_crosses, st_equals, st_intersects, st_overlaps, st_relate, st_touches and st_within) is critical to ensure optimal query performance.

A case where the performance difference will be significant is when the less selective geometry is set as the second geometry argument in the operator - st_intersects (b.shape, a.shape) verses st_intersects (a.shape, b.shape). For example, if the objective is to discover all parcels that intersect a specific neighborhood, it is very important that the neighborhood's geometry is specified as the second argument, and not as the first argument for the relational operator. When specified as the second argument in the operator, the Oracle optimizer is allowed to use the single neighborhood geometry as the input to search the parcels spatial index. The query below demonstrates the neighborhood's geometry (b.shape) specified as the second argument.

Code:

SQL> SELECT COUNT(*) FROM parcels a, neighborhoods b 
  2  WHERE b.name = 'CLIFTON' AND
  3  sde.st_intersects(a.shape, b.shape) = 1

If the query was written with the a.shape and b.shape reversed,

Code:

SQL> SELECT COUNT(*) FROM parcels a, neighborhoods b 
  2  WHERE b.name = 'CLIFTON' AND
  3  sde.st_intersects(b.shape, a.shape) = 1

then each parcel would be the input geometry used to search the neighborhood's spatial index, and the time required to execute the query would be significantly longer.