Arcade Count intersecting polyline features by distinct value in field

[PaulSweeney3]

I have a polygon layer and a polyline layer for the polyline layer i am able to count the distinct features using the following arcade expression. The distinct values are found by removing the last two numbers of the string 

Var textlenght =COUNT(TEXT($feature["label"]))
Var label = textlenght-2
var stringleft = Left($feature["label"], label)
var sql = "label LIKE '%" + stringleft+"%'"
var Filt = filter($layer,sql)
return count(Filt)‍‍‍‍‍‍

For the polygon layer i am able to get the total length of those polyline s in my first layer  that intersect it using the following expression

// get the ug Duct feature set:
var sql = "(surface_ty = '" + 2 + "'"+" OR "+"surface_ty = ' " +3+ "'"+" OR "+"surface_ty = ' " +4+ "'"+" OR "+"surface_ty = ' " +5+ "'"+" OR "+"surface_ty = ' " +6+ "'"+" OR "+"surface_ty = ' " +7+ "')"+" AND "+"(status = '" + 1 + "'"+" OR "+"status = ' " +3+ "')"
var lines = filter(FeatureSetByName($map,"LLD_Fibre_Duct_TLI"),sql)
// polylines that intersect with the polygon
var relevantlines = Intersects($feature, lines);

var cnt = Count(relevantlines);

var totlengthUG = 0;

if (cnt > 0) {
 for (var line in relevantlines) {
 var lineint = Intersection(line, $feature);
 totlengthUG += LengthGeodetic(lineint, 'm');
 } 
}
return round(totlengthUG,2)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

my problem is the the polylines are overlapping and each polyline represents a duct in a trench. I want to be able to combine the two functions and divide the total length by the count of distinct polylines in order to get the trench length . I guess i would have to iterate through the feature set of the label field in the polyline and replace the values ,then sum them into an array where i can count the new distinct values i just cant figure out how to get this iteration to work. Xander Bakker‌ perhaps you can help ? 

[XanderBakker]

Hi Paul Sweeney ,

In your second code block I see that you are using a pretty long SQL query, but it does not contain any variables. Will it contain variable in the future? If not you could simplify it to:

var sql = "(surface_ty IN ('2', '3', '4', '5', '6', '7') AND status IN ('1', '3'))";‍‍

And do you have additional spaces in front of ' 3', ' 4', ' 5', ' 6' and ' 7'? Because that is what your SQL is querying for.

I don't see any problem in combing both expression into a single one and using the count from the first with the total length from the second to calculate trench length. It would be easier to see the data to understand what the limitations might be.

[PaulSweeney3]

Hi Xander Bakker‌

Thanks for getting back. In relation to the sql yes i think the reason it was like that is because i considered using variables at the start and then it changed, it was just easier for me to change it to a number. The number relates to a surface type. Your suggestion is much neater however.

I probably did not explain myself fully the first time , the label where i take the last two letters may vary over the polygon so for example you may have 6 polylines in total intersecting the polygon 4 of these maybe overlapping polylines with the same label number and 2 may be another set of overlapping polylines with a different label with different lengths and different surfaces see example below. What i would like to show is the distance in grass total and the distance in footpath total for example. 

orig label 	New Label 	      Length Surface
123456/28	  123456	          1.5	grass
123456/29	  123456	          1.5	grass
123456/30	  123456	          1.5	grass
123456/31	  123456	          1.5	grass
123456/32	  123456	          1.5	grass
678910/45	  678910	           3	footpath 
678910/46	  678910	           3	footpath 
599910/48	  599910	           5	grass
599910/49	  599910	           5	grass
	
Total length 	        23.5
Total trenches 	         3	
Distance in grass 	11.5
Distance in footpath 	3	
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

i tired to combine them originally but i couldn't figure out how to iterate through the polyline feature and group the various distinct labels and sum them by surface. 

[XanderBakker]

Hi Paul Sweeney ,

Thanks for the additional explanation. It is possible to loop through the features, intersect the geometry and read out the surface and length to determine the total length per surface. If you would not intersect the geometry it wold be much easier using the GroupBy function as explained here (in Spanish, sorry): Incluir reportes estadísticas en las ventanas emergentes con Arcade usando GroupBy and in English in the blog post by Paul Barker: What’s New in Arcade 1.8 

[XanderBakker]

I can help you creating the expression, but I would need access to the data. You can create a group in AGOL, share the data or a portion of it and the map and invite muy AGOL user "xbakker.spx" to the group. That way I can see what is possible and provide you with a working Arcade expression.

[PaulSweeney3]

Hi Xander Bakker‌

Thanks for your response that blog link is useful I can just about figure out what's happening I ll give it a shot and try,  if i am really stuck I may ask for your help . A couple of additional points I had hope to use collector and pass values to survey123 however I realised featureset s were not supported.  I am now calculating the fields first  to pass them through.i am setting up a model in pro to do this but I was wondering if arcade expressions ,specifically using featuresetbyportalitem , are achievable using the python Api  and would it be a faster method then model builder to run. 

Do you know if collector or the new field maps app  will support featuresets anytime soon?

Thanks  

[XanderBakker]

Hi Paul Sweeney ,

Do you know if collector or the new field maps app  will support featuresets anytime soon?

Unfortunately, I do not know if this will be supported anytime soon. I do have ArcGIS Field Maps installed, but I haven't had the opportunity to play with it a lot. The best way of getting an answer to this question, would be to engage in the early adopter community and talk directly to the product managers and developers and being able to influence those features that will be included in the final version: ArcGIS Field Maps (Early Adopter Site)

A couple of additional points I had hope to use collector and pass values to survey123 however I realised featureset s were not supported.  I am now calculating the fields first  to pass them through.i am setting up a model in pro to do this but I was wondering if arcade expressions ,specifically using featuresetbyportalitem , are achievable using the python Api  and would it be a faster method then model builder to run. 

Using a model, a python script, a home baked GP tool or a Jupyter notebook and the Python API are all ways you can use to do this. Make sure the identity that you are using has access to the data and use the option that works best for you. 

[PaulSweeney3]

HI Xander Bakker‌

Thanks xander i might post the question in the early adopter site if it hasn't already been asked. in relation to the model option 

I have set about creating the model on ArcGIS Pro and i have run into an issue wiithon of my expressions. Which totals the number of unique sections intersecting a polygon based on shape length. 

var portal = Portal('https://www.arcgis.com');
// get the ug Duct feature set:
var sql = "(surface_ty = '" + "1" + "')"+" AND "+"(status = '" + 1 +"')";
var Ducts = Intersects($feature,filter(FeatureSetByPortalItem(portal, 'xxxxxxxxxxx', 0),sql));
var groupSections = GroupBy(Ducts,['Shape__Length'],[{ name: 'outputStatName', expression: '1', statistic: 'COUNT' }]);
// count the overlapping lines
var cnt = Count(groupSections);
return cnt

Using calculate field the expression validates ok  but it will not run it fails with the error. 

002717: Invalid Arcade expression—ArcGIS Pro | Documentation with no real description of why it is failing 

As a solution i found if i use the distinct function instead of grouby  it works and returns the desired output but i was wondering if this is a bug or is groupby not available to use in Arcgis pro ?

[XanderBakker]

Hi paul.sweeneyTLI ,

Have a look at the expression below and the comments in the code explaining some things to look at:

// I would change the variable name portal, since it is a existing function
var myportal = Portal('https://www.arcgis.com');

// If the sql does not have any variables, don't make it more complex than needed
//var sql = "(surface_ty = '" + "1" + "')"+" AND "+"(status = '" + 1 +"')";
var sql = "(surface_ty = '1') AND (status = '1')";

// for testing cut the nested expressions into single lines
// limite the fields to the 1 that you need and don't extract the geometry
var fs = FeatureSetByPortalItem(myportal, 'xxxxxxxxxxx', 0 ,['Shape__Length'], False);
Console("fs:" + Count(fs));

// filter the featureset
var fsfiltered = Filter(fs, sql);
Console("fsfiltered:" + Count(fsfiltered));

// during intersection note that the first argument is the featureset and the second the feature!
var Ducts = Intersects(fsfiltered, $feature);
Console("Ducts:" + Count(Ducts));

// groupby is only used to group features by an attribute and return some statistic
// example: group ducts by material and return the total length by material
// doing a count on the results of a GroupBy will get you the number of different categories
// var groupSections = GroupBy(Ducts,['Shape__Length'],[{ name: 'outputStatName', expression: '1', statistic: 'COUNT' }]);

// count the overlapping lines
var cnt = Count(Ducts);

return cnt;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍