Consistent/visible SQL expressions and query definitions in layers

482
3
01-05-2024 12:27 AM
Status: Open
Labels (1)
Bud
by
Notable Contributor

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:

  1. 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.
  2. SQL expression is a visible definition query.
  3. The SQL definition (full-blown SELECT query) is stored in the Query section in the Properties —> Source tab.
  4. The SQL definition (full-blown SELECT query) is displayed in the Catalog item properties —> Definition tab.
  5. "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)
3 Comments
Bud
by

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.

Bud_0-1704430870685.png

Bud_2-1704431652408.png

Bud_3-1704431661893.png

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.

Bud_17-1704433845837.png

Bud_18-1704434043362.png

Bud_19-1704434057029.png

 

Make Feature Layer

The SQL expression is a definition query.

Data is editable.

Bud_4-1704431754459.png

Bud_7-1704432052013.pngBud_6-1704431787761.png



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.

Bud_9-1704432453182.png

Bud_10-1704432468585.png

Bud_11-1704432492419.png

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.

Bud_12-1704432727637.png

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 layersFile geodatabase views do not behave the same way; they don’t have a Query section in the Source tab in the Properties window.

Bud_13-1704432983451.png

Bud_15-1704433538327.png

Unlike the other properties window screenshots above, this is the Catalog item properties, not the Contents feature layer properties:

Bud_14-1704433148071.png

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.

Bud_0-1704861965902.png

Bud_1-1704861975684.png

 

Related: 

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

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