0

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)

1
  • RDBMS makes a plan for the executions of your query and tries to optimize it for execution time. This restriction comes from the optimizer logic. Try to use EXPLAIN with your query to check if spatial indexes are used.
    – Zoltan
    Jan 27 at 7:07
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.