Using FeatureSetBy functions in Arcade to drill-down to other layers and tables
Today a great blog was posted by Paul Barker on the new FeatureSet functions: What’s new with Arcade: Taking a stroll through FeatureSets (Part 1) and also Kelly Gerrow shared a great document on this topic: https://community.esri.com/community/gis/web-gis/arcgisonline/blog/2018/12/10/overlapping-features-i... .
I didn't wanted to be left behind, so I thought I would share some examples using hydrants, the maintenance history and sub sectors and hydraulic sectors to show what you can achieve with the new Arcade functions released in ArcGIS Online on December 5th
Consider we have a web map with several layers and a table:
- Hidrantes is a point layer with hydrants
- Sub Sector is a polygon layer with a sub division of the area of interest
- Sector Hidraulico is a polygon layer merging sub sectors into hydraulic sectors
- Hist Mantenimiento is a table with the historic maintenance records for each hydrant
First of all, let’s apply some Arcade to visualize the hydrants based on their age. This was already possible in the previous release, so no new things here yet. We have the installation date stored in the field FECHAINSTALACION and we can use the DateDiff and Now functions to determine the age in years, which happens on the first two lines. After this we classify the value into significant ranges:
var fecha_inst = $feature.FECHAINSTALACION;
var edad = DateDiff(Now(), fecha_inst, "years");
var clase_edad = "";
if (edad > 50) {
clase_edad = "Mayor a 50 años";
} else if (edad > 40) {
clase_edad = "Entre 40 y 50 años";
} else if (edad > 30) {
clase_edad = "Entre 30 y 40 años";
} else if (edad > 20) {
clase_edad = "Entre 20 y 30 años";
} else if (edad > 10) {
clase_edad = "Entre 10 y 20 años";
} else if (edad > 5) {
clase_edad = "Entre 5 y 10 años";
} else {
clase_edad = "Menor a 5 años";
}
return clase_edad;
The result is a text describing the age, for which we can define representative symbols (here using the Firefly symbols):
No fancy symbols for the other two polygon layers to avoid distracting the attention from the hydrants.
We would like to show in the pop-up window of each hydrant the list of all maintenance carried out on the hydrant. Both the maintenance history table and the hydrant layer contain the ID of the hydrant. The table also contains the dates and type of maintenance.
In the configuration of the pop-up of the hydrants we can add an Arcade expression like this one:
var tbl = FeatureSetByName($datastore,"Hist_Mantenimiento");
var codigo = $feature["COD_HIDRANTE"];
var sql = "COD_HIDRANTE = '" + codigo + "'";
Console(sql);
var mantenimientos = Filter(tbl, sql);
var cnt = Count(mantenimientos);
var historia = "";
if (cnt > 0) {
historia = cnt + " Mantenimiento(s):";
for (var mantenimiento in mantenimientos) {
var txt_fecha = Text(mantenimiento.Fecha_Mantenimiento, ' - (Y/MM/DD) ');
var txt_man = txt_fecha + mantenimiento.Mantenimiento;
historia += TextFormatting.NewLine + txt_man;
}
} else {
historia = "No hay mantenimientos";
}
return historia;
First of all, we access the table with the maintenance history and assign it to a variable tbl. Then we read the id of the current hydrant for which we want to create the pop-up. We define a SQL expression using the hydrant ID which is then used to filter the history table. The result is stored in a variable called mantenimientos. We can do a Count to obtain the number of maintenance carried out on the hydrant. If the count (cnt) is larger than 0, we start reading out the details and construct a list of maintenance including the date and type of maintenance.
The result will appear like this after we configure a simple custom html to show the attributes and result of the Arcade expression in the pop-up:
Now let’s continue with doing something with these new Arcade functions to get some data hydrants at the sub sector level. Since the number of maintenance obviously depend on the age of the hydrant, it would be nice to get some details on this at the sub sector level.
In the pop-up configuration of the sub sectors we can add the following Arcade expression:
var fs = FeatureSetByName($datastore,"Hidrantes");
var hidrantes = Intersects(fs, $feature);
var cnt = Count(hidrantes);
var edad_tot = 0;
for (var hidrante in hidrantes) {
var edad = DateDiff(Now(), hidrante.FECHAINSTALACION, "years");
edad_tot += edad;
}
var resultado = "";
if (cnt > 0) {
var edad_media = edad_tot / cnt;
resultado = "Hay " + cnt + " hidrantes en el sub sector";
resultado += TextFormatting.NewLine + "La edad media de los hidrantes es de " + Round(edad_media, 2) + " años";
} else {
resultado = "No hay hidrantes en el sub sector";
}
return resultado;
This expression will:
- Access the Hydrants layer
- Do an intersect with the current sub sector feature
- Count the number of hydrants in the sub sector
- Calculate the age for each hydrant and some all ages
- Calculate the mean age of the hydrants and format the text with this information
The result will appear like this:
In this case there are 93 hydrants in the sub sector and the mean age is 20.64 years.
This is fun, but can we also show the names of the sub sectors that are contained by a hydraulic sector using these new Arcade functions? The answer is yes. Let’s add the following expression to the hydraulic sectors:
var fs = FeatureSetByName($datastore,"Sub Sector");
var subsectores = Intersects(fs, $feature);
var cnt = Count(subsectores);
var resultado = "Listado de los " + cnt + " Subsectores:";
for (var subsector in subsectores) {
resultado += TextFormatting.NewLine + subsector.IDSAP;
}
return resultado;
Just access the sub sectors layer and Intersect with the current hydraulic sector and voila…, rights? No, not so right. When you use an Intersect between two polygon layers, it will probably return too many features due to the fact that when two polygons have a matching boundary they will probably be included in the result, leading to more sub sectors than we want.
A way to avoid this is to use a small negative buffer on the hydraulic sector and do the Intersect with that buffered geometry like this:
var fs = FeatureSetByName($datastore,"Sub Sector");
var feature_buf = Buffer($feature, -100, 'meters');
var subsectores = Intersects(fs, feature_buf);
var cnt = Count(subsectores);
var resultado = "Listado de los " + cnt + " Subsectores:";
for (var subsector in subsectores) {
resultado += TextFormatting.NewLine + subsector.IDSAP;
}
return resultado;
Notice the Buffer with a negative distance that is created on line 2 and used on line 3 to get the rights polygons.
The resulting pop-up will show like this:
To be honest, I don’t like the unsorted list of sub sectors, so let’s order this list using OrderBy:
var fs = FeatureSetByName($datastore,"Sub Sector");
var feature_buf = Buffer($feature, -100, 'meters');
var subsectores = OrderBy(Intersects(fs, feature_buf), 'IDSAP ASC');
var cnt = Count(subsectores);
var resultado = "Listado de los " + cnt + " Subsectores:";
for (var subsector in subsectores) {
resultado += TextFormatting.NewLine + subsector.IDSAP;
}
return resultado;
Which will result in a sorted list of sub sectors:
I hope that these examples will help you to understand what is possible using Arcade with the latest update in ArcGIS Online, but before you jump in and start creating fabulous Arcade expressions I want to quote Uncle Ben (Spider-Man) for a moment:
With great power comes great responsibility...
As Paul Barker mentions in his blog, be careful and always validate that the performance of your expressions yield in an acceptable response time for your end-user.
Hi Naomi Begg ,
Could you create a new question next time? This keep the document a bit cleaner and if others have the same question, they will be able to find an answer more easily.
So, what will probably be happening here is the way you added the service to the map. In case you use the map service and not specify the index of the layer, Arcade functionality will be limited: https://maps.taupodc.govt.nz/server/rest/services/property/Reserve/MapServer
Configuring Arcade in the pop-up does not provide access to the $map and is restricted to the information in the current feature only:
If you add the index of the layer, it will not create a grouplayer and add the layer normally and Arcade functionality should be normal: https://maps.taupodc.govt.nz/server/rest/services/property/Reserve/MapServer/0
Access to $map:
Hi Nathan Fazer ,
Let me try and answer the questions you posted:
What is mean by "the exact name of the relationship"? Is that the name of the related table/layer in the feature service? Or the name of that related table/layer in the map itself?
If the layer where you are configuring the pop-up has a relationship to another layer or table, you can navigate through the interface and get to the related features. Click on ">" next to the "$feature":
Then you will scroll down and below the attributes of the feature you will find "Related records". Click on the ">" next to that:
This will list all the related tables and feature layers:
Click on the blue link to insert the correct code into the expression builder:
I have a point layer of cases and a related table of household contacts. I can't even get this to work:
FeatureSetByRelationshipName($feature, 'householdcontacts', ['housecontactname', 'housecontactrelationship'], false);
Use the navigation as explained above to see of the name of the relationship is correct.
This is the popup of my cases layer. So $feature is getting the point feature, 'householdcontacts' is the related table, and the two fields in brackets are the fields I want to return. But the Test shows Null and the popup shows nothing.
The function "FeatureSetByRelationshipName" will return a featureset. It may show when you test the expression in the expression builder, but it will not be shown in the pop-up. You will have to extract the relevant information and construct the string that you want to show in the pop-up.
Hi @XanderBakker ,
Thanks for a great example and workflow. I am basically trying to replicate this workflow, but I get stuck when trying to summarize the info in my subsectors in MapViewer Beta in AGOL. When using MapViewer Classic in Portal it works.
I wanted to use MapViewer Beta for this, and since our Portal is currently on 10.8 I shared the data through a distributed collaboration to our AGOL account. I get the previous expressions for summarizing related table records for individual assets to work.
In my Arcade window in MapViewer beta it all looks great and I get a correct looking result, in my pop-ups however, I only get a result when there is no objects in the sub-sector.
This is what it looks like when I write my expression. - It recognises dates and the number of objects.
I am quite novice in terms of using Arcade, but my guess here is that it has something to do with me not accessing the data correctly, however it seems strange to me that I get a result in the Arcade window?
Here is my code: (basically just copy/paste frome yours)
var fs = FeatureSetByName($map,"Belysningsstolpar_Gävle");
var belysning = Intersects(fs, $feature);
var cnt = Count(belysning);
Console(cnt)
var age_tot = 0;
for (var lyse in belysning) {
var age = DateDiff(Now(), lyse.Installationsdatum, "years");
age_tot += age;
Console(age_tot)
Console(lyse.Installationsdatum)
}
var resultat = "";
if (cnt > 0) {
var age_media = age_tot / cnt;
resultat = "Det finns " + cnt + " Belysningsstolpar i subsektorn";
resultat += TextFormatting.NewLine + "Medelåldern är " + Round(age_media, 2) + " år";
} else {
resultat = "Inga belysningsstolpar i subsektorn";
}
return resultat;
Could it be accessing the data or is there something different when you use MapViewer Beta?
Best Regards
Rickard
Hi @RickardNasstrom ,
It is strange that it would not work in Map Viewer Beta, since it should. I just did a test with the example that I constructed for this post and it works as expected in the Map Viewer Beta:
The first thing where I would look at is renaming the layer and not using special characters provides a valid result. I don't see anything else that could explain this behaviour.
Thanks for testing and your quick reply. I just tried with the same feature layer in AGOL Classic MapViewer where it works.
This is how my pop-up looks in Beta
And here is in classic:
I'll try to get rid of my special charachters, see if it helps.
Thanks for a great guide!
This post has been super helpful. I've read through it and all the comments. I have a very similar use case but need a slightly different output.
I have a polygon layer of buildings and a table of category codes that are included in a relationship class. The field that links them is an ID field which is called "UID" in the buildings layer and "UID_Child" in the category code table. I am trying to edit my output so that in the case of multiple occurrences of the same category code within one building, that code would be listed once along with a count of how many times it occurs, rather than listing duplicate codes. Here's my current code:
//identify related table data
var related_table = FeatureSetById($datastore, "11");
//filter relate table for records matching unique ID
var filter_query = "UID_Child = '" + $feature["UID"] + "'";
Console('filtering related data records')
var related_data_filtered = Filter(related_table, filter_query);
//count records matching UID in related table
var related_data_filtered_count = Count(related_data_filtered);
var output = []
Console('records filtered: '+ related_data_filtered_count)
//push catcode value from related data table to output
if (related_data_filtered_count > 0) {
for (var related_data_row in related_data_filtered) {
Push(output, related_data_row.CATCODE)
}
} else {
return "No Related Records."
}
Console(output)And here's what the popup looks like:
So for this feature for example, I would want it to read "610112 (10 or however many times it occurs)" I think I need to use a for loop to count how many times each catcode occurs. I've tried a ton of different variations with no success. I'll put one sample below. Do you have any ideas?
var filter_query = "UID_Child = '" + $feature["UID"] + "'";
var related_data_filtered = Filter($datastore["11"], filter_query);
var related_data_filtered_count = Count(related_data_filtered);
Console("Filtered data count: " + related_data_filtered_count);
var catcodeCounts = {}; // Object to store counts of each unique CATCODE
if (related_data_filtered_count > 0) {
Console("Processing related records...");
// Loop through the filtered related features
for (var i = 0; i < related_data_filtered_count; i++) {
var feature = related_data_filtered[i];
var catcode = feature["CATCODE"]; // Accessing the CATCODE attribute directly
// Increment count for the current CATCODE or initialize count to 1
if (!catcodeCounts[catcode]) {
catcodeCounts[catcode] = 1;
} else {
catcodeCounts[catcode] += 1;
}
Console("Processed CATCODE: " + catcode);
}
// Construct output with counts of each unique CATCODE
var output = "";
for (var catcode in catcodeCounts) {
output += "CATCODE " + catcode + ": " + catcodeCounts[catcode] + "\n";
}
Console("Output: \n" + output);
return output;
}
Console("No related records found.");
return "No Related Records.";
