Queries Chapter 9 TMGT 882 Geographic Information Systems for Transportation Dr. Yi Qi Fall 2024 TSU 1 Outline • What is a query? • Three types of queries • Boolean operators/Venn diagrams • Buffers • Extraction functions • Selection types • Managing query results Dr. Yi Qi Fall 2024 TSU 2 What is a query? Dr. Yi Qi Fall 2024 TSU • Extract certain records from a map or table • Records meet certain criteria • Aspatial queries • All states with population greater than $1,000,000. • Spatial queries • All parcels that lie completely within the flood plain Types of queries • In an interactive query, the user visually identifies the desired features in a map or records in a table – States west of the Mississippi River. • In an attribute query, an expression is used to find records with values meeting a specified condition – Counties with more than 100,000 people. Dr. Yi Qi Fall 2024 TSU • In a spatial query, a spatial relationship between two layers is evaluated – Cities within 50 miles of a major earthquake. Attribute Queries • Attribute queries use expressions applied to a table to find records that meet one or more conditions – Select records in the Cities table where the state is Arizona (a). Dr. Yi Qi Fall 2024 TSU • Expressions are executed using Structured Query Language (SQL) • The Add Clause builder converts simple intuitive input into SQL SQL expressions Dr. Yi Qi Fall 2024 TSU • The Clause Builder is an easy, intuitive way to enter expressions • Occasionally, it is convenient or necessary to write expressions directly in SQL • Clicking the SQL button shows the SQL expression and allows you to edit it Operators • Operators act on objects to produce a result • GIS queries use several types of operators Arithmetic operators (+ − × ÷ ^) act onnumbers : 3 + 5 Logical operators ( = > <   ) test conditions and return true or false STATE = NJ, GPA > 3.0 Boolean operators ( AND OR XOR NOT ) test pairs of conditions and return true or false. MAJOR = Geography AND GPA  3.5 Dr. Yi Qi Fall 2024 TSU Spatial operators ( intersect contains proximity) test spatial relationships between features. Cities within 50 miles of a volcano. Boolean operators • Boolean operators are used when multiple conditions must be tested • STUDENTMAJOR = English AND GPA > 3.0. • STUDENTMAJOR = Spanish OR STUDENTMAJOR = German. • Boolean operators test two True/False conditions and return a result of True or False • AND: returns true if both conditions are true. • OR: returns true if either or both conditions are true. • XOR: returns true if one condition is true and the other is false (doesn’t matter which one is the true one). Dr. Yi Qi Fall 2024 • NOT: returns true if the first condition is true and the second condition is false. TSU Venn diagrams • The shaded areas show the records that would be selected using the Boolean expression. Dr. Yi Qi Fall 2024 TSU Interpreting a Venn diagram Dr. Yi Qi Fall 2024 TSU • Let T = [all students in University] • Let A = [Geography majors] • Let B = [students from New York] • Let C = [students from New Jersey] • Notice that A overlaps with both B and C, because Geography majors could come from New York or New Jersey • Notice that B does not overlap with C, because a student cannot be from New York and from New Jersey Partial matches • Most databases allow searches for partial matches, such as rock types with ‘stone’ in them, like limestone, sandstone, and so on Clause Builder SQL Dr. Yi Qi Fall 2024 TSU Spatial queries • Spatial queries test the spatial relationship between two layers a) Counties that contain volcanoes b) Counties that intersect rivers – Parcels within a flood plain. – Cities within 5 miles of an interstate highway. Dr. Yi Qi Fall 2024 TSU Spatial operators Dr. Yi Qi Fall 2024 TSU • There are three basic operators that test spatial relationships • Intersect tests whether feature touch • Contains/Within tests whether a feature is inside another • Proximity tests whether features are within a specified distance of another • More relationships are available, but all of them are variations or special cases of the three basic operators, as shown by the colors Types of containment • The contains operator permits the feature to lie on the edge of (share a boundary with) the containing feature • The completely contains operator does not permit a shared boundary • The yellow counties are contained by Oregon, but only the blue counties are completely contained by Oregon Dr. Yi Qi Fall 2024 TSU Target and source features Dr. Yi Qi Fall 2024 TSU • When executing a spatial query, the target layer is the one from which the features will be selected • The source layer is the one that the target features are compared to • Here, the counties are the target layer and the volcanoes are the source layer Select Layer By Location target layer • Spatial queries are done using the Select Layer By Location tool Source layer Dr. Yi Qi Fall 2024 TSU Select counties that intersect rivers Spatial query examples Select counties that Dr.completely Yi Qi contain state Fallcapitals 2024 TSU Select counties that are within 200 miles of Denver 17 Spatial query examples Dr. Yi Qi Fall 2024 TSU Select counties that intersect rivers Select rivers that intersect Texas 18 Spatial query examples Select cities that are Dr.within Yi Qi 20 miles of an Fall 2024 highway interstate TSU Select cities that are completely within counties named Washington 19 Limitation of spatial queries Dr. Yi Qi Fall 2024 TSU • Spatial queries can only select, or not select, entire features • Selecting rivers that intersect Texas yields the entire river length, some of which lies outside the state • Selecting rivers within Texas yields nothing, because all of these rivers have sections outside the state • Isolating the portions of rivers that lie inside Texas requires different tools, like clip or intersect Logical consistency and queries • Logical consistency refers to how well a data set reflects real-world relationships • Data with poor logical consistency may cause queries to work inconsistently – These Congressional district boundaries (green) should match the county boundaries (brown). – Because they don’t, a spatial query using contains or within will not work as expected. Dr. Yi Qi Fall 2024 TSU Accuracy and queries • Accuracy and precision issues may also plague spatial queries – The generalized points and lines representing Minneapolis, St. Paul, and the Mississippi River do not intersect, even though the real world entities do. – Selecting cities that intersect rivers would not work with these data. Dr. Yi Qi Fall 2024 TSU – A search distance could be used to help it work a little better. • For example, assume that cities within 5 km of a river do intersect it. Buffering Multiple buffers around points Dr. Yi Qi Fall 2024 TSU Single buffers around lines delineate the area (or boundary) of the region within a specified distance of a set of features 23 Buffers Dr. Yi Qi Fall 2024 TSU Dissolving buffers Dr. Yi Qi Fall 2024 TSU • By default, each feature gets its own buffer • The default buffers for these roads overlap each other • In many cases, the buffers should be dissolved in order to generate a single, clean buffer region • Be sure to set the Dissolve option appropriately when creating buffers Extraction functions • Clip works like a cookie cutter to extract features (roads) that lie inside the boundary of another data set (city limits) – It would keep the red roads and remove the gray ones. • Erase would keep the features outside the boundary Dr. Yi Qi Fall 2024 TSU – It would remove the red roads and keep the gray ones. • Both clip and erase create new data sets Effect on areas • Extraction functions may change the lengths or areas of features that cross the boundary • After extraction, any userdefined area or length fields should be updated using the Calculate Geometry Attributes tool Dr. Yi Qi Fall 2024 TSU Processing layers with selections • Operations on a layer usually affect only the selected features – If you performed summary statistics on the counties, only the selected ones containing volcanoes would be included in the statistics. • This is called honoring the selected set Dr. Yi Qi Fall 2024 TSU Clear selection after every task! Interactive selection Dr. Yi Qi Fall 2024 TSU • In interactive selection, the user visually picks the desired features from a map – If you know where Houston, TX is, it’s easier to click it than run a tool. • Selection tools permit different shapes to be used for selecting features Selectable layers Dr. Yi Qi Fall 2024 TSU • By default, every feature that passes through the selection shape (green rectangle) will be selected (b) • The List By Selection panel in the Contents pane (a) uses check boxes to control which layers are selectable – If only States is checked, then the green rectangle would select only the states (c). – The panel also indicates how many feature are selected in each layer (a). Selection type • A selection type parameter determines what happens to an existing selection if another query is performed on the same layer They are helpful when creating a selection based on both attribute and spatial queries Dr. Yi Qi Fall 2024 TSU The Boolean Two-Step • Selection methods can be used to apply a Boolean operator to two separate queries Dr. Yi Qi Fall 2024 TSU Using selection types • Selecting earthquakes in Mineral County, Nevada (condition A) with magnitudes greater than 5.0 (condition B) • Condition to be evaluated: A AND B Dr. Yi Qi Fall 2024 TSU – Use Select Layer By Location to select the earthquakes in the county (green quakes). – Change the selection method to Select from current selection. – Use Select Layer By Attribute to select the earthquakes with magnitude greater than 5.0 (circled green quakes). Managing query results • It is often an advantage to preserve a set of selected features for future use • There are several ways – Make a new layer from already selected features using a menu item. • Has no effect on data stored on disk • Does not require an additional data set to be stored. Dr. Yi Qi Fall 2024 TSU Managing Query Results – Export the selected features as a new data set. Create a new data set on disk Dr. Yi Qi Fall 2024 TSU Assignment 6 • Practice Exercises in Chapter 9 (P307) – 1,2,3,4,5 Please Using the data in (C:\TMGT 882_2024\gisclass_new\mgisdata\Usa fold) Dr. Yi Qi Fall 2024 TSU Due on Nov 6h Create your ArcGIS Online account before the Nov 6 Lecture ( Guest Lecture) https://www.arcgis.com/sharing/rest/oauth2/sig nup?client_id=arcgisonline&redirect_uri= http://www.arcgis.com&response_type=tok en 36 
Purchase answer to see full attachment