6.1 How Can Data Be Retrieved From a GIS for Analysis?

One type of analysis is to find which areas or locations meet particular criteria—for instance, to take a map of U.S. Congressional Districts and identify which districts have a Democratic representative and which have a Republican representative. Alternatively, you might have a map of all residential parcels, and you might want to be able to see which houses have been sold in the last month. In both of these examples, you would perform a database query to select only those records from the attribute table (see Chapter 5) that reflect the qualities of the objects you want to work with.

In other cases, before beginning the analysis, you might only want to utilize a subset of your GIS data. For instance, rather than dealing with all Congressional Districts in the entire United States, you might want to examine only those in Ohio. Or in the housing example, if you have a dataset of all residential parcels in a county (which is likely thousands of parcels), you might only want to do analysis related to one (or a handful) of the parcels, rather than examining all of them. You’d want to go into the dataset and select only those parcels required for your study. Again, in these cases, the query would only select the records you want to deal with.

In GIS, queries are composed in the Structured Query Language (SQL) format, like a mathematical function. SQL is a specific format that is used for querying a layer or database to find what attributes meet certain conditions. For example, a layer made up of points representing cities in Ohio also has a number of fields representing its attributes, such as city name, population, and average income. If you just wanted to do analysis with one of these records (the city called “Boardman,” let’s say), then a query could be built to find all records where the field called CITY_NAME contained the characters of ‘Boardman’ as: CITY_NAME = ‘Boardman’. This should return one record for you to use in your analysis. Another example would be to select all cities in Ohio with a population greater than or equal to 50,000 persons. The attribute table has a field called POP2010 (representing the 2010 city population) that contains this information. Thus, a query of POP2010 >= 50000 could be built, and all records with a population field containing a value of greater than or equal to the number 50,000 would be selected for your use (see Figure 6.2 for an example of a query in GIS).

A query will use one of the following relational operators:

• Equal (=): Use this when you want to find all values that match the query. For instance, querying for CITY_NAME = ‘Poland’ will locate all records that exactly match the characters in the string that comprise the word ‘Poland.’ If the record were called ‘Poland Township’ it would not be located because the character string looks for an exact match.
• Not Equal (<>): Use this when you want to find all the records that do not match a particular value. For instance, querying for CITY_NAME <> ‘Poland’ will return all the records that do not have a character string of the word ‘Poland’ (which would probably be all of the other cities with names in the database).
• Greater Than (>) or Greater Than Or Equal To (>=): Use this for selecting values that are more than (or more than or equal to) a particular value.
• Less Than (<) or Less Than Or Equal To (=<): Use this for selecting values that are below (or below and equal to) a particular value.

While a simple query only uses one operator and one field, a compound query enables you to make selections using multiple criteria. There are different ways of linking multiple criteria together for creating one of these compound queries, each using a different option of querying (referred to as a Boolean operator):

• AND: Suppose you want to select cities in Ohio that have a population of over 50,000 (a variable called POP2010 in the attribute table) and an average household income (a variable called AVERAGEHI) of more than $30,000. A compound query can combine these two requests by saying: POP2010>= 50000 AND AVERAGEHI > 30000. Because “AND” is being used as the operator, only records that match both the criteria will be returned by the query. If an attribute only meets one criterion, it won’t be selected. An “AND” query is referred to as an intersection since it returns what the two items have in common.
• OR: A different query could be built by saying: POP2010>= 50000 OR AVERAGEHI > 30000. This would return records of cities with a population of greater than or equal to 50,000, or cities with an average household income of greater than $30,000, or cities that meet both. When an OR operator is being used, records that meet one or both criteria are selected. An “OR” query is referred to as a union since it returns all the elements of both operations.
• NOT: A third query could be built around the concept that you want all of the data related to one criterion, but you want to exclude what relates to the second criterion. For instance, if you want to select cities with a high population, but not those with a higher average household income, you could build a query like: POP2010>= 50000 NOT AVERAGEHI > 30000 to find those particular records. A “NOT” query is referred to as a negation since it returns all the elements of one dataset, but not what they have in common with the other.
• XOR: A final type of query can be built using the idea that you want all of the data from each layer, except for what they have in common. For instance, if you wanted to find those cities with a high population or those that had high incomes, but not both types of cities, your query would be: POP1990>= 50000 XOR AVERAGEHI > 30000. XOR works as the opposite of AND since it returns all data, except for the intersection of the datasets. An “XOR” query is referred to as an exclusive or, since it acts like a Union but leaves out the Intersection data.

Compound queries can use multiple operations to select multiple records by adhering to an order of operations, such as: (POP2010>=50000 AND AVERAGEHI > 30000) OR CITY_NAME = ‘Boardman’. This will select cities that meet both the population and the income requirement, and would select cities named ‘Boardman’ as well. The result of a query will be a subset of the records that are chosen, and then those records can be used for analysis, rather than the whole data layer. Selected records can also be exported to their own dataset. For instance, the results of the previous query could be extracted to compose a new GIS layer, made up of only those records (and their corresponding spatial objects) that meet the query’s criteria. Once you have your selected records or new feature layers, you can start to work with them in GIS (see Hands-on Application 6.1: Building SQL Queries in GIS for an example of doing queries with online GIS utility).