Arcade: Intersect feature layers and summarise related table fields

[CPoynter]

Hi,

I have looked at several Arcade related postings and trying to string together some code to perform the following operation:

1) Intersect a point feature layer with related table with regional polygon feature layer (LLS Layer is where the Arcade expression is being displayed in pop-up):

This portion works ok, but I may need to know the count of 'species sampled'. f.species is within the feature layer.

2) I wish to summarise records in the point feature layer related table:

   End result is pop-up will show:

• region name [from feature layer]
• f.species and count [from feature layer]
• microbe name and the count of microbe (eg. above would be 'Pasteruella aerogenes: 4 samples') [related table]
• Each test item and count (eg. 'Amikacin - Intermediate (3 samples)) [related table]
• I will also need a decision tree if I have cases where I have a number of mixed samples intermediate/sensitive/resistant) [related table]

I am thinking I will need one common expression tailored for:

• each microbe or
• each microbe and each test item or
• combinations or each

Advice greatly appreciated.

[XanderBakker]

Hi Craig Poynter ,

For the summary you could use the GroupBy function. This will allow you to summarize on a featureset and get the count of microbes. To learn more about the GroupBy function I recommend you to read the blog post by Paul Barker: https://www.esri.com/arcgis-blog/products/arcgis-online/mapping/whats-new-in-arcade/ 

I also have a document explaining the use of GroupBy in a more detailed way, but this is in Spanish: https://community.esri.com/docs/DOC-13951-incluir-reportes-estadísticas-en-las-ventanas-emergentes-c... 

[CPoynter]

Hi Xander Bakker,

Thank you for the links. I have had a read, but syntax is confusing me with trying to summarise from layer file and associated records in related table. $map, $datastore, $feature, $layer usage and pulling from related table fields is proving difficult.

I can add you to a group if you wished to look at data if that helps.

Regards,

Craig

[XanderBakker]

Hi cpoynter ,

Sure, having access to the data would help a lot to see what is possible! My AGOL user is "xbakker.spx".

[CPoynter]

Hi Xander Bakker,

I invited you to Group.

Regards,

Craig

[CPoynter]

Hi Xander Bakker,

I also wish to add the output to a html formatted table.

Regards,

Craig 

[XanderBakker]

Hi cpoynter , 

Thanks for sharing. Hopefully later today I have some time to look at the data. I must say that creating a customized HTML output, will requiere multiple Arcade expressions and will increase complexity. I will focus primarily on a single expression that will return a "report" using the GroupBy I mentioned earlier, but I will also provide some pointers on how to advance from there.

[XanderBakker]

Hi Craig Poynter ,

Doing a little test on your data, I think there might be another challenge: performance. As far as I understand you want to click on the polygon, query the intersecting points and retrieve all related data stored in a table of those selected points.

Have a look below what this would look like:

var intersectedpoints = Intersects(FeatureSetByName($map,"CUMULATIVE REGIONAL ANTIBIOGRAM"), $feature)
Console("points found: " + Count(intersectedpoints));

var dct_species = {};
var dct_microbes = {};
for (var pnt in intersectedpoints) {
    var specie = pnt.species;
    if (HasKey(dct_species, specie)) {
        dct_species[specie] += 1;
    } else {
        dct_species[specie] = 1;
    }
    //Console("pnt: " + pnt.job_name + " | " + pnt.species);
    var fs = FeatureSetByRelationshipName(pnt, "form_microbe_repeat");
    for (var f in fs) {
        var microbe = f.microbe;
        if (HasKey(dct_microbes, microbe)) {
            dct_microbes[microbe] += 1;
        } else {
            dct_microbes[microbe] = 1;
        }        
    }
    //Console(" - related records: " + Count(fs));
}
Console(dct_species);
Console("");
Console(dct_microbes);

var result = "Species:";
for (var s in dct_species) {
    result += TextFormatting.NewLine + " - " + s + ": " + dct_species[s];
}

result += TextFormatting.NewLine + TextFormatting.NewLine + "Microbes:"
for (var m in dct_microbes) {
    result += TextFormatting.NewLine + " - " + m + ": " + dct_microbes[m];
}

return result;
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The result is a single string containing:

Species:
 - equine: 45

Microbes:
 - actinobacillus_pasteurella_sp_: 1
 - blood_culture_fluid_mixed_intes: 4
 - bordetella_bronchiseptica_: 2
 - enterobacter_cloacae_: 3
 - escherichia_coli_: 1
 - escherichia_coli_mucoid_: 1
 - escherichia_coli_non_haemolytic: 4
 - gram_negative_cocci_coccobacill: 1
 - gram_negative_coccobacilli_: 2
 - gram_negative_pleomorphic_cocco: 1
 - lactobacillus_sp_: 1
 - pasteruella_aerogenes: 4
 - pasteurella_like_organism_: 2
 - staphylococcus_aureus_: 8
 - staphylococcus_aureus_intermedi: 1
 - staphylococcus_sp_: 1
 - streptococcus_dysgalactiae_subs: 4
 - streptococcus_equi_subsp_zooepi: 6
 - streptococcus_sp_alpha_haemolyt: 3
 - streptococcus_sp_gp_c_: 3‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

However, executing time is horrible, since it consults for every specie the related records. There are at least 46 requests sent to the server and that results in a horrible response time. 

I think there might be another way, but let me check that first.

[XanderBakker]

Hi Craig Poynter ,

Something that wil reduce the number of requests to the server and will increment the performance is a scenario where the globalid is used to query the  parentglobalid in the related table. It believe it still takes too long, but now it is 12 seconds instead of 30:

var intersectedpoints = Intersects(FeatureSetByName($map,"CUMULATIVE REGIONAL ANTIBIOGRAM"), $feature);

var guids = [];
for (var pnt in intersectedpoints) {
    guids[Count(guids)] = "'{" + Upper(pnt.globalid) + "}'";
}

var result = "Species:"
if (Count(guids) > 0) {
    var lst = Concatenate(guids, ", ");
    var sql = "parentglobalid IN (" + lst + ")";
    var fs = Filter(FeatureSetByName($map,"service_e12489f2b6a940b4b95e858ebaa42a27 - microbe_repeat"), sql);

    var stats_species = GroupBy(fs, "microbe", 
        [{name:"count", expression:"microbe", statistic:"COUNT"}]);

    for (var stat in stats_species) {
        result += TextFormatting.NewLine + " - " + Trim(Proper(Replace(stat.microbe, "_", " "))) + "  (" + stat.count + ")";
    }
} else {
    var result = "No related records..."
}

return result;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Result:

[CPoynter]

Hi Xander Bakker,

These are a great result. Is it possible to have a result so that it lists Species (eg. equine) > Microbe (eg. Blood Culture) > Treatment > Sensitivity result? If I had a template for one combination I could repeat for each of the combinations I would need to compile for adding to a html table (many copies of the same expression to build my table).

I am thinking this would require filtering by species, then microbe and then treatment and result. Accessing the related tables and the associated syntax had me struggling. Is this a possibility?

I appreciate all you have achieved so far.

Regards,

Craig