Dynamic/calculated field in ArcGIS Online via Arcade expression?

3092
7
03-18-2022 03:54 PM
Labels (2)
DanielShaffer2
New Contributor III

Hi again!

I'm working in AGO with ArcMap and have Pro available.

Problem: I need to symbolize a layer of water monitoring stations by size and color based on:

  • Number of samples at that location
  • Water Temp on the most recent collection date.

I have a related table with all the sampling data and a feature layer of my sampling stations.

Using the Join One:One from the Analysis tools in MV Classic (outputting Layer View), I can join the related table to the sample locations, and I can either:

  • Keep one record that has the latest collection date and all of that sample's data.; OR
  • Generate statistics, including the MAX collection date and the Join Count (#related records/station) but NOT the actual water temp for that collection date.

I also cannot symbolize based on attributes in a related table (cannot access feature sets in Change Style Expression Builder), so...

How can I capture BOTH the collection date AND actual water temp WHILE generating the Join Count at each station?

The only way I can think to do this is to create a Dynamic Field Calculation in a new field of the Joined Layer View that will regularly update the related record count for each station. Is that possible? Am I anywhere near the right track?

Is there any other way to access the related table from the Change Style Expression Builder?

@XanderBakkeryou seem to have most of this figured out. Any ideas?

(Thought about a One:Many join, but I can't filter based on a MAX date, or anything else that is useful.)

Been beating my head against the wall for two days on this one.

Thanks in advance for any help. I can pop a map together Monday if that's helpful, but I think I've described the problem well enough for now. And I have a lot of failed attempts to clean up! 🙂

 

0 Kudos
7 Replies
XanderBakker
Esri Esteemed Contributor

Hi @DanielShaffer2 ,

Sorry for getting back to you so late, but I was occupied organizing an event that took place yesterday.

A couple of thoughts in case this is still current:

  • Since you are using ArcGIS Online, I suppose that you want to symbolize the information there. This might limit the possibilities like using Attribute Rules which are available in ArcGIS Pro and ArcGIS Enterprise (but not in AGOL or ArcMap).
  • You are right that it is complex and limited to visualize information from a related table. You can use Arcade in a Field Calculation (static) that accesses data from the related table and includes it as fields so that you can use multiple fields to visualize the results in the web map. However, if the data underneath changes in time, you will have to include a mechanism to update the values with a certain interval. 

Let me know if you still need help and if so, it would be nice to have access to some sample data to have a better understanding of what you want to achieve. 

0 Kudos
DanielShaffer2
New Contributor III

Thanks for getting back to me, @XanderBakker.

I think I have found a way to do what I need for now.

Creating a Joined Layer View in MVClassic, keeping the most recent record, allows me to symbolize based on a single, or multiple attributes from that record (good). I can create the Join Count by accessing the related table directly and filtering on a monitoring station name. So that's good for the popup. But, I still cannot use that number to change the Style of the Layer View.

Given the proximity of some of the monitoring stations, I think we may move away from symbolizing size based on sample count...it's messy. However, I would still LOVE to find a way to symbolize based on data from a related table. I'm sure I'll want to do this in the future.

If a static field calculation, updated by a Python script or an associated script in a Jupiter Notebook or something would work, I'm all for it. Little to no experience with these, respectively, but I'm game to try!

Here is the map I'm currently working in. I've started over, so there should not be too much unnecessary junk in there.

https://abra.maps.arcgis.com/home/webmap/viewer.html?webmap=0c262e79b0494517bdcf306d248fee00

Thanks for any help or direction!

Dan

0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi @DanielShaffer2 ,

To do some testing I will need to extract some of your data from the map, republish the data in my portal and look at the possible scenarios to accomplish this. It may take some time to do this, but I will have a look at it.

0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi @DanielShaffer2 ,

I extracted the data and published it to my organization to do some testing and wanted to provide a small example of what you could do with Arcade.

For instance, I can extract the last measurement of the water temperature from the Chemistry table by doing this:

var station = $feature["Station_Name"];
var sql = "Location = @station";
var tbl = OrderBy(Filter(FeatureSetByName($datastore,"Chemistry_Import", ["Location", "Water_Temp_C", "Collection_Date"]), sql), "Collection_Date DESC");
var cnt = Count(tbl);

var result = Null;
if (cnt > 0) {
    var temp = First(tbl)["Water_Temp_C"];
    result = temp;
}

return result;

 

Or I can extract the date on which the latest temperature was measured, like this:

var station = $feature["Station_Name"];
var sql = "Location = @station";
var tbl = OrderBy(Filter(FeatureSetByName($datastore,"Chemistry_Import", ["Location", "Water_Temp_C", "Collection_Date"]), sql), "Collection_Date DESC");
var cnt = Count(tbl);

var result = Null;
if (cnt > 0) {
    var dt = First(tbl)["Collection_Date"];
    result = dt;
}

return result;

 

Or the count of the Chemistry samples at each location:

var station = $feature["Station_Name"];
var sql = "Location = @station";
var tbl = Filter(FeatureSetByName($datastore,"Chemistry_Import", ["Location"]), sql);
var cnt = Count(tbl);

return cnt;

 

Or do some statistics of getting the average temperature at each location:

var station = $feature["Station_Name"];
var sql = "Location = @station";
var tbl = Filter(FeatureSetByName($datastore,"Chemistry_Import", ["Location", "Water_Temp_C"]), sql);
var cnt = Count(tbl);

var result = Null;
if (cnt > 0) {
    var temp = Mean(tbl, "Water_Temp_C");
    result = temp;
}

 

Having the (aggregated) data at the feature level allows you to use multiple attributes for visualization, like the latest temperature and the number of samples:

XanderBakker_0-1650408221762.png

... or the mean temperature versus the latest temperature:

XanderBakker_1-1650408366220.png

This method is probably not the best when you have a lot of information that needs to be updated (a lot of field calculations to perform). In that case, it might be better to create a Notebook and use Python to update the data. This way you can also create a scheduled task that will execute every defined interval to update the data without any manual intervention. 

0 Kudos
DanielShaffer2
New Contributor III

Hi, Xander!

Thanks for having a deeper look at this. Could you direct me to some tutorial or basic material on the Notebook/Python option?

Your code examples above work great for Popups, but not within the Change Style dialogs. (OrderBy, Filter & FeatureSets are not available there). I was pretty much there already with popups.

Using the Joined table displaying as a Layer View, I also had already figured out Style options...and it sort of works...though for some reason not all numeric fields seem to be available to use for styling when they should.

So, to do what I want to do, the Notebook/Python scheme seems to be it. I'll go ahead and look at the ESRI training catalog options, but if you know of one lesson that may allow me to zero right in on this issue that would be helpful.

Also, if I'm totally missing some way that your examples DO allow for changing Style, please let me know. I'm way beyond feeling embarrassed at overlooking something obvious. I have "two under 3" right now, so sleep and "sharpness" are hard to come by!

Thanks again for your time and help!

Dan

0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi @DanielShaffer2 ,

Just to clarify: "Your code examples above work great for Popups, but not within the Change Style dialogs. (OrderBy, Filter & FeatureSets are not available there). I was pretty much there already with popups.

As I mentioned at the end of my previous response, the Arcade expression would be used in multiples Field Calculations and as you also confirmed will not be your first option.

A scheduled Python Notebook is probably a lot better to accomplish the desired result. I recommend having a look at the path "Learn Python with ArcGIS Notebooks" Path: https://learn.arcgis.com/en/paths/learn-python-with-arcgis-notebooks/ , which should be available to you without cost.

Here is an example of a Notebook that I have used in the past to update information in a featureset based on related data.

# load libraries
from arcgis.gis import GIS
from copy import deepcopy

# create a connection to the active portal (AGOL)
gis = GIS("home")

# Item Added From Toolbar
# Title: ValvulasIBAL_v02 | Type: Feature Service | Owner: xbakker.spx
item = gis.content.get("00000000000000000000000000000000")  # replace with your item ID

# I have a hosted service with one featureset and 1 table, that why I use 0 as index
flayer = item.layers[0]
tbl = item.tables[0]
print("features: {}".format(len(flayer.query(where="1=1"))))
print("records: {}".format(len(tbl.query(where="1=1"))))

# returns:
# - features: 58
# - records: 6

# function to determine the last state of the asset
def UltimoEstado(tbl, glob):
    sql = "ParentGlobalID='{" + glob + "}'"  # my query based in global ID
    top_filter = {"topCount": 1, "orderByFields": "FechaCambio desc"}  # filter definition, take top 1 when data is ordered by date (descending)
    ts = tbl.query(where=sql, order_by_fields='FechaCambio DESC')  # apply query to table
    cnt = 0
    estado = "Abierta" # if there is no related record, default to "Open" status for valve
    if len(ts) > 0:
        for row in ts:
            if cnt == 0:
                estado = row.attributes["EstadoValvula"]  # read out the value from the field than contains the state of the asset
            else:
                # only take the first record for the most recent status
                break
            cnt += 1
    return estado 
    

# similar function to read out the last update date
def UltimaGestion(tbl, glob):
    sql = "ParentGlobalID='{" + glob + "}'"
    top_filter = {"topCount": 1, "orderByFields": "FechaCambio desc"}
    ts = tbl.query(where=sql, order_by_fields='FechaCambio DESC')
    cnt = 0
    ultimagestion = None  # if there is no related record, default date to None
    if len(ts) > 0:
        for row in ts:
            if cnt == 0:
                ultimagestion = row.attributes["FechaCambio"]  # read out the value from the field than contains the date of the update
            else:
                # only take the first record for the most recent date
                break
            cnt += 1
    return ultimagestion   
    

# define a query to get all the features
fs = flayer.query(where="1=1")


# Loop to run through all the valves
actualizar_features = []
for feat in fs:
    # read the globalID 
    glob = feat.attributes["GlobalID"] 
    # call the function llamar to get the last update from the related table (state and date)
    estado = UltimoEstado(tbl, glob)
    ultimagestion = UltimaGestion(tbl, glob)
    # make a deep copy of the current feature
    nuevo_feature = deepcopy(feat)

    # update the values in the deepcopy
    nuevo_feature.attributes['EstadoActual'] = estado
    nuevo_feature.attributes['UltimaGestion'] = ultimagestion
    # add the updated feature to a list
    actualizar_features.append(nuevo_feature)

# update the featureset
flayer.edit_features(updates=actualizar_features)

 

Make sure when you create a new notebook to select the "Standard" option to avoid unnecessary consumption of credits.

0 Kudos
DanielShaffer2
New Contributor III

Thanks, Xander!

I can see now where I misread your response.

Sorry for the late reply, but I've been cranking on another project. I'll have a look at the recommended path and see how that fits with the code you sent.

Much appreciated!

Damiel