+2
Here is the background. Esri uses subtype/domain objects in its GDB implementation instead of native RDBMS' relational tables with foreign key constraints. The result is often "nu-disciplined" databases from the RDBMS standpoint. I hope to create relational tables to protect data assets by extracting all the domains in our GDB.
Best answer by bwn
View original
+19
Check if these articles can help you: https://community.safe.com/s/article/extract-geodatabase-coded-domain-definitions
+2
@oscard, The article you referred to appears to show reading known domains from a feature class. We are looking for a method that reads ALL existing domains defined in an entire SDE-based or File-based GDB.
+1
There is a function in the Esri data access (da) module that lets you list all domains.
import arcpy
domains = arcpy.da.ListDomains("C:/Boston/Boston.gdb")
for domain in domains:
print('Domain name: {0}'.format(domain.name))
if domain.domainType == 'CodedValue':
coded_values = domain.codedValues
for val, desc in coded_values.items():
print('{0} : {1}'.format(val, desc))
elif domain.domainType == 'Range':
print('Min: {0}'.format(domain.range[0]))
print('Max: {0}'.format(domain.range[1]))I expect that you do not want to use an Esri licence? But since it is a proprietary structure you really don't have a choice. You could use a python caller inside a workbench.
+32
As an alternative you can use the custom transformer "Read ESRI GDB Domain Data" which you can find from the FME hub, but the way it handles the key-value pairs it fails when the description has a colon in it. (For example "Paint: green".) I created an FME version once, but it also wasn't foolproof either. I think @kimo is right, for the best results you should use Python to do this.
+2
Thanks, @kimo and @nielsgerrits . I tried the Hub Xformer "Read_ESRI_GDB_Domain_Data," which requires an input attribute known to have domain. Yes, I'll explore Python and Esri DA as suggested.
+2
@Bo Guo You need to extract the domains and subtypes from an Esri Geodatabase XML Workspace document using the FME XML reader, as described in the article: Geodatabase XML Workspace Document Schema Reader
+32
@Bo Guo You need to extract the domains and subtypes from an Esri Geodatabase XML Workspace document using the FME XML reader, as described in the article: Geodatabase XML Workspace Document Schema Reader
@Mark Stoakes is it possible to automate the export of the XML Workspace document without the use of ESRI's software?
+1
@Mark Stoakes is it possible to automate the export of the XML Workspace document without the use of ESRI's software?
No. You have to use another ArcGIS function. I have recently written a tool to export a geodatabase to Excel, including translating the domain descriptiions and adding the domains as separate tables. But all in python using the arcpy module.
+2
@nielsgerrits as @kimo mentions, I don't think you have an alternative to Esri tools. But you could probably use arcpy as described here: Export XML Workspace Document
+3
@Bo Guo I literally just posted the Solution to our GIS team on this yesterday!
You don't say what backend database is being used, but in SQL Server the Domain Codes vs Descriptions can be extracted with SQL like so:
SELECT items.Name AS "Domain", codedValue.value('Code[1]','nvarchar(max)') AS "Code", codedValue.value('Name[1]', 'nvarchar(max)') AS "Value"
FROM dbo.[GDB_ITEMS] AS items INNER JOIN dbo.[GDB_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'Note depending on implementation, it may be "dbo." as your ESRI GDB qualifer or something else, but it is pretty straightforward to find out by reviewing the table list inside the DB.
Oracle syntax is a little different for handling the XML, but there is a fair bit of literature posted by ESRI and users on how to do it in Oracle or SQL Server.
So the Solution is either:
Either way will return in into a normalised table format like this, being the complete list of all Domains, Codes and Values used in the Geodatabase
+2
@Bo Guo I literally just posted the Solution to our GIS team on this yesterday!
You don't say what backend database is being used, but in SQL Server the Domain Codes vs Descriptions can be extracted with SQL like so:
SELECT items.Name AS "Domain", codedValue.value('Code[1]','nvarchar(max)') AS "Code", codedValue.value('Name[1]', 'nvarchar(max)') AS "Value"
FROM dbo.[GDB_ITEMS] AS items INNER JOIN dbo.[GDB_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'Note depending on implementation, it may be "dbo." as your ESRI GDB qualifer or something else, but it is pretty straightforward to find out by reviewing the table list inside the DB.
Oracle syntax is a little different for handling the XML, but there is a fair bit of literature posted by ESRI and users on how to do it in Oracle or SQL Server.
So the Solution is either:
Either way will return in into a normalised table format like this, being the complete list of all Domains, Codes and Values used in the Geodatabase
@bwn , This is perhaps the cleanest way and certainly most efficient way to extract the domains and their values for an entire GDB! Since we also have the need to extract FeatureClass-Domain dependencies. we wrote a simple SQL for that:
WITH domains AS (SELECT items.uuid AS domain_id, items.Name AS "Domain"
FROM dbo.[GDB_ITEMS] AS items
JOIN dbo.[GDB_ITEMTYPES] AS itemtypes
ON items.Type = itemtypes.UUID
WHERE itemtypes.Name = 'Coded Value Domain'),
fcs AS ( SELECT items.uuid AS fc_id, items.name AS Feature_class, DatasetInfo1 As geom_col
FROM dbo.[GDB_ITEMS] AS items
JOIN dbo.[GDB_ITEMTYPES] AS itemtypes
ON items.Type = itemtypes.UUID
WHERE itemtypes.Name = 'Feature Class')
SELECT Feature_Class, domain
FROM [dbo].[GDB_ITEMRELATIONSHIPS]
JOIN domains ON domain_id = DestID JOIN fcs ON fc_id = OriginID
ORDER BY Feature_class
+3
@bwn , This is perhaps the cleanest way and certainly most efficient way to extract the domains and their values for an entire GDB! Since we also have the need to extract FeatureClass-Domain dependencies. we wrote a simple SQL for that:
WITH domains AS (SELECT items.uuid AS domain_id, items.Name AS "Domain"
FROM dbo.[GDB_ITEMS] AS items
JOIN dbo.[GDB_ITEMTYPES] AS itemtypes
ON items.Type = itemtypes.UUID
WHERE itemtypes.Name = 'Coded Value Domain'),
fcs AS ( SELECT items.uuid AS fc_id, items.name AS Feature_class, DatasetInfo1 As geom_col
FROM dbo.[GDB_ITEMS] AS items
JOIN dbo.[GDB_ITEMTYPES] AS itemtypes
ON items.Type = itemtypes.UUID
WHERE itemtypes.Name = 'Feature Class')
SELECT Feature_Class, domain
FROM [dbo].[GDB_ITEMRELATIONSHIPS]
JOIN domains ON domain_id = DestID JOIN fcs ON fc_id = OriginID
ORDER BY Feature_class
Yep, that is the next bit I had to tackle as well and half expected that might be the next comment/question! 😉 Getting the Domain list of Codes and Desriptions is one thing, but the next painful thing is to otherwise have to manually map out which of those Domains goes to which Field.
I had similar SQL bashed up for that (borrowing heavily from the ESRI user forums) to get the Feature Class, Field Name, Domain Name as another table, although I haven't developed it in the case of where different Domains have been used on different SubTypes.
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
+2
@bwn , This is perhaps the cleanest way and certainly most efficient way to extract the domains and their values for an entire GDB! Since we also have the need to extract FeatureClass-Domain dependencies. we wrote a simple SQL for that:
WITH domains AS (SELECT items.uuid AS domain_id, items.Name AS "Domain"
FROM dbo.[GDB_ITEMS] AS items
JOIN dbo.[GDB_ITEMTYPES] AS itemtypes
ON items.Type = itemtypes.UUID
WHERE itemtypes.Name = 'Coded Value Domain'),
fcs AS ( SELECT items.uuid AS fc_id, items.name AS Feature_class, DatasetInfo1 As geom_col
FROM dbo.[GDB_ITEMS] AS items
JOIN dbo.[GDB_ITEMTYPES] AS itemtypes
ON items.Type = itemtypes.UUID
WHERE itemtypes.Name = 'Feature Class')
SELECT Feature_Class, domain
FROM [dbo].[GDB_ITEMRELATIONSHIPS]
JOIN domains ON domain_id = DestID JOIN fcs ON fc_id = OriginID
ORDER BY Feature_class
Well done, and very timely for my project! :)
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
