Easy How-To: Symbology Using Related Records

03-25-2020 10:31 AM
New Contributor III
6 9 4,303

I've had a longstanding need to visualize, query, and filter features using values from a related table, and came up with the following solution, which works great for my use case.

The use case involves managing stormwater catch basin inspection and cleaning (I've simplified the workflow for purposes of this post). The customer wanted field workers to be able to open Collector and quickly see basins that need attention or haven't been inspected/cleaned in over a year, and provide that same information in a dashboard. 

It's very easy to set up:

  1. Add fields to the feature layer to hold the attributes to bring over from the related table
  2. Grab the most recent record from the related table, and write values from that record over to the feature layer using the ArcGIS API for Python
  3. Put script on PythonAnywhere and set it to run every 60 seconds
  4. Configure web map symbology with a simple Arcade expression to show expired and failed inspections

Details on each step above:

  1. Self-explanatory. I named the fields "Status" and "LastCleaning".
  2. Wrote the script shown at the end of this post to grab records from the related table from the past day, sort them by time and drop duplicate records related to an asset (in case there were two inspection/cleaning records within the past 24 hours -- for example, a failed inspection on Wednesday afternoon was resolved on Thursday morning), then use a unique identifier ('FacilityID') to update the asset with data from the most recent inspection/cleaning. Shout out to Michael Duncan‌ for help with the date query.
    from arcgis import GIS
    from arcgis.features import FeatureLayer
    import pandas as pd
    from arcgis.features import SpatialDataFrame
    from datetime import datetime, timedelta
    import time
    gis = GIS(f"https://someorg.maps.arcgis.com", 'someuser', 'somepass')
    def update_basins():
        one_day = datetime.today() - timedelta(days=1)
        string_day = one_day.strftime('%Y-%m-%d %H:%M:%S')
        where_query = f"DateInspected >= DATE '{string_day}'"
        catch_basins = gis.content.get('21343f6579b74cf212576e5614db8866')
        catch_basins_lyr = catch_basins.layers[0]
        catch_basins_sdf = SpatialDataFrame.from_layer(catch_basins_lyr)
        catch_basins_fset = catch_basins_lyr.query()
        catch_basins_features = catch_basins_fset.features
        cleanings_url = 'https://services9.arcgis.com/iERASXD4kaw1L6en/arcgis/rest/services/this_is_an_example/FeatureServer/1'
        cleanings_lyr = FeatureLayer(cleanings_url)
        cleanings_sdf = SpatialDataFrame.from_layer(cleanings_lyr)
        cleanings_fset = cleanings_lyr.query(where=where_query, out_fields='DateInspected, FacilityID, Status')
        cleanings_features = cleanings_fset.features
        df=cleanings_sdf.sort_values('DateInspected', ascending=False)
        overlap_rows = pd.merge(left = catch_basins_sdf, right = df, how='inner', on = 'FacilityID')
        catch_basin_features = catch_basins_fset.features
        cleaning_updates = cleanings_fset.features
        def update(basins, cleanings):
            for FacilityID in overlap_rows['FacilityID']:
                    basin_feature = [f for f in catch_basin_features if f.attributes['FacilityID'] == FacilityID][0]
                    cleaning_feature = [f for f in cleanings_features if f.attributes['FacilityID'] == FacilityID][0]
                    basin_feature.attributes['LastCleaning'] = cleaning_feature.attributes['DateInspected']
                    basin_feature.attributes['Status'] = cleaning_feature.attributes['Status']
                    print(f"Updated {basin_feature.attributes['FacilityID']} status to {basin_feature.attributes['Status']}", flush=True)
        update(catch_basins_features, cleaning_updates)
    while True:

  3. Set up an "Always-On" task on PythonAnywhere to continually run the script. This is a very easy process. Just set up a PythonAnywhere account (the free tier would probably be fine for this application), upload your script file, and add the script on the Tasks tab as an Always-On Task. Now, the script writes the most recent inspection/cleaning record to the catch basins attribute table every 60 seconds:
  4. And lastly, just a simple Arcade expression to symbolize by the status of each basin (Current, Expired, or Needs Attention):
    var present = Now()
    var last_cleaning = $feature.LastCleaning
    var cleaning_age = DateDiff(present, last_cleaning, 'years');
    If (cleaning_age < 1 && $feature.Status == 'CleaningComplete') {
        return "Current"
    } else if ($feature.Status == 'NeedsAttention') {
        return "Needs Attention"
    } else if (cleaning_age > 1) {
        return "Expired"
    } else if (IsEmpty($feature.Status)) {
        return "Record missing or incomplete"

I hope this is helpful to someone. Feel free to offer suggestions or ask questions. Hang in there everyone!