ArcGIS Pro 2.6.8; Oracle 18c 10.7.1 EGDB; SDE.ST_Geometry:
In the Select Layer By Location (Data Management) docs, the Relationship Parameter — Intersect (DBMS) section says:
For the Relationship parameter, the Intersect (DBMS) option may provide better performance than the Intersect option when using enterprise geodatabase data; however, this option is only supported under specific conditions. If all conditions are met, the spatial operation will be performed in the enterprise geodatabase database management system (DBMS) rather than on the client.
...The user connecting to the geodatabase must have privileges to create a view in the database where the feature classes are stored.
In other words, the tool creates a database view called SELECTIONOUTPUTNAME.
select "OBJECTID" from ( SELECT a.OBJECTID FROM INFRASTR.ACTIVE_TRANSPORTATION a,
INFRASTR.AGOL_PARKINGLOT b WHERE SDE.ST_INTERSECTS(a.SHAPE, b.SHAPE) = 1 ) esri_sql
Question:
What is the intended use case for this technique? I know the docs suggest that the db is sometimes faster than the application, but I find that hard to believe in this particular case. My experience is that spatial queries in the database are way slower than the equivalent spatial operation the the application. In what kinds of scenarios is this technique used?
Solved! Go to Solution.
In very large datasets, the Spatial Operations perform faster in the RDBMS SQL Spatial Functions (when properly tunned), than in the client application ArcGIS Pro.
For example, If the Featureclass has 1 TB of data, and the client and the database are in the same LAN local area network, low latency and high bandwidth, it means that the 1 TB dataset needs to move from the database to the client to be processed, the client machine might need to have a lot of memory, etc. therefore letting the database to process the spatial operation using the spatial index and the spatial functions will be faster.
In very large datasets, the Spatial Operations perform faster in the RDBMS SQL Spatial Functions (when properly tunned), than in the client application ArcGIS Pro.
For example, If the Featureclass has 1 TB of data, and the client and the database are in the same LAN local area network, low latency and high bandwidth, it means that the 1 TB dataset needs to move from the database to the client to be processed, the client machine might need to have a lot of memory, etc. therefore letting the database to process the spatial operation using the spatial index and the spatial functions will be faster.
For our notes, the following tips from @VinceAngelo (from way back in 2011) might also be useful when tuning spatial queries:
[When exporting a feature class, you can develop a workflow to] query in spatial index order, which would optimize created feature order, which should result in improved performance with every query. And if you're going to do that, then you should also optimize your coordinate reference to reduce storage and improve query performance.
Related: