Chapter 8 Queries What are queries Extract certain

  • Slides: 71
Download presentation
Chapter 8 - Queries What are queries? • Extract certain records from a map

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

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

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

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

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

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

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

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

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

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]

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]

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

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

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

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

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

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

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

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

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

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

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’

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’)

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

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

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?

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

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

• 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

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

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

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

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

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

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

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 counties that intersect rivers Select rivers that intersect Texas

More examples Select cities that are within 20 miles of an interstate highway Select

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

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,

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

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

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 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.

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

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

Clipping data frame layers

General information about queries in Arc. Map • Interactive selection – Choose features by

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

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

View by Selection Right-click to open menu Visibility Clears selection this layer only Selectable toggle Clear selection

Selection options

Selection options

Viewing selected features States for which POP 2000 > 8 million Highlighted in table

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

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

Clear Selection One layer/table All layers/tables

Interactive Selection Click on feature to select Hold down shift key to select more

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

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 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"

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

Select By Attributes Select By Location Target layer Source layer Spatial operator

Intersect

Intersect

Within distance of

Within distance of

Using a selected set

Using a selected set

Within the same layer

Within the same layer

Selection methods Available for all three types of selection

Selection methods Available for all three types of selection

The Boolean Two-Step OR T NOT A AND Applying selection methods facilitates using multiple

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

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

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

Spatial query Cities within 50 mi of earthquakes

Attribute query Cities within 50 mi of earthquakes Having > 500, 000 people

Attribute query Cities within 50 mi of earthquakes Having > 500, 000 people

Creating subsets from queries Creating layers • Common operation following a query • Creates

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

Exporting selections Creates new feature class

Creating a definition query • Redefine a layer to include a subset of the

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.