topic Does Select By Location (ArcObjects) use spatial indexes? in ArcObjects SDK Questions

Does Select By Location (ArcObjects) use spatial indexes?

Bud — Sun, 02 Apr 2023 02:42:57 GMT

Does the Select By Location tool in ArcGIS Pro make use of spatial indexes in the DB when performing spatial operations? For example, SDO_GEOMETRY (Oracle) and SDE._ST_GEOMETRY spatial indexes.

I want to understand why Select By Location/ArcObjects is so fast compared to spatial SQL queries. I talk more about it here:

Re: Does Select By Location (ArcObjects) use spatial database indexes?

DuncanHornby — Sat, 01 Apr 2023 20:10:01 GMT

I'm going to stick my neck out and say yes. I base that opinion on my experience. If the input dataset to a select by location is a shapefile without it's spatial index I often observe poor performance. Add a spatial index and I see significant improvement with such tools. I've never used SDE so I'm assuming that would behave the same as I see that as a superior storage format.

Re: Does Select By Location (ArcObjects) use spatial database indexes?

Bud — Sun, 02 Apr 2023 14:38:23 GMT

Thanks. Interesting.

I did a test on some SDO_GEOMETRY (Oracle) test data.

With spatial indexes:
- Select By Location - Intersect: 0.5 seconds
- Spatial SQL query: 4.5 seconds
Without spatial indexes (dropped via SQL client):
- Select By Location - Intersect: 6 seconds
- Spatial SQL query: 1hr 50mins.

Conclusion:

With SDO_GEOMETRY data, Select By Location seems to use spatial indexes. But it's still relatively fast when spatial indexes are missing; way faster than a spatial SQL query.

Re: Does Select By Location (ArcObjects) use spatial database indexes?

Bud — Sun, 02 Apr 2023 20:15:05 GMT

Side note:

In older versions of Oracle, SDO_GEOMETRY spatial indexes were required in order for spatial querying to work. The Esri docs used to say this: (until they were updated in 2022)

How To: Create Oracle Spatial function-based indexing
When creating a query layer that references an Oracle view containing an Oracle Spatial attribute, the attribute must have a spatial index. Without the spatial index, spatial queries cannot be executed.

However, in Oracle 18c and beyond, spatial indexes are optional but strongly recommended.

Spatial Index No Longer Required
Creating and using a spatial index is no longer required for the use of any Oracle Spatial and Graph features. However, spatial indexes are highly recommended, and not using them can negatively affect performance in some cases.