I'm trying to write an Arcade data expression to calculate summary statistics for a line layer of pipes, grouped by a polygon layer of sewersheds, but I'm struggling. Goal output would be the following table:
| Sewershed | Length of all pipe features |
| A | 10,000 |
| B | 9,000 |
| C | 11,000 |
Here's the code that I've got so far, but I'm struggling to figure out how to get the sum of all pipe lengths, per intersecting feature (sewershed).
// Get sewersheds.
var sdata = FeatureSetByPortalItem(Portal('portalURL'), 'sewershedLayerID', 13);
// Get pipes.
var pdata = FeatureSetByPortalItem(Portal('portalURL'), 'pipeLayerID', 11);
// List all sewersheds
var sheds = Distinct(sdata, "sewershed"); // List of sewersheds.
// This line successfully returns what I want by the pipe type, but there's no intersection happening here. Instead of pipe type, I want the intersecting sewershed as the groupby category, and that's why I'm attempting this data expression instead of just bringing the layer into the dashboard.
return GroupBy(pdata, ['type'], {name: 'Total Pipe Length', expression: 'Shape__Length', statistic: 'sum'});
// Iterate each pipe and get the sewershed, but this isn't working and seems really inefficient anyway given that I have many thousands of pipes.
for (pipe in pdata) {
var y = First(Intersects($feature, sdata));
if (y == null) return;
}
I'm hoping to extrapolate the solution here to additional datasets. For example, I'll need to create the number of properties served in each sewershed, and similar for other datasets.
