Transcript Powerpoint

Query and Reasoning
Types of Queries
• Most GIS queries will select spatial features
• Query by Attribute (Select by Attribute)
– Structured Query Language (SQL) – a database
computer language designed for managing data in
a relational database.
• Query by Location (Select by Location)
– Spatial Query
– Buffering a Distance from a feature
– Overlapping features
Relational Database Structure
• Most common database structure used today
– MS Access
– ESRI ArcInfo & ArcGIS (including ArcMap)
– Oracle
• Very robust; Can be expanded as needed
• Allows very complicated searches while maintaining
very simple sets of tables
• Can become very large and complex
Relational Database Structure
• Employs an ordered set of attribute values called tuples
grouped into 2D tables called relations
• A relation has a header and body
• The header consists of a fixed set of attributes (i.e. fields)
• The body consists of a set of tuples, a row is a single tuple
• Relations can be linked by a Primary Key field.
• A values within a field must come from the same
Domain. A domain is the set of scalar values from which
the actual values can be drawn.
Relational Database Structure
Relation:
Suppliers
4 Tuples
4 Attributes
S#
S1
S2
S3
S4
SNAME STATUS
Clarke
Smith
Jones
Smith
10
20
10
30
City
Phoenix
Tucson
Tucson
Flagstaff
Domain for City: All the cities in Arizona
Primary Key: Unique identifier for relation, no two rows can have the
same value in that column – only S# can serve as a primary key.
Table Record Relationships
One
One to One
Many to One
One to Many
Relational Database Structure
• Most common database structure used today
– MS Access
– ESRI ArcInfo (including ArcMap)
– Oracle
• Very robust; Can be expanded as needed
• Allows very complicated searches while maintaining
very simple sets of tables
• Can also become very large and complex
Quadrat # Collector
Date
Site #
Latitude
Longitude
1
Smith
06/10/04
2
35.1923
-106.9478
2
Jones
07/11/04
3
35.1923
-106.8488
3
Smith
07/11/04
..
35.2345
-106.9502
…
…
…
..
..
..
Quadrat # Species #
1
2
1
4
2
2
..
..
Table Record
Relationships
Species table
Quadrats table
Species code no. table
Links
Species # Species Name
Type
1
Conomyrma insana
Generalist
2
Pogonomyrmex rugosus
Specialist
3
…
…
…
…
…
Relational Database Structure
• All Relations must satisfy four properties
–
–
–
–
They can not contain duplicate tuples
There is no ordering of the tuples
There is no ordering of the attributes
All attributes values are atomic
Queries
• All tabular queries use Boolean Logic.
•Boolean logic involves True/False sets (Yes/No, 1/0)
on which Boolean logical operators (or connectors)
such as AND, OR, NOT and XOR can be applied.
• Going back to basic set theory, a group of individuals
are either in a specific set or not.
Queries
• You can define a set by delineating a condition
using logic (or relational) operations such as:
–
–
–
–
–
–
EQ or =
NE or <>
GE or >=
LE or <=
GT or >
LT or <
[operand_1] is equal to [operand_2]
[operand_1] is not equal to [operand_2]
[operand_1] is greater than or equal to [operand_2]
[operand_1] is less than or equal to [operand_2]
[operand_1] is greater than [operand_2]
[operand_1] is less than [operand_2]
• Only EQ and NE can be used on character string data.
You can the write a query or logic expression to find different
sets with a relation like the one below
ID#
Name
Gross
Income
Age
Gender
1
Smith
40,000
35
M
2
Jones
27,000
25
M
3
Jordan
57,000
30
M
4
Miller
30,000
28
F
5
Hiller
18,000
18
M
6
Brown
100,500
45
F
7
White
21,000
36
F
8
Richards 33,000
52
F
9
Hurley
60,000
50
M
10
O’Brain
30,000
33
M
We can then define a set of tuples
with a logic expression such as:
Gender = F
The members of the Set Gender
= F are ID#’s 4, 6, 7, 8
The logic expression Age > 40
results in a set with three
members: ID#’s 6, 8 , 9
Complex Queries
• You can then write expressions with logical
connector to define sets based on multiple
conditions:
–
–
–
–
–
Gender = F: 4, 6, 7, 8
Age > 40: 6, 8, 9
Gender = F AND Age > 40: 6, 8
Gender = F OR Age > 40: 4, 6, 7, 8, 9
Gender = F NOT Age > 40: 4, 7
Gender = F XOR Age > 40: 4, 7, 9
States with more than 45% of its
Population Under 15 years of Age
Select by Location
Select by Location Tool
• Adds, updates, or removes a selection on the input layer
based on spatial relationships to features in another
layer.
• Spatial Relationships:
•
•
•
•
•
•
•
Intersect
Within a Distance
Contains
Completely Contains
Contains Clementini
Within
Completely Within
•
•
•
•
•
•
Within Clementini
Are Identical To
Boundary Touches
Share a Line Segment With
Crossed by the Outline Of
Have Their Center in
Contain vs Within
• CONTAIN: selects features in the Input
Feature Layer which contain a feature in the
Selecting Features layer. The Selecting
Features can be inside as well as on the
boundary of the Input Feature Layer.
• WITHIN: will select features in the Input
Feature Layer which are within or contained
by features in the Selecting Features layer.
3 Contain Spatial Relationships
• CONTAIN: selects features in the Input Feature Layer which
contain a feature in the Selecting Features layer. The Selecting
Features can be inside as well as on the boundary of the Input
Feature Layer.
• COMPLETELY_CONTAINS: Selecting Features layer does not
intersect the boundary of the Input Feature Layer.
• CONTAINS_CLEMENTINI: the results will be identical to
CONTAINS with the exception that if the feature in the Selecting
Features layer is entirely on the boundary of the Input Feature
Layer, with no part of the contained feature properly inside the
feature in the Input Feature Layer, the input feature will not be
selected.
Intersect
Are Within A Distance of
Are Within
Contain
Contain
Contain
Implementation
• Selection Menu in ArcMap
– Select by Attribute is also available from a Table Options Menu
• ArcToolbox
– Data Management  Layers and Table View
Overlay Functions
• Result is a new data layer, not a selection
features. Used on feature (vector) data.
• Geoprocessing Menu – Analysis Tools
– Buffer: Creates a polygon with a defined radius
– Clip: Extracts input features that overlay the clip features
• Special tool for Raster/Grid Clips
– Union: Outputs all features, overlap and non-overlap
– Intersect: Outputs only overlapping features
– Merge: Combines multiple input datasets
• Append: Combines an input dataset with an existing dataset
– Dissolve: Aggregates features based on specified attributes
Proximity analysis: buffering
• Create a new area within a user-defined distance of an
existing entity
e.g., to determine areas impacted by a proposed highway
Point
Area
Line