Data Expressions - Intersects with a Count and Sum

[JerrySneary]

Hi,

@XanderBakker 

I've written the Data Expression below and it works but it is very slow. Too slow to be used in the dashboard I've created. I'm creating this because no matter how many times I inform people that my maps and dashboards are interactive they always seem to forget.

Below are two images. The one on the left is two indicator widgets and a list widget, and if you select an item in the list widget it will filter the two indicators along with a map. I've been asked if I could just add the Registrations and Participants next to each District. The image on the right is a list widget accomplishing what was asked but it can take anywhere from 30 secs to over a minute to load.

I'm still new to writing code and I've read that depending on how you write Arcade it can impact its performance.

var boundaries = FeatureSetByPortalItem(
    Portal('https://arcgis.com/'),
    'a1795cc14f9744d68787f649a7865715',
    1);
    
var nno = FeatureSetByPortalItem(
    Portal('https://arcgis.com/'),
    '6d13be8f482e434ea129f751c00385b0',
    0);
    
var outputDict = {'fields': [
    { 'name': 'MEMBER', 'type': 'esriFieldTypeString'},
    { 'name': 'Type', 'type': 'esriFieldTypeString'},
    { 'name': 'DISTRICT', 'type': 'esriFieldTypeString'},
    { 'name': 'District_Sort', 'type': 'esriFieldTypeString'},
    { 'name': 'NNO_Count', 'type': 'esriFieldTypeInteger'},
    { 'name': 'NNO_Attendance', 'type': 'esriFieldTypeInteger'}], 
    'geometryType': '', 'features': []};

var index = 0;

for (var boundary in boundaries) {
   var nnoint = Intersects(boundary,nno)
   var nnocount = Count(nnoint)
   var nnosum = Sum(nnoint, 'Expected_Attendance')
   outputDict.features[index++] = { 
            'attributes': {'MEMBER': boundary['MEMBER'],
            'Type': boundary['Type'],
            'DISTRICT': boundary['DISTRICT'],
            'District_Sort': boundary['District_Sort'],
            'NNO_Count': nnocount,
            'NNO_Attendance': nnosum}
            
}}

return FeatureSet(Text(outputDict));

[JerrySneary]

This was my original iteration.

var boundaries = FeatureSetByPortalItem(
    Portal('https://arcgis.com/'),
    'a1795cc14f9744d68787f649a7865715',
    1);
    
var nno = FeatureSetByPortalItem(
    Portal('https://arcgis.com/'),
    '6d13be8f482e434ea129f751c00385b0',
    0);
    
var outputDict = {'fields': [
    { 'name': 'MEMBER', 'type': 'esriFieldTypeString'},
    { 'name': 'Type', 'type': 'esriFieldTypeString'},
    { 'name': 'DISTRICT', 'type': 'esriFieldTypeString'},
    { 'name': 'District_Sort', 'type': 'esriFieldTypeString'},
    { 'name': 'NNO_Count', 'type': 'esriFieldTypeInteger'},
    { 'name': 'NNO_Attendance', 'type': 'esriFieldTypeInteger'}], 
    'geometryType': '', 'features': []};


var nnocount = 0;
var nnosum = 0;
var index = 0;

for (var boundary in boundaries) {
   var nnoint = Intersects(boundary,nno)
   nnocount = Count(nnoint)
   nnosum = Sum(nnoint, 'Expected_Attendance')
   outputDict.features[index++] = { 
            'attributes': {
            'MEMBER': boundary.MEMBER,
            'Type': boundary.Type,
            'DISTRICT': boundary.DISTRICT,
            'District_Sort': boundary.District_Sort,
            'NNO_Count': nnocount,
            'NNO_Attendance': nnosum
            }
            
}}

return FeatureSet(Text(outputDict));

[JohannesLindner]

Loading feature sets is slow. It gets much slower if you load all fields and the geometries. You can make it much faster by only loading the fields you need, and only loading the geometry if you need to work with it.

It's not really intuitive, but you don't need to load the geometries for Intersects().  EDIT: Nope, this is wrong

Just using these tricks, I got your script from 33 seconds to 0.6 seconds! EDIT: Yes, but it doesn't return the correct results anymore

var boundaries = FeatureSetByPortalItem(
    Portal('https://arcgis.com/'),
    'a1795cc14f9744d68787f649a7865715',
    1,
    ['MEMBER', 'Type', 'DISTRICT', 'District_Sort'],
    false
    );
    
var nno = FeatureSetByPortalItem(
    Portal('https://arcgis.com/'),
    '6d13be8f482e434ea129f751c00385b0',
    0,
    ['Expected_Attendance'],
    false
    );

// rest is the same

[JerrySneary]

Hi @JohannesLindner ,

Edit: Well, I thought this fixed it. With the geometry turned off the intersect doesn't work. It returns 0 for the Count and nothing for the Sum. As soon as I change it from false to true both the Count and Sum work again, but the script is slow again. 

Thank you so much for your help. This is exactly what I needed. I'm still trying to figure out all the ins and out of coding/Arcade. What's funny is when I first started trying to figure out how to write this data expression that is how I wrote my variables. Below was the first expression I wrote, but I found out that you can't write the expression as you do in pop-ups. I changed it when I found this Esri Webpage with an example I used.

var boundaries = FeatureSetByPortalItem(
    Portal('https://arcgis.com/'),
    'a1795cc14f9744d68787f649a7865715',
    1,
    ['MEMBER', 'Type', 'DISTRICT', 'District_Sort'],
    false
    );
    
var nno = FeatureSetByPortalItem(
    Portal('https://arcgis.com/'),
    '6d13be8f482e434ea129f751c00385b0',
    0,
    ['Expected_Attendance'],
    false
    );

var fsIntersection = Count(Intersects(boundaries,nno));

return fsIntersection

  Again, Thank you so much.

[JohannesLindner]

This expression didn't work (and it also doesn't work in a popup), because you were trying to intersect two FeatureSets.

Intersect() either works with two Features/Geometries (returning a Boolean) or with a FeatureSet and a Feature/Geometry (returning a FeatureSet). What you were trying just isn't implemented in the language.

Glad I could help you. Please mark my answer as solution, so this question gets shown as answered.

[JerrySneary]

Hi @JohannesLindner ,

I did mark it as a solution but then had to take it off as I noticed I wasn't getting any Count or Sum results. I added an edit to my response above, but I'll put it here too. Is the edit below happening to you too?

Edit: Well, I thought this fixed it. With the geometry turned off, the intersect doesn't work. It returns 0 for the Count and nothing for the Sum. As soon as I change it from false to true both the Count and Sum work again, but the script is slow again. 

Kind regards,
Jerry

[JohannesLindner · Accepted solution]

My apologies, I really fudged that up...

I know that limiting what you load can speed scripts up, so that was the first thing I tried. I got a huge speed boost and left it at that without checking the result or diving deeper into your script. And I must have some confusion about loading geometries and Intersects(). Could have sworn you didn't need the geometries, but obviously that's wrong.

At least loading only the needed fields really gives a significant (although not 33 seconds...) speed boost:

loading time in seconds
boundaries, all fields: 0.641
boundaries, spec fields: 0.002
nno, all fields: 0.312
nno, spec fields: 0.003

Anyway, I did a quick profiling of your script. Turns out the loading (esp. with only needed fields) and Intersecting is quite fast. What's dragging the speed down is the Count() and Sum() functions. They seem to be implemented as simple for loops, and they take a long time, especially for large nnoint. In this profile, I measured the time to do both Count and Sum, they took roughly the equal amount of time each.

Load 1: 0.009
Load 2: 0.001
boundary 0
	Intersects: 0
	Count & Sum: 4.3629999999999995
	total: 4.367
boundary 1
	Intersects: 0
	Count & Sum: 2.787
	total: 2.791
boundary 2
	Intersects: 0
	Count & Sum: 0.864
	total: 0.866
boundary 3
	Intersects: 0
	Count & Sum: 1.081
	total: 1.085
boundary 4
	Intersects: 0.001
	Count & Sum: 1.321
	total: 1.325
boundary 5
	Intersects: 0
	Count & Sum: 0.947
	total: 0.949
boundary 6
	Intersects: 0
	Count & Sum: 0.484
	total: 0.486
boundary 7
	Intersects: 0
	Count & Sum: 0.894
	total: 0.895
boundary 8
	Intersects: 0
	Count & Sum: 1.155
	total: 1.157
boundary 9
	Intersects: 0
	Count & Sum: 0.668
	total: 0.67
boundary 10
	Intersects: 0
	Count & Sum: 0.544
	total: 0.547
boundary 11
	Intersects: 0.001
	Count & Sum: 0.509
	total: 0.512
boundary 12
	Intersects: 0
	Count & Sum: 0.594
	total: 0.595
boundary 13
	Intersects: 0
	Count & Sum: 0.469
	total: 0.471
boundary 14
	Intersects: 0
	Count & Sum: 0.512
	total: 0.513
boundary 15
	Intersects: 0
	Count & Sum: 0.538
	total: 0.54
boundary 16
	Intersects: 0
	Count & Sum: 0.788
	total: 0.791
boundary 17
	Intersects: 0.001
	Count & Sum: 0.595
	total: 0.598
boundary 18
	Intersects: 0
	Count & Sum: 0.641
	total: 0.644
boundary 19
	Intersects: 0
	Count & Sum: 0.845
	total: 0.847
boundary 20
	Intersects: 0
	Count & Sum: 0.533
	total: 0.535
boundary 21
	Intersects: 0
	Count & Sum: 0.532
	total: 0.533
boundary 22
	Intersects: 0
	Count & Sum: 0.505
	total: 0.507
boundary 23
	Intersects: 0
	Count & Sum: 0.711
	total: 0.713
boundary 24
	Intersects: 0
	Count & Sum: 0.657
	total: 0.659
boundary 25
	Intersects: 0
	Count & Sum: 0.542
	total: 0.544
boundary 26
	Intersects: 0
	Count & Sum: 0.53
	total: 0.531
boundary 27
	Intersects: 0
	Count & Sum: 0.466
	total: 0.467
boundary 28
	Intersects: 0
	Count & Sum: 0.924
	total: 0.925
boundary 29
	Intersects: 0
	Count & Sum: 0.523
	total: 0.523
boundary 30
	Intersects: 0
	Count & Sum: 0.648
	total: 0.649
boundary 31
	Intersects: 0
	Count & Sum: 0.512
	total: 0.513
boundary 32
	Intersects: 0
	Count & Sum: 0.501
	total: 0.502
boundary 33
	Intersects: 0
	Count & Sum: 0.658
	total: 0.659
total: 29.769

As Count() and Sum() are implemented as for loops under the hood, you can speed things up by writing the code yourself, using only one for loop to calculate both sum and count:

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
    }
    // append feature
}

This speeds up the script by about 50%, which is obvious, because we cut 50% of the loops.

Load 1: 0.007
Load 2: 0.002
boundary 0
	Intersect: 0
	Count & Sum: 2.073
	Append feature: 0
	total: 2.075
boundary 1
	Intersect: 0
	Count & Sum: 1.004
	Append feature: 0.001
	total: 1.007
boundary 2
	Intersect: 0
	Count & Sum: 0.745
	Append feature: 0
	total: 0.748
boundary 3
	Intersect: 0
	Count & Sum: 0.517
	Append feature: 0
	total: 0.52
boundary 4
	Intersect: 0
	Count & Sum: 0.544
	Append feature: 0
	total: 0.547
boundary 5
	Intersect: 0
	Count & Sum: 0.71
	Append feature: 0.001
	total: 0.713
boundary 6
	Intersect: 0
	Count & Sum: 0.497
	Append feature: 0
	total: 0.499
boundary 7
	Intersect: 0
	Count & Sum: 0.537
	Append feature: 0
	total: 0.539
boundary 8
	Intersect: 0
	Count & Sum: 0.759
	Append feature: 0
	total: 0.761
boundary 9
	Intersect: 0
	Count & Sum: 0.326
	Append feature: 0
	total: 0.329
boundary 10
	Intersect: 0
	Count & Sum: 0.573
	Append feature: 0
	total: 0.577
boundary 11
	Intersect: 0
	Count & Sum: 0.317
	Append feature: 0.001
	total: 0.319
boundary 12
	Intersect: 0
	Count & Sum: 0.289
	Append feature: 0
	total: 0.292
boundary 13
	Intersect: 0
	Count & Sum: 0.241
	Append feature: 0
	total: 0.243
boundary 14
	Intersect: 0
	Count & Sum: 0.229
	Append feature: 0
	total: 0.231
boundary 15
	Intersect: 0
	Count & Sum: 0.261
	Append feature: 0
	total: 0.262
boundary 16
	Intersect: 0
	Count & Sum: 0.469
	Append feature: 0
	total: 0.472
boundary 17
	Intersect: 0.001
	Count & Sum: 0.249
	Append feature: 0
	total: 0.253
boundary 18
	Intersect: 0
	Count & Sum: 0.288
	Append feature: 0
	total: 0.291
boundary 19
	Intersect: 0
	Count & Sum: 0.395
	Append feature: 0
	total: 0.398
boundary 20
	Intersect: 0
	Count & Sum: 0.231
	Append feature: 0
	total: 0.234
boundary 21
	Intersect: 0
	Count & Sum: 0.245
	Append feature: 0
	total: 0.247
boundary 22
	Intersect: 0
	Count & Sum: 0.205
	Append feature: 0
	total: 0.206
boundary 23
	Intersect: 0
	Count & Sum: 0.284
	Append feature: 0
	total: 0.285
boundary 24
	Intersect: 0
	Count & Sum: 0.225
	Append feature: 0
	total: 0.228
boundary 25
	Intersect: 0
	Count & Sum: 0.254
	Append feature: 0.001
	total: 0.259
boundary 26
	Intersect: 0
	Count & Sum: 0.256
	Append feature: 0
	total: 0.261
boundary 27
	Intersect: 0
	Count & Sum: 0.244
	Append feature: 0
	total: 0.25
boundary 28
	Intersect: 0
	Count & Sum: 0.386
	Append feature: 0.001
	total: 0.389
boundary 29
	Intersect: 0
	Count & Sum: 0.27
	Append feature: 0
	total: 0.274
boundary 30
	Intersect: 0.001
	Count & Sum: 0.273
	Append feature: 0
	total: 0.275
boundary 31
	Intersect: 0
	Count & Sum: 0.248
	Append feature: 0.001
	total: 0.25
boundary 32
	Intersect: 0
	Count & Sum: 0.235
	Append feature: 0
	total: 0.238
boundary 33
	Intersect: 0
	Count & Sum: 0.321
	Append feature: 0
	total: 0.322
total: 15.649000000000001

Sadly, I don't see a way to speed it up further.

[JerrySneary]

@JohannesLindner 

Thank you. Your help has been greatly appreciated.