3

In ArcGIS Desktop, I can use the Validate Features option in the Editor Toolbar menu to validate feature class columns against their respective domains. More info here in the Validating Features section.

However, this functionality only works on spatial feature classes, not on non-spatial tables. Additionally, even if I were using a spatial feature-class, the feedback that the Validate Features function provides is too limited to be useful.

I want to select records in non-spatial tables where column values don't match their respective domains.

How can I do this?

3

Sample data:

USER1.A_NUMBERS_TABLE 
+--------------+
| NUMBER_FIELD |
+--------------+
|            1 |
|            2 |
|            3 |
|            4 |
|            5 |
|           66 |
+--------------+

A_NUMBERS_DOMAIN_VW:

SELECT
    SUBSTR(EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Code'),1,255) AS Code,
    SUBSTR(EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Name'),1,255) AS Description
FROM
    SDE.GDB_ITEMS_VW I 
JOIN 
    SDE.GDB_ITEMTYPES IT 
    ON I.Type = IT.UUID, TABLE(XMLSEQUENCE(XMLType(Definition).Extract('/GPCodedValueDomain2/CodedValues/CodedValue'))) CodedValues 
WHERE
    I.NAME = 'A_NUMBERS_DOMAIN'


+------+-------------+
| CODE | DESCRIPTION |
+------+-------------+
|    1 | ONE         |
|    2 | TWO         |
|    3 | THREE       |
|    4 | FOUR        |
|    5 | FIVE        |
+------+-------------+

Solution:

Use a sub-query.

In the Select by Attributes window:

NUMBER_FIELD NOT IN (
    SELECT 
        CODE 
    FROM 
        USER1.A_NUMBERS_DOMAIN_VW)

Where I got the idea:

I often use the sub-query from the Query for duplicate records in a feature class table example:

In the Select by Attributes window:

NUMBER_FIELD In (
    SELECT 
        NUMBER_FIELD 
    FROM 
        USER1.A_NUMBERS_TABLE 
    GROUP BY 
        NUMBER_FIELD 
    HAVING Count(*)>1 )

This is a pretty clever way to execute SQL in the Select by Attributes window, without being able to execute full SQL statements.

However, this only works in SQL-based databases such as enterprise geodatabases and personal geodatabases (not file geodatabases) -- as per the documentation. And I don't think it would work between different databases (query from one database to another).


Note: In the Select by Attributes window, we are limited to using only the WHERE clause (ESRI often calls this an SQL expression). Using sub-queries in the Select by Attributes window like this is extremely inefficient; sub-queries are really just a last resort. It would be much more appealing to use a join, rather than a sub-query, but we can't do joins because we can't execute full SQL statements from the Select by Attributes window.

1

Another way to do this is to do an in-map join from the table to the domain view.

As mentioned in my previous answer, a view can be created to get the domain values:

A_NUMBERS_DOMAIN_VW:

SELECT
    SUBSTR(EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Code'),1,255) AS Code,
    SUBSTR(EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Name'),1,255) AS Description
FROM
    SDE.GDB_ITEMS_VW I 
JOIN 
    SDE.GDB_ITEMTYPES IT 
    ON I.Type = IT.UUID, TABLE(XMLSEQUENCE(XMLType(Definition).Extract('/GPCodedValueDomain2/CodedValues/CodedValue'))) CodedValues 
WHERE
    I.NAME = 'A_NUMBERS_DOMAIN'


+------+-------------+
| CODE | DESCRIPTION |
+------+-------------+
|    1 | ONE         |
|    2 | TWO         |
|    3 | THREE       |
|    4 | FOUR        |
|    5 | FIVE        |
+------+-------------+

Then we can simply Select by Attributes where the joined domain CODE is null.

The downside to this approach is: if the domain has lots of values (think hundreds) then the join becomes quite slow. This is because there isn't an index on the domain view.

The no-index problem can be solved by A) exporting the domains as a table and creating an index, instead of using a dynamic view or B) by making a materialized view (if you have the appropriate permissions). But, option B might be more work than it's worth.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.