idea Consistent/visible SQL expressions and query definitions in layers in ArcGIS Pro Ideas

Consistent/visible SQL expressions and query definitions in layers

Bud — Thu, 11 Jan 2024 02:51:40 GMT

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:

SQL expression is hidden; not visible in the properties. The .LYRX JSON may or may not include the SQL expression. It depends on the layer type.
SQL expression is a visible definition query.
The SQL definition (full-blown SELECT query) is stored in the Query section in the Properties —> Source tab.
The SQL definition (full-blown SELECT query) is displayed in the Catalog item properties —> Definition tab.
"Subset" information is stored in a "Selection definition set" in the Properties —> Selection tab. The SQL is not visible.

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:

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?
Similarly, could the "Selection definition set" of a Selection Layer be stored as a definition query? (i.e. OBJECTID IN (1,2,3...) so that we can see the SQL and use it elsewhere? Such as convert a selection to a definition query.
Could database views be standardized so that they all have the same Feature Layer Properties —> Source tab —> Query section? (in mobile, file, and enterprise geodatabase views)

Re: Consistent/visible SQL expressions and query definitions in layers

Bud — Sat, 24 Feb 2024 16:28:48 GMT

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.

Make Feature Layer

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.

Selection Layers

The "subset" information is stored in a "Selection definition set" in the Properties —> Selection tab.

Data is editable.

Re: Consistent/visible SQL expressions and query definitions in layers

Bud — Wed, 10 Jan 2024 00:29:42 GMT

Esri Case #03521080 - Mobile GDB database view: Feature Layer properties — Source tab — Query section

Support logged an enhancement for this issue, the current status for the enhancement is In Review.

ENH-000164223 - Needs more clarification on mobile geodatabase view layer properties > tab Source > Query section

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

Re: Consistent/visible SQL expressions and query definitions in layers

Bud — Thu, 22 Feb 2024 14:37:13 GMT

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