ArcGIS Pro 3.2.1; Mobile Geodatabase (except where noted for the query layer)
Summary:
There seem to be a few different ways SQL expressions and queries are stored in feature layers and Catalog items:
It's confusing when expressions and queries are stored in so many different ways. And it's not ideal when the SQL expression is hidden. Can anything be done to standardize SQL expressions and queries in layers? For example:
Details:
Make Query Table - allows for joining tables (is a spatial feature layer, unlike Make Table View which is a standalone table)
The SQL expression is hidden. It's not a definition query.
Data is not editable.
I saved the layer as a .lyrx file. The SQL expression is stored as a "whereClause" in the JSON source.
{ "type" : "CIMLayerDocument", "version" : "3.2.0", "build" : 49743, "layers" : [ "CIMPATH=map/querytable.json" ], "layerDefinitions" : [ { "type" : "CIMFeatureLayer", "name" : "QueryTable", "uRI" : "CIMPATH=map/querytable.json", "sourceModifiedTime" : { "type" : "TimeInstant" }, "useSourceMetadata" : true, "description" : "main.QueryTable", "layerElevation" : { "type" : "CIMLayerElevationSurface", "elevationSurfaceLayerURI" : "CIMPATH=Map/8534c3b1c33b44cbac3bcdf2d05bc946.json" }, "expanded" : true, "layerType" : "Operational", "showLegends" : true, "visibility" : true, "displayCacheType" : "Permanent", "maxDisplayCacheAge" : 5, "showPopups" : true, "serviceLayerID" : -1, "refreshRate" : -1, "refreshRateUnit" : "esriTimeUnitsSeconds", "blendingMode" : "Alpha", "allowDrapingOnIntegratedMesh" : true, "autoGenerateFeatureTemplates" : true, "featureElevationExpression" : "0", "featureTable" : { "type" : "CIMFeatureTable", "displayField" : "type", "editable" : true, "dataConnection" : { "type" : "CIMTableQueryNameDataConnection", "workspaceConnectionString" : "DATABASE=main;SERVER=C:;INSTANCE=sde:sqlite:C:\\2023 Files\\GDBs10\\New Mobile Geodatabase.geodatabase;DB_CONNECTION_PROPERTIES=C:\\2023 Files\\GDBs10\\New Mobile Geodatabase.geodatabase;IS_GEODATABASE=true;AUTHENTICATION_MODE=OSA;IS_NOSERVER=0", "workspaceFactory" : "SQLite", "dataset" : "main.QueryTable", "datasetType" : "esriDTFeatureClass", "tables" : "main.A_TEST_FC", "whereClause" : "type = 'b'", "shapeType" : "esriGeometryPolygon", "featureType" : "esriFTSimple", "shapeFieldName" : "Shape"
...
Unrelated, but worth mentioning: Make Query Table - Article ID:000010848 Create a one-to-many join in ArcMap
Make Table View (is a non-spatial standalone table, unlike Make Query Table which is a feature layer)
The SQL expression is a definition query.
Data is editable.
The SQL expression is a definition query.
Data is editable.
Make Query Layer (ArcGIS Pro 2.6.8; Oracle 18c 10.7.1 EGDB)
The SQL definition (full-blown SELECT query) is stored in the Query section in the Properties —> Source tab.
Data is not editable.
Only enterprise geodatabases support query layers, not mobile or file geodatabases.
Create Database View (mobile geodatabase)
The SQL definition (full-blown SELECT query) is displayed in the Catalog item properties —> Definition tab.
Data is not editable.
The Query section in the screenshot below isn't actually editable; it brings up the Edit Query Layer dialog, but the dialog isn’t useable/functional. See: Mobile geodatabase query layers. File geodatabase views do not behave the same way; they don’t have a Query section in the Source tab in the Properties window.
Unlike the other properties window screenshots above, this is the Catalog item properties, not the Contents feature layer properties:
SQL formatting (line breaks and indents) are not preserved when the view is created using ArcGIS Pro. Whereas query layers, definition queries, and Select by Attributes do display/preserve SQL formatting without issue.
Selections in .LYRX files, .MAPX files, etc.:
They seem to be different yet again. There is no SQL expression in the JSON; there is only a binary reference to those ObjectIDs.
The "subset" information is stored in a "Selection definition set" in the Properties —> Selection tab.
Data is editable.
Related:
ENH-000164223 - Needs more clarification on mobile geodatabase view layer properties > tab Source > Query sectionAs mentioned in the enhancement, mobile geodatabase view layer Properties > tab Source > Query section offers the ability to edit query layer, however the SQL query isn't actually editable, we need clarification on whether the Query section / Edit Query Layer should be removed from the Source tab, or the query editing functionality should be added to both mobile and file gdbs.
Regarding:
The SQL expression is hidden in Make Query Table layers. Could that be changed so that the SQL expression is a visible definition query, similar to other tools that make layers?
On the other hand, I've come across a scenario where it is beneficial to hide the SQL Expression instead of it being a definition query: Add Join not keeping all target features
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.