Chapter 8 Queries What are queries Extract certain
- Slides: 71
Chapter 8 - Queries What are queries? • Extract certain records from a map or table • Records meet certain criteria – Aspatial queries • All parcels with value greater than $100, 000. – Spatial queries • All parcels that lie completely within the flood plain
Selecting features of interest Selecting aspen stands from a forest vegetation layer. Using statistics on areas (m 2) Minimum: 12, 900 Maximum: 750, 500 Sum: 10, 529, 000 [COV_TYPE] = “TAA”
Exploring patterns Are aspen stands randomly scattered or clustered? Do they occur in particular portions of the forest? What are the distributions of stand densities?
Isolating for more analysis Are there any mature stands with large trees and open crowns? Where are they? [TREE_SZ 96] = 'L' AND [DENSITY 96] = 'A'
Exploring spatial relationships What fraction of stands are intersected by roads? What types of trees are adjacent to aspen stands?
Queries involving surfaces Over what range of elevations do aspen occur? Do aspen occur above 1500 m elevation? Raster query [Elevation] > 1500
Attribute queries- SQL • Many databases use a special query language called Structured Query Language • Can write queries that work in multiple DBMS environments • Queries can be saved and reused • Nearly always case-sensitive
SQL Query Examples Some Valid Queries SELECT *FROM cities WHERE "POP 1990" >= 500000 SELECT *FROM counties WHERE “BEEFCOW_92” < “BEEFCOW_87” SELECT *FROM parcels WHERE “LU-CODE” = 42 AND “VALUE” > 50000 Programs may have an interface to help users build SQL expressions SELECT *FROM rentals WHERE “RENT” > 700 AND “RENT” < 1500 In most databases, SQL expressions are case-sensitive “Smith” ≠ “SMITH”
Queries as sets T • • Let T = [all students in University] Let A = [students from New York] Let B = [Geography majors] Let C = [English majors] A B C Queries are used to extract subsets (records) of interest from a set (table). Multiple criteria may be used (such as Geography majors from New York)
Single criteria T A • • Let T = [all students in University] Let A = [students from New York] Let B = [Geography majors] Let C = [English majors] B C Select students from T where [Home_State] = “NY” Select students from T where [Major] = “Geography”
Double criteria T A B • • Let T = [all students in University] Let A = [students from New York] Let B = [Geography majors] Let C = [English majors] C Select students from T where [Home_State] = “NY” OR [Home_State] = “NJ” Select students from T where [Home_State] = “NY” AND [Major] = “Geography”
AND vs OR? T A • • Let T = [all students in University] Let A = [students from New York] Let B = [Geography majors] Let C = [English majors] B C Select students from T where [Home_State] = “NY” OR [Home_State] = “NJ” Select students from T where [Home_State] = “NY” AND [Major] = “Geography” Each condition is tested separately. If AND is used, then BOTH must be true. If OR is used, then either may be true.
Boolean expressions AND and OR are known as Boolean operators are used to evaluate pairs of conditions. A AND B A OR B
AND vs OR? T A B • • Let T = [all students in University] Let A = [students from New York] Let B = [Geography majors] Let C = [English majors] A AND B C A B Select students from T where [Home_State] = “NY” AND [Major] = “Geography”
AND vs OR? T A B • • Let T = [all students in University] Let A = [students from New York] Let B = [Geography majors] Let C = [English majors] A OR B C A Select students from T where [Home_State] = “NY” OR [Major] = “Geography” B
What do you get? T A B C • • Let T = [all students in University] Let A = [students from New York] Let B = [Geography majors] Let C = [English majors] Select students from T where [Major] = “Geography” AND [Major] = English” B AND C Select students from T where [Major] = “Geography” OR [Major] = “English” B OR C Select students from T where [State] = “NY” AND [Major] = “English” A AND C Select students from T where [State = “NY” OR [Major] = “English” A OR C
What do you get? T A B C • • Let T = [all students in University] Let A = [students from New York] Let B = [GPA > 3. 0] Let C = [GPA < 2. 0] Select students from T where [State] = “NY” AND [GPA] > 3. 0 A AND B Select students from T where [State] = “NY” OR [GPA] > 3. 0 A OR B Select students from T where [GPA] > 2. 0 AND [GPA] < 3. 0 ? AND ? Select students from T where [GPA] > 2. 0 OR [GPA] < 3. 0 ? OR ?
Other Boolean operators Some databases use additional operators besides AND and OR. A AND B A A XOR B A OR B B A NOT B
What do you get? T A B C A AND B B AND A A OR B B OR A A XOR B B XOR A B XOR C A NOT B B NOT A B NOT C C NOT B A AND B A • • Let T = [all students in University] Let A = [students from New York] Let B = [Geography majors] Let C = [English majors] A XOR B A OR B B A NOT B
Commutation of operators • AND, OR and XOR are commutative – A AND B == B AND A – A OR B == B OR A – A XOR B == B XOR A • NOT is not commutative – A NOT B ≠≠ B NOT A
Order of operations • Boolean operators have equal order or precedence • Evaluation occurs from left to right • Parentheses must be used to change order
What do you get? A AND B OR C T ( A AND B ) OR C A A AND (B OR C) (A OR B ) AND C B A OR (B AND C) C A AND B A • • Let T = [all students in University] Let A = [students from New York] Let B = [Geography majors] Let C = [English majors] A XOR B A OR B B A NOT B
Test: AND vs OR Remember—you test each feature separately Wrong Right “Land-use” = ‘RES’ AND “Land-use” = ‘COM’ “Land-use” = ‘RES’ OR “Land-use” = ‘COM’ “Pop 2000” ≥ 5000 OR “Pop 2000” < 9000 “Pop 2000” ≥ 5000 AND “Pop 2000” < 9000
Multiple conditions • Test using these parcels (“LU” = ‘RES’ or “LU” = ‘COM’) and “Value” > 100000 “LU” = ‘RES’ or (“LU” = ‘COM’ and “Value” > 100000) $75, 000 $125, 000 RES $75, 000 $125, 000 COM $125, 000
Searching for partial matches • Sometimes you need to find one string within another rather than an exact match – Find all customer names beginning with “Mac” or “Mc” – Find all zip codes beginning with 0 • Typically uses a “wildcard” character – *Mac* or *Mc* – 0*
The Like Operator • “NAME” LIKE ‘%(D)%’ – Finds all of the (D) Democrats • % is wildcard • Ignores Don or Danforth • “NAME” LIKE ‘%New %’ – Would find New Hampshire and New York, but not Newcastle or Kennewick
Spatial Queries Basic spatial relationships • Intersection – Does the road cross the aspen? – Do two polygons share areas or boundaries? • Containment – Is the aspen inside a geology unit? – Is a road inside a geolgoy unit? • Proximity – How many aspen stands within 200 meters of a road?
Spatial operators • Spatial queries can employ a number of operators to test the basic conditions of intersection, containment, and proximity.
• The operators test relationships between two layers at a time. – The target layer is the one containing the features to be selected – The source layer is the one containing the features being compared to. Select the aspen stands that are intersected by roads. Select the roads that are intersected by aspen stands.
Intersection operators • Features intersect when any part of one feature touches, crosses, or overlaps another feature. The lower set includes “special cases” of intersecting features.
Containment operators • Features that enclose all of another feature contain it. • Within is the inverse of contain
Types of containment • Contains Columbia Jefferson Within is the inverse of contains. Columbia county is within Oregon. Jefferson county is completely within Oregon. – One feature lies inside another and may share a boundary – Oregon contains Columbia county • Completely contains – One feature lies inside another without touching the boundary – Oregon does not completely contain Columbia county, but does completely contain Jefferson county
Clementini operators • Eliseo Clementini and his coauthors defined a special set of topological relationships concerning containment*. • Clementini considers the boundary of a polygon to be separate from its inside or outside. • The Clementini operator is equivalent to the standard operator except when the source feature lies only on the boundary of the target feature. *Eliseo Clementini, Paolino Di Felice, and Peter van Oosterom, A Small Set of Formal Topological Relationships Suitable for End-User Interaction. Proceedings of the Third International Symposium on Advances in Spatial Databases, pp. 277 -295, June 23 -25, 1993.
Clementini example • The Rio Grande River lies on the border of Texas For the example, we ignore the Rio Grande after it leaves Texas… – The Contains operator would select the Rio Grande – The Clementini Contains operator would NOT select the Rio Grande because the state boundary is not considered part of Texas Conversely, the Rio Grande is within Texas using the standard operator, but is not within Texas using the Clementini operator.
Proximity operators • This operator tests whether the target features are within a specified distance of the source features. Volcanoes within 100 km of an interstate
More examples Select counties that contain state capitals Select counties that are within 200 miles of Denver
More examples Select counties that intersect rivers Select rivers that intersect Texas
More examples Select cities that are within 20 miles of an interstate highway Select cities that are within counties named Washington
Scale and accuracy issues • When testing spatial relationships, consider that features are often generalized. Consider selecting cities that lie on (intersect) rivers. A single point or line cannot adequately represent location at this scale. Selection becomes a hit or miss affair. One can use buffers to allow a little room for error.
Topology issues Shannon County Bennett County Pine Ridge Indian Reservation South Dakota Nebraska Here, Shannon County should contain the Pine Ridge Reservation, but it does not. • In the real world, certain boundaries coincide. • This condition won’t hold true for many feature classes unless they have been specifically checked and corrected for logical consistency. • Keep in mind that your data sets may contain topological inconsistencies that may affect your results.
Extraction functions • Similar to Select By Location except that they can split features that cross the boundaries. – Clip extracts features within the boundary – Erase keeps features outside the boundary clip erase
Extraction functions Extracts portions of features based on an overlay layer. Clip keeps the features inside the feature boundary. clip/erase layer Erase keeps the features outside the feature boundary. Roads are the input layer, land use is the clip layer.
The clip layer The clip and erase functions ignore internal boundaries and attributes in the clip layer. The entire region is used like a simple cookie cutter to extract features from the input layer. The attributes of the input layer are simply brought through unchanged.
Length/area changes Clip/erase can change the lengths or areas of features on the boundary. Geodatabase feature classes have Shape_Length and Shape_Area fields that are automatically updated. A user-defined MILES field would contain incorrect lengths after the clip. All other length or area-based fields are NOT updated.
On-the-fly clipping • Temporary clip applied to a map layout • Does not create new layers or affect lengths or areas of the source layers • Can be performed on many layers simultaneously • Can be removed when no longer needed • Set as a data frame property
Clipping data frame layers
General information about queries in Arc. Map • Interactive selection – Choose features by pointing to them on the screen • Select By Attribute – Select features based on attribute criteria • Select By Location – Select features based on their spatial relationships
Selection tools Selection menu Table of Contents List By Selection
View by Selection Right-click to open menu Visibility Clears selection this layer only Selectable toggle Clear selection
Selection options
Viewing selected features States for which POP 2000 > 8 million Highlighted in table Highlighted in map
Using Selected features • Once a layer has a query placed upon it, all subsequent operations on that layer use ONLY the selected features. Volcanoes selected, then buffered Buffer uses only selected volcanoes Statistics only include selected volcanoes
Clear Selection One layer/table All layers/tables
Interactive Selection Click on feature to select Hold down shift key to select more than one feature Draw a rectangle that passes through features to be selected. Draw a circle with a specified radius.
Selectable Layers All layers selectable Click to toggle selectability States selectable
Select by Graphics Use Drawing toolbar to create graphic Then Select by Graphics
Select by Attributes Select layer Some Valid Queries SELECT *FROM cities WHERE "POP 1990" >= 500000 SELECT *FROM counties WHERE “BEEFCOW_92” < “BEEFCOW_87” SELECT *FROM parcels WHERE “LU-CODE” = 42 AND “VALUE” > 50000 SELECT *FROM rentals WHERE “RENT” > 700 AND “RENT” < 1500 Note: Shapefile tables use quotes for field names; geodatabase tables use brackets
Select By Attributes Select By Location Target layer Source layer Spatial operator
Intersect
Within distance of
Using a selected set
Within the same layer
Selection methods Available for all three types of selection
The Boolean Two-Step OR T NOT A AND Applying selection methods facilitates using multiple steps to apply multiple criteria—like using Boolean operators. A OR B C Create new selection A; Add B to current selection A AND B Create new selection A; Select B from current selection A NOT B Create new selection A; Remove B from current selection B
Applying selection methods Create new selection: hearts = yellow Add to current selection: hearts = red Add to current selection: hearts = blue Remove from selection: hearts = red Select from selection: hearts = blue Select from selection: hearts = orange
Combining queries FEMA problem Distribute funding to quakeprone cities with large populations Both a spatial condition and an attribute condition Must be performed in sequence, although order doesn’t matter Cities within 50 mi of earthquakes Cities with population > 500, 000 This is an AND operation.
Spatial query Cities within 50 mi of earthquakes
Attribute query Cities within 50 mi of earthquakes Having > 500, 000 people
Creating subsets from queries Creating layers • Common operation following a query • Creates a new layer with only the selected features Note that the new layer still refers to the original feature class with all the features. But it appears to contain only the selected features.
Exporting selections Creates new feature class
Creating a definition query • Redefine a layer to include a subset of the actual features • Only selected features appear on map and in table • Has no effect on data stored on disk • Temporarily treats a layer as being smaller than it actually is • Does not require an additional data set to be stored.
- Antigentest åre
- Using subqueries to solve queries
- Standing query
- Action queries in access
- Dimensional modeling basics
- Multirelation queries
- Wildcard query in information retrieval
- Any queries
- Complex sql join queries
- Basic retrieval queries in sql
- Hotel database sql queries
- Sql queries for insert update and delete
- Answering my queries
- Wide world importers diagram
- Sql queries for banking database
- Conjunctive queries
- Canned queries
- Ingres algorithm
- Teradata ordbms
- For any queries
- Symbiosis login page
- Sql dml
- Suggestions and queries
- Any queries
- J queries
- Eyegaze
- Texas rrc production query
- Texas railroad commission online queries
- Sql server management studio recover unsaved queries
- Ir queries
- Power bi extract transform load
- Magwitch description extract
- For clarity of aqueous extract test container autoclaved at
- Settling velocity stokes law
- Mrs twit description extract
- Read the extract from your pen friend pete's letter
- Eduqas english language past papers 2017
- Oliver twist workhouse extract
- Haldin product
- Neem extracts market
- Extract from the prelude poem
- Extract from the prelude
- Context definition
- Animal farm extract analysis
- Commonality extract elsewhere
- Basking shark poem
- What is the extract about
- How to extract dna from anything living
- Extract subclass
- How to answer an extract question
- Extracted synonym
- Between a rock and a hard place analysis
- Omnishambles tv comedy
- Extract xml oracle ejemplos
- Jane eyre extract
- Gerstegras extract poeder werking
- Jamaica inn extract
- Catechol oxidase and potato extract lab report
- Nlp word cloud
- Longjax mht side effects
- Giant knotweed extract fungicide
- Image morphology is used to extract
- Angel kind
- Extract captions from video
- Act
- A kestrel for a knave extract
- Extract transform and load automation
- Extract text
- Extract from romeo and juliet
- Sodium extract
- Extract text
- Small island extract