Filter expression syntax and operators with Geocortex Access Control [Geocortex Tech Tip]

Geocortex Access Control provides fine-grained permissions that allow you to go beyond the out-of-the-box permissions of ArcGIS Server and control who can see the layers, fields and features of your services.

In this week’s Geocortex Tech Tip, we are going to demonstrate to you some of the syntax you can use with attribute filters to filter out the content of the layers you are providing to your end-users.

Watch on YouTube.

Video Transcript

“Hi, my name is Ryan Cooney. I’m the Product owner of Geocortex Access Control. Today, I’m going to show you some the syntax you can use when you are using attribute filters to filter out the content of the layers you are providing to your end-users. Let’s have a look!

The data I am working with today is the Ohio Department of Transportation. It is a culverts data set that represents the point location of culverts throughout the state. One thing that is particularly useful for this data and attribute filters is that each point has a county field that represents that county that it falls in. So, this one is from Harrison County, this one is from Jefferson County. So, we are going to use that to set up our attribute filters.

I’m going to start by creating a filter that just shows Harrison County here. To do that, I’m going to go into Access Control, and I’m going to find that service. So, it’s a hosted feature service in this case, there’s the layer and I can add an attribute filter to it. When I do this, I need to pick who it’s applying to, let’s just apply it to everybody right now.

Here, I am prompted for a ‘Filter Condition’, now this is a layer definition expression, often referred to a where clause, and the syntax is a SQL. It’s the same SQL that ArcGIS rest end-points understand.

What we need to do is provide something that is going to filter out where the County is equal to, Harrison in this case. It’s worth having a look at the layer itself, the rest end-point, just to confirm the field names, in this case, it’s ‘county’, it’s all lower case, but field names are not case sensitive in any of our clauses.

If I want the county to be equal to Harrison, I need to wrap the string value of the field in single quotes. For single type fields, use quotes, for numeric fields, no need for any quotes.

If I save that. Now, I can go back to my map and I’m just going to zoom in to trigger it to redraw. There we can see it’s now only showing Harrison County.

I can do something similar, in this case, it was equals, there’s another operator, the not equal to operator, just a less than, greater than. This will show me everything, but Harrison county.

I’m just triggering a redraw, it’ll bring up the data of the rest of the state.

Now, these expressions can be combined, using and or operators, so If I change this back to equals, I could change this to County = ‘HARRISON’ OR County = ‘JEFFERSON’. That should pick up the neighboring one. There we go!

Now, this same thing can be written another way, just ‘OR’ together a bunch of these. You can replace this with a different kind of expression called an in clause when you got a big last. Rather than having to do county or this or this, you can just do an in clause that does the same thing.

So, the County is in the list of Harrison or Jefferson or wherever else you may want. That’s going to produce the exact same result.

I’m going to show an example where we are going to use an ‘AND’ statement. So, let’s go back to ‘County=Harrison’ and in this case, I’m going to say ‘AND’ and I’m going to use another field to filter up the data. This one is going to be a drainage area, ‘drainage_area_acres > 1’. This is a numeric field, so I can use a greater than the operator. Here we go. So, just data in this county and only the points with drainage area and greater than one.

There are some other things you can do. There are other operators, there is a ‘LIKE’ operator, which allows you to do ‘Start With’ style queries. So, if I do County LIKE ‘H%’ this is going to get me all counties that start with an H, not quite sure what we’ve got here, I’ll zoom out to state level. There could be a bunch. So, there are a few in here.

One thing to be cautious with is that these queries are going to run every time your end-users are looking for data, this query has to run. We want to choose queries that are going to be efficient. A like query that is doing a start with type of operation is quite inefficient. Even though you can do it, I would avoid doing that myself. You really want to be doing these queries on fields that are indexed because you want them to be as fast as possible.

Something similar we can do is if you had to deal with case sensitivity issues, there are operators that can convert case. I wouldn’t do this exact one, but this one will upper case the value of Harrison, actually this is a little ridiculous. You wouldn’t do this because you are hard coding this value, what you would do is upper case county, I kind of wrote that backward.

If you had mixed cased data for the county, what you’d do is your uppercase the entire field and make that equal to the value that you want here. Again, don’t do that if you don’t have to, because we want to be as fast as possible in every other operation that you’re doing on this query, there’s a cost to it.

Finally, all of this isn’t useful if we aren’t providing different filters to different people. So, what I’m going to do is create a new one. I’m going to assign it to a specific user, that is different from me. So, a John Doe user.

I’m going to make this one. So, John Doe should be able to see that County = ‘JEFFERSON’. So, my current user, it’s just myself, I go and see this, but if I open a new incognito window here and open up the exact same map, I should be able to sign in as that other user, I should be able to see what I want.

Let’s head back on the screen. And there we go, just Jefferson County.

So, attribute filers, different users seeing different things, and we are able to construct those attribute filters using SQL where clauses. Thanks for watching and enjoy taking advantage of attribute filters!”

Want to learn more about what Geocortex Access Control has to offer? Click the button below for additional product information, or to schedule a demo.

Discover Geocortex Access Control