Long Term Resource Monitoring Program

Program Report

97-P010

Long Term Resource Monitoring Program

Component Database Spatial Query Tool

User’s Guide

August 1997
The Environmental Management Technical Center issues LTRMP Program Reports

to provide Long Term Resource Monitoring Program partners

with programmatic documentation, procedures manuals, and annual status reports.

Environmental Management

Technical Center

CENTER DIRECTOR

Robert L. Delaney

GEOSPTIAL APPLICATIONS

ACTING DIRECTOR

Norman W. Hildrum

INFORMATION AND TECHNOLOGY

SERVICES ACTING DIRECTOR

Linda Leake

REPORT EDITOR

Mark V. Wenger

Cover graphic by Mi Ae Lipe-Butterbrodt

Mention of trade names or commercial products does not constitute endorsement

or recommendation for use by the U.S. Geological Survey, U.S. Department of the Interior.

Printed on recycled paper 
Long Term Resource Monitoring Program

Component Database Spatial Query Tool

User’s Guide

by

Douglas A. Olsen

August 1997

U.S. Geological Survey

Environmental Management Technical Center

575 Lester Avenue

Onalaska, Wisconsin 54650
Suggested citation:

Olsen, D. A. 1997. Long Term Resource Monitoring Program Component Database Spatial Query Tool User’s Guide.

U.S. Geological Survey, Environmental Management Technical Center, Onalaska, Wisconsin, August 1997. LTRMP 97-P010.

12 pp. + Appendix

Additional copies of this report may be obtained from the National Technical Information Service, 5285 Port Royal Road,

Springfield, Virginia 22161 (1-800-553-6847 or 703-487-4650). Also available to registered users from the Defense Technical

Information Center, Attn: Help Desk, 8725 Kingman Road, Suite 0944, Fort Belvoir, Virginia 22060-6218 (1-800-225-3842 or

703-767-9050).
iii

Contents

Page

Preface ......................................................................... v

Abstract ........................................................................ 1

Introduction ..................................................................... 1

The LTRMP Component Database Spatial Query Tool .................................... 1

Text Conventions ................................................................. 2

Starting the Query Tool ............................................................ 2

Overview of the Main Menu Bar ..................................................... 3

Using the LTRMP Component Database Spatial Query Tool ................................ 3

Reference ...................................................................... 12

Appendix ..................................................................... A-1

Figures

Figure 1. Select pool and component menu ............................................. 2

Figure 2. Pool 8 water quality—full pool view .......................................... 4

Figure 3. Subsetting sites by defining a polygon around the area of interest .................... 5

Figure 4. Entering a query to apply against the spatial subset of sites ......................... 6

Figure 5. Entering a background query ................................................ 7

Figure 6. Sites meeting background query criteria ........................................ 7

Figure 7. Entering primary query ..................................................... 8

Figure 8. Sites meeting background and primary query criteria .............................. 8

Figure 9. Output options menu ...................................................... 9

Figure 10. Selecting fields to be output to an ASCII file .................................. 10

Figure 11. Example of output ASCII file .............................................. 10

Figure 12. Using background settings options to display bathymetry and structures ............. 11
v

Preface

The Long Term Resource Monitoring Program (LTRMP) was authorized under the Water Resources

Development Act of 1986 (Public Law 99-662) as an element of the U.S. Army Corps of Engineers'

Environmental Management Program. The LTRMP is being implemented by the Environmental Management

Technical Center, a U.S. Geological Survey science center, in cooperation with the five Upper Mississippi

River System (UMRS) States of Illinois, Iowa, Minnesota, Missouri, and Wisconsin. The U.S. Army Corps

of Engineers provides guidance and has overall Program responsibility. The mode of operation and respective

roles of the agencies are outlined in a 1988 Memorandum of Agreement.

The UMRS encompasses the commercially navigable reaches of the Upper Mississippi River, as well

as the Illinois River and navigable portions of the Kaskaskia, Black, St. Croix, and Minnesota Rivers.

Congress has declared the UMRS to be both a nationally significant ecosystem and a nationally significant

commercial navigation system. The mission of the LTRMP is to provide decision makers with information

for maintaining the UMRS as a sustainable large river ecosystem given its multiple-use character. The longterm goals of the Program are to understand the system, determine resource trends and effects, develop

management alternatives, manage information, and develop useful products.

This report was prepared under Strategy 2.3.1 Multicomponent Syntheses of the Operating Plan

(USFWS 1993). This report was developed with funding provided by the Long Term Resource Monitoring

Program.
Long Term Resource Monitoring Program

Component Database Spatial Query Tool

User’s Guide

by

Douglas A. Olsen

Abstract

The Environmental Management Technical Center stores point-sampled data for fisheries, water quality, and

invertebrates in a relational database management system. These component data are collected at fixed and

random sites in each of the key Long Term Resource Monitoring Program (LTRMP) study Pools: 4, 8, 13, 26,

and the open river reach on the Mississippi River; and the La Grange Pool on the Illinois River. While the

geographic locations of these data are also collected and stored, the tools to automate the integration of these data

with spatial data had not previously been developed. A need existed for an application that would facilitate

spatial subsetting and visualization of the distribution of sites meeting certain query criteria. The LTRMP

Component Database Spatial Query Tool is a Unix-based ARC/INFO ARC Macro Language application that

integrates the component data with spatial data by taking advantage of the graphical display and query

capabilities of ARC/INFO. The application features an easy-to-use graphical user interface and requires limited

knowledge of Geographic Information Systems (GIS) to use effectively.

Introduction

The Environmental Management Technical Center (EMTC) stores point-sampled data for fisheries, water

quality, and invertebrates in a relational database management system. These component data are collected

at fixed and random sites in each of the key Long Term Resource Monitoring Program (LTRMP) study Pools:

4, 8, 13, 26, and the open river reach on the Mississippi River; and the La Grange Pool on the Illinois River.

While the geographic locations of these data are also collected and stored, the tools to automate the integration

of these data with spatial data had not previously been developed. A need existed for an application that would

facilitate spatial subsetting and visualization of the distribution of sites meeting certain query criteria.

The LTRMP Component Database Spatial Query Tool

The LTRMP Component Database Spatial Query Tool is a Unix-based ARC/INFO ARC Macro Language

application that integrates the component data with spatial data by taking advantage of the graphical display

and query capabilities of ARC/INFO. The application features an easy-to-use graphical user interface and

requires limited knowledge of Geographic Information Systems (GIS) to use effectively.

The user firstselects a study pool and component, zooms to the area of interest, then creates a spatial subset

of sampling sites by graphically defining a bounding box or polygon. Next, the user constructs a query and

applies it to the spatial subset of sites. Sites meeting or not meeting query criteria can be displayed on the

screen, output to a postscript file, or the records meeting query criteria can be output to an ASCII file.

Although the LTRMP Component Database Spatial Query Tool was designed to be as simple to use as

possible, it does require a fundamental understanding of GIS concepts and terminology. Also required is a

basic understanding of UNIX and the window manager on the host system, basic relational database theory,

and an understanding of the LTRMP component database structure and sampling methodologies. These topics

will not be covered in this user's guide. For a detailed description of the LTRMP component database structure
2

Figure 1. Select pool and component menu.

and sampling methodologies, see LTRMP Program Report 95-P002 "Long Term Resource Monitoring Program

Procedures."

Text Conventions

MAIN MENU BAR OPTIONS are in bold uppercase.

SUB-MENU OPTIONS are in uppercase.

Menu Titles have uppercase first letters.

Starting the Query Tool

To run the application, open a cmdtool window, move it to the lower right corner of the screen, and size

it to about 10 dialog lines. Change your directory to "[location_of_install]/integration". Start up ARC, then

type "&r start" to launch the application. All user output files will be written to the directory:

"[location_of_install]/integration/user_output" (Figure 1).
3

Overview of the Main Menu Bar

The POOL/COMPONENT button will bring up a menu for selecting the desired pool and component.

The ZOOM pull-down menu has options for:

ZOOM IN Zoom in by defining box.

ZOOM OUT Zoom out from center of current view area.

The MEASURE pull-down menu has options for:

DISTANCE Measure distance along a line.

AREA Measure area within polygon.

WHERE Get X and Y coordinate for location.

IDENTIFY List GIS grid code for sampling site.

The PERFORM QUERY button will bring up a menu of options for subsetting spatially, entering logical

expressions, and defining output options.

The BACKGROUND SETTINGS button will bring up a menu with options for selecting different

combinations of background themes.

The REFRESH button will redraw the current view.

The RESET button will reset the view extent to the entire currently selected pool and clear any spatial or

logical subsets.

The QUIT button will exit the application.

Using the LTRMP Component Database Spatial Query Tool

Using the application consists of six basic steps:

1. Select Pool and Component

2. Zoom to Area of Interest

3. Subset Sampling Sites Spatially

4. Build Logical Expression(s) to Apply to Spatial Subset of Points

5. Select Output Options

6. Submit Query

1. Select Pool and Component

The Pool/Component menu will be displayed at program startup. Select the desired pool and component

then click the Apply button. The selected pool will be drawn in the graphics display window and the pool

and component name will be displayed at the lower left. To change pool or component, use the

POOL/COMPONENT button from the main menu bar (Figure 2).
4

Figure 2. Pool 8 water quality—full pool view.

2. Zoom to Area of Interest

ZOOM IN and ZOOM OUT options are available from the ZOOM pull-down menu on the main menu bar.

When the ZOOM IN option is selected, you will be prompted from the cmdtool window to define a bounding

box for the area you wish to zoom in to. Use the first mouse button to indicate opposing box corners. The

ZOOM OUT option will zoom out from the center of the existing view. Use the RESET option from the main

menu bar to set the view extent back to the entire currently selected pool.

3. Subset Sampling Sites Spatially

Select the PERFORM QUERY option from the main menu bar. The Perform Query menu will be

displayed with options for subsetting spatially, entering a logical expression, and defining output options.

There are two icons under the "Subset Spatially" section; one for defining a bounding box, and one for

defining a bounding polygon. When the "box" icon is selected you will be prompted from the cmdtool window

to define a box around the sites you wish to include in the spatial subset. Use the first mouse button to indicate

opposing box corners. When the "poly" icon is selected, a text-based menu will be displayed in the cmdtool

window:

Define the polygon 

<1,2 to enter, 4 to remove last point, 5 to remove polygon, 9 to end> 
5

Figure 3. Subsetting sites by defining a polygon around the area of interest.

Use these options to define a polygon bounding the sites you wish to include in the spatial subset (Figure 3).

Note: The defined spatial subset will remain set until specifically changed. Closing the Perform Query menu,

using the Zoom options, or changing the logical expression will not alter the spatial subset.

4. Build Logical Expression(s) to Apply to Spatial Subset of Points

Select the "Build Logical Expression" icon from the Perform Query menu. The Build Logical Expression

menu will be displayed with options for building a logical expression to apply to the previously selected spatial

subset. A scrolling list of items from the selected component table will be displayed at the top of the menu.

Descriptions of the items can be viewed by selecting the "Describe" button. The "Add" button will add the

item name from the scrolling list to the logical expression entry line. The buttons for logical operators and

operands will add the respective operator or operand to the logical expression entry line (see Appendix for a

detailed description on building logical expressions). The "Clear" button will clear the logical expression entry

line. The Background Query section pertains to submitting and displaying two queries. See below section,

"The Background Query Option", for more information on this option (Figure 4).
6

Figure 4. Entering a query to apply against the spatial subset of sites.

After the desired logical expression has been entered into the logical expression entry line, select the

"Apply" button. Note: The defined logical expression will remain active until specifically changed from the

Build Logical Expression menu. Closing the Perform Query menu, using the Zoom options, or changing the

spatial subset will not alter the logical expression.

The Background Query Option

This option facilitates display of two queries, the second being a subset of the first. This option requires

that steps 4 and 6 be executed twice, once to create and save the background query subset of sites and once

to enter the secondary query that will be applied to the background query subset of sites. To display and

save a background query subset, enter the background query criteria on the logical expression line, select

the "Save as Background" option and proceed through steps 5 and 6 (Figures 5 and 6). To enter the

secondary query reopen the Perform Query Menu. The previously entered background query should be

listed as the "Current Background Query". Enter the desired logical expression to be applied against the

background query subset on the logical expression entry line, select the "Display Background Query" option

and proceed through steps 5 and 6 (Figures 7 and 8). Selecting "Off" in the Background Query Options

section will disable background query functionality. Use the checkbox labeled, "Draw Only Selected Sites",

to toggle the display of sites not in the background subset.
7

Figure 5. Entering a background query.

Figure 6. Sites meeting background query criteria.
8

Figure 7. Entering primary query.

Figure 8. Sites meeting background and primary query criteria.
9

Figure 9. Output options menu.

5. Select Output Options

If output options other than screen display are desired, select the "Output Options" icon from the Perform

Query menu. The Output Options menu will be displayed with choices for map output or ASCII file output.

Select one or both options and type in the desired filename without an extension (Figure 9). Output files will

be written to the user_output directory with the naming convention:

filename.ps (postscript file)

filename.txt (ASCII file)

If the "Output map" option from the Output Options menu was selected, a secondary Map Options menu

will be displayed. Select the desired map options and then select Apply. If the "Output ASCII file" option

from the Output Options menu was selected, a secondary ASCII file output menu will be displayed. Select

the items to be output to the ASCII file by highlighting the desired item in the scrolling list and selecting the

add button (Figure 10). Select the "Apply" button when done defining output fields. To output all items, leave

the output items entry line blank and select the "Apply" button. The ASCII file output will be commadelimited with character items enclosed in single quotes (Figure 11). Note: The defined output options will

remain set until specifically changed. Closing the Perform Query menu, using the Zoom options, changing

the spatial subset, or changing the logical expression will not alter the output options. Make sure to change

filenames between runs to prevent overwriting existing files.
10

Figure 10. Selecting fields to be output to an ASCII file.

Figure 11. Example of output ASCII file.
11

Figure 12. Using background settings options to display bathymetry and structures.

6. Submit Query

Select the "Apply" button from the Perform Query menu to apply the logical expression to the selected

spatial subset of sampling sites, update the screen display, and output any selected output file types. After the

program has executed, the screen will be refreshed, and the sampling sites meeting query criteria will be drawn

in red and annotated with the number of times the site has been visited and met query criteria. The sites not

meeting the query criteria will be drawn in white (Figure 6). If background query options have been set, the

sites in the background query subset will be drawn in green, the sites meeting secondary query criteria will be

drawn in red, and the sites not in the background query subset will be drawn in white. Note: When using

background themes other than Land/Water, the sites will be drawn in colors appropriate to the selected theme

(Figure 12).

Vegetation, land use, hydrology, and geomorphic characteristics vary significantly over the Upper

Mississippi River System study area. When integrated with monitoring data, spatial databases can assist

scientists in determining ecological status and trends and help explain causal relationships. This is the first in a

series of tools that will facilitate the full integration of LTRMP monitoring databases with the wealth of

spatial data now available for the Upper Mississippi River floodplain.
12

Reference

U.S. Fish and Wildlife Service. 1993. Operating Plan for the Upper Mississippi River System Long Term

Resource Monitoring Program. Environmental Management Technical Center, Onalaska, Wisconsin,

Revised September 1993. EMTC 91-P002R. 179 pp. (NTIS #PB94-160199)
A-1

Appendix

Building Logical Expressions

Logical expressions have three components:

Logical operands

Logical operators

Logical connectors

Logical operands include:

The name of a field in a database table (e.g., STREAMS-ID)

A constant numerical value (e.g., 10)

A character string in single quotation marks (e.g., 'HIGH')

Logical operators include:

= 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

CN Operand-1 contains the character string Operand-2

Character operands only (e.g., NAME CN 'MAIN')

NC Operand-1 does not contain the character string Operand-2

Character operands only (e.g., NAME NC 'MAIN')

IN Operand-1 is in the specified set of constants or character

expressions.

(e.g., CLASS IN [3,4,7,8] or TEXT IN ['a','b','c','d'])

Logical connectors include:

AND For the condition to be evaluated as true, the logical expressions on both sides of the AND must

be true.

OR For the condition to be evaluated as true, the logical expression on one or the other side of the

“OR” must be true. The condition will also be evaluated as true if both logical expressions are

true.

The simplest logical expressions take the following form:

<operand-1> <logical operator> <operand-2>

For example:

CLASS < 8
A-2

Up to eight logical expressions of this simple form can be combined to form more complex expressions

by using logical connectors; for example:

CLASS >= 2 AND CLASS <= 8 OR SUIT = 5

All logical operators and connectors have equal precedence. Operations are performed in sequence from

left to right. However, parentheses can be used to request that logic within parentheses have the highest

precedence.

NOTE: Each element of a logical expression (i.e., operand, logical operator, logical connector, parentheses)

must be separated by blanks.
REPORT DOCUMENTATION PAGE Form Approved

OMB No. 0704-0188

Public reporting burden for this collection of information is estimated to average 1 hour per response, including the time for reviewing instructions, searching existing

data sources, gathering and maintaining the data needed, and completing and reviewing the collection of information. Send comments regarding this burden estimate or

any other aspect of this collection of information, including suggestions for reducing this burden, to Washington Headquarters Services, Directorate for Information

Operations and Reports, 1215 Jefferson Davis Highway, Suite 1204, Arlington, VA 22202-4302, and to the Office of Management and Budget, Paperwork Reduction

Project (0704-0188), Washington, D.C. 20503

1. AGENCY USE ONLY (Leave blank) 2. REPORT DATE 3. REPORT TYPE AND DATES COVERED

August 1997

4. TITLE AND SUBTITLE 5. FUNDING NUMBERS

Long Term Resource Monitoring Program Component Database Spatial Query Tool User’s Guide

6. AUTHOR(S)

Douglas A. Olsen

7. PERFORMING ORGANIZATION NAME AND ADDRESS 8. PERFORMING ORGANIZATION

 REPORT NUMBER

9. SPONSORING/MONITORING AGENCY NAME(S) AND ADDRESS(ES) 10. SPONSORING/MONITORING

U.S. Geological Survey 

Environmental Management Technical Center 97-P010

575 Lester Avenue

Onalaska, Wisconsin 54650

 AGENCY REPORT NUMBER

11. SUPPLEMENTARY NOTES

12a. DISTRIBUTION/AVAILABILITY STATEMENT 12b. DISTRIBUTION CODE

Release unlimited. Available from National Technical Information Service, 5285 Port Royal Road, Springfield, Virginia

22161 (1-800-553-6847 or 703-487-4650. Available to registered users from the Defense Technical Information Center,

Attn: Help Desk, 8725 Kingman Road, Suite 0944, Fort Belvoir, Virginia 22060-6218 (1-800-225-3842 or 703-767-

9050).

13. ABSTRACT (Maximum 200 words)

The Environmental Management Technical Center stores point-sampled data for fisheries, water quality, and invertebrates in a relational database management system. 

These component data are collected at fixed and random sites in each of the key Long Term Resource Monitoring Program (LTRMP) study pools: 4, 8, 13, 26, and the

open river reach on the Mississippi River; and the La Grange Pool on the Illinois River. While the geographic locations of these data are also collected and stored, the

tools to automate the integration of these data with spatial data had not previously been developed. A need existed for an application that would facilitate spatial

subsetting and visualization of the distribution of sites meeting certain query criteria. The LTRMP Component Database Spatial Query Tool is a Unix-based

ARC/INFO ARC Macro Language application that integrates the component data with spatial data by taking advantage of the graphical display and query capabilities

of ARC/INFO. The application features an easy-to-use graphical user interface and requires only very basic knowledge of Geographic Information Systems (GIS) to use

effectively.

14. SUBJECT TERMS 15. NUMBER OF PAGES

Component database, GIS, spatial query, visualization 12 pp. + Appendix

16. PRICE CODE

17. SECURITY CLASSIFICATION 18. SECURITY CLASSIFICATION 19. SECURITY CLASSIFICATION 20. LIMITATION OF ABSTRACT

 OF REPORT OF THIS PAGE OF ABSTRACT

Unclassified Unclassified Unclassified
The Long Term Resource Monitoring Program (LTRMP) for the Upper Mississippi

River System was authorized under the Water Resources Development Act of 1986 as 

an element of the Environmental Management Program. The mission of the LTRMP

is to provide river managers with information for maintaining the Upper Mississippi 

River System as a sustainable large river ecosystem given its multiple-use character.

The LTRMP is a cooperative effort by the U.S. Geological Survey, the U.S. Army Corps

of Engineers, and the States of Illinois, Iowa, Minnesota, Missouri, and Wisconsin.