Do spatial definition queries use SQL or ArcObjects?

Bud

Do spatial definition queries use SQL or ArcObjects code to perform the query operation? (ArcGIS Pro 3.5)

---

Assorted thoughts:

My best guess is ArcObjects is used, not SQL, to perform the query. I say that because I don't see any SQL when I switch to SQL Editor mode (Pro 3.5 Home Use; Oracle 18c Express database; SDO_Geometry; non-GDB; ).

I suppose using ArcObjects instead of SQL makes sense:

• Spatial database queries are often orders of magnitude slower than ArcObjects, even if the SQL query and spatial index are set up & used correctly.
• File geodatabases don't have spatial SQL functions.
• I assume ArcObjects would be easier to implement, instead of writing database-specific SQL spatial queries for each kind of geodatabase, each kind of enterprise database, and each kind of spatial type (i.e. ST_GEOMETRY vs SDO_GEOMETRY).
  Maybe a lot of the ArcObjects spatial querying code already existed for tools like Select By Location [or clipping layers in the map properties] and could be repurposed for spatial definition queries.

 

It almost seems like Esri did something similar to what I suggested in this comment (it was originally a separate idea): https://community.esri.com/t5/arcgis-pro-ideas/spatial-definition-query/idc-p/1552967/highlight/true#M32579

"One option would be Spatial SQL Definition Queries. But...spatial database queries are notoriously slow and are difficult to set up correctly...so I'm wondering if “Select By Location” functionality could be used as an alternative mechanism, since that tool is surprisingly fast, even for large datasets."

 

Our organization is on Pro 3.4.3 for the foreseeable future, which doesn't have the new spatial definition query functionality. As an alternative, I wonder if I could steal the SQL from the Select Layer by Location GP tool, since it has an "Intersect (DBMS)" option, which creates a database view that has a spatial SQL query: https://community.esri.com/t5/arcgis-pro-ideas/select-layer-by-location-intersect-dbms-create-a/idi-p/1365586 Maybe I could put that SQL logic in a regular SQL-based definition query in Pro 3.4.3.

 

Or just write an SQL spatial definition query from scratch. And test the performance to see if it's acceptable.

JoshuaBixby

Regarding the first question of this post, i.e., are spatial definition queries SQL based, I think comments in this post and Spatial Definition Query — Copy parameters as text to clipboard correctly infer the answer is "no, spatial definition queries are not implemented using SQL."

Often times when diving into the weeds with questions like this, it is helpful to research the ArcGIS Pro SDK | Documentation.  Looking at the DefinitionQuery Class - ArcGIS Pro, there is a GeometryUri Property (DefinitionQuery) - ArcGIS Pro:

Gets the geometry uri to filter rows in the dataset.

What one doesn't see when looking at the DefinitionQuery Class documentation is any property or method that would indicate the type of spatial operation to apply to the geometry obtained from the GeometryUri.  Those missing properties or methods strongly indicate there is only one way the geometry is handled, and that is intersection at this point.

What one also doesn't see in the documentation is what format is used for storing the information at the GeometryUri.  Usually the property or methods for interacting with stored geometry will be include part of the format name, like "FromWKT."  It turns out the geometry at the GeometryUri is binary, which was unexpected for me.  Looking at the binary format, I am fairly certain it is Esri shape formatted binary byte buffer, essentially the same format used for shape files.