topic Re: Comparing Two Feature Classes and Updating One of Them in Data Management Questions

Comparing Two Feature Classes and Updating One of Them

DavidBuehler — Thu, 21 Aug 2014 20:15:32 GMT

I am trying to automate the production of a feature class with daily updates. I would like to use a Python Script do this. The basic concept is there is a SQL table that is updated with addresses, the script needs to pull that table into a intermediate file geodatabase, geocoded it, compare the geocoded results to the existing SDE feature class, and remove any old entries from the SDE feature class, append any new features, and leave alone any that are the same. I get the first chunk of my script to do what I want it to do. When I get to the compare and update part, my initial line of thinking will not work in all cases (especially when there are only removals). I know there has to be a more elegant way of doing the compare and update portion. Any suggestions?

This is what the portion that does not work all the time:

# Put in error trapping in case an error occurs when running tool
CoMGIS_SDE_Warrants = #Path to SDE Feature Class
try:

# Make a layer from the feature class
arcpy.MakeFeatureLayer_management(MAW_GeoCode,"newCompare_lyr")

   # Selecting all existing features
   arcpy.SelectLayerByLocation_management("newCompare_lyr", "INTERSECT", CoMGIS_SDE_Warrants, "", "NEW_SELECTION")

   # Selecting only new features
   arcpy.SelectLayerByLocation_management("newCompare_lyr", "INTERSECT", CoMGIS_SDE_Warrants, "", "SWITCH_SELECTION")

# Write the selected features to a new featureclass
arcpy.Append_management("newCompare_lyr", CoMGIS_SDE_Warrants,"TEST","","")

arcpy.Delete_management("newCompare_lyr")

# Make a layer from the Existing Features
arcpy.MakeFeatureLayer_management(CoMGIS_SDE_Warrants,"existCompare_lyr")

   # Selecting all existing features
   arcpy.SelectLayerByLocation_management("existCompare_lyr", "INTERSECT", MAW_GeoCode, "", "NEW_SELECTION")

   # Selecting only Non-Active Warrants
   arcpy.SelectLayerByLocation_management("existCompare_lyr", "INTERSECT", MAW_GeoCode, "", "SWITCH_SELECTION")

# Delete Non-Active Warrants
arcpy.DeleteFeatures_management("existCompare_lyr")

   arcpy.Delete_management("existCompare_lyr")

except:
   print arcpy.GetMessages()

print "Success"

Re: Comparing Two Feature Classes and Updating One of Them

RiyasDeen — Fri, 22 Aug 2014 00:49:55 GMT

Hi David,

Assuming you have a common primary field in your FGDB and SDE Feature Class.

Iterate through FGDB FC (A) and SDE FC (B), and create two lists of primary fields.
A - B, will give list of additions in FGDB (Use Insert cursor to insert features to SDE)
B - A will give list of deletions in SDE (Delete features from SDE)
Use Geometry equals, if you want to compare geometry and update SDE geometry.

Reference links:

List Difference - Python, compute list difference - Stack Overflow‌

Insert Cursor - ArcGIS Help 10.1‌http://resources.arcgis.com/en/help/main/10.1/index.html#//018w0000000t000000

Update Cursor - ArcGIS Help 10.1‌

Geometry Equals - ArcGIS Help 10.1‌

Hope this helps.

Re: Comparing Two Feature Classes and Updating One of Them

DavidBuehler — Fri, 22 Aug 2014 16:30:54 GMT

Riyas,

Thank you for confirming what I need to do. I get the concepts, but do not know how to get there. I do have a primary field, which would be the Warrant ID. It looks like 2007-031. I looked at the List Difference link you sent, and did more digging. I guess I am not seeing how to do this. This is by the far the most advanced thing I have attempted to tackle.

Re: Comparing Two Feature Classes and Updating One of Them

RiyasDeen — Mon, 25 Aug 2014 00:09:09 GMT

Hi David,

Attached script should do the trick for you. modify the source, destination and fieldName to point to your script parameters.

Re: Comparing Two Feature Classes and Updating One of Them

DavidBuehler — Mon, 25 Aug 2014 14:38:14 GMT

Riyas,

Thank you for the script. After seeing what is going on, I have a better understanding of what is going on. Hopefully, I will be able to apply this knowledge in the future.

When I run the script with the changed parameters, I get a run time error of "An invalid SQL statement was used. [MAW_Geocode]". MAW_Geocode is the source parameter (the intermediate file geodatabase feature class).

It hits in the line:

for r in insertRows:

insertCursor.insertRow(r)

Any suggestions?

Re: Comparing Two Feature Classes and Updating One of Them

XanderBakker — Mon, 25 Aug 2014 15:21:20 GMT

I changed the code from Riyas Deen‌ a bit:

import arcpy

def createWhereClause(fc, fld_name, value):
    if len(arcpy.ListFields(fc, fld_name)) == 1:
        fld = arcpy.ListFields(fc, fld_name)[0]
        if fld.type == "String":
            where = "{0} = '{1}'".format(arcpy.AddFieldDelimiters(fc, fld_name), value)
        else:
            where = "{0} = {1}".format(arcpy.AddFieldDelimiters(fc, fld_name), value)
    return where

def getPrimaryFieldValues(fc, field):
    return [r[0] for r in arcpy.da.SearchCursor(fc, [field])]

def getSelectCursor(fc, whereClause):
    return arcpy.da.SearchCursor(fc, ["*"], whereClause)

def diff(a, b):
    return list(set(a) - set(b))

#source = arcpy.GetParameterAsText(0)
#destination = arcpy.GetParameterAsText(1)
#fieldName = arcpy.GetParameterAsText(2)
source = r"C:\tempdelete\PL_TO_LINE.gdb\PL\VERT_TO_POINIT"
destination = r"C:\tempdelete\PL_TO_LINE.gdb\PL\VERT_TO_POINIT_DEST"
fieldName = "ROUTE_LINK_NO"

sourceValues = getPrimaryFieldValues(source, fieldName)
destinationValues = getPrimaryFieldValues(destination, fieldName)

additions = diff(sourceValues, destinationValues)
deletions = diff(destinationValues, sourceValues)

with arcpy.da.InsertCursor(destination, ["*"]) as insertCursor:
    for a in additions:
        where = createWhereClause(source, fieldName, a)
        insertRows = getSelectCursor(source, where)
        for r in insertRows:
            insertCursor.insertRow(r)

for d in deletions:
    where = createWhereClause(destination, fieldName, d)
    with arcpy.da.UpdateCursor(destination, ["*"], where) as deleteCursor:
        for d in deleteCursor:
            deleteCursor.deleteRow()

How did you define your field name?

Is the intermediate geodatabase a file geodatabase or a personal one?

Kind regards, Xander

Re: Comparing Two Feature Classes and Updating One of Them

DavidBuehler — Mon, 25 Aug 2014 15:31:06 GMT

Xander,

source = "//fdrive/GIS/Depts/PD/proj/ActiveWarrants/ActiveWarrants.gdb/MAW_Geocode"

destination = "//fdrive/GIS/Depts/PD/proj/ActiveWarrants/SDE to MFLDSQL4.sde/CoMGIS.SDE.Warrants"

fieldName = "WarrentNumber"

The field is a text field.

The intermediate geodatabase is a file geodatabase.

Re: Comparing Two Feature Classes and Updating One of Them

DavidBuehler — Mon, 25 Aug 2014 16:10:32 GMT

Xander,

I now get a different error message:

Traceback (most recent call last):

File "C:\Python27\ArcGIS10.1\Lib\site-packages\Pythonwin\pywin\framework\scriptutils.py", line 326, in RunScript

exec codeObject in __main__.__dict__

File "F:\Home\GIS\Depts\PD\proj\ActiveWarrants\Test2.py", line 39, in <module>

insertCursor.insertRow(r)

TypeError: value #1 - unsupported type: tuple

Any thoughts?

Re: Comparing Two Feature Classes and Updating One of Them

XanderBakker — Mon, 25 Aug 2014 16:43:17 GMT

May have something to do with the fields setting, which is "*":

When using "*", geometry values will be returned in a tuple of the x,y-coordinates (equivalent to the SHAPE@XY token).

Might be better to construct a list with the field names...

Re: Comparing Two Feature Classes and Updating One of Them

XanderBakker — Mon, 25 Aug 2014 17:27:07 GMT

OK, so if we get rid of the "*" as field list it would look something like this. Please note that the code has not been tested on any data.

import arcpy

def createMatchingFieldList(fc1, fc2):
    lst1 = [fld.name for fld in arcpy.ListFields(fc1)]
    lst2 = [fld.name for fld in arcpy.ListFields(fc2)]
    return list(set(lst1) & set(lst2))

def createWhereClause(fc, fld_name, value):
    if len(arcpy.ListFields(fc, fld_name)) == 1:
        fld = arcpy.ListFields(fc, fld_name)[0]
        if fld.type == "String":
            where = "{0} = '{1}'".format(arcpy.AddFieldDelimiters(fc, fld_name), value)
        else:
            where = "{0} = {1}".format(arcpy.AddFieldDelimiters(fc, fld_name), value)
    return where

def getPrimaryFieldValues(fc, field):
    return [r[0] for r in arcpy.da.SearchCursor(fc, [field])]

def getSelectCursor(fc, flds, whereClause):
    return arcpy.da.SearchCursor(fc, flds, whereClause)

def diff(a, b):
    return list(set(a) - set(b))

#source = arcpy.GetParameterAsText(0)
#destination = arcpy.GetParameterAsText(1)
#fieldName = arcpy.GetParameterAsText(2)
source = r"C:\tempdelete\PL_TO_LINE.gdb\PL\VERT_TO_POINIT"
destination = r"C:\tempdelete\PL_TO_LINE.gdb\PL\VERT_TO_POINIT_DEST"
fieldName = "ROUTE_LINK_NO"

# create a list of field names which are in both featureclasses
flds = createMatchingFieldList(source, destination)

sourceValues = getPrimaryFieldValues(source, fieldName)
destinationValues = getPrimaryFieldValues(destination, fieldName)

additions = diff(sourceValues, destinationValues)
deletions = diff(destinationValues, sourceValues)

with arcpy.da.InsertCursor(destination, flds) as insertCursor:
    for a in additions:
        where = createWhereClause(source, fieldName, a)
        insertRows = getSelectCursor(source, flds, where)
        for r in insertRows:
            insertCursor.insertRow(r)

for d in deletions:
    where = createWhereClause(destination, fieldName, d)
    with arcpy.da.UpdateCursor(destination, flds, where) as deleteCursor:
        for d in deleteCursor:
            deleteCursor.deleteRow()

Kind regards, Xander

Re: Comparing Two Feature Classes and Updating One of Them

DavidBuehler — Mon, 25 Aug 2014 18:06:12 GMT

Xander,

Thank you very much. I ran it, tested, and it worked like a charm. I will spend some time to truly understanding what is all going on in the script you posted. Thank you again for helping out a newbie at python scripting.

Re: Comparing Two Feature Classes and Updating One of Them

XanderBakker — Mon, 25 Aug 2014 18:29:00 GMT

Remember that Riyas Deen‌ did most of the work.

To help you a little in the process of understanding the code, here goes...

There are a number of functions that can be reused and help to make the code more readable

line 3 - 6 (createMatchingFieldList) uses list comprehensions to create a list of the field names in both featureclasses. The lists are converted to sets which van be used with the & to create a set with only those elements that exist in both sets. The List() converts the resulting set to list
Line 8 - 15 (createWhereClause) uses the type of the field (the code will only handle string and numeric fields correctly) and the arcpy.AddFieldDelimiters to add quotes or brackets to the field depending on the datasource. This is powerful way of making code run correctly on different types of data (shapefiles, file, personal and enterprise geodatabases)
line 17 and 18 (getPrimaryFieldValues) uses again a list comprehension to create a list of values in a given field. It actually loop through the data and returns the first field r[0] and adds it to a list (hence the square brackets).
line 20 and 21 (getSelectCursor) creates a search cursor with the given fields and where clause.
line 23 and 24 (diff) returns a list of element which are in list a, but not in list b.

The rest of the code does the job:

lines 34 - 40 contains the calls to the various functions (creating the list of fields, the list of values for both featureclasses and determining the difference)
lines 42 - 47 creates an insert cursor on the destination and loop through the elements in "additions" and inserts the rows (from the source) into the destination
lines 49 to 53 uses an update cursor on the destination to remove those features that are in the destination, but not in the source.

Kind regards, Xander

Re: Comparing Two Feature Classes and Updating One of Them

AimeiChen — Tue, 08 Dec 2015 20:05:58 GMT

Hi Xander,

The python script you shared here was very useful, thanks!

The add function insert new records and delete function remove old records, is it possible to update attribute value if the record exist in both tables/features?

Thanks,

Amy

Re: Comparing Two Feature Classes and Updating One of Them

XanderBakker — Wed, 09 Dec 2015 00:01:05 GMT

I suppose that is possible, but what would you like to register in the destination featureclass is a feature ID still exists, but attributes have changed? Should the record be marked as changed or do you want to overwrite the field values? Be aware that only the listed fields (those that both featureclasses have) will be checked to see if attributes changed.

Re: Comparing Two Feature Classes and Updating One of Them

AimeiChen — Wed, 09 Dec 2015 14:31:03 GMT

Xander,

Thanks for your quick reply!

I have a feature layer, 10 attribute fields were captured in GIS and 20 fields were from City Works and rest were our inputs. In GIS, the feature layer is identical to the 10 fields in the destination features. So I'd like to send GIS updates to the layer automatically (overwrite the 10 field values) and keep updating/editing the rest attributes. Is it possible to do that?

Thanks,

Amy

Re: Comparing Two Feature Classes and Updating One of Them

DavidBuehler — Wed, 18 Dec 2019 20:49:28 GMT

Aimei,

Did you figure this out?

Re: Comparing Two Feature Classes and Updating One of Them

AimeiChen — Thu, 19 Dec 2019 13:34:08 GMT

David,

my solution is exporting the feature layer into File geo database, split it into three layers and update them accordingly. then merge them into one.

Thanks,

Aimei