topic Re: Arcade: Intersect feature layers and summarise related table fields in ArcGIS Online Questions

Arcade: Intersect feature layers and summarise related table fields

CPoynter — Fri, 08 May 2020 04:43:20 GMT

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.

Re: Arcade: Intersect feature layers and summarise related table fields

XanderBakker — Mon, 11 May 2020 17:17:23 GMT

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-con-arcade-usando-groupby

Re: Arcade: Intersect feature layers and summarise related table fields

CPoynter — Tue, 12 May 2020 04:10:26 GMT

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

Re: Arcade: Intersect feature layers and summarise related table fields

XanderBakker — Tue, 12 May 2020 14:39:34 GMT

Hi cpoynter ,

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

Re: Arcade: Intersect feature layers and summarise related table fields

CPoynter — Thu, 14 May 2020 00:41:18 GMT

Hi Xander Bakker,

I invited you to Group.

Regards,

Craig

Re: Arcade: Intersect feature layers and summarise related table fields

CPoynter — Thu, 14 May 2020 00:43:19 GMT

Hi Xander Bakker,

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

Regards,

Craig

Re: Arcade: Intersect feature layers and summarise related table fields

XanderBakker — Thu, 14 May 2020 16:10:39 GMT

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.

Re: Arcade: Intersect feature layers and summarise related table fields

XanderBakker — Sat, 11 Dec 2021 22:36:12 GMT

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.

Re: Arcade: Intersect feature layers and summarise related table fields

XanderBakker — Sat, 11 Dec 2021 22:36:14 GMT

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:

Re: Arcade: Intersect feature layers and summarise related table fields

CPoynter — Fri, 15 May 2020 04:11:51 GMT

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

Re: Arcade: Intersect feature layers and summarise related table fields

XanderBakker — Fri, 15 May 2020 13:12:05 GMT

Hi Craig Poynter ,

Creating a html table with an Arcade expression is only possible in ArcGIS Pro at the moment. So, yes it would be possible, but only in ArcGIS Pro. In ArcGIS Online, any html returned is not parsed as html but as plain text. To create a html styles table with content from the related table would requiere an arcade expression for each cell of the table and some additional one to hide rows if there is not content. This would take hundreds of Arcade expressions, and forever to return the result to the user.

For now, if this 10-15 seconds of processing time is acceptable for your end users (personally, I don't think it will be) you could include the other information as plain text in the pop-up. You need to take into account that when information starts to grow, processing time will further increase.

Re: Arcade: Intersect feature layers and summarise related table fields

Czapiga_Jason — Wed, 15 Sep 2021 20:30:01 GMT

Thank you so much! I've been trying to figure this out for weeks. I had a similar workflow, but my code only returned one record from the related table. I used the example you posted above and it worked great! Here's mine that DID NOT work as an example for others that might be taking this path...use Xander's solution above!

var complaintPts = FeatureSetByName($map,"ComplaintPoints",['*'],true)
var townComplaints = Intersects($feature, complaintPts)
var tbl = FeatureSetByName($map,"IncidentRepeat",['*'],true);
var FeatureID =''
for (var f in townComplaints)
{
FeatureID = f.globalid
var sql = "parentglobalid = '" + FeatureID + "'"
var complaints = Filter(tbl,sql)
var x = GroupBy(complaints,'Species',
{name: 'SpCount', expression: '1', statistic: 'COUNT'})

var popup=""
for (var row in x){
popup += row.Species +": "+row.SpCount + TextFormatting.NewLine
}
}
return popup