Comparing Two Feature Classes and Updating One of Them

[DavidBuehler]

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"

[RiyasDeen]

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.

[DavidBuehler]

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.

[RiyasDeen]

Hi David,

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

[DavidBuehler]

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?

[XanderBakker]

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

[DavidBuehler]

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.

[DavidBuehler]

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?

[XanderBakker]

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...

[XanderBakker · Accepted solution]

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