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.
Is there a way to extract ALL domains in an Esri GDB?
Best answer by bwn
@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:
- Get the DBA to CREATE VIEW ... AS SELECT ... and just consume that as a regular "Table" through a Reader
- Put the above into an SQLExecutor or SQLCreator
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
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.