[MUSIC] Hello again. Welcome back. In this lesson, we're going to continue where we left off last lesson in writing query strings and expand our concept of SQL with introduction of Boolean operators. Bullion operators is just a sort of fancy way of saying things that help us chain together multiple parts of a query string to create more specific queries that help us return only the data we need. So let's bring back up that select by attribute dialog. I want to emphasize again that this isn't the only spot that this matters in ArcGIS, but we're using it so that you can visualize what we're doing with Arc query strings right now. So let's remove that previous query. I'm going to move it over here. And let's go to owner name and add that. And let's get the unique values. And we'll use this first one here. So let's find all the land that is owned by the United States Fish and Wildlife Service. So I'll add that in there, and again, notice that it puts in the underlying value, not the value that it shows us in the owner name field. If I click apply, I get 112 records down here, and if I switch only to those, we can see that. And let's just scroll, so we can see a few of those lands in here, in the data view. So, let's take a step back for a moment and think of how this query is evaluated. What happens when you run a query is it goes through the where clause, and for each record in the database table, it evaluates whether or not the where clause matches and it does this by assessing the value in this clause here and then saying, is it true or false? Does the owner name equal zero one two five, or Fish and Wildlife Service in this case? If owner name is Fish and Wildlife Service, then that evaluates the true and all the records that evaluate to true come back to me as a selection. All the records that evaluate to false because their owner name does not equal Fish and Wildlife Service do not come back. The same principle occurs if we switch that equals to the not equals. If I run this now, even though I'm negating the equals sign here and saying not equals this still has to evaluate to true for me to get the record, so does the owner name, is the owner name not equal to Fish and Wildlife Service, 0125? Correct. Owner name is equal to Department of Defense, not Fish and Wildlife Service. So owner name not equal Fish and Wildlife Service. That's true, I get the record back right here. So let's switch it back, though. Now that you have that concept of true and false, those are what bouillon values are and knowing this information helps us chain these parts of the statement together with bouillon operators. So what if I want to do more than just select based upon a single criteria? That's what all these items here are, the and and the or. And allows us to say multiple criteria must be met, and or allows us to say one of these criteria must be met. This or that. So, let's start with the and. Let's say that we wanted to know all the fish and wildlife service lands that meet a certain conservation status code, and that's the gap status code which we can see over in the data table. I think I scrolled too far. Maybe not. Yeah, right over here. Gap status code. So, that's this field. And if I get unique values, I can see that there are four values here. And let's say we want this top first value here. The managed for biodiversity, disturbance events proceed or are mimicked. So I'm going to say owner name is Fish and Wildlife Service and, oop, don't keep it selected. Click after it, click and. And then, gap status is equal to one. The manage for biodiversity disturbance events proceed are mimicked and right now I can see that I have one and two in here and if I click apply all I get are the ones that have status code of one. Breaking this down again, this first part evaluates the true and this second part evaluates the true and so if we were to kind of visually, if we are pretending that we are evaluating this query now, I'm not actually writing a query in the box, but what it evaluates to in the background is true and true. The first portion on the left here evaluates to true. So that kind of gets replaced with it. And this second portion gets replaced with true for all these remaining records here. And then true and true returns true. And ultimately, once we get a full true for this record, then the record gets returned. Now pretend that the status code isn't one and for one of those records where the gap status was two managed for biodiversity disturbance events are suppressed. What happens is this becomes true and false. The first part matches still, the Fish and Wildlife Service still manages the land for some of the records that we're looking at, but the second part, the gaps status code equal one, that criteria doesn't match and so that turns into false. And evaluating true and false together, equals false because if either of them are false on the side of that true and false, then the whole thing becomes false and the record does not match. But I could say well, I want one of two records, or one of two types of status. I am going to put parenthesis around this and then we're going to use that war operator. And I can add the status code again and say equals two. And now if either of those match and the Department of Fish and Wildlife owns the land then we are going to get those records back. When I click apply I see that I get those records that were number two back now. So for this first record, what we get is true because fish and wildlife service manages the land as we know and then we get and. This first part still evaluates the false, the gap status code is not equal to one in this record, it's equal to two. So we have false, or gap status code equals two. Now it's true. Gap status code is equal to two in this record. Or, in this portion. So then, we can break it down to be true and false or true and then we do this again and false or true where one of those needs to be true evaluates to true, and then once again we have true and true. So the final result is all of the criteria were true for this record and the record matches, and we get it back. One thing that often trips up people who are new to this type of logic is that the OR statement is not an exclusive OR. Not in the way that we use it in English, usually. That is, it's not that only one thing in the or statement can evaluate to true. It's that at least one thing in the or statement must evaluate to true. Zero of the items in the or statement evaluating to true means it's false. One or two, either or both of them evaluating to true means the entire or statement evaluates to true. So let's get rid of this again, and consider this conceptually again. We have an or operator that says one of these two items on either side of this or operator needs to match. And we have an and operator that says both items on either side of this need to match. Now we can chain these together even further, and make really complicated query statements I'd say be very careful about how you do this and make sure to provide parenthesis to indicate which two items are being compared. There is an order of operations for how this is handled if I don't provide parenthesis, but to the viewer it can be a little ambiguous and it can be hard to get your logic straight. So make sure you put in your parenthesis so that you know and the computer knows exactly how you're indenting for this to be evaluated. These logical patterns go far beyond select by attributes which is why I'm spending so much time emphasizing them with you. This query string, like we showed last time, you can use that in Microsoft access or other database systems with slight dialect changes that you get used to as you as you use the software, but you can also use it in your definition queries, which we showed in the last class to subset the data you're viewing. You can use it in other tools that can subset the data that you're working with directly. You can use it in Python when you're loading up a feature class. And you can use it just as a guide to the kind of logic you need in thinking about conditional statements and basic programming. That is going to come into play when we start working with the field calculator because you might want to start using criteria like this to write out whole new fields. I encourage you to play around with this on some of your own data, just see what happens when you type in different types of queries. And specifically get used to mentally evaluating each part of that query in your mind so that In matching against the record in your data table so that you can be very familiar with what's going on and be able to debug your queries much more quickly. Okay, that's it for this lesson. In this lesson I showed you just how to work with Boolean operators in the select by attributes tool, but also how that connects to the broader world of SQL queries that you will need to use in other parts in GIS. We specifically talked about the and operator which joins two parts of the query together and says both of these portions must be correct or true for the record in question for that record to be returned. And we also dealt with the or operator, which says one of these two needs to be true in order for the record to be matched. In the next lesson, we'll put some of this logic into practice by adding in calculating fields. See you there.
