Skip navigation
All Places > ArcGIS API for Python > Blog > 2019 > February
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"

Let's say you have a Hosted Feature Layer named worldEQ which contains data on Earthquakes that have occurred throughout the world for the last 50 years:

 

earthquakes

 

You wish to create a view named worldEQView from this Hosted Feature Layer. To do that, you could use the following snippet:

from arcgis import GIS
from arcgis.features import FeatureLayerCollection
gis = GIS("https://www.arcgis.com", "username","password")

# Search for Source Hosted Feature Layer
source_search = gis.content.search("world_earthquakes")[0]
source_flc = FeatureLayerCollection.fromitem(source_search)

# Create View from Source Hosted Feature Layer
new_view = source_flc.manager.create_view(name="worldEQView")

 

This works out great and your view is created:

viewresult

 

 

Let's suppose you next want to use the view to show only earthquakes that occurred before the year 1988. Reviewing the Data tab of the view's Item Details, you see that you can filter by a column year_:

tbl

 

 

When you set a View Definition, that definition is defined at the service level. If you quickly set a test definition in the ArcGIS Online/Portal for ArcGIS user interface and take a look at the view service's Service Definition, you'll see the property that needs to be updated is viewDefinitionQuery:

 

Click on 'View' in the View's Item Details page

 

servurl

 

Next, click on the Layer:

 

layerrest

 

Click on 'JSON'

 

 

jsonrest

 

 

Scroll all the way to the bottom to see the 'viewDefinitionQuery' property:

defrest
Note: changing the value of viewDefinitionQuery also updates the related definitionQuery property

 

 

To update the viewDefinitionQuery property with the ArcGIS API for Python, you do the following:

# Search for newly created View
view_search = gis.content.search("worldEQView")[0]
view_flc = FeatureLayerCollection.fromitem(view_search)

# The viewDefinitionQuery property appears under layers
view_layer = view_flc.layers[0]

# Define a SQL query to filter out events past 1988
view_def = {"viewDefinitionQuery" : "year_ < 1988"}

# Update the definition to include the view definition query
view_layer.manager.update_definition(view_def)

 

You should be able to see this update reflected after refreshing the view Item Details page > Visualization

 

def

 

 

Altogether, the script to create a View from the Hosted Feature Layer and then to set a View Definition is:

from arcgis import GIS
from arcgis.features import FeatureLayerCollection
gis = GIS("https://www.arcgis.com", "username","password")

# Search for Source Hosted Feature Layer
source_search = gis.content.search("world_earthquakes")[0]
source_flc = FeatureLayerCollection.fromitem(source_search)

# Create View from Source Hosted Feature Layer
new_view = source_flc.manager.create_view(name="worldEQView")

# Search for newly created View
view_search = gis.content.search("worldEQView")[0]
view_flc = FeatureLayerCollection.fromitem(view_search)

# The viewDefinitionQuery property appears under layers
view_layer = view_flc.layers[0]

# Define a SQL query to filter out events past 1988
view_def = {"viewDefinitionQuery" : "year_ < 1988"}

# Update the definition to include the view definition query
view_layer.manager.update_definition(view_def)

 

This can be generalized into a standalone script like this one:

 

import sys
from arcgis import GIS
from arcgis.features import FeatureLayerCollection

def search_layer(conn,layer_name):
    search_results = conn.content.search(layer_name, item_type="Feature Layer")
    proper_index = [i for i, s in enumerate(search_results)
                    if '"' + layer_name + '"' in str(s)]
    found_item = search_results[proper_index[0]]
    flc = FeatureLayerCollection.fromitem(found_item)
    return flc

def create_view(conn, source_flc, view_name, layer_index, view_def):
    new_view = source_flc.manager.create_view(name=view_name)
    # Search for newly created View
    view_flc = search_layer(conn, view_name)
    # The viewDefinitionQuery property appears under layers
    view_layer = view_flc.layers[layer_index]
    # Update the definition to include the view definition query
    view_layer.manager.update_definition(view_def)
    print("View created")

def main():
    conn = GIS("https://www.arcgis.com",
               "username", "password")
    # Index of the Layer to be filtered
    layer_index = 0
    # Define a SQL query to filter out events past 1988
    view_def = {"viewDefinitionQuery" : "year_ < 1988"}
    # Search for target Hosted Feature Layer
    source_flc = search_layer(conn, "world_earthquakes")
    # Create View from Hosted Feature Layer
    create_view(conn, source_flc, "worldEQView", layer_index, view_def)

if __name__ == '__main__':
    sys.exit(main())

 

If you need to define an area of interest, this would be approached like so:

view_def = {"viewLayerDefinition":{"filter":   
{"operator":"esriSpatialRelIntersects","value":
{"geometryType":"esriGeometryEnvelope","geometry":
{"xmin":4485937.7074932605,"ymin":1543545.165101517,
"xmax":9417043.276225261,"ymax":6239836.182941515,
"spatialReference":{"wkid":102100,"latestWkid":3857}}}}}}
view_layer.manager.update_definition(update_dict)

The `gis.users.create()` method creates users for your Web GIS in ArcGIS Enterprise or ArcGIS Online. The recent update to ArcGIS Online introduced new parameters when creating users not yet implemented for users when created in Enterprise. The API for Python release 1.5.2 `create()` method tried to populate these new parameters when creating users in Enterprise, leading to an error. We logged BUG-000119265 to track the problem.

 

We've caught the problem. We have containment. We've updated the `gis.users.create()` method with the API for Python version 1.5.3, which will be coming soon. The method now properly unpacks the parameters whether the `create()` method is run for Enterprise or ArcGIS Online. We've corrected it and are moving onward. Stay tuned for future releases full of great new functionality!