Data Expression with Groupby & Intersection

[JerrySneary]

Hi, @XanderBakker or anyone else

I originally created a data expression that intersected a point layer with a polygon layer and returned a count of points per polygon and a sum of a field.

// Create FeatureSet for polygons
var boundaries = FeatureSetByPortalItem(
    Portal('https://arcgis.com/'),
    'a1795cc14f9744d68787f649a7865715',
    12,
    ['Type', 'DISTRICT'],
    true
    );
    
// Create Featureset for points
var nno = FeatureSetByPortalItem(
    Portal('https://arcgis.com/'),
    '8de09d32c583450584fe901ecaed1f6f',
    0,
    ['Survey_Year', 'Expected_Attendance'],
    true
    );


var outputDict = {'fields': [
    { 'name': 'Type', 'type': 'esriFieldTypeString'},
    { 'name': 'DISTRICT', 'type': 'esriFieldTypeString'},
    { 'name': 'NNO_Count', 'type': 'esriFieldTypeInteger'},
    { 'name': 'NNO_Attendance', 'type': 'esriFieldTypeInteger'}], 
    'geometryType': 'esriGeometryPolygon',
    'features': []
    };

var index = 0;

for (var boundary in boundaries) {
   var nnoint = Intersects(boundary,nno)
    var nnocount = 0
    var nnosum = 0
    for(var nnoi in nnoint) {
        nnocount ++
        nnosum += nnoi.Expected_Attendance
    }
   outputDict.features[index++] = { 
            'attributes': {
                'Type': boundary['Type'],
                'DISTRICT': boundary['DISTRICT'],
                'NNO_Count': nnocount,
                'NNO_Attendance': nnosum
        },
        'geometry': Geometry(boundary)
}}

return FeatureSet(Text(outputDict));

Now I've been asked to split each of the counts and sums for each district by Survey_Year. I lost count of how many iterations I did on the above code but could never figure it out. I am now trying to use this code to accomplish my goal but I can't seem to figure it out, if I'm even going in the right direction.

// Create FeatureSet for polygons
var boundaries = FeatureSetByPortalItem(
  Portal('https://arcgis.com/'),
  'a1795cc14f9744d68787f649a7865715',
  12,
  ['Type', 'DISTRICT'],
  true
);

// Create Featureset for points
var nno = FeatureSetByPortalItem(
  Portal('https://arcgis.com/'),
  '8de09d32c583450584fe901ecaed1f6f',
  0,
  ['Survey_Year', 'Expected_Attendance'],
  true
);

// Perform spatial join and aggregation
var intersectedFeatures = Intersects(nno, boundaries);

var aggregatedFeatures = GroupBy(
  intersectedFeatures,
  ['Survey_Year', 'DISTRICT'],
  [{ name: 'NNO_Count', expression: 'intersectedFeatures', statistic: 'COUNT' },
   { name: 'Expected_Attendance', expression: 'Expected_Attendance', statistic: 'SUM'}]
);

// Return aggregated features as a FeatureSet
return aggregatedFeatures;

Any help would be appreciated.

Jerry

[JerrySneary · Accepted solution]

I figured it out. Just in case it could help someone else I'll paste the code below.

// Create FeatureSet for polygons
var boundaries = FeatureSetByPortalItem(
    Portal('https://arcgis.com/'),
    'a1795cc14f9744d68787f649a7865715',
    12,
    ['Type', 'DISTRICT'],
    true
);

// Create Featureset for points
var nno = FeatureSetByPortalItem(
    Portal('https://arcgis.com/'),
    '8de09d32c583450584fe901ecaed1f6f',
    0,
    ['Survey_Year', 'Expected_Attendance'],
    true
);

var outputDict = {
    'fields': [
        { 'name': 'Survey_Year', 'type': 'esriFieldTypeString'},
        { 'name': 'Type', 'type': 'esriFieldTypeString'},
        { 'name': 'DISTRICT', 'type': 'esriFieldTypeString'},
        { 'name': 'NNO_Count', 'type': 'esriFieldTypeInteger'},
        { 'name': 'NNO_Attendance', 'type': 'esriFieldTypeInteger'}
    ],
    'geometryType': 'esriGeometryPolygon',
    'features': []
};

var index = 0;

for (var boundary in boundaries) {

    var district = boundary['DISTRICT'];
    var nnoint = Intersects(boundary, nno);

    // Create an object to store counts and sums by Survey_Year
    var surveyYearData = Dictionary();

    for (var nnoi in nnoint) {
        var surveyYear = nnoi.Survey_Year;
        var expectedAttendance = nnoi.Expected_Attendance;

        if (!HasKey(surveyYearData, surveyYear)) {
            var dictVars = Dictionary("count", 0, "sum", 0);
            surveyYearData[surveyYear] = dictVars;

        }

        surveyYearData[surveyYear].count++;
        surveyYearData[surveyYear].sum += expectedAttendance;
    }    

    // Add aggregated data to the output feature set
    for (var currYear in surveyYearData) {
        var currCount = surveyYearData[currYear].count;
        var currSum = surveyYearData[currYear].sum;

        outputDict.features[index++] = {
            'attributes': {
                'Survey_Year': currYear,
                'Type': boundary['Type'],
                'DISTRICT': district,
                'NNO_Count': currCount,
                'NNO_Attendance': currSum
            },
            'geometry': Geometry(boundary)
        };
    }
    
}
//return null;
return FeatureSet(Text(outputDict));