Skip to main content

Hi,

 

It seems there is no easy way to do this from what I have read on this topic. I really wish for a way to extract coded domains from feature classes in an SDE geodatabase. 

 

I don’t even have to have the field names since I have named the domains after the featureclass, subtype and field it is attatched to.

 

How can I get a hold of domain lists within FME?

 

The point for me is to regularly be able to pull out lists of the options my users have in different fields since these need to be looked over now and then.

Nothing out of the box. I think it makes the most sense to do this using PythonCallers.

Nowadays (with chatgpt and other AI solutions) it aint that hard to get it done. The only things you need are FME, ArcGIS Pro and some time to tinker.

There are some custom transformers on the fme hub, but I think they are built for file geodatabases.


The code I posted here should work, just switch out the reference to the gdb with your sde connection file

Extract domain value from GDB file? | Community (safe.com)


Hi @sofia ,

This can be done relatively easily with SQLExecutor or SQLCreator and some sample SQL that ESRI and ArcGIS Power Users have provided.  In our system, these queries are so useful we’ve gone further and set them up as permanent Database Views because they are used for Lookups in Eg. Power BI, Schema analysis, broader data analytics etc.

The advantage of this, beyond able to use more broadly and having very fast query performance, is do not need any ArcGIS or ArcPy to be on the FME machine needing to query the SDE metadata, which also means we don’t need to use SDE Readers to perform Domain Value to Code translations, we can just use plain SQL Server Spatial/Non-Spatial Readers and join them to the Domain/SubType SQL Views where needed.

For the below, just need to switch out the database domain for GDB_ITEMS and GDB_ITEMTYPES table.  Ours is “dbo”.

For SDE on MS SQL Server, getting Domain Name vs Domain Code vs Domain Value is
 

SELECT items.Name AS "Domain", codedValue.value('Coded1]','nvarchar(max)') AS "Code", codedValue.value('Named1]', 'nvarchar(max)') AS "Value"
FROM dbo."GDB_ITEMS] AS items INNER JOIN dbo.mGDB_ITEMTYPES] AS itemtypes
ON items.Type = itemtypes.UUID
CROSS APPLY items.Definition.nodes
('/GPCodedValueDomain2/CodedValues/CodedValue') AS CodedValues(codedValue)
WHERE itemtypes.Name = 'Coded Value Domain'

You can further get a list of FCs/Tables vsFields vs Domains applied to them with

SELECT
i.Name AS FeatureClass
,xVal.value('Name 1]','nvarchar(max)') Field
,xVal.value('DomainName.1]', 'nvarchar(max)') Domain
FROM dbo.GDB_ITEMS i JOIN dbo.GDB_ITEMTYPES it ON i.Type = it.UUID
CROSS APPLY i.Definition.nodes('/*/GPFieldInfoExs/GPFieldInfoEx') dx(xVal)
WHERE i.NAME IS NOT NULL AND xVal.value('DomainName.1]', 'nvarchar(max)') IS NOT NULL

Getting SubTypes is

SELECT items.name AS "ObjectName", subtype.value('SubtypeNamev1]', 'nvarchar(max)') AS "Description", 
subtype.value('SubtypeCodev1]', 'int') AS "Code"
FROM dbo.GDB_ITEMS AS items INNER JOIN dbo.GDB_ITEMTYPES AS itemtypes
ON items.Type = itemtypes.UUID
CROSS APPLY
items.Definition.nodes('/DEFeatureClassInfo/Subtypes/Subtype') AS Subtypes(subtype)


Eg,  Execution time less than 1 second:
 



Similarly SubTypes, SubType Descriptions per SDE Object
 

 

..and finally which Fields have Domains applied to them, and what that Domain is.
 

 


Reply