Transcript 4.2 Queries

Wen M. Andrews
J. Sargeant Reynolds Community College
Richmond, Virginia
NSF DUE-1205110;
0903270
GeoTEd Partners
NSF DUE-1205110;
0903270
What are Query Expressions (SQL)
 Query expressions adhere to Structured Query Language
(SQL) which is a special purpose programming language
designed for managing data held in a relation database
management systems (RDBMS)
 It is the standard language for relational database
management systems such as ArcGIS.
 Some common relational database management systems
that use SQL are: Oracle, Sybase, Microsoft SQL Server,
Access, Ingres, etc.
NSF DUE-1205110;
0903270
Query Expressions (continue)
Queries are used to perform tasks on database tables such as:
 Selects
 Inserts
 Updates
 Creates
 Deletes
Simply put queries are used to communicate with databases to get
specific information on a selected portion of a larger database
Saving the generated query can produce a new database without
changing the original database
NSF DUE-1205110;
0903270
General Format of a Query
Typical query expressions use the general form as
follows:
 Select * From <Layer or dataset> Where <Field_name>
<Operator> <Value or String>
The table name that follows the keyword from
specifies the table that will be queried to retrieve the
desired results
The where clause (optional) specifies which data
values or rows will be returned or displayed
NSF DUE-1205110;
0903270
Georeferencing in ArcGIS
 Start with a map with the data you will match “to”
 Add the raster dataset you want to georeference
 Assign control points
 Matching a point on the target dataset to the base data
already on the map
 Continue until the datasets are lined up as well as
possible
Source: ArcGIS 10.1 help
NSF DUE-1205110;
0903270
General Format of a Query (continue)
Conditional selections used in the where clause:
• = Equal
• > Greater than
• < Less than
• >= Greater than or equal
• <= Less than or equal
• <> Not equal to
• LIKE
NSF DUE-1205110;
0903270
What Can SQL’s Do?
• Execute queries against a database
• Retrieve data from a database
• Insert records in a database
• Update records in a database
• Delete records from a database
• Create new databases
• Create new tables in a database
• Create stored procedures in a database
• Create views in a database
• Set permissions on tables, procedures, and views
NSF DUE-1205110;
0903270
Creating ArcGIS Queries
ESRI provides an easy to use “Query Builder” tool to create
the necessary queries without knowing how to write the
SQL statement.
Versions of Query Builder are used in several tools in
ArcMap but the most common is from the Selection menu
in the Standard toolbar.
The two main options are:
 Attribute queries
 Spatial/Location queries
NSF DUE-1205110;
0903270
Anatomy of an Attribute Query
Select appropriate action for data
Available fields based on
columns in the selected
table
“Where” condition options
Available values
from selected table
SQL Statement Box
This screen allows the user to create the
SQL express ion easily without
programming knowledge. The user selects
the appropriate layer to query (layer), how
to save it (method), the fields of interest, &
the conditions that the user is wanting to
pull out
NSF DUE-1205110;
0903270
Attribute Query Example
This is the completed screen once the
users has chosen all the fields. In
this example the query is going to
“Create a new selection” based on
the field “ProjectTyp” that meets the
requirement of looking only at the
“Final Subdivision”.
This is a very simple query.
It is important to remember, it is
necessary to know what your data
fields are before making your
selection.
NSF DUE-1205110;
0903270
Attribute Query Example
When the user opens the table associated with the query expression
they will see something similar to what is below. Those rows that are
selected will be outlined on the map showing their location relative to
the other features of the dataset.
In this
example the
total dataset
contained
2465 records,
only 44 met
the criteria in
the query
expression.
NSF DUE-1205110;
0903270
Attribute Query Example
When looking at the map the
areas meeting the query
conditions will be highlighted.
This is only a temporary file, it is
good to show visually where the
conditions are and to completed
some simple analysis on the
data.
NSF DUE-1205110;
0903270
Attribute Query Example
If this information is necessary for later use
it is a good idea to save this as a separate or
new data layer which can be pulled into the
current project or future projects.
When exporting the
data be sure to select
the right output
location and select the
correct file type
NSF DUE-1205110;
0903270
Advanced Attribute Query Example
This is an example of a more complex
query using the “equal to” and the “and”
condition. This means that the only data
to be returned must meet both
conditions: Final Subdivision and
Approved.
This returned only 23 rows of data out
of 2465 instead of 44
NSF DUE-1205110;
0903270
Attribute vs. Spatial Queries
Query By Attribute
 Requires an SQL statement either generated in a command line or by using
the Query Builder
 Can be simple with only one condition in the “where” clause or complex
with multiple conditions
 Involves only one layer but can involve multiple fields
 Can be points, lines, or polygons
Query By Location
 This query shows the spatial relationship to other features within the same
layer or in another layer
 Base or source layer usually is a polygon feature
 More than one feature layer (point or line) can be chosen to pull data from
multiple datasets
NSF DUE-1205110;
0903270
Anatomy of a Spatial Query
Select appropriate
action for data
Choose which layers you want to
have included in the search. Spatial
queries allow for data from more
than one layer to be displayed.
Select the layer that is
being used as the location
or base layer, typically a
polygon.
NSF DUE-1205110;
0903270
Anatomy of a Spatial Query
Select appropriate
action for
selecting data
Can apply a buffer to the search criteria
NSF DUE-1205110;
0903270
Questions?
Wen M. Andrews
Professor and Program Head
Department of Architectural & Civil Engineering Technology
J. Sargeant Reynolds Community College
Richmond, Virginia
[email protected]
NSF DUE-1205110;
0903270