Geodatabase fields can be assigned domains. ArcGIS Desktop uses the domains as lookup tables (a.k.a. validation tables).
When users edit data in the attribute table
or attribute editing window
, the values are restricted to the list of codes in the domains. This is a useful tool for enforcing data integrity at the application level.
However, it is easy for users to accidentally circumvent the domains and enter erroneous values by using the field calculator
, ArcCatalog LOAD tool
, geoprocessing tools
, etc.. This is the main cause of errors in geodatabase tables at my organization.
Is there a way to use domains to restrict values that can be entered (beyond the attribute table) to truly force data integrity?
Best Answer
Business rules vs data integrity rules
When you model your database, you specify the business logic in two spaces:
a. The data integrity rules. This includes among others having an integer column so users won't be able to enter strings.
b. The business domain rules. This includes having check/unique/foreign key constraints on columns so it won't be possible to store duplicate values or illegal values.
I think it's a valid choice to use geodatabase domains to set up rules for the business domain, however you should keep in mind that those rules are really just helpers as they do not expose any restrictions on the database table itself. On the other hand, you might like your geodatabase playing a central role in defining the business rules for ArcGIS end users.
Possible solution
Here is what has worked for me best over last years. Say you have a feature class
Roads
(stored as a database table) which stores information about the roads and it has at least one column calledType
.RoadType
with two columns,Id
andDescription
.Roads
feature classType
column with theRoadType
tableId
column. This means that it won't be possible to define for a feature as a road type a value that is not in theId
column.RoadType
table to create a geodatabase domain (can be done either inarcpy
if you are comfortable with scripting or using GP tools).Advantages of the solution
You keep your business domain rules in a plain database table available for other applications who can work with SQL and they don't have to work with XML (as when one is trying to get information about geodatabase domains using plain SQL). If your database table will be edited by other applications that are not aware of ArcGIS geodatabase domains (maybe you have a non-spatial batch operation loading data into the
Roads
feature class every night), you can be sure that the data integrity will be enforced.You are still using geodatabase domains for ArcGIS users who are editing data in a rich client such as ArcMap and when editing feature's attributes, they will see the available choices for a field that has a domain associated with it. This is indeed helpful.
When you find out that you should add a new road type, it's all about writing a short SQL snippet that will insert new rows into the
RoadType
table and then usingarcpy
to update the geodatabase domain accordingly.This makes maintaining the geodatabase domains very easy - should you move your database data to another database where you don't have Esri enterprise geodatabase enabled, your data integrity rules will still be enforced. Also, if you will load your database tables into a new enterprise geodatabase, you can always create geodatabase domain using the
RoadType
table since your business rules table (RoadType
) is following your feature class.QA/QC using ArcGIS
One would also like to perform various QA/QC operations on the business data. Non-spatial data integrity can be enforced fairly easily as described above. However, checking for spatial integrity can be a lot more difficult to perform. Even though one could write SQL using Oracle Spatial or ST_Geometry functions, I'd suggest using ArcGIS tools for this.
You can use multiple tools for QA/QC that can do a lot of checks in an automated fashion. It is important to note that you can schedule your Python modules to run at a certain time using Windows Task Scheduler.
Geodatabase topology (you can also use GP tools such as
Validate Topology
andExport Topology Errors
as part of your Python script).Data Reviewer extension provides a lot of checks. Again, you can easily schedule a task as specified here in the Help page.
Custom Python scripts that where you can use the power of
arcpy
to generate reports on the QA/QC done and edit your data as needed.