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...

"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-... 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.

[Bud]

Filter features with definition queries

Definition queries are composed of one or more clauses. There are two types of clauses: attribute clauses and spatial clauses. All clauses are written in SQL syntax and constructed using the query builder.

I'm wondering if "All clauses are written in SQL syntax" is misleading, and doesn't apply to spatial definition queries.

[TanuHoque]

@Bud 

Thanks for pointing that out. We will work on updating our help doc to make it clear. Attribute clauses are written in SQL syntax. Spatial clauses are as you suspected complicated!

[Bud]

Maybe the spatial definition query functionality was copied from map clip layers:

Credit for this observation goes to @AlfredBaldenweck in Spatial Definition Query — Highlight chosen layer

[TanuHoque]

you folks are right that we tried to reuse the same user experience/UI from Clip Layers functionality. And that is where the similarity ends, the implementation is completely different.

[JoshuaBixby]

ArcObjects?  I understand what is meant, but I do think it is important to point out for readers that ArcObjects has nothing to do with ArcGIS Pro.  From ArcObjects Help for .NET developers (ArcObjects .NET 10.8 SDK):

ArcObjects is a library of Component Object Model (COM) components that make up the foundation of ArcGIS....

...

ArcGIS Desktop is a suite of integrated applications including ArcMap, ArcGlobe, ArcScene, and ArcCatalog.

The original ArcGIS Desktop applications, i.e. ArcMap, ArcCatalog, etc..., were built on top of Microsoft Component Object Model (COM) while ArcGIS Pro is built on top of Microsoft .NET platform.  Although there is an ArcObjects .NET SDK, that SDK is a effectively just a wrapper to help .NET developers interact with COM objects.

What term should be used instead of "ArcObjects?"  Should we say ".NET", or go with something more generic like "native code" or "compiled code?"  I don't know if there is a clear winner, but I think it best to avoid using "ArcObjects."

[JoshuaBixby · Accepted solution]

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.

[TanuHoque]

@Bud, 

As @JoshuaBixby pointed out above, spatial clauses are stored as binary geometry objects with a layer's definition query. And they do not get translated into a sql queries at run time.

At a very high level, to make the query runs efficiently, we have taken a hybrid approach. If you trace calls made to enterprise databases, you will notice a sql with spatial search component (this is not the geometry that you picked as the spatial clause geometry). In many cases, that call would return some false positives. That is why we apply a secondary spatial filter in ArcObjects level to weed those out.

I'm not going to get into details here as I might not have all correct information in my hands. We might look into some possibilities of writing some posts at a later time with details.

Thanks for your interest in this new enhancement. We are hoping this will help with your workflows. Please keep us posted with your feedback and ideas.