How We Update Feature Classes with Data From Related Tables

Anonymous User · ‎02-14-2019

Our Storm Water personnel maintain several feature classes in an enterprise geodatabase, which is currently at version 10.6. Each of these feature classes is related to two tables: one for inspections and one for events. Each feature class has a field which represents the number of days since the feature was visited, either by inspection or event. They symbolize their layers by this field, using graduated colors, to show how long it's been since the feature was visited. For example, red features haven't been visited in a long time while green ones have been visited recently. Storm Water wanted this field updated automatically every night.

I wrote a Python script which performs the update. I recently extrapolated it to be very general, to apply to any geodatabase layer and any number of related tables. I call the method once per feature class. I apologize if the documentation is not in a format to your liking, but I followed some examples I found on-line. Since it is just a script, you're free to modify it to your whim:

# Script written by Roger Dunn, Information Technology Division, City Manager's Office, City of Orem, Utah
# February 2019
# Requires ArcGIS Desktop Standard or Advanced.
# Written with ArcPy for Desktop 10.6.1
# Target geodatabase is a 10.6 geodatabase affectionately called Knight, but really called OREMEGDB

# Use ArcPy
import arcpy

# DateTime functions needed
import datetime

def UpdateMasterFeatureClassDayRangeFieldFromMostRecentDetailEventTable(\
    arcSDEConnectionFileName \
    , masterFeatureClassName \
    , masterKeyFieldName \
    , masterDayRangeFieldName \
    , detailEventTableNames \
    , detailEventForeignKeyFieldNames \
    , detailEventDateFieldNames \
    , valueIfNoHistoryFound
    ):
    '''
    Updates an ArcGIS geodatabase feature class field which represents the number of days
    since something happened in a detail event table (which may or may not be related via
    a relationship class in ArcGIS).  This method doesn't return a value.

    :param str arcSDEConnectionFileName: The name of an .sde connection file.  If this
    script is running in ArcGIS Desktop, then it suffices to use 'Database Connections\'
    followed by the name of the file.  If this is automated, then the .sde file should
    be in the same directory as this script.
    
    :param str masterFeatureClassName: The name of the feature class to be modified.  The
    connection information in arcSDEConnectionFileName should have the user name and
    password (saved) of a user with permissions to modify masterFeatureClassName.  Note
    that masterFeatureClassName does not include the name of the parent feature dataset.

    :param str masterKeyFieldName: The name of the primary key field in the master feature
    class.

    :param str masterDayRangeFieldName: The name of the field in the master feature class
    which will hold the date range from today's date back to the most recent event in any
    of the detailEventTableNames.

    :param str[] detailEventTableNames: The names of tables related to the master feature
    class.

    :param str[] detailEventForeignKeyFieldNames: For each table listed in detailEventTableNames,
    include the name of the field in that table that corresponds to the master feature class'
    primary key field.  Therefore, this list is as long as detailEventTableNames.

    :param str[] detailEventDateFieldNames: For each table listed in detailEventTableNames,
    include the name of the field in that table that contains the event date.  Therefore, this
    list is as long as detailEventTableNames.

    :param var valueIfNoHistoryFound: The default value to insert in the master feature class'
    DayRangeField if no history can be found in any of the detailEventTableNames for that
    particular feature.
    ...
    '''

    # Change the environment workspace, and allow previous temporary datasets be overridden
    arcpy.env.workspace = arcSDEConnectionFileName
    arcpy.env.overwriteOutput = True
    # Store "today" (don't let it change during the script's iterations
    scriptToday = datetime.date.today()
    # Create a dictionary from the detail tables where the key is the unique ID of the master record
    # and the value is the most recent date for a detail record.
    # Initialize the dictionary
    valueDict = {}
    for detailTableIndex in range(len(detailEventTableNames)):
        detailFields = [\
            detailEventForeignKeyFieldNames[detailTableIndex] \
            , detailEventDateFieldNames[detailTableIndex] \
        ]
        with arcpy.da.SearchCursor(detailEventTableNames[detailTableIndex], detailFields) as readRows:
            for readRow in readRows:
                masterID = readRow[0]
                dateVal = readRow[1]
                if not masterID is None and not dateVal is None:
                    dateOnly = dateVal.date()
                    if not masterID in valueDict:
                        valueDict[masterID] = dateOnly
                    else:
                        if dateOnly > valueDict[masterID]:
                            valueDict[masterID] = dateOnly
                    del dateOnly
                del masterID
                del dateVal
                del readRow
        del detailFields
    del detailTableIndex
    #print valueDict

    # With valueDict now populated, it's time to modify the masterFeatureClass' DayRangeField with
    # what we've found
    
    masterFields = [masterKeyFieldName, masterDayRangeFieldName]
    edit = arcpy.da.Editor(arcSDEConnectionFileName)
    # Edit session is started without an undo/redo stack for versioned data (for second argument,
    # use False for unversioned data)
    edit.startEditing(False, True)
    try:
        with arcpy.da.UpdateCursor(masterFeatureClassName, masterFields) as masterRows:
            for masterRow in masterRows:
                masterID = masterRow[0]
                if masterID in valueDict:
                    daysSince = (scriptToday - valueDict[masterID]).days
                else:
                    daysSince = valueIfNoHistoryFound
                # If the calculated value for the date range doesn't equal what's already in the
                # field, modify it.  We only want delta rows where there's really a change.
                if masterRow[1] != daysSince:
                    # Start an edit operation
                    edit.startOperation()
                    try:
                        masterRow[1] = daysSince
                        masterRows.updateRow(masterRow)
                        #print '{0} {1} updated. Field {2} = {3}'.format(masterKeyField, masterID, masterEditField, daysSince)
                        # Stop the edit operation.
                        edit.stopOperation()
                    except:
                        edit.abortOperation()
                del daysSince
                del masterID
                del masterRow
    finally:
        # Stop the edit session and save the changes
        edit.stopEditing(True)
    del edit
    del valueDict
    del scriptToday
    del masterFields

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I call the function like this, but I'm not going to give out the real database, feature class, and table names:

from UpdateLayersFromTables import UpdateMasterFeatureClassDayRangeFieldFromMostRecentDetailEventTable

# Update Feature Class 1
UpdateMasterFeatureClassDayRangeFieldFromMostRecentDetailEventTable( \
    'MyConnection.sde' \
    , 'MySQLDB.SchemaOwner.FeatureClass1' \
    , 'UniqueID' \
    , 'DaysSince' \
    , ['MySQLDB.SchemaOwner.Class1Inspections', 'MySQLDB.SchemaOwner.Class1Events'] \
    , ['FeatClass1UniqueID', 'FeatClass1UID'] \
    , ['InspectionDate', 'EventDate'] \
    , 10000 \
    )

# Update Feature Class 2
UpdateMasterFeatureClassDayRangeFieldFromMostRecentDetailEventTable( \
    'MyConnection.sde' \
    , 'MySQLDB.SchemaOwner.FeatureClass2' \
    , 'UID' \
    , 'DaysSince' \
    , ['MySQLDB.SchemaOwner.Class2Inspections', 'MySQLDB.SchemaOwner.Class2Events'] \
    , ['FeatClass2UniqueID', 'FeatClass2UID'] \
    , ['InspectionDate', 'EventDate'] \
    , 10000 \
    )
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

"Roj"

DanDeegan · ‎02-25-2019

Bookmarked. There is a good chance I will encounter a use for this in the next few months. I've already been creating dictionaries of our attribute tables instead of copying the tables and creating joins. Dictionaries are very fast.

Thanks in advance.

RobertWeber · ‎11-07-2019

We are using Collector and Survey123 together and updating the feature class with the status from the most recent inspection which is stored in a related table I have done by querying the related table and updating the features accordingly. Still our set up is definitely more straightforward.

# create dataframe from the asset point layer
df = itemObject.layers[0].query().sdf

# create dataframe from related table of inspections
relatedtblDF = itemObject.tables[0].query().sdf


# get the GlobalId for each feature in asset point layer
for id in df['GlobalID']:
    #  get the last recorded status in related table of inspections

    status = relatedtblDF.loc[relatedtblDF['ParentID'] == id, 'Status'].to_list()

    lateststatus = status[-1]
    
#     update the asset point dataframe with the latest status from the related table of inspections 
    df.loc[df['GlobalID'] == id, 'assetStatus']=lateststatus‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Anonymous User · ‎01-02-2020

There is a reason that my implementation is more complex. The stake holder wanted to update one field in the master class based on date fields in TWO detail classes. The code has to look at the latest inspection in both of the related tables, get the maximum, and then do date math to find the number of days different. I also set up variables and method parameters instead of hard-coding any field names. Still, I can tell your grasp of Python is stronger than mine, so a mix of our code could be close to ideal.

I also want to add another note. I used to have a problem where the call to stopEditing would raise an exception about the state of the edits. I found another thread on this forum that mentioned that the key to not raising the exception is to delete (del) the variable holding the cursor. In my code above, this is masterRow.