List all feature classes and their subtypes, and domain values from a geodatabase
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.
Page 1 / 1
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.
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.