Access FGDB system tables using SQL

Bud — Thu, 31 Aug 2023 13:41:18 GMT

It would be helpful if we could access file geodatabase system tables using SQL. For example, provide a dynamic 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.

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.

Related (but different): File Geodatabase views should support domains

idea Access FGDB system tables using SQL in ArcGIS Pro Ideas

Access FGDB system tables using SQL