Sync changes from view to feature class (using a GP tool; without fully replacing the data)

Question

I have an ST_GEOMETRY view with 50,000 rows (in an Oracle 18c/10.7.1 geodatabase). The view references a WORKORDER table in an external system (via a dblink/Oracle 12c).

Problem:

The view is too slow to be used in ArcGIS; there is a 5-10 second refresh-time due to the dblink.

---

As an alternative to using the view directly (in real-time), I want to synchronize the view's resultset to a feature class (near-real-time).

I would sync any changes in the view to the feature class on a 1-minute schedule (scheduled task on the server).

• I would only need to sync new or modified rows. Rows never get deleted in the external system — so I won't need to worry about deletes.
• The changes to the data in the view are minimal: there is an average of only 5 rows changed per minute.
• The logic would look like this: Sync rows from the view to the feature class where the changed_date in the view is newer than the last_edited_date in the feature class (or where the record doesn't exist in the feature class).

---

Question:

Is there a geoprocessing tool that I could use, on a schedule, to sync the data from the view to the feature class?

• I think I would prefer the sync to happen at a row-by-row level, rather than doing a full truncate and replace (truncate & replace would seem excessive, especially on a 1-minute schedule).

Answer 1

I think you're going to have to write a script using a combination of tools to accomplish what you want. The simplest approach is probably going to be to delete any rows that have been modified and then use the append tool to re-create them and add your new records all in one pass.

Answer 2

The ArcPy ends up being lengthy:

import arcpy,sys
arcpy.env.overwriteOutput = True


##************************
##Execute as:  C:\Progra~1\ArcGIS\Pro\bin\Python\Scripts\propy ViewUpdateFC.py
##************************


###***********Change these if necessary
LinkField = "WONUM" ##A common key field of unique identifiers
editDateFieldFC = "LAST_SYNC_DATE" ##The featureclass date field
editDateFieldVIEW = "CHANGEDATE"  ##The View date field
####Data Paths
FAKE_WORKORDER_VIEW = "C:\\Users\\<<USER>>\\AppData\\Roaming\\ESRI\\Desktop10.6\\ArcCatalog\\LOCAL_SDE.sde\\GISDATA.dbo.FAKE_WORKORDER_VIEW"
WORKORDER_COPY_FC = "C:\\Users\\<<USER>>\\AppData\\Roaming\\ESRI\\Desktop10.6\\ArcCatalog\\LOCAL_SDE.sde\\GISDATA.dbo.WORKORDER_COPY_FC"
###***********


def buildDictionary(inputFC, attributes, primaryKey): 
    # Build a dictionary of the primary key and its fields
    d = {}
    cur = arcpy.SearchCursor(inputFC)
      
    for row in cur:
        compareList = []
        for attribute in attributes:
            # print "attribute: " + attribute
            # print "ROWVAL: " + row.getValue(attribute)
            compareList.append(row.getValue(attribute))
        d[row.getValue(primaryKey)] = compareList
    del cur
    return d


def DeleteFeatures(lstRecordsToDelete):
    strFeatures = "','".join(lstRecordsToDelete)
    removestring = LinkField + " IN ('" + strFeatures + "')"
    with arcpy.da.UpdateCursor(WORKORDER_COPY_FC, [LinkField], removestring) as cursor:
        for row in cursor:
            cursor.deleteRow()


def Update():

    ###Build lists of attribute columns to compare
    attributesToCompare1 = [LinkField] 
    attributesToCompare1.append(editDateFieldFC)
    attributesToCompare2 = [LinkField] 
    attributesToCompare2.append(editDateFieldVIEW)
        
    #Get the list of unique IDs of features with differences
    d1 = buildDictionary(WORKORDER_COPY_FC, attributesToCompare1, LinkField)
    d2 = buildDictionary(FAKE_WORKORDER_VIEW, attributesToCompare2, LinkField)
    diffList = [str(key) for key in set(d1.keys() | d2.keys()) if d1.get(key) != d2.get(key)]

    ###IF no difference, end the script
    if(len(diffList) == 0):
        sys.exit("Layers are in sync")


    ### Create a where clause of the features that are different to use with the cursors
    strFeatures = "','".join(diffList)
    DeltaOIDS = LinkField + " IN ('" + strFeatures + "')"
    

    ### Make a dictionary of the the differing VIEW features AND their edit dates
    dictView = {}
    with arcpy.da.SearchCursor(FAKE_WORKORDER_VIEW, [LinkField,editDateFieldVIEW],DeltaOIDS) as ViewCursor:
        for row in ViewCursor:
            LinkFieldValue = str(row[0])
            changed_date = str(row[1])
            dictView[LinkFieldValue] = changed_date

    ### Make a dictionary of the the differing faetureclass features AND their edit dates
    dictFC = {}
    with arcpy.da.SearchCursor(WORKORDER_COPY_FC, [LinkField,editDateFieldFC],DeltaOIDS) as FCCursor:
        for row in FCCursor:
            LinkFieldValue = str(row[0])
            last_edited_date = str(row[1])
            dictFC[LinkFieldValue] = last_edited_date

    ###Cycle through the Featureclass features, get compare dates with the same feature in the VIEW.  
    ###If the View is later or the feature is not in the featureclass, then add the feature to the list of features to update
    lstUpdateRecords = []
    for key, value in dictView.items():
        LinkFieldValue = key
        Viewchanged_date = value
        if key in dictFC.keys(): 
            FClast_edited_date = dictFC[key]
            # print("Viewchanged_date: " + str(Viewchanged_date) + "  --  FClast_edited_date: " + str(FClast_edited_date))
            if(Viewchanged_date > FClast_edited_date):
                # print ("Date diff: " + str(Viewchanged_date))
                lstUpdateRecords.append(key)
        else:
            # print("Key: " + key + " not found")
            lstUpdateRecords.append(key)

        # print("lstUpdateRecords: " + str(lstUpdateRecords))
        
    ###Delete features from the featureclass that are to be updated        
    DeleteFeatures(lstUpdateRecords)

    strFeatures = "','".join(lstUpdateRecords)
    strDeltupdate =  LinkField + " IN ('" + strFeatures + "')"
    print("strDeltupdate: " + str(strDeltupdate))

    ###Append the features from the VIEW to the featureclass, replacing the one above that were deprecated, then deleted.
    ###Note that NO_TEST is used because all columns are the same except for the date fields, and a field make has been used for those.
    print("Append")
    arcpy.Append_management(FAKE_WORKORDER_VIEW, WORKORDER_COPY_FC, "NO_TEST", "WONUM \"WONUM\" true true false 25 Text 0 0 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,WONUM,-1,-1;CLASSIFICATION \"CLASSIFICATION\" true true false 635 Text 0 0 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,CLASSIFICATION,-1,-1;STATUS \"STATUS\" true true false 40 Text 0 0 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,STATUS,-1,-1;DIVISION \"DIVISION\" true true false 62 Text 0 0 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,DIVISION,-1,-1;ASSETNUM \"ASSETNUM\" true true false 50 Text 0 0 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,ASSETNUM,-1,-1;LOCATION \"LOCATION\" true true false 30 Text 0 0 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,LOCATION,-1,-1;OWNERGROUP \"OWNERGROUP\" true true false 30 Text 0 0 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,OWNERGROUP,-1,-1;LONGITUDEX \"LONGITUDEX\" true true false 8 Double 8 38 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,LONGITUDEX,-1,-1;LATITUDEY \"LATITUDEY\" true true false 8 Double 8 38 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,LATITUDEY,-1,-1;LAST_SYNC_DATE \"LAST_SYNC_DATE\" true true false 8 Date 0 0 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,CHANGEDATE,-1,-1", "",strDeltupdate)



Update(WORKORDER_COPY_FC,FAKE_WORKORDER_VIEW)