Wen M. Andrews

Download Report

Transcript Wen M. Andrews

Wen M. Andrews
J. Sargeant Reynolds Community College
Richmond, Virginia
NSF DUE-1205110;
GeoTEd Partners
NSF DUE-1205110;
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 relational 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;
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;
General Format of a Query
Typical query expressions use the general form as
 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;
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
NSF DUE-1205110;
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;
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;
Anatomy of an Attribute Query
Select appropriate action for data
Available fields based on
columns in the selected
“Where” condition options
Available values
from selected table
SQL Statement Box
This screen allows the user to create the
SQL expression 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;
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
NSF DUE-1205110;
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
2465 records,
only 40 met
the criteria in
the query
NSF DUE-1205110;
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
NSF DUE-1205110;
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;
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
This returned only 23 rows of data out
of 2465 instead of 40
NSF DUE-1205110;
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;
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
NSF DUE-1205110;
Anatomy of a Spatial Query (cont)
Select appropriate
action for
selecting data
Can apply a buffer to the search criteria
NSF DUE-1205110;
Spatial Query Example
In this example the user
wants to find all the
Community Centers, Public
Schools, and Points of
Interest located in a specific
Elementary School Zone
which is highlighted by the
blue outline. The selected
zone was chosen with the
selection tool, clicking on
the polygon of interest.
The blue circles are the
facilities that met the criteria
in the query from the target
18NSF DUE-1205110;
Spatial Query Example (cont)
By opening the tables and choosing the selected
points the user is able to see the information
from each table on the selected features.
Again, this is a temporary file and does not
affect the original database. As with “Attribute
Queries” if this is needed for later use the data
must be saved from each layer into a new
19NSF DUE-1205110;
Anatomy of an Individual Query
 There is a third type of query
that is used quite often for a
quick analysis of a layer. This
is used by completing the
following steps:
 Select layer
 Select “Properties”
 Select “Definition Query”
 Select “Query Builder” tool
 Create the query
 Verify
 Hit “OK”
20NSF DUE-1205110;
Individual Query Example (cont)
 This is what the screen would look like
before the query is run and then what it
would look like after the query is run.
 Remember this query was run on only
one layer, the elementary school zones.
 This could then be used for quick
analysis or as a starting point for the
spatial query to look at the data within
the selected zones.
 Remember this is still just a temporary
file and does not change the original
21NSF DUE-1205110;
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;