Easy How-To: Symbology Using Related Records

8924
11
03-25-2020 10:31 AM
JosephRhodes2
Occasional Contributor II
9 11 8,924

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)
        df=df.drop_duplicates(subset="FacilityID")
    
        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
        cleaning_updates.reverse()
    
        def update(basins, cleanings):
            for FacilityID in overlap_rows['FacilityID']:
                try:
                    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']
                    catch_basins_lyr.edit_features(updates=[basin_feature])
                    print(f"Updated {basin_feature.attributes['FacilityID']} status to {basin_feature.attributes['Status']}", flush=True)
                except:
                    continue
    
        update(catch_basins_features, cleaning_updates)
    
    while True:
        update_basins()
        time.sleep(60)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

  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!

11 Comments
DrewMoffitt
New Contributor II

Great article, thanks!

JeanneBaker1
New Contributor

If you were using Portal instead of ArcGIS Online could you use ArcGIS Notebooks instead of PythonAnywhere?

JosephRhodes2
Occasional Contributor II

Hi Jeanne,

At a minimum, you'd have to remove the date query because it uses the datetime module, which is not part of the default ArcGIS Notebooks library. I don't think you can install additional Python modules to the Notebooks environment. However, you can probably do without the date query - it's only there to cull the list of inspections so the script doesn't have to comb through every inspection ever, just the ones from the past day.

Besides that, I'd recommend just giving it a try on some non-production services. I'd love to hear how it works out!

BertKraan
New Contributor II

I spend two days on this so far, and it has been an education (thanks for that :-)) but I can't get it to work. the altered attributes should be written back to the feature layer in line 42 right? doesn't happen, also line 43 never gets executed ...

I modified overlap_rows to merge on two separate fields in fc and table ( ...left_on='GlobalID_x', right_on='foreignkey' )since I have a unrelated GlobalID in my table ...

All seems ok looking at my debugger: lines 40 and 41 get executed and I can see the new values in the attributes but then ... I dunno.

Any idea on where to look for?

thanks for your time, Bert

JosephRhodes2
Occasional Contributor II

Hi Bert,

What do you mean by "I can see the new values in the attributes"? That would be the objective of the script, so I must be misunderstanding your description.

Joe

BertKraan
New Contributor II

Hi Joe,

I ment  to say I can see the new values in the debugger’s variables window but not in the actual records of the feature class.

I'm logged on as owner of the data and the feature class has editing enabled.

(to be honest, I followed Visualizing related data with Join Features in ArcGIS Online  as a work around so my product is live at the moment and no deadlines were harmed) but it would be nice to understand what's going on.

JosephRhodes2
Occasional Contributor II

Hey Bert,

Sorry, I didn't fully parse your original question. Using this method with GlobalIDs as both the primary and foreign keys will not work because there's nothing to match to in the related table. The Global IDs for records in the related table are going to be unique to those records, and the script looks for an identical attribute value.

However, you could build the relationship with GlobalID as the primary key and a GUID as the foreign key. ArcGIS will automatically populate the destination GUID field with the GlobalID, which would give you keys you could match as a string.

I'm glad you have a solution. In cases where there are thousands of records, it could be problematic because joins use one credit per feature. But it sounds like credit usage was acceptable for your particular application, so good work!

BertKraan
New Contributor II

After a week I had another go at my version of the script. Inching my way using notebook to see and understand line for line what happened where:

After I had somewhat of an understanding and I had a succesful df.merge I switched back to vscode and used the structure of your script to complete my own ... now it works and my co-workers can see the updated symbology. The workaround I described earliere isn't as good as this because you cannot take a view offline in collector, let alone the credits.

Thanks again for the blog and your replies!

Bert

by Anonymous User
Not applicable

I was afraid of this. Is there no other way to symbolize with related records in Web Map? Having done a fair bit of research on the topic by now, I would believe it if not, but it's still disappointing to have to run an external script for something that works automatically within AGOL for popups. 

Thanks for writing up this tutorial! I'm going to have to find another way for my project since I'm unwilling to support a script for what the project is, but I might have to try this someday for something else, and it certainly looks like it would get the job done. 

LindsaySpencer
New Contributor III

Great read - I am in the same boat with @anonymous55 User and I can say that I do not want to be supporting this through a script. Additionally, this process only seems to address the most recent related record. My needs are to summarize the set of related records for a single feature and use that feature set to determine symbology. Please upvote this idea if you find it useful! 

marksm_macomb
Occasional Contributor

Thank you for creating this post, because this ability will be incredibly useful for me! I just wanted to add my experience with trying to get this to work for me several years after this post was created, if it will help anyone else because I did run into some issues with your original code. 

For context, I'm not ~great~ at python, and I don't know a lot of the jargon, but I can get by. I am running my code in an ArcGIS Online Notebook, and will set it up on a schedule that way. One of the problems I ran into while trying your code was that my related table is set up with the GlobalID as the primary key and a ParentGUID as the foreign key. I did have an asset ID field in both the Parent and Child tables but the field name spellings did not match. At first I tried getting the code to work with the GlobalID and ParentGUID, however I kept getting errors in the for loop that I was not smart enough to figure out through Googling. I eventually went back and changed the spelling of the asset ID fields to match so I could better follow your code 1:1, and that made it so much easier.

The other issue I ran into was that the "SpatialDataFrame" package no longer exists, so I used a different method of creating my spatial data frames that I pulled from some other GeoNet posts.

Here is what I ended up with that worked for me:

from arcgis.gis import GIS
from arcgis.features import FeatureLayer
import pandas as pd
from arcgis.features import FeatureCollection
from datetime import datetime, timedelta

gis = GIS("home")

def update_manholes():
    
    one_day = datetime.today() - timedelta(days=1)
    string_day = one_day.strftime('%Y-%m-%d %H:%M:%S')
    where_query = f"Date_Time >= DATE '{string_day}'"

    ssManhole = gis.content.get('ITEM ID HERE')
    ssManhole_lyr = ssManhole.layers[4]
    ssManhole_fset = ssManhole_lyr.query()
    ssManhole_features = ssManhole_fset.features
    ssManhole_sdf = ssManhole_fset.sdf

    inspections = gis.content.get('ITEM ID HERE')
    inspections_lyr = inspections.tables[0]
    inspections_fset = inspections_lyr.query(where=where_query)
    inspections_features = inspections_fset.features
    inspections_sdf = inspections_fset.sdf

    df=inspections_sdf.sort_values(by=['Date_Time'], ascending=False)
    df=df.drop_duplicates(subset=["ParentGUID_"])

    overlap_rows = pd.merge(left = ssManhole_sdf, right = df, how='inner', on = 'FACILITYID')
    ssManhole_features = ssManhole_fset.features
    inspection_updates = inspections_fset.features
    inspection_updates.reverse()

    def update(manholes, inspections):
        for FACILITYID in overlap_rows['FACILITYID']:
            try:
                manhole_feature = [f for f in ssManhole_features if f.attributes['FACILITYID'] == FACILITYID][0]
                inspection_feature = [f for f in inspections_features if f.attributes['FACILITYID'] == FACILITYID][0]
                manhole_feature.attributes['LastInspectionDate'] = inspection_feature.attributes['Date_Time']
                manhole_feature.attributes['Cover_Condition'] = inspection_feature.attributes['Cover_Condition']
                ssManhole_lyr.edit_features(updates=[manhole_feature])
                print(f"Updated {manhole_feature.attributes['FACILITYID']} cover condition to {manhole_feature.attributes['Cover_Condition']}", flush=True)
            except:
                continue

    update(ssManhole_features, inspection_updates)

update_manholes()