topic Re: Reproducing Labelling SQL Query as Arcade Label expression in ArcGIS Pro Questions

Reproducing Labelling SQL Query as Arcade Label expression

MattSmithCarto — Thu, 03 Mar 2022 01:19:10 GMT

Trying to simplify the number of label classes . So I'm trying to set the expressions used in the When logical function by replicating the SQL Quaries previously used to distinguish between label classes.

But I'm struggling to replicate this one

FEATURETYPE = 'Railway' And STATUS = 'Operational' And OWNER NOT IN ('Private', 'Aurizon') AND ROUTENAME NOT LIKE '%System' AND ROUTENAME NOT LIKE '%Yard' AND ROUTENAME NOT LIKE '%Siding'

I know wildcards are a bit tricky in Arcade which is where I'm getting stuck.

Any help would be appreciated.

Re: Reproducing Labelling SQL Query as Arcade Label expression

jcarlson — Thu, 03 Mar 2022 13:46:32 GMT

Is there a reason not to simply use SQL? If you're in ArcGIS Pro, you can write your filters in SQL without issue.

You mention you're using the "when" function. What's the rest of your expression look like?

Re: Reproducing Labelling SQL Query as Arcade Label expression

RhettZufelt — Thu, 03 Mar 2022 15:20:31 GMT

This post might help.

Re: Reproducing Labelling SQL Query as Arcade Label expression

MattSmithCarto — Thu, 03 Mar 2022 23:12:59 GMT

I actually haven't written it yet - I got caught by the first expression/result couplet which is the most complicated. So I can't really post that yet. We are trying to stick with Arcade as our language where possible.

The reason of trying to move away from seprate label classes defined by SQL is that I'm preparing a vector tile basemap that my organisation will then leverage to turn into different flavours using the vector tile editor or editing the JSON.

The idea is to minimise the number of layers that need to be re-symbolised by combining what would've been several label classes into one. These label classes have the same symbology but have a different output string depending on their operational status. So an active railway line would be labelled "Main Line", but an abandoned line would be labelled "Silver Spur Branch (Abandoned)" , a dismantled line "Brisbane Valley Line (dismantled)" and so on. But we don't need label classes in the vector tile package for each of them. ...If that makes sense...

Re: Reproducing Labelling SQL Query as Arcade Label expression

MattSmithCarto — Thu, 03 Mar 2022 23:14:37 GMT

Thanks, yes I had seen that thread, but none of solutions proposed worked for me.

Re: Reproducing Labelling SQL Query as Arcade Label expression

RichlandGIS — Thu, 03 Mar 2022 23:36:12 GMT

Sorry, thought I posted a different link that used the Includes function in leu of "IN" but can't find that one right now and headed out for the weekend.

Something like this is what I was thinking for replacing the "IN" function, then the link I provided you could extract the "LIKE" equivalent. Putting the ! (as below) means NOT IN, remove it for IN.

var field= $feature.MUTCDcode; var arr = ["R1-1", "R2-1(25)", "R3-1", "R3-5R", "R3-8(Mod)", "R8-3(FL)", "W11-2", "W16-7PL", "W16-7PR", "D3-1", "D3-1(HS)", "D3-1(RB)", "D3-1(OH)", "D9-2", "M5-1L", "S3-1", "R3-17"]; !Includes(arr, field);

so for this one, anything not in the list (arr) returns True.

Re: Reproducing Labelling SQL Query as Arcade Label expression

jcarlson — Fri, 04 Mar 2022 00:19:39 GMT

Ah, I see. You want the label expression to dynamically pipe in different values.

IN basically equates to the Includes function. Checking for an "ends with" is a bit trickier without wildcards, but we can do it by grabbing the Right portion of a string equal to the length of the searched text, and see if it's equal to the searched text. To put that in code:

// a test string var str = 'Here\'s a sample' // this returns true Right(str, 5) == 'ample' // this returns false Right(str, 5) == 'fishy'

We can actually build our own custom function for this. Let's call it EndsWith.

Okay, maybe it would look like this:

var bad_names = [ 'Private', 'Aurizon' ] // Custom function returns boolean if searchText is at the end of text function EndsWith(searchText, text){ // Get length of search text var strlen = Count(searchText) // Look at end of text for that string return(Right(text, strlen) == searchText) } var f = $feature['FEATURETYPE'] var s = $feature['STATUS'] var o = $feature['OWNER'] var r = $feature['ROUTENAME'] if( f == 'Railway' && s == 'Operational' && Includes(bad_names, o) && !EndsWith('Yard', r) && !EndsWith('Siding', r) && !EndsWith('System', r) ) { return 'Some Category' }

Now, I notice that all your conditions must be true, since these are all "AND" statements in the original SQL.

Depending on where you're working, and what version Arcade you have access to, you might try using the All function. Try this instead of lines 21 - 29 of the expression above.

var conditions = [ f == 'Railway', s == 'Operational', Includes(bad_names, o), !EndsWith('Yard', r), !EndsWith('Siding', r), !EndsWith('System', r) ] if( All(conditions, Boolean)){ return 'Some Category' }

That would allow you to establish all your conditions prior to the if/else section, which can help make your expression more readable.

Re: Reproducing Labelling SQL Query as Arcade Label expression

MattSmithCarto — Tue, 28 Jun 2022 22:00:52 GMT

Realised I hadn't replied to this. Thanks very much Josh. Exactly what I needed.