idea Add a SQL Clause to the FeatureSet tool calls in ArcGIS Online Ideas

Add a SQL Clause to the FeatureSet tool calls

DougBrowning — Thu, 27 Jan 2022 14:45:41 GMT

I would like to see a SQL clause type parameter added to the FeatureSet tools like Search Cursor has.

FeatureSetByName( map, title, fieldList?, includeGeometry? )

SearchCursor(in_table, field_names, {where_clause}, {spatial_reference}, {explode_to_points}, {sql_clause})

I am using FeatureSet wrapped with a Filter now and it does work

var sql = "PointID = '" + $feature.PointID + "'";
var tbl = Filter(FeatureSetByName($map,"Points", ['DesignLat'], false), sql);
return First(tbl).DesignLat

In my case I have 100,000+ records in this table but I just want the 1 record. The way it is now the return is all 100,000 then I have to filter out the one I need. If a sql clause was added to the call it would just have to return not only just one row but just one field. Would be so much faster right?

I have 30 of these simple lookups in one popup and it is getting slow. Of course the other bummer with Arcade setup is I have to query these 100,000+ records 30 times! Would be nice to some do one lookup but have the return in multiple fields.

thanks!

Re: Add a SQL Clause to the FeatureSet tool calls

jcarlson — Thu, 27 Jan 2022 15:16:04 GMT

I've been curious about this sort of thing. If the FeatureSet() is nested within the Filter(), is the script still pulling the whole FeatureSet? It seems that way.

The Filter actually seems to be the big time suck, at least in a quick test. So it would seem that if the filter could be applied at the FeatureSet level, performance would improve.

Oddly, the "nested" style seems to actually perform worse than doing it in two steps. I tested it a bunch of times, and the two-step method is consistently as quick or significantly quicker than the nested.

var start = Now() var fs = FeatureSetByName($datastore,"Building_Footprints") var end = Now() Console(`Pulling FeatureSet: ${DateDiff(end, start, 'milliseconds')} ms`) var filter_start = Now() var filter_fs = Filter(FeatureSetByName($datastore,"Building_Footprints"), "OBJECTID = 1") var filter_end = Now() Console(`Pulling Filtered FeatureSet: ${DateDiff(filter_end, filter_start, 'milliseconds')} ms`) var twostep_start = Now() var twostep_fs = FeatureSetByName($datastore,"Building_Footprints") var twostep_filter_fs = Filter(twostep_fs, "OBJECTID = 1") var twostep_end = Now() Console(`Pulling FeatureSet, then Filtering: ${DateDiff(twostep_end, twostep_start, 'milliseconds')} ms`)

Returns

Pulling FeatureSet: 1 ms
Pulling Filtered FeatureSet: 19 ms
Pulling FeatureSet, then Filtering: 15 ms

Re: Add a SQL Clause to the FeatureSet tool calls

DougBrowning — Thu, 27 Jan 2022 15:24:33 GMT

Good to know. I have seen great speed increases by having it not return the geometry.

And by having it return just the one field vs using *. Note specifying a field had a bug for awhile in Field Maps (I think) but I reported it and they fixed it.

Here the ['DesignLat'] is just getting one field and the false is so no geometry is returned. I just mentioned it since your code seems to return it all.

FeatureSetByName($map,"Points", ['DesignLat'], false)

thanks

Oh and big thanks on Console I had not see that before. Finally a way to see what my script is doing! Now we just need a var tracker.

Re: Add a SQL Clause to the FeatureSet tool calls

jcarlson — Thu, 27 Jan 2022 19:43:28 GMT

Yes, good point. I typically don't return geometry unless I need it, but I was just doing a quick speed test. Turns out the nested and two-step processes are basically equivalent.

function speedtest(n){ var comps = [] for(var i=0; i<n; i++){ var start = Now() var fs = FeatureSetByName($datastore,"Building_Footprints", ['OBJECTID'], False) var end = Now() var filter_start = Now() var filter_fs = Filter(FeatureSetByName($datastore,"Building_Footprints", ['OBJECTID'], False), "OBJECTID = 1") var filter_end = Now() var twostep_start = Now() var twostep_fs = FeatureSetByName($datastore,"Building_Footprints", ['OBJECTID'], False) var twostep_filter_fs = Filter(twostep_fs, "OBJECTID = 1") var twostep_end = Now() Push(comps, { attributes: { fs: DateDiff(end, start), filtered: DateDiff(filter_end, filter_start), twostep: DateDiff(twostep_end, twostep_start), diff: DateDiff(filter_end, filter_start) - DateDiff(twostep_end, twostep_start) } }) } var out_dict = { fields: [ {name: 'fs', alias: 'Pulling the FeatureSet', type: 'esriFieldTypeInteger'}, {name: 'filtered', alias: 'Nested FeatureSet and Filter', type: 'esriFieldTypeInteger'}, {name: 'twostep', alias: 'Pulling FeatureSet and Filtering Separately', type: 'esriFieldTypeInteger'}, {name: 'diff', alias: 'Difference', type: 'esriFieldTypeInteger'}], geometryType: '', features: comps } var set = FeatureSet(Text(out_dict)) return `Mean Values across ${n} Tests: FeatureSet returned in ${Mean(set, 'fs')} Nested Filter returned in ${Mean(set, 'filtered')} Two-Step Filter returned in ${Mean(set, 'twostep')} Nested - Two-Step: ${Mean(set, 'diff')}` } speedtest(250)

Returns:

Mean Values across 250 Tests:
FeatureSet returned in 1.832
Nested Filter returned in 160.868
Two-Step Filter returned in 162.384
Nested - Two-Step: -1.516