Data Expression with GroupBy and Intersect

[erica_poisson]

Hi - 

I have the following data expression in my dashboard and it is working perfectly, however my users want to be able to filter this chart by "watershed", which is not currently possible because this is not a field in my data or data expression. I have a separate hosted feature layer for watersheds that I will use to determine this. 

I've used "Intersects" to calculate values in Field Maps & to show additional information in pop-ups, but I am not sure where exactly I should be fitting it into this expression to get what I want, which is a table that has a count of inspections, with their project ID and watershed. 

Please see below.

This is my existing data expression that is working well:

// Create a FeatureSet for stable and unstable inspections. 
// Group the features by the CGP file number 

// Create filtered feature set for stable inspection points
var filterStablePts = Filter(FeatureSetByPortalItem(Portal('https://mass-eoeea.maps.arcgis.com'), '12345x', 1, ['Point_Status', 'SV_CGP_File_Number'], true), "Point_Status = 'Stable'");

var StablePts = GroupBy(filterStablePts,
  ["SV_CGP_File_Number"],
  [
    {name: "stable", expression: "Point_Status", statistic: "count"},
  ]
);

// Create filtered feature set for unstable inspection points
var filterUnstablePts = Filter(FeatureSetByPortalItem(Portal('https://mass-eoeea.maps.arcgis.com'), '12345x', 1, ['Point_Status', 'SV_CGP_File_Number'], true), "Point_Status = 'Unstable'");

var UnstablePts = GroupBy(filterUnstablePts,
  ["SV_CGP_File_Number"],
  [
    {name: "unstable", expression: "Point_Status", statistic: "count"},
  ]
);

// Create filtered feature set for general observation points
var filterObsPts = Filter(FeatureSetByPortalItem(Portal('https://mass-eoeea.maps.arcgis.com'), '12345x', 1, ['Point_Status', 'SV_CGP_File_Number'], true), "Point_Status = 'General Obs.'");

var ObsPts = GroupBy(filterObsPts,
  ["SV_CGP_File_Number"],
  [
    {name: "observation", expression: "Point_Status", statistic: "count"},
  ]
);

// Create filtered feature set for stable revisit (table) records
var filterStableRevis = Filter(FeatureSetByPortalItem(Portal('https://mass-eoeea.maps.arcgis.com'), '12345x', 3, ['R_Point_Status', 'R_CGP_File_Number'], true), "R_Point_Status = 'Stable'");

var StableRevis = GroupBy(filterStableRevis,
  ["R_CGP_File_Number"],
  [
    {name: "stablerevis", expression: "R_Point_Status", statistic: "count"},
  ]
);

// Create filtered feature set for unstable revisit (table) records
var filterUnstableRevis = Filter(FeatureSetByPortalItem(Portal('https://mass-eoeea.maps.arcgis.com'), '12345x', 3, ['R_Point_Status', 'R_CGP_File_Number'], true), "R_Point_Status = 'Unstable'");

var UnstableRevis = GroupBy(filterUnstableRevis,
  ["R_CGP_File_Number"],
  [
    {name: "unstablerevis", expression: "R_Point_Status", statistic: "count"},
  ]
);

// Create an empty dictionary to hold the data
var combinedDict = {
  fields: [
    {name: "File_Number", type: "esriFieldTypeString"},
    {name: "Pt_Status", type: "esriFieldTypeString"},
    {name: "Num_Inspections", type: "esriFieldTypeInteger"},
  ],
  geometryType: "",
  features: [],
};

// Fill the new dictionary with data
var i = 0;

// Loop through features and parse them into appropriate fields within dictionary
// Stable point inspections
for (var m in StablePts) {
  combinedDict.features[i] = {
    attributes: {
      File_Number: m["SV_CGP_File_Number"],
      Pt_Status: "Stable",
      Num_Inspections: m["stable"],
    },
  };
  i++;
}

// Unstable point inspections
for (var n in UnstablePts) {
  combinedDict.features[i] = {
    attributes: {
      File_Number: n["SV_CGP_File_Number"],
      Pt_Status: "Unstable",
      Num_Inspections: n["unstable"],
    },
  };
  i++;
}

// General observation points
for (var q in ObsPts) {
  combinedDict.features[i] = {
    attributes: {
      File_Number: q["SV_CGP_File_Number"],
      Pt_Status: "General Observation",
      Num_Inspections: q["observation"],
    },
  };
  i++;
}

// Stable revisit records (table)
for (var o in StableRevis) {
  combinedDict.features[i] = {
    attributes: {
      File_Number: o["R_CGP_File_Number"],
      Pt_Status: "Stable Revisit",
      Num_Inspections: o["stablerevis"],
    },
  };
  i++;
}

// Unstable revisit records (table)
for (var p in UnstableRevis) {
  combinedDict.features[i] = {
    attributes: {
      File_Number: p["R_CGP_File_Number"],
      Pt_Status: "Unstable Revisit",
      Num_Inspections: p["unstablerevis"],
    },
  };
  i++;
}

return FeatureSet(Text(combinedDict));

This is where I am with adding in the Intersects piece - I've tried a few different things in different places within here, but I can't figure out where this should go/how to incorporate it. We have a separate Watersheds hFL which I am attempting to intersect against. See lines 67, 68, 81 below.

// Create a FeatureSet for stable and unstable inspections. 
// Group the features by the CGP file number 

// Create filtered feature set for stable inspection points
var filterStablePts = Filter(FeatureSetByPortalItem(Portal('https://mass-eoeea.maps.arcgis.com'), '12345x', 1, ['Point_Status', 'SV_CGP_File_Number'], true), "Point_Status = 'Stable'");

var StablePts = GroupBy(filterStablePts,
  ["SV_CGP_File_Number"],
  [
    {name: "stable", expression: "Point_Status", statistic: "count"},
  ],
);

// Create filtered feature set for unstable inspection points
var filterUnstablePts = Filter(FeatureSetByPortalItem(Portal('https://mass-eoeea.maps.arcgis.com'), '12345x', 1, ['Point_Status', 'SV_CGP_File_Number'], true), "Point_Status = 'Unstable'");

var UnstablePts = GroupBy(filterUnstablePts,
  ["SV_CGP_File_Number"],
  [
    {name: "unstable", expression: "Point_Status", statistic: "count"},
  ]
);

// Create filtered feature set for general observation points
var filterObsPts = Filter(FeatureSetByPortalItem(Portal('https://mass-eoeea.maps.arcgis.com'), '12345x', 1, ['Point_Status', 'SV_CGP_File_Number'], true), "Point_Status = 'General Obs.'");

var ObsPts = GroupBy(filterObsPts,
  ["SV_CGP_File_Number"],
  [
    {name: "observation", expression: "Point_Status", statistic: "count"},
  ]
);

// Create filtered feature set for stable revisit (table) records
var filterStableRevis = Filter(FeatureSetByPortalItem(Portal('https://mass-eoeea.maps.arcgis.com'), '12345x', 3, ['R_Point_Status', 'R_CGP_File_Number'], true), "R_Point_Status = 'Stable'");

var StableRevis = GroupBy(filterStableRevis,
  ["R_CGP_File_Number"],
  [
    {name: "stablerevis", expression: "R_Point_Status", statistic: "count"},
  ]
);

// Create filtered feature set for unstable revisit (table) records
var filterUnstableRevis = Filter(FeatureSetByPortalItem(Portal('https://mass-eoeea.maps.arcgis.com'), '12345x', 3, ['R_Point_Status', 'R_CGP_File_Number'], true), "R_Point_Status = 'Unstable'");

var UnstableRevis = GroupBy(filterUnstableRevis,
  ["R_CGP_File_Number"],
  [
    {name: "unstablerevis", expression: "R_Point_Status", statistic: "count"},
  ]
);

// Create an empty dictionary to hold the data
var combinedDict = {
  fields: [
    {name: "File_Number", type: "esriFieldTypeString"},
    {name: "Pt_Status", type: "esriFieldTypeString"},
    {name: "Num_Inspections", type: "esriFieldTypeInteger"},
    {name: "Watershed", type: "esriFieldTypeString"},
  ],
  geometryType: "",
  features: [],
};

// Feature set for watershed intersection
var watersheds = FeatureSetByPortalItem(Portal('https://mass-eoeea.maps.arcgis.com'), '4a7abb05f6eb42d78e31587b05e3ebec', 0, ['Label_Field'], false);
var watershed = First(Intersects(combinedDict,watersheds));

// Fill the new dictionary with data
var i = 0;

// Loop through features and parse them into appropriate fields within dictionary
// Stable point inspections
for (var m in StablePts) {
  combinedDict.features[i] = {
    attributes: {
      File_Number: m["SV_CGP_File_Number"],
      Pt_Status: "Stable",
      Num_Inspections: m["stable"],
      Watershed: m[watershed],
      }],
    },
  };
  i++;
}

// Unstable point inspections
for (var n in UnstablePts) {
  combinedDict.features[i] = {
    attributes: {
      File_Number: n["SV_CGP_File_Number"],
      Pt_Status: "Unstable",
      Num_Inspections: n["unstable"],
    },
  };
  i++;
}

// General observation points
for (var q in ObsPts) {
  combinedDict.features[i] = {
    attributes: {
      File_Number: q["SV_CGP_File_Number"],
      Pt_Status: "General Observation",
      Num_Inspections: q["observation"],
    },
  };
  i++;
}

// Stable revisit records (table)
for (var o in StableRevis) {
  combinedDict.features[i] = {
    attributes: {
      File_Number: o["R_CGP_File_Number"],
      Pt_Status: "Stable Revisit",
      Num_Inspections: o["stablerevis"],
    },
  };
  i++;
}

// Unstable revisit records (table)
for (var p in UnstableRevis) {
  combinedDict.features[i] = {
    attributes: {
      File_Number: p["R_CGP_File_Number"],
      Pt_Status: "Unstable Revisit",
      Num_Inspections: p["unstablerevis"],
    },
  };
  i++;
}

return FeatureSet(Text(combinedDict));

Any help would be appreciated. I've never tried this in a data expression with GroupBy statements before and feel like I am missing something fundamental here. 

Thank you,

[JohannesLindner]

The Intersects() function takes a Featureset and a Feature/Geometry and returns a Featureset of Features that intersect the input Feature.

• You're trying to intersect 2 Featuresets
• One of those (combinedDict) is actually a Dictionary, and even if it was a FS, it would have no features yet

You have to do the intersect before the GroupBy, because grouped Featuresets don't have geometries.

This is untested:

// define your input layers and their output status
var p = Portal('https://mass-eoeea.maps.arcgis.com')
var layers = [
    {
        id: '12345x',
        subid: 1,
        query: "Point_Status = 'Stable'",
        file_number: 'SV_CGP_File_Number',
        out_status: 'Stable',
    },
    {
        id: '12345x',
        subid: 1,
        query: "Point_Status = 'Unstable'",
        file_number: 'SV_CGP_File_Number',
        out_status: 'Unstable',
    },
    {
        id: '12345x',
        subid: 1,
        query: "Point_Status = 'General Obs.'",
        file_number: 'SV_CGP_File_Number',
        out_status: 'General Observation',
    },
    {
        id: '12345x',
        subid: 3,
        query: "R_Point_Status = 'Stable'",
        file_number: 'R_CGP_File_Number',
        out_status: 'Stable Revisit',
    },
    {
        id: '12345x',
        subid: 3,
        query: "R_Point_Status = 'Unstable'",
        file_number: 'R_CGP_File_Number',
        out_status: 'Unstable Revisit',
    },
]
// load the watersheds
var watersheds = FeatureSetByPortalItem(p, '4a7abb05f6eb42d78e31587b05e3ebec', 0, ['Label_Field'], false)

// create an empty feature array
var features = []

// iterate over the input layers
for(var i in layers) {
    var layer = layers[i]
    // load and filter
    var fs = FeaturesetByPortalItem(p, layer.id, layer.subid, [layer.file_number], true)
    var filtered_fs = Filter(fs, layer.query)
    // iterate over the features
    for(var f in filtered_fs) {
        // get the intersecting watershed
        var i_ws = First(Intersects(f, watersheds))
        var i_ws_name = IIf(i_ws == null, "No watershed", i_ws.Label_Field)
        // add the feature to the array
        var new_f = {attributes: {
            File_Number: f[layer.file_number],
            Pt_Status: layer.out_status,
            Watershed: i_ws_name,
            }}
        Push(features, new_f)
    }
}

// create a Featureset with all these features
var combined_dict = {
  fields: [
    {name: "File_Number", type: "esriFieldTypeString"},
    {name: "Pt_Status", type: "esriFieldTypeString"},
    {name: "Num_Inspections", type: "esriFieldTypeInteger"},
    {name: "Watershed", type: "esriFieldTypeString"},
  ],
  geometryType: "",
  features: features,
}
var combined_fs = Featureset(Text(combined_dict))

// GroupBy to get the counts
var grouped = GroupBy(
    combined_fs,
    ["File_Number", "Pt_Status", "Watershed"],
    [{name: "Num_Inspections", expression: "1", statistic: "COUNT"}]
)
return grouped

[erica_poisson]

Hi @JohannesLindner  -

Thank you for the code! It does work, but it takes a LONG time for the featureset to populate (today was actually the first day I waited long enough for it to work - the other day when I tested this I gave up after a minute of a spinning wheel).

I am wondering if there are ways to speed this up/make it more efficient? My feature class (the points classified as stable/unstable) has 2,300+ features and continues to grow at a slow pace.

Do you have any suggestions?

Thank you!

[JohannesLindner]

Hey Erica,

Josh raised a very interesting point in this blog.

Basically, Featuresets aren't loaded into your local memory, so the Filter() and Intersects() are executed on the server (your portal/AGOL). This leads to many requests to the server, potentially slowing down the expression massively.

Try the following:

• Copy Josh's Memorize function to the top of your script.
• in lines 41 & 50, use the Memorize function like so:

var watersheds = Memorize(FeaturesetByPortalItem(...))

var fs = Memorize(FeaturesetByPortalItem(...))

[erica_poisson]

Hi Johannes,

Thank you for this suggestion. Unfortunately, I am getting an unknown error when I test my expression. Here is what I've done:

function Memorize(fs) {
    var temp_dict = {
        fields: Schema(fs)['fields'],
        geometryType: '',
        features: []
    }

    for (var f in fs) {
        var attrs = {}

        for (var attr in f) {
            attrs[attr] = Iif(TypeOf(f[attr]) == 'Date', Number(f[attr]), f[attr])
        }

        Push(
            temp_dict['features'],
            {attributes: attrs}
        )
    }

    return FeatureSet(Text(temp_dict))
}

// skip some lines to make this concise... 

// load the watersheds
var watersheds = Memorize(FeatureSetByPortalItem(p, '4a7abb05f6eb42d78e31587b05e3ebec', 0, ['NAME'], false))

// create an empty feature array
var features = []

// iterate over the input layers
for(var i in layers) {
    var layer = layers[i]
    // load and filter
    var fs = Memorize(FeaturesetByPortalItem(p, layer.id, layer.subid, [layer.file_number], true))

[JohannesLindner · Accepted solution]

Ah, I didn't realize that Josh ignored the geometries... Try this:

function Memorize(fs) {
    var temp_dict = {
        fields: Schema(fs)['fields'],
        geometryType: Schema(fs).geometryType,
        features: []
    }

    for (var f in fs) {
        var attrs = {}

        for (var attr in f) {
            attrs[attr] = Iif(TypeOf(f[attr]) == 'Date', Number(f[attr]), f[attr])
        }

        Push(
            temp_dict['features'],
            {attributes: attrs, geometry: Geometry(f)}
        )
    }

    return FeatureSet(Text(temp_dict))
}

// ...


// change the false to true
var watersheds = Memorize(FeatureSetByPortalItem(p, '4a7abb05f6eb42d78e31587b05e3ebec', 0, ['Label_Field'], true))

[erica_poisson]

 Hi @JohannesLindner  -

Thank you! This speeds up my dashboard significantly! I go from about 2 minutes to load to maybe 10-15 seconds. 

[JohannesLindner]

Hey Erica,

that's great to hear!

Please add you support to this Idea, to hopefully have this functionality in native Arcade some day.