[MUSIC] Hello everyone, and welcome back. In this lesson, we're going to begin a short series of lectures on writing query strings so that you can subset your data and create more powerful selections in ArcGIS. I'm going to specifically show you the basics of writing queries, talk about the general purpose SQL queries, and show you how to use the equal to operator, the not equal to operator, and the greater than and less than operators to subset your data. So let's start out by opening our attribute table. Right-click and bring that up. And I'm going to dock it to the bottom of the screen here. So we can watch it as we do everything else. And then let's bring up the Select By Attributes dialog. So, when I'm talking about query strings, I'm talking about this part at the bottom here. What we are building with Select By Attributes. And the first thing to notice is, again, I think I mentioned this before when we talked about Select By Attributes, but this select star or asterisk from the featured class name WHERE and then we fill in the rest of this. And this statement here, this select dot, dot, dot portion of the statement, this indicates that we are writing in a language called sequel, or SQL. SQL is the standard language of relational databases. And they're called relational databases because they have multiple tables, or feature classes in this case, that reference each other with relationships between the tables. We won't go deeply into relational databases, but know that this language comes from there so that you can both look up SQL and relational databases if you need to dive deeper into this information. So one thing that's both misleading and convenient about that select statement is that the word select doesn't have anything in particular to do with ArcGIS's selections. SQL uses the select statement in order to say, only show me the rows that match this query. And that's what we're writing. We're writing queries against the database when we write select statements. So, let's start by writing a quick query here. And we'll keep it simple for the moment and do something that you already know how to do which is just provide one attribute and operator and a value. So the attribute we'll say is Owner Type, and double-click to add it to the box. And then I'll make it an equals. And then I'm going to get unique values on owner type, and we'll make it Federal to add it to the box. Notice that it just adds the zero one. That's because this data set has coded values, so it's actually stored in the data table as the zero one. But what it shows us is Federal. Select By Attributes is nice enough to show us both the coded value and the underling value. And when I click Apply, it selects just those records where Owner Type is Federal, as we expect. If I only show the selected records, I can see that all of them are Federal. Now just to key you in on some terminology, this portion of writing is the where clause. It's the portion of the query that specifies the criteria that determine the records to come back to us. And in this case coming back to us just means that ArcGIS creates a selection that has those records. And the other records still are there in the background. But true to form for a database, it only operates on the selected records. Breaking down this where clause, we have three items here. We have a field, we have an operator, and we have a value. And the operator does some sort of comparison against the field and the value to specify a criteria that determines which records get matched. In this case, we say match records, where the owner type is equal to zero one, or federal. Now, we can do the reverse of this, and say, where the owner type is not equal to federal. And this angle bracket, angle bracket operator is equivalent to not equals. It's an older way of saying not equals. These days you often see exclamation equals for not equals. But in this case we're saying, show me all the records where the owner type Isn't federal so it could be any of these other options and that's useful when we don't necessarily know or care what the other options are. We just know what we want to exclude. And if I apply that instead now, we'll see that our selection changes and the select item here that was previously selected and the Owner Types all changed over here. So now we see Private and State, but we do not see Federal in this listing anymore. And that's expected, because we just excluded it with our query string. Now, similarly, we can find small or large quantities by using the greater than or less than operators. So if I change my field entirely, let's use the shape area field, and I can say where the shape area is greater than, let's find a good point here. Let's say 300,000. And I click Apply and I get a different selection and you can see that all the very small polygons here aren't selected. Let's make it bigger, maybe a million, in order to exclude some of the other small ones, make it a little easier to see. So yeah, we just excluded those other small ones and then we can once again, reverse that, to say shape areas less than a million and apply and all of those big polygons are no longer selected and the small ones are. So, these are really simple aspects of query strings. But, before we go any further, I want to show to you that you can run these exact same queries within a database engine and get the same results. So, let's do some sort of really simple query here, and let's just query based on a specific object ID. So that we get at one particular record, and I'm just going to say equals 51, some number. If I click Apply we get one record back, which is expected because only one record has that primary key of 51, and that looks like it's Bohm Woods Nature Preserve. I'm going to switch to Microsoft Access, where I have the same data open as a data table. And if I want to scroll down, I can find record number 51 in this case and see that it's Bohm Woods Nature Preserve. But let's write that as a query in Access so that we can use the same language, SQL, to access the data within Microsoft Access and within ArcGIS. So I'll go to Create > Query Design, I'm going to close this box out and we'll switch to this SQL mode at the bottom, SQL. And now I'll write out that beginning part of the query, and I'll do SELECT * FROM protected_areas_2015 WHERE. And then I don't have to create a new line there, but I do, just for readability. And I will now say OBJECTID = 51. And if I go up to the query tools at the top, I can now click Run. And just as expected, it returns just that record to me. And I can see the information that I can see in ArcGIS. Now note that the field aliases don't show up here. It only shows the field names because the aliases are an ArcGIS specific feature of this data set. And while this example is really simple, it shows you that we're writing in the language of databases because we can use that same query within Microsoft Access and within ArcGIS. I can verify that again if I go to SQL mode and show ArcGIS at the same time. The only thing that we had to change is some dialect specific stuff. ArcGIS needs field names delimited with these brackets here and Microsoft Access doesn't. Okay that's it for this lesson. In the next lesson we are going to continue right where left off and teach you more about query strings. In this lesson we covered the basics of SQL and queries in ArcGIS and I showed you how to match records based upon equal sign, the not equals sign, and the greater to and less than operators. Next time we're going to start chaining these things together to make much more complicated queries. And then we'll also discuss some other places you can use these queries. See you there.
