Best way to find discrepancies between two attribute tables with different fields

[ChloePintarch]

I have two Enterprise geodatabase feature classes that I am working with for an entire county. Both have account numbers for every different parcel of land, and in one of the feature classes (called Parcels), there is an attribute field that is marked 01 if the parcel is a preserved piece of land. The other feature class (Easements) stores just the parcels that are preserved, and does not have the same attribute field in its table. There are discrepancies (errors) that I need to fix, and I am trying to figure out the easiest way possible to do so, as Parcels has about 190,000 polygons for every parcel in the county. Here is what I need to do:

Find the parcels in the Parcel layer that are NOT marked as 01 (preserved), and need to be fixed.

Find the parcels in the Parcel layer that ARE marked as 01 that are not preserved and need to be fixed.

I have tried the Select by Location tool as well as the Select by Attribute tool, and I have used every different relationship possible under the Select by Location tool, but I still do not know of the quickest way to come up with a list of parcels that need to be marked as 01 as well as a list of parcels that need to have their 01 mark removed. I even tried to do a table compare with that tool, but since the attribute field does not exist in the Easements layer, it does not work. I hope this is worded directly enough, I have been struggling with this for a while and am at a loss, so I figured I'd take it here and see if anyone can help.

[jcarlson]

Can you specify how you would actually know when you're seeing an error? Is it simply that 01 Parcel features should have an associated Easement?

[ChloePintarch]

So the Easements layer shows which accounts should have 01 marked in the Parcels layer. Visually, it is very easy to see which ones need to be marked with 01, but I have trouble when using the Select by Location tool, as the geometries for the polygons are different (sometimes, not all of a parcel is preserved under an easement, so the geometries of the parcels in the Easements layer can be slightly different than the geometries of the same parcels in the Parcels layer). If it was a smaller dataset I would do this manually, but there are about 2,400 preserved parcels, and about 190,000 parcels in the county, so manually is not an efficient option. Hope this makes sense.

[jcarlson · Accepted solution]

One of the "Select By Location" relationships is Have their center in. I don't know how different the geometries are, but if they're sort of similar, then selecting using the feature's center might be more reliable. You could try:

1. Set a Definition Query on Parcels: only show parcels without an 01
2. Run Select by Location
   1. Input: Parcels
   2. Relationship: Have their center in
   3. Selecting: Easements

The resulting set of features would be Parcel features that need their value update to be 01.

Similarly, running the inverse would be:

1. Set Definition Query on Parcels: only show parcels with 01
2. Run Select by Location
   1. Input Parcels
   2. Relationship: Have their center in
   3. Selecting: Easements
   4. Check the box for "invert spatial relationship"

The resulting set of features would be Parcels marked 01 that aren't covered by an easement, and would need their status changed.

[ChloePintarch]

This was the most straightforward way that I could have done this. Thank you so much for your help!

[jcarlson]

If this kind of validation is going to be a regular thing, I would suggest looking into Attribute Rules, if that's an option for you. Using Arcade, you'd be able to spatially intersect a feature (or it's centroid) with another layer, and flag features as errors depending on the output of the expression.

Alternately, if you trust the output of an expression like that, an Arcade expression in Field Calculator would work as well. There wouldn't be any need to pre-select your features, as the expression could handle that for you.

[ChloePintarch]

Attribute rules are not an option for us yet as we are not fully migrated over to Pro and I want to make sure that backwards compatibility is not compromised. With different geometries for parcels between Easements and Parcels, I think the way that you instructed will work best.

[DanLee]

Perhaps you can try the following, assuming the common field name is ACCOUNT_NUMBER and the field storing "01" in Parcels is named "PRESERVED_P".

1. Use Calculate Field to specify a new field, say PRESERVED_E, and calculate it to "01".

2. Use Join Field to transfer the new field, PRESERVED_E, to Parcels via the common field, ACCOUNT_NUMBER.  Now your Parcels know which ones are preserved in Easements or not.

3.  Use Select Layer By Attributes to select "PRESERVED_P is Null AND "PRESERVED_E = 01". The selected parcels should be "that are NOT marked as 01 (preserved), and need to be fixed".

4. Use Select Layer By Attributes to select "PRESERVED_P = 01 AND "PRESERVED_E is Null". The selected parcels should be "that ARE marked as 01 that are not preserved and need to be fixed".

Does that work for you?

[ChloePintarch]

Unfortunately I don't believe we can run Calculate Field analysis on our layers. We get errors every time, possibly due to restrictions with editing our layers in the context that I am in. However, I will try this in our testing environment, because I believe this will help as well. Thank you for your response!

[DanLee · Accepted solution]

I see.  I suggested Calculate Field was to make it explicit.  It could work without a new/calculated field if the Easements has a field, say FIELD_E, with non-null values for all records. Here is the shorter solution if doable:

Still assume the common field name is ACCOUNT_NUMBER and the existing field in Easements as FIELD_E (without null).

1. Use Add Join to join Parcels with Easements via the common field, ACCOUNT_NUMBER.  Now your Parcels know which ones are preserved in Easements or not. Note: once tables are joined, the table names will appear before field names.

2.  Use Select Layer By Attributes to select "Parcels.PRESERVED_P is Null AND "Easements.FIELD_E Is Not Null". The selected parcels should be "that are NOT marked as 01 (preserved), and need to be fixed".

3. Use Select Layer By Attributes to select "Parcels.PRESERVED_P = 01 AND "Easements.FIELD_E is Null". The selected parcels should be "that ARE marked as 01 that are not preserved and need to be fixed".

Could this work for you?