gis

MODULE 4: QUERIES

Categories of Queries

Boolean Expressions & Boolean Operators

Attribute Queries

Spatial Queries

Extraction Functions

INTRODUCTION

Queries The fundamental tool for geographic information systems that serve a critical role: --used for data exploration --used to search for patterns --used to extract features to perform another function

A query extracts features or records from a feature data set or table and isolates them for further use --for printing --for calculating statistics on the selected set --for editing --for graphing --for creating new files --for performing additional queries

A simple query simply involves --looking at a map or table and using a mouse to select the desired record or records --called “interactive selection” in MGIS

An attribute query involves --using records in the attribute table to test a condition --finding all cities with population over 1 million, for example --can be performed on either an attribute table or a standalone table

Query Types, Attribute Queries, and SQL

A spatial query is used to select features from two different tables based on the spatial relationships between them --which cities fall within a certain county --rivers within a state --airports within 25 miles of a particular city

Spatial queries can only be used on spatial data layers

Spatial and attribute queries can be combined --selecting cities with over 50,000 inhabitants that are located with 50 miles of an airport --must be performed separately, but in any order

Attribute Queries Attribute data queries require the use of logical expressions that are interpretable by a database management system --structure of expresssions varies from one system to another --ArcGIS uses Structured Query Language (SQL) for query expressions

SQL is designed to manipulate relational databases --it is a command language that allows a GIS such as ArcGIS to communicate with a database (MS Access, Oracle, DB2, MS SQL, etc.) --developed by IBM in the 1970s

LOGICAL OPERATORS, BOOLEAN EXPRESSIONS & BOOLEAN OPERATORS SQL in ArcGIS uses logical operators (<, >, =, etc) to test conditions between two or more attributes --written as an expression

Examples from Mastering ArcGIS:

SELECT*FROM cites WHERE [POP1990] > = 500000

SLECT *FROM counties WHERE {BEEFCOW_92] <

[BEEFCOW_87]

SELECT *FROM and WHERE are keywords that are necessary according to the syntax rules of SQL

The lowercase words such as ‘cities’ give the name of the table from which to select

The terms in double quotes (shapefiles) or brackets (geodatabase feature classes) are the field names

Query Expressions and Boolean Operators

Query expressions (where conditions) consist of Boolean expressions and connectors

A Boolean expression contains two operands and a logical operator:

Parcel.PIN = ‘P101’

Where PIN and P101 are operands and = is a logical operator

BOOLEAN OPERATORS

Boolean operators evaluate two input conditions, which may be either true or false, and return a set of records meeting those conditions

The primary Boolean Operators are:

AND --selects only those records that are COMMON to both sets

OR --selects ALL records in both sets

XOR --selects only those records from both sets that are NOT common to both sets

NOT --selects only those records that can be found in one set, but not in the other

Example of an SQL Expression Using Boolean Operators:

Select cities with more than 10,000 inhabitants, but less than 100,000 inhabitants, for the year 2010: SELECT *FROM cities WHERE “POP2010” ≥ 1000 AND ≤ 10000

Venn Diagrams Showing How

Boolean Operators Can Be Used to

Evaluate Two Input Conditions

SEARCHING FOR PARTIAL MATCHES

Sometimes a user may want to perform a query on text fields that looks for partial matches to a condition

Examples: Locate all customers with the surname Smith Locate all Streets that contain Maple --a city may have more than one street with Maple in their names --often they are given different appellations such as Maple Street, Maple Road, Maple Avenue, etc.

To solve such problems, database management systems and GIS use operators such as LIKE and CONTAINS --allow searches for a particular substring within another string

LIKE searches for the specified set of characters within the field --returns any record containing those characters

LIKE is combined with the wildcard character % (shapefiles) or * (geodatabase files) --the wildcard character stands for other letters that may appear in the text string

Examples: NAME LIKE ‘New %’ (shapefile) NAME LIKE ‘New * (geodatabase file) Finds New York and New Haven --but not Newcastle (latter contains no spaces) NAME LIKE ‘%Smith%’ --accounts for additional letters or spaces in the name

SPATIAL QUERIES

Spatial Queries Unique to GIS since they select features in spatial data files based on the spatial relationships between them

Examples: How many wells are located within five miles of a river

How many parcels are located inside a floodplain

A spatial query uses two layers and one spatial condition --features of layer being selected are compared spatially with the features of the second layer --those features meeting the criteria are then selected

GIS coordinates of objects in different spatial data sets that coincide with one another do not always match --would want to specify a search radius when evaluating a spatial condition --thus, features need not exactly match

While attribute queries use logical attributes (<,>,=, etc.) to test relationships between attributes --spatial queries use spatial operators such as containment, intersection, and proximity to test for spatial relationships between two datasets

SPATIAL QUERIES

Spatial Operators and the Conditions They Test

Containment tests whether features from one dataset contain features from another dataset

Variants: Whether features in dataset A contain features in dataset B: If a feature in A completely contains features in B --all features in A that fully surround features in B

If a feature in A contains features in B --features in A contain features in B but they also share a boundary

Whether features in dataset A fall inside features within dataset B: --are within, are completely within are the operators that test for these conditions

have their centroid in tests whether the center of a feature in A lies inside a feature in B

Intersection returns any feature in A that touches, crosses, or overlaps any part of a feature in B --intersect, touch the boundary of, share a line segment with, are crossed by the outline of all test for this condition --are identical to refers to the condition in which the feature in A equals the feature from B exactly

SPATIAL QUERIES and EXTRACTION FUNCTIONS Proximity tests how close features in A are to features in B --are within a distance of, share a line segment with, and touch the boundary of are operators that test for this condition

Sometimes a problem calls for testing spatial relationships --using a subset of features rather than the entire layer

Example ”select cities within 50 miles of the Mississippi River” --use an attribute query to isolate the Mississippi River --then use a spatial query to select the cities

Extraction Functions Extraction functions separate features of interest from a larger group --queries can be considered one type of extraction function --cannot, however, truncate features when they cross a boundary --a spatial query either selects or does not select entire feature

There are two extraction functions that can truncate features when they cross a boundary:

1. Clip works like a cookie cutter to truncate the features of one file based on the outline of another a. The input layer is the layer the clip is performed on

b. The clip layer provides the boundary that the input layer’s features are clipped to, it is the “cookie cutter”

2. Erase works in the opposite sense of a clip a. Features that fall outside of the clip boundary are kept

b. Features that fall inside of the clip boundary are discarded

Since clips and erases alter feature geometry

--the Calculate Geometry tool must be used to update lengths and areas of user-defined fields

Selecting all streams that intersect

Calcasieu Parish boundary

Clipping Streams layer to

Calcasieu Parish boundary