File Geodatabase views should support domains

AJR

File geodatabases now support views (which is great), but the views can't have domains applied to them (this should be fixed). If I have a view that pulls out a subset of data from a table and that table has domains applied to it, I should be able to apply those same domains to the file geodatabase view of the data subset, but this can't be done. It makes (some) sense that views aren't editable, but since they are registered with the geodatabase I can't think of any reason why allowing domains to be applied to them shouldn't be possible.

SSWoodward · ‎08-01-2023

Thanks for the Idea, @AJR

Could you help me understand your desired workflow a bit more? Are you asking for the ability to edit fgdb database views? OR are you asking to see domain descriptions in the view as opposed to the domain codes?

Bud · ‎08-02-2023

A different take on this idea might be:
Need to access FGDB system tables using SQL. For example, provide a way to query for domain values (codes and descriptions) for a given domain.

We can do it in enterprise geodatabases like Oracle EGDBs.

What I do is create individual views to get the domain values (codes and descriptions) for a given domain.

The old way (EXTRACTVALUE has been deprecated in Oracle):

create or replace view d_atn_type as 
elect
    substr(extractvalue(codedvalues.column_value,'CodedValue/Code'),1,255) as code,
    substr(extractvalue(codedvalues.column_value,'CodedValue/Name'),1,255) as description
from
    sde.gdb_items_vw i 
join 
    sde.gdb_itemtypes it 
    on i.type = it.uuid, table(xmlsequence(xmltype(definition).extract('/GPCodedValueDomain2/CodedValues/CodedValue'))) codedvalues 
where
    i.name = 'ATN_TYPE'

The new way (using XMLTABLE):

create or replace view d_atn_type as
select      
    cast(rownum as number(38,0)) as rownum_,
    x.code,
    x.description,
    i.name as domain_name
from        
    sde.gdb_items_vw i
cross apply xmltable(
    '/GPCodedValueDomain2/CodedValues/CodedValue' 
    passing xmltype(i.definition)
    columns
        code        varchar2(255) path './Code',
        description varchar2(255) path './Name'
    ) x    
where      
    i.name = 'ATN_TYPE'

Then in a query/view where I'm selecting data from an EGDB table or FC, I join from the field that has the domain code to the domain view to get the domain description:

select
    objectid,
    atn_id,
    type as type_code,
    d.description as type_description
from
    infrastr.active_transportation a
left join
    infrastr.d_atn_type_vw d
    on a.type = d.code

But that's not possible with file geodatabases, since we can't access the system tables via SQL.

AJR · ‎08-02-2023

@SSWoodward - I'm not asking to be able to edit a file geodatabase view (although that would be nice), I'm looking to be able to apply domains to a file geodatabase view. So in answer to your question, I'm looking to see the domain descriptions displayed when viewing the fgdb view data (rather than the real data values).

Bud · ‎08-02-2023

SSWoodward · ‎08-02-2023

Thanks for clarifying @AJR

I've moved this back to open to gain support.

AJR · ‎05-10-2024

And really, this isn't file geodatabase specific issue. Domains should be able to be applied to enterprise geodatabase registered views.

Bud

ArcCatalog 10.7.1; Oracle 18c 10.7.1 EGDB:

To my surprise, if the database view is registered with the geodatabase, then it's possible to set a domain using ArcCatalog > Properties or using the Assign Domain To Field tool in ArcGIS Pro 2.9.5. The domain is honored in the attribute table in both ArcMap 10.7.1 and ArcGIS Pro 2.9.5.

Likewise, if the database view is registered with the geodatabase, then it's possible to set a field alias using ArcCatalog > Properties or using the Alter Field tool in ArcGIS Pro 2.9.5.

More info in Updating registered view field alias and domains in Data Design Fields.

Bud

ArcGIS Pro 2.9.5

I tried to use a domain on a FGDB database view (registered with the GDB), but unfortunately, I got an error:

ERROR 000499: table is not editable

However, it did work with a registered database view in a mobile geodatabase. And as mentioned in my previous comment, it works in an enterprise geodatabase too.

Bud

Esri Support Case:
#03701528 - Database view (registered with GDB) — Assign Domain To Field works in eGDB and mGDB, but not fGDB

SSWoodward

@AJR , @Bud

This Idea seems to have grown into multiple Ideas so I am going to refocus it back to the original Idea. If other Ideas need to be opened that are related, I encourage that.

The original Idea here, as I understand it is:

@AJR wrote:
File geodatabases now support views (which is great), but the views can't have domains applied to them (this should be fixed). If I have a view that pulls out a subset of data from a table and that table has domains applied to it, I should be able to apply those same domains to the file geodatabase view of the data subset, but this can't be done....
... I'm looking to see the domain descriptions displayed when viewing the fgdb view data (rather than the real data values).

When I make a view of file geodatabase data that has a domain applied, using the below steps, I do see the domain descriptions, and not the real data values. I have laid out a simple repro case below.

@ARJ, can you let me know how you would change my workflow so that I can see what you are attempting to do, and identify where the gap in functionality resides. This workflow is occurring in ArcGIS Pro 3.3.1.

1. Open a file geodatabase containing any class which has domains applied to at least one field.

2. Create a view on that feature class using the Create Database View Geoprocessing tool, lets just say with the query, 'SELECT * from <class_name>'

3. Open the attribute table of that view, from the catalog pane. Make sure the 'Show domain and subtype descriptions' option is checked in the attribute table.

4. Note that the fields with domains applied, display domain descriptions and not field values.

Is this the behavior you are looking for?