How We Update Feature Classes with Data From Related Tables

3157
3
02-14-2019 09:58 AM
by Anonymous User
Not applicable
3 3 3,157

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"

3 Comments