vxEPA
United States
Environmental Protection
Agency
Watershed Health Assessment
Tools Investigating Fisheries
WHAT IF Version 2: A
Manager's Guide to New
Features
RESEARCH AND DEVELOPMENT
-------
EPA/600/R-06/109
September 2006
Watershed Health Assessment Tools
Investigating Fisheries
WHAT IF Version 2
A Manager's Guide to New Features
Ecosystems Research Division
National Exposure Research Laboratory
Athens, GA 30605-2700
U.S. Environmental Protection Agency
Office of Research and Development
Washington, DC 20460
-------
Notice
The research described in this document was funded by the U.S. Environmental
Protection Agency through the Office of Research and Development. The research described
herein was conducted at the Ecosystems Research Division of the U.S. Environmental Protection
Agency National Exposure Research Laboratory in Athens, Georgia. Mention of trade names or
commercial products does not constitute endorsement or recommendation for use. Although this
work was reviewed by EPA and approved for publication, it may not necessarily reflect official
Agency policy.
Acknowledgments
Numerous people contributed administrative, database, software development, and
technical support to this project. Special thanks are given to Benjamin Daniel (CSC, Athens
GA), Ronald Beloin (CSC, Athens GA), Michael Galvin (CSC, Athens GA), Stephen Alberty
(CSC, Athens GA), Tom DeMoss (USEPA/Region 3 and CVI), Paul Kinder (CVI), Jennifer
Newland (CVI), Ron Preston (CVI) and Louis Reynolds and Margaret Passmore
(USEP A/Region III). We also thank Robert Swank for editorial review of this report.
-------
Foreword
Streams and rivers provide important ecological services, including habitat for fishes and
other organisms, and drinking water supplies, yet these ecosystems are among the most impaired
across the country. Management of these ecosystems involves the assessment of probable causes
of impairments and remediation/restoration alternatives, as well as forecasting future condition in
a scientifically defensible fashion to more effectively protect valued ecosystems. Communities,
watershed groups and states require decision support tools for managing the quality of aquatic
systems. Community-based environmental management is a long-term goal of the Agency, and
providing the methods/tools and technical transfer mechanisms to achieve this goal are critical to
the role of ORD. Effective client collaborations are the most efficient means to achieve this
result.
This report is the result of collaboration with the Canaan Valley Institute (CVI) in which
a decision analysis toolkit was produced in order to support management of fisheries in the Mid-
Atlantic Highlands. Although there are many ecological endpoints that are important indicators
of the condition of aquatic communities and their associated watersheds, fish health is arguably
one of the most important, since fishability is a principal designated use for surface waters under
the Clean Water Act. The approach used here can be applied to aid CVI and other agencies in
the management of aquatic resources in the Mid-Atlantic Highlands, and may serve as a model
for management tools for aquatic systems in other regions.
Eric J. Weber, Ph.D.
Acting Director
Ecosystems Research Division
Athens, Georgia
in
-------
Abstract
The Canaan Valley Institute (CVI) is dedicated to addressing the environmental problems
in the Mid-Atlantic Highlands (MAH). Their goal is to develop and implement solutions to
restore damaged areas and protect aquatic systems. In most wadeable streams of the Mid-
Atlantic Highlands region of the eastern United States, habitat alteration resulting from
agriculture and development is the primary stressor for fish communities. Sedimentation is the
primary source of habitat degradation in Highlands's streams, and productive, sustainable
fisheries, i.e., trophy trout streams, are among the valued aquatic endpoints. Planned restoration
activities in the region include riparian zone restoration and stream channel design to mitigate
near stream inputs and stabilize streambanks. Natural Stream Channel Design (NSCD) is also
being investigated by CVI for further optimization of instream habitats for fish communities.
Models that predict the responses offish and stream insect populations and communities to key
habitat characteristics are necessary for CVT's watershed management goals, both for
determining where to restore and how, as well as evaluating the most probable outcome of
various alternatives. The USEPA National Exposure Research Laboratory (NERL) has
developed a suite of modeling tools to be used for this purpose. The CVI Watershed Health
Assessment Tool Investigating Fisheries, WHAT IF version 2, currently contains five
components: 1) a Regional Prioritization Tool for identifying areas for riparian restoration and
aquaculture; 2) a Hydrologic Tool for predicting hydrologic inputs to streams and bankfull flow;
3) a Clustering Tool for predicting fundamental fish assemblages; 4) a Habitat Suitability Tool
for both fishes and benthic insects to relate habitat measures to the suitability of streams for
specific species; and 5) the BASS (Bioaccumulation and Aquatic Systems Simulator) model, a
generalized aquatic ecosystem simulator to examine fish community dynamics over time. This
document describes enhancements to WHAT IF v. 1 based on feedback from state managers,
specifically regional stream prioritization, bankfull flow and aquatic insect habitat suitability and
contains appendices with software documentation. The USEPA Environmental Monitoring and
Assessment Program (EMAP) surface water dataset is the basis of the habitat associations
derived among fish and insect species and communities.
IV
-------
Table of Contents
Acknowledgments ii
Foreword iii
Abstract iv
1 Introduction 1
2 Regional Prioritization Tools for Riparian Restoration and Aquaculture 9
2.1 Introduction 9
2.2 Which Highlands streams are most suitable for restoration? 10
2.3 Which Highlands streams are most suitable for aquaculture? 13
3 Hydro Tool: Predicting Bankfull Flow and Geometry for Small Streams 17
3.1 Introduction 17
3.2 What do flood conditions look like in my stream? 18
3.3 Adding anew stream/site record to the database 21
4 Habitat Suitability and Macroinvertebrates 27
4.1 Introduction to habitat suitability assessment and benthic indices 27
4.2 What is the quality of the benthic macroinvertebrate community of my stream? 27
4.3 What is the quality of the benthic macroinvertebrate community of my stream? (continued) 35
References 41
Appendix A 43
Appendix B Overview 43
Appendix C 45
Appendix D How WHAT IF Works 45
Appendix E Software Architecture 46
Appendix F Database Schema 80
Appendix G External Text Files 82
-------
List of Figures
Figure 1-1 Mapping interface included with WHAT IF version 2 4
Figure 1-2 Location of EMAP sample sites within the Mid-Atlantic Highlands region 6
Figure 1-3 Highlighted questions new to WHAT IF v.2 covered in this document 8
Figure 2-1 Regional query tool for riparian restoration with default parameters 10
Figure 2-2 Regional query tool for riparian restoration with constrained parameters 11
Figure 2-3 Regional display of query results, showing map controls and data table 12
Figure 2-4 Options for selecting streams of interest in map selection window 13
Figure 2-5 Regional query tool for aquaculture 14
Figure 2-6 Regional query tool showing existing trout streams 15
Figure 2-7 Regional query tool showing streams with species of concern 16
Figure 2-8 Map selection window showing streams with species of concern returned from query tool.... 17
Figure 3-1 Hydrology bankfull tool wizard 19
Figure 3-2 Map selection window for stream identification and selection 20
Figure 3-3 Map selection window for Teter Creek, WV 21
Figure 3-4 Stream data Stream data record window 23
Figure 3-5 Stream data record window with new site name action 24
Figure 3-6 Stream data record tools tab window 25
Figure 3-7 Hydrology bankfull tool 26
Figure 4-1 Stream wizard tool forbenthic stream selection 28
Figure 4-2 Map selection window with default extent 29
Figure 4-3 Map selection window showing Teter Creek and duplicate name 29
Figure 4-4 Map selection window showing details of Teter Creek 30
Figure 4-5 Stream data record for Teter Creek with default General information tab 31
Figure 4-6 Stream data record for Teter Creek showing Habitat and Landscape tab 32
Figure 4-7 Stream data record showing Biota tab and associated data fields 33
Figure 4-8 Stream data record location tab 34
Figure 4-9 EPT habitat suitability tool 35
Figure 4-10 EPT tool showing explanatory fields and value outside accepted range 36
Figure 4-11 EPT tool with predicted value and confidence limits 37
Figure 4-12 MBII calculator 38
Figure 4-13 MBII calculator with trial selected 39
Figure 4-14 Benthictool wizard showing completion of all steps 40
vi
-------
Figure A. 1 A simplified view of the WHAT IF database of stream site data. A location can have one or
more sampling events, and a sampling event one or more groups of data values 44
Figure A. 2 Overall component organization 47
Figure A. 3 WHAT-IF Database tables illustrating the relationships for an example SQL query 55
Figure A. 4 Trial Importer allows the calculator to choose which inputs to import 63
Figure A. 5 The calculator has successfully imported the stream depth figure from the Hydrology
calculator 63
Figure A. 6 Form after inheriting from Calculator 67
Figure A. 7 A form with two textboxes and a label 67
Figure A. 8 A form that is data bound. The trial name appears in the trial control manager 69
Figure A. 9 Diagram of the backend dataset for My Calculator 74
Figure A. 10 MyCalculator with data in controls 74
vn
-------
List of Tables
Table 1 Calculators and their respective trial counts 45
Table 2 CVI database tables and their descriptions 51
Table 3 CVI database tables by category 53
Table 4 Important data fields and their sizes and/or values 54
Table 5 Descriptions of the different columns in trial datum. Note: only the most relevant columns are
covered here 60
Vlll
-------
List of Acronyms
BASS Bioaccumulation and Aquatic System Simulator
BMP Best Management Practice
CVI Canaan Valley Institute
EMAP Environmental Monitoring and Assessment Program
EPT Ephemeroptera, Plecoptera and Trichoptera
ERD Ecosystems Research Division
GIS Geographic Information System
GUI Graphical User Interface
HSI Habitat Suitability Index
IBI Index of Biological Integrity
MAH Mid-Atlantic Highlands
MBII Macrobenthic Invertebrate Index
NERL National Exposure Research Laboratory
REMAP Regional EMAP
USEPA U.S. Environmental Protection Agency
USGS U.S. Geological Survey
IX
-------
1 Introduction
The mission of the Canaan Valley Institute (CVI) is to address the environmental
problems in the Mid-Atlantic Highlands through a program of environmental stewardship that
considers and integrates natural, economic, and human concerns in the management of natural
resources (CVI 2002). To accomplish this mission requires sound science that combines
theory, detailed knowledge, monitoring and modeling (CVI 2002). CVI developed its own
geographic information system (GlS)-based management tool, Landscape Analyst, using the
proprietary Arc View GIS (ESRI, Redlands, CA), for estimating land use change impacts on
water quantity and quality (http://www.canaanvi.org/). However, the adoption and widespread
application of that tool has been less than anticipated. In order to access Landscape Analyst,
users must posses the required GIS software and utilities, and be familiar with GIS software
and its operation. Complexity of the software user interface, as well as the supporting science
modules, is a barrier to widespread adoption of the tool. Refining their aquatic assessment
needs further, CVI posed these questions on behalf of regional stakeholders: What conditions
will sustain aquatic endpoints in the long-term? Can we evaluate restoration techniques like
natural stream channel design (NSCD) and agriculture and forestry stream best management
practices (BMPs) for their effectiveness in improving aquatic endpoints? Can we create "what
if scenarios and evaluate management actions based on the response of aquatic endpoints?
In support of CVI, the USEPA National Exposure Laboratory's Ecosystem Research
Division (ERD) has conducted research to develop watershed modeling tools for CVI and
their stakeholders in the Mid-Atlantic Highlands. Specifically, enhancements have been made
to WHAT IF version 2 that provide the information and methods required for additional
aquatic ecosystem assessments, including:
• regional stream prioritization for restoration and aquaculture (Regional Exploration)
• addition of state monitoring data (WV REMAP)
• predicting flood conditions in ungauged/unmeasured streams of interest (Hydro Tool)
• the evaluation of the physical habitat variables that affect suitability for fish and insect
species and families/guilds to specify restoration and BMP actions for selected species
(Habitat Suitability and Macroinvertebrates Calculator)
-------
The result is a suite of tools packaged for regional application to wadeable Mid-Atlantic
Highlands streams, the Canaan Valley Institute - Watershed Health Assessment Tools
Investigating Fisheries, CVI-WHAT IF. WHAT IF version 2 is entirely open source and does
not require proprietary software. It is not GIS-based, nor does it contain models difficult to
apply for all but expert users. The models are complementary to the information provided,
and are designed to be efficient and problem focused. For example, WHAT IF incorporates
statistical hydrology in the toolset rather than a watershed hydrology model. Models that
require a high degree of input data processing and model setup put undue burden on novice
users. A fitted parameter model such as the Hydrologic System Program Fortran (HSPF),
though widely used, is complex and calibration intensive, and does not match the aquatic
assessment needs for evaluating outcomes of near and instream restorations. Similarly,
models with a large degree of overhead in their use, including sediment transport and
hydrodynamic models are unsuitable for the majority of CVI stakeholders and are not
straightforward in application. Instead, the emphasis is on the needs of managers for regional
mapping and visualization, while also providing local models for stream assessment and
management for scenario development.
Figure 1-1 illustrates the mapping interface, with highlighted areas displaying the
various map controls, selection buttons and query tools. As supported by WHAT IF v. 1, all
streams can be selected within a state or county of interest through selection of the pulldown
list. All streams can also be queried by entering text in the appropriate box and selecting the
field of interest from the database in the pulldown list (e.g., stream name, dominant species,
or data source). The map window, legend, selection button, map controls, information
window and approximate scale have been added in separate frames that can be increased or
decreased as needed. For example, the table listing the stream attributes is expandable by
selecting and moving the area below the scroll bar. Map features in the legend can be turned
on and off as needed, with the default to automatically display all features at the appropriate
map scale. Both individual and multiple streams are selected using the map selection button
by 'lassoing' map features.
In the example shown in Figure 1-1, Little Kanawha Creek was selected using the map
selection tool. This is indicated by the red circle outlining the selected stream, in addition to
the attributes displayed in the information window. Regardless of the method of query
-------
utilized, the total number of streams that satisfy the search criteria or are selected by the map
selection tool is displayed in the lower left-hand corner of the interface. The approximate
scale is displayed as "approximate map height," and the geographic position of the cursor is
continuously updated by the latitude and longitude coordinates at the bottom on the map
window. The commonly used map controls are provided for map navigation as radio buttons
as well, including zoom in and out, zoom (return) to full actual extent, panning and labeling.
Panning is useful for adjusting the map in any direction to bring features to the center of the
window, scrolling across familiar terrain, and following streams and other line features such
as railroads and road networks that cross streams. The label tool permits the query of any map
feature, with the result displayed in the information window. Multiple streams that have been
selected by text query or map selection are highlighted in blue in the attribute table. Streams
can be deselected from the attribute table by deletion (the "delete" key on the keyboard),
though it is not possible at this time to delete multiple stream entries. Double clicking a
stream entry in the attribute table serves to select that stream for WHAT IF tool use and
scenario development. The button labeled "Open Little Kanawha Creek" also selects the
stream of interest, with the added benefit that the stream name displayed on the button is the
currently selected stream that will be used in the following assessment and management
scenarios.
-------
Figure 1-1 Mapping interface included with WHAT IF version 2.
tJSite Search
State County Data Source Dominant sp mass Dominant sp count Game Impacted Degradation I Location ID
* Remapsites
Emapsites
* Airports
Cities
Dams
# Gauges
Roads
"w Railroads
"i/ Rivers
Streams
D Urbanareas
Huc16
Huc8
Counties
Physio
V D States
latitude: 38.65333 deg
longitude: -80.5565 deg
Remapsites: Little
Kanawha Creek
States: West Virginia
.
«" " "l /
: •-•*:/-:•-•
:-v ' .
** •..*«' •• •* • •
• •»..;,. *
Open Little Kanawha Creek (50302030103.2001.1 on 2001-09-17
| Approximate map height: 1416 km
Lat: r 39.75399 deg '' Lon: ' -30.13433 deg
-------
WHAT IF consists of models of varying complexity, suited to the problem needs of
the manager rather than imposing a 'one model fits all needs' restriction. In this manner, the
toolset is question driven as opposed to model-focused. WHAT IF is stream-based, permitting
users to evaluate streams of interest for outcomes of management approaches and specific
restoration actions. Habitat quality and aquatic ecosystem response models have been linked
to a regional hydrologic model that simulates habitat characteristics (e.g., water depth, current
velocity and water temperature) that determine the survival, reproduction, and recruitment of
fish and aquatic invertebrates. To facilitate the use and application of these models, graphical
user interfaces (GUI), supporting databases, and libraries of management scenarios were
developed. The Canaan Valley Institute (CVI) software/toolset is a stream-based decision
support tool that is object-oriented in design and easily maintained. Ultimately, what has been
developed, using available data collected by the USEPA Environmental Monitoring and
Assessment Program (EMAP), is a framework based on the biogeography offish suitable for
applying all models for regional assessments of important fish health issues in the Mid-
Atlantic Highlands.
The EMAP surface water dataset (http://www.epa.gov/nheerl/arm/) is a stratified,
random sampling design containing over 300 sites and 600 samples (multiple site visits) used
in the final research product (Figure 1-2). Fish count data were pre-processed by conversion
to biomass for both clustering and habitat suitability analyses, using a utility model associated
with the BASS model. In this manner, actual communities were derived from the base data so
that conclusions could be made with respect to biomass per unit area and ultimately the
carrying capacity of impaired versus restored streams. The EMAP dataset represents two
index periods (1993-1995 and 1997-1998).The habitat associations are statistically valid
within the region of the Mid-Atlantic Highlands, which includes portions of Pennsylvania,
Maryland, West Virginia and Virginia. Watershed characteristics, including stream and near
stream habitat quantitative data, are used in addition to fish, benthic insect, and periphyton
data (i.e., attached algae). Field data were collected from late Spring to late Summer and span
a range of stream sizes and watershed areas throughout the Highlands. In addition to the
EMAP streams, WHAT IF v.2 includes Regional EMAP (REMAP) data from the state of
West Virginia, adding data from over 100 streams collected in the index periods 2001-2002.
-------
Figure 1-2 Location of EMAP sample sites within the Mid-Atlantic Highlands region.
• — — ^ •
. " • PA" •«••*»"
r/-\ . •••••• » ••*
_ * * " • w—
%••
300 Kilometers
The overall flow of information between the various models is as follows. The
Hydrology Tool supplies stream conditions for sites in the WHAT IF database, as well as a
means of calculating conditions (mean monthly flow, bankfull flow, depth and water
temperature) for new streams of interest. The Clustering Tool provides a means of assigning
the most probable fish community to a stream based on combinations of remotely sensed and
field collection data. This tool also permits a user to perform simple screening level
assessments of habitat changes (e.g., water flow, temperature and percent fines) and the
probable changes in the assigned stream community. After identifying a stream of interest and
assigning a fish community, the Habitat Suitability Calculator is used to evaluate that stream's
potential to support members of the potential community, on a fish-by-fish basis. Habitat
features of interest are those that relate to key aspects offish life history requirements: water
velocity, water depth, stream bottom composition (substrate) and amount of refugia (cover,
riparian vegetation, etc.). The BMP tool that is part of the Suitability Calculator provides a
means of translating habitat changes (i.e., restoration actions under consideration) into
suitability scores for each fish in the community. BASS is a generalized aquatic ecosystem
-------
simulator capable of assessing a variety of managed freshwater ecosystems. Here it accepts
the assigned fish community properties as initial conditions, as well as any habitat multipliers
investigated using the BMP tool. Stream depth and temperature information is also passed
from the Hydrology Tool to BASS. With these tools, environmental managers are better able
to characterize and quantify relationships between stressors and stream responses for valued
ecological resources in a manner that supports diagnosis of current condition and assists in
management activities. Examples will be provided for the new Tools included in WHAT IF
v.2 in the following sections (Figure 1-3).
-------
Figure 1-3 Highlighted questions new to WHAT IF v.2 covered in this document.
View Window Help
Regional Exploration Questions
Which Highlands streams are most
suitable for riparian restoration?
Which Highlands streams are most suitable for
aquaculture?
What is the average flow depth, streamflow,
and water temperature in my stream?
What do flood conditions look like in my stream?
Clustering
What fishes might I have in my stream?
How might species composition change with
changes in watershed and stream characteristics?
Habitat Suitability and Macroinvertebrates
How suitable is my stream for specific fish species?
How might suitability be improved for
these species?
How does suitability change with BMP s for
these species?
What is the quality or the benthic
macroinvertebrate community of my stream?
How do I start the model, and what does a 5
year simulation show for a stream site?
How does the proportion of trophy-sized trout
change with various stocking programs?
About Guided Step Questions
-------
2 Regional Prioritization Tools for Riparian Restoration and Aquaculture
2.1 Introduction
Regionwide evaluation of ecological resources is an assessment need of managers and
is outlined in the CVI Highlands Action Plan (CVI, 2002). The general approach described
for the Highlands is that management efforts are best applied where political, economic,
social and ecological factors all coincide in support of optimal watersheds for restoration. In
WHAT IF, the combination of a mapping interface with a query tool of the relational database
provide for a wide range of assessments. Stream selection for aquaculture is another example
application of this approach and will be covered in the following section.
Streams selected for restoration are those expected to respond best to this management
action based on a combination of ecological (stream habitat and biology) and watershed
parameters. Riparian restoration improves stream habitat condition by:
• decreasing sediment delivery
• decreasing nutrient delivery
• decreasing stormflow (overland runoff)
• decreasing stream temperature
These parameters have been identified in the EMAP data or translated where possible to a
suitable surrogate. For example, sedimentation is translated to percent fines, whereas bank
slope, stream width and the percent of stream length consisting of pools are used directly.
Figure 2-1 illustrates the query interface and lists the additional landscape parameters, i.e., the
percentages of the watershed that are forested and urbanized, as well as the extent of the
riparian zone (percent of the stream reach) that are also used.
-------
2.2 Which Highlands streams are most suitable for restoration?
Figure 2-1 Regional query tool for riparian restoration with default parameters.
HJCVI-WHATIF?
Data View Window Help
Regional Query
Which Highlands streams are most suitable for riparian restoration?
For the streams that are in the WHAT IF database, identify streams for which the following
variables fall within the indicated ranges.
Include
!*'
% watershed forested
% watershed urbanized
% of reach with riparian
cover
stream width (m)
% of reach with pools
bank slope (degrees)
% fine substrate
Minimum Maximum
100
25
fo
Q
o
[go
[20
[70
[50
[50
Results
N umber of S (reams by S late
State N
DE 0
MD 8
NC 2
NJ 1
NY 8
PA 90
VA 51
WV
157
Found 318
out of 558 sites with data.
Show Sites on Map
« 8ack to Questions «
Prior to displaying the regional map produced by the query parameters, the number of
streams present in each state that satisfy the query are calculated. Managers can then change
the query parameters further to either widen or constrain the number of streams returned.
10
-------
Figure 2-2 shows the result of a more constrained query with the associated streams in each
state.
Figure 2-2 Regional query tool for riparian restoration with constrained parameters.
tfcj CVI - WHAT IF?
Data View Window Help
Which Highlands streams are most suitable for riparian restoration?
For the streams that are in the WHAT IF database, identify streams for which the following
variables fall within the indicated ranges.
% watershed forested
watershed urbanized
% of reach with riparian
cover
stream width (m)
% of reach with pools
bank slope (degrees)
%fine substrate
Show Sites on Map
« Back to Questions «
Note that the "Show Sites" button can be used to rerun the query when cells are
updated as well as to return the resulting regional map. Selected streams are displayed in
turquoise in Figure 2-3. The map interface includes map controls, automatic display of
11
-------
additional map layers when the appropriate map scale is selected and the stream data
displayed in a data table that can be sorted by clicking on the column labels. In this example,
streams have been sorted by state, inverting the order of the sort by double-clicking the label.
Streams can be sorted by any of the column labels.
Figure 2-3 Regional display of query results, showing map controls and data table.
iW Site Search
Showing streams with the criteria: % watershed forested between 50 and 90; % watershed urbanized between 0 and 10; % fine substrate
between 0 and 50; fraction of reach with riparian cover between 0 and 50; % of reach with pools between 0 and 70; width of stream (m)
between 0 and 20; bank slope (degrees) between 0 and 50
ID I Modified
EH 3/22/2006
770
782
794 3/22/2006
Teter Creek Teter Creek Flood region=North
3/22/2006 North Fork Cross Cree North Fork Cro Flood region=North REMAP W Brooke
3/22/2006 Harmon Creek Harmon Creek Flood region=North REMAP WV Brooke
GirtyRun GirtyRun Flood region=North REMAP WV Brooke
Data Source Dominant sp mass Domin-
50200010802 (null) River
50301010807 (null) Striped
50301010801 (null) Mottlec
50301060202 (null) Central jrj
>]
106 records found
Open I
Approximate map height: 1149 km Lat:
Lon:
Cancel
EMAP and WV REMAP streams in the database are displayed according to the legend
shown in the left hand frame of Figure 2-3, in addition to the streams returned from the query.
Note that the text describing the current query is displayed at the top of the window until
removed from view by clicking on the "X" in the top right corner. Figure 2-4 illustrates the
three options for selecting a stream of interest from the map interface: 1) double-clicking the
stream row in the table, 2) clicking the Map Selection button and drawing a lasso around the
stream in the map window, or 3) clicking the Open button once a stream has been selected in
12
-------
the table. Note that using the Map Select feature can be challenging if used at a small map
scale, since streams and other features that are proximate can be selected unintentionally.
Figure 2-4 Options for selecting streams of interest in map selection window.
iW Site Search
State and County
00®
Database Search
| Record
T| | < search texrj
Data Source Dominant sp mass Domin
Modified
Record
Stream
T eter Creek Flood reaion=N orth
3/22/2006 North Fork Cross Cree North Fork Cro Flood region=North REMAP WV Brooke
3/22/2006 Harmon Creek Harmon Creek Flood region=N orth REMAP WV Brooke
3/22/2006 GirtjRun GirtyRun Flood region=North REMAP WV Brooke
50301010807 (null)
50301010801 (null)
50301060202 (null)
Striped
Mottlec
Central •
•J"
...... ..
!. /.*. I/,.':
106 records found
Approximate map height: 1149 km Lat:
2.3 Which Highlands streams are most suitable for aquaculture?
Similar to the approach taken for riparian restoration, regional stream evaluation for
fisheries management involves query operations to the relational database of parameters
directly related to carrying capacity for sport fishes: insect productivity and dissolved oxygen
(Figure 2-5). In addition, streams can also be displayed and queried in the region containing
at least one species of trout (Figure 2-6). Because of concerns to threatened and endangered
species (both state and federally listed), managers can include or exclude streams containing
species in these groups in the assessment (Figures 2-5 and 2-7).
13
-------
Figure 2-5 Regional query tool for aquaculture
Regional Query
Which Highlands streams are most suitable for aquaculture?
For the streams that are in the WHAT IF database, identify streams that are existing trout streams,
or, where the following variables fall within the indicated ranges, and that have no threatened or
endangered species present.
Include
Minimum
Maximum
benthic invertebrate
biomass (ind/mA2)
dissolved oxygen (mg/l)
Include all streams that have trout (Rainbow, Brown, or Brook)
present, regardless of criteria above
Exclude streams with these threatened or endangered species
American Brook lamprey (Lampetra appendix)
Black Sculpin (Cottus baileyi)
Blackside Darter (Percina maculata)
Bridle Shiner (Notropis bifrenatus)
Candy Darter (Etheostoma osburni)
Emerald Shiner (Notropis atherinoides)
Gilt Darter (Percina evides)
Longear Sunfish (Lepomis rnegalotis)
Mirror Shiner (Notropis spectrunculus)
Mountain Brook lamprey (Ichthyomyzon greeleyi)
Potomac Sculpin (Cottus girardi)
Rainbow Darter (Etheostoma caeruleum)
Results
Number of Streams by State
State
DE
MD
NC
NJ
NY
PA
VA
WV
Found 112
out of 51 5
2
7
67
14
15
sites with data.
Show Sites on Map
« Back to Questions «
14
-------
Figure 2-6 Regional query tool showing existing trout streams.
H
Regional Query
Which Highlands streams are most suitable for aquaculture?
For the streams that are in the WHAT IF database, identify streams that are existing trout streams,
or, where the following variables fall within the indicated ranges, and that have no threatened or
endangered species present.
Include
Minimum
Maximum
Results
benthic invertebrate
bio mass (ind/rn"2)
dissolved oxyqen (rng/T)
elude all streams that have trout (Rainbow, Brown, or Brook)
/esent, regardless of criteria above
Exclude streams with these threatened or endangered species
American Brook lamprey (Lampetra appendix)
Black Sculpin (Cottus baileyi)
Blackside Darter (Percina maculata)
Bridle Shiner (Notropis bifrenatus)
Candy Darter (Etheostoma osburni)
Emerald Shiner (Notropis atherinoides)
Gilt Darter (Percina evides)
Longear Sunfish (Lepomis megalotis)
Mirror Shiner (Notropis spectrunculus)
Mountain Brook lamprey (Ichthyornyzon greeleyi)
Potomac Sculpin (Cottus girardi)
Rainbow Darter (Etheostoma caeruleum)
N umber of S (reams by S tale
State
> DE
MD
NC
NJ
NY
PA
VA
WV
N
0
12
0
0
9
136
29
46
n
:nd 232
of 798
sites with data.
Show Sites on Map
« Back to Questions «
15
-------
Figure 2-7 Regional query tool showing streams with species of concern.
H
Regional Query
Which Highlands streams are most suitable for aquaculture?
For the streams that are in the WHAT IF database, identify streams that are existing trout streams,
or, where the following variables fall within the indicated ranges, and that have no threatened or
endangered species present.
Include
Minimum
Maximum
Results
benthic invertebrate
bio mass (ind/rn"2)
dissolved oxyqen (rug/I)
t— Include all streams that have trout (Rainbow, Brown, or Brook)
present, regardless of criteria above
kclude streams with these threatened or endangered species
American Brook lamprey (Lampetra appendix)
Black Sculpin (Cottus baileyi)
Blackside Darter (Percina maculata)
Bridle Shiner (Notropis bifrenatus)
Candy Darter (Etheostoma osburni)
Emerald Shiner (Notropis atherinoides)
Gilt Darter (Percina evides)
Longear Sunfish (Lepomis megalotis)
Mirror Shiner (Notropis spectrunculus)
Mountain Brook lamprey (Ichthyornyzon greeleyi)
Potomac Sculpin (Cottus girardi)
Rainbow Darter (Etheostoma caeruleum)
N umber of S (reams by S tale
State
> DE
MD
NC
NJ
NY
PA
VA
WV
N
2
27
19
3
17
153
163
95
n
sites with data.
Show Sites on Map
« Back to Questions «
Returning to the example of the default query, clicking "Show Sites" in Figure 2-5
results in the map in Figure 2-8, with the extent of the map window automatically set to the
subset of streams satisfying the search criteria.
16
-------
Figure 2-8 Map selection window showing streams with species of concern returned
from query tool.
ft-J Site Search
ESS
Showing streams with the criteria: trout are present or benthic invertebrate biornass (ind/m"2) is between 5000 and 15000; dissolved
oxygen (mg/l) is between 5 and 16; no threatened or endangered species present
Data Source Dominant sp mass Domin.
^_
43 1/5/2006
477 1/5/2006
462 1/5/2006
beaver run
octoraro creek
owens creek
beaver run
octoraro creek
owens creek
MD
MD
MD
carroll
cecil
frederick
maia98-066_ White Sucker Mottlec
rnaia98-165_ American Eel Americ.
rnaia97-045 White Sucker Mottlec -
# Remapsites
Emapsites
• Airports
Cities
» Dams
# Gauges
/v Roads
s\s Railroads
ftr Rivers
*\t Streams
n Urbanare
D Huc16
D HucS
n Counties
D Physio
~
112 records found
Open [)
Approximate map height 1 090 km
Lat:
Map Controls
-------
hydraulic characteristics (Mohamoud and Parmar, 2006). To enhance the predictive potential
of the regression equations and to reduce the percentages of the variability not explained by
the models, separate regression equations were developed for each physiographic province.
The Mid-Atlantic region consists of four physiographic provinces: Appalachian Plateau, Blue
Ridge, Ridge and Valley, and Piedmont. We combined the Ridge and Valley and Blue Ridge
Physiographic Provinces into one and developed a total of three sets of regression equations.
The Hydrology Tool is applicable to watersheds in these physiographic provinces of
Maryland, Pennsylvania, Virginia and West Virginia with drainage areas that range from 3 to
400 square miles.
Bankfull flow is that flow which fills the channel to the top of one or both of its banks.
It is of interest both for its significance to developed areas, where overbank events cause
flooding and associated losses of property and life, as well as for stream protection and
restoration. In the latter case, the interval between bankfull flow events, typically on the order
of 1 - 5 years, determines the rate at which streams are restructured: altering bed composition
through scouring and creating new channel geometry (i.e., meanders) and depositional zones.
An intended application of the Hydrology Tool is the estimation of bankfull flow and
geometry for natural stream channel design, a practice used in stream channel restoration.
Given bankfull flow, the Tool then estimates mean bankfull depth, width, flow area, wetted
perimeter, hydraulic radius and flow velocity for stable, natural channels.
3.2 What do flood conditions look like in my stream?
Selecting the second question of the Hydrology Tool from the interface in Figure 1-3
provides the Wizard Tool of step-by-step actions for locating and launching the Bankfull Tool
(Figure 3-1). Clicking "Do It" results in the national map extent display in Figure 3-2, with no
streams selected by default in the data table.
18
-------
Figure 3-1 Hydrology bankfull tool wizard.
Guided Steps
What do flood conditions look like in my stream?
The task is to open the Hydrology Bankfull Calculator for a site data record and examine the Regions
Options.
Open a Site Data Record window. First open the site searching
window, either by choosing 'Open Site Data Record' and 'Search for
Site Record' from the Data menu, or by clicking the 'Do it' link.
OK!
Doit
In the 'Database Search' panel, select 'Bankfull Hydro' as the search
criterion. Choose a site from the resulting list and click the 'Use
Selected Site' button.
required
required
required
optional
optional
optional
« Back to Questions «
Figure 3-3 illustrates the use of the search result of selecting the field "Stream" [name]
and using the stream "Teter" creek from the prior exercise. Note that the map window zooms
to the stream of interest, identified by the red-colored icon, and automatically displays
additional data layers such as roads, stream networks and USGS gauges. Selection of the
stream of interest is achieved by double clicking the row in the data table, using "Map Select"
and drawing a box around the stream location in the map window (holding down the left
mouse button), or using the "Open" button at the bottom of the window. This returns the
window illustrated in Figure 3-4, with the reminder that the database is read only, requiring
19
-------
the manager to save a working copy of the stream as illustrated in Figure 3-5. This is
achieved by using the "Data" pulldown menu in the main WHAT IF interface (Figure 1-3) to
select "Save Site Data Record As" and renaming the stream. Note that the stream can be
named by the default (original) name provided. Figure 3-6 shows that organized under the
"Tools" tab, all WHAT IF tools and trials are organized, and trials can be selected by double
clicking the trial of interest, or clicking the highlighted control button "Open Tool." Clicking
on this button or double-clicking the Hydrology [bankfull] "base trial 1" icon produces Figure
3-7. Figure 3-7 shows the user entered values for the left and right side channel slopes, and
the resulting calculated other stream property values obtained by clicking the "Predict Stream
Characteristics" button.
Figure 3-2 Map selection window for stream identification and selection.
State and County
Database Search
Browse
| Record
j^J |
Search
Map Selection
State County Data Source Dominant sp mass Dominant
* Remapsites
Emapsites
# Airports
Cities
* Dams
* Gauges
*** Roads
fv Railroads
fv Rivers
f^t Streams
n Urbanareas
D Hud 6
D Huc8
D Counties
D Physio
Map Controls
-------
Managers/assessors will not typically need to change the Manning's coefficient or drainage
area; the left, and right side channel slopes and channel bed slope are the most commonly
adjusted input parameters. The predicted flow in cubic feet per second in this example is
1825. The "Bankfull Flow" input box is used when that measured flow is available (by
clicking the radio button). The new predicted values are displayed in the highlighted area of
Figure 3-7.
Figure 3-3 Map selection window for Teter Creek, WV.
U Site Search
State and County
State County Data Source Dominant sp mass Dominant
V # Remapsites
v* Emapsites
» Airports
Cities
* Dams
V * Gauges
•s fv Roads
V /v Railroads
V f*r Rivers
V fv Streams
D Urbanareas
P Hud 6
D Huc8
D Counties
n Physio
Map Controls
p- Adjust layers on
scale change
Information
Open Teter Creek (50200010802.2001.1 on 2001-09-24)
Cancel
1 record found
Approximate map height: 54 km
Lat:
Lon:
3.3 Adding a new stream/site record to the database.
Managers should note that all site data records included in the WHAT-IF database are
marked as "read only." In other words, the base dataset is write protected as shown in Figure
3-4. In order to work with any of the streams listed in the database, such as experimenting
with the calculators to evaluate variable sensitivities, one will first need to have a writable
21
-------
copy of the Site Data Record. To copy the site, with the particular site open, select "Copy Site
Data Record..." from the "Data" menu. This will bring up a window asking for a new name.
Supply a name and click "OK". The original site will close and the new site will be available.
It is now possible to edit many fields, including the site description, and new trials can be
created with the each of the calculators.
When a site is copied, as shown in Figure 3-5, a complete copy is made of all site data,
including any trials present as well as any BASS model files linked to the site. Most data
appearing in a trial of a calculator can be edited. BASS files can be imported into the site as
well. There are a few limitations that should be mentioned however. The original data as
displayed in the site profile tab is not editable. One cannot change the geographic location of
the new site.
The new stream record and its associated data are stored in the WHAT-IF database
alongside the original sites. It is possible to make a copy of a copy of a Site Data Record,
because the copy behaves much like the original. Note as well that having multiple copies
may change the number of hits returned from query questions.
22
-------
Figure 3-4 Stream data Stream data record window.
ite Data Record: Teter Creek READ-ONLY
General Site Profile 1 Tools 1 BASS Model Files 1
Site Record Name |TeterCreek
Stream Name [TeterCreek
Author
EPA
Last Modified
3/22/2006 10:23:41 AM
Description:
(this record only)
——————————————— (QQuid affect Otner records)
Site Attributes
W Game Fish Present (Srnallmouth bass, Largemouth bass. Trout)
P Negatively Impacted (Degradation index exceeds 66)
Degradation Index (0-100)
Flood region=North
This is a read-only site. Use Data-> Save Site Data Record As... to make a copy.
23
-------
Figure 3-5 Stream data record window with new site name action.
;iir:"""""'" : :
General ] $it
P ease enter a name for the new site data
Site Record
Stream Narm
Author
EPA
Last Modifiei
3/22/2006 10: 23: 41 AM
Description:
s, Largemouth bass. Trout)
exceeds 66)
Degradation Index (0-100)
Flood region=North
This is a read-only site. Use Data-> Save Site Data Record As... to make a copy.
24
-------
Figure 3-6 Stream data record tools tab window.
Hi! Site Data Record: Teter Creek
General | Site Profile Tools | BASS Model Files j
Controls
Open Tool
Refresh
^ Teter Creek
•\ BASS Chemical Exposure 2.2
\BASSPIot1.Q
•\ BASS Stocking.'Harvest 2.2
- "\ Clustering [abundance] 1.0
JVTJ[ base trial 1
.- "\ Clustering [biomass] 1.0
|fTJ[ base trial 1
- \ EPT 1.0
JV[ base trial 1
- \ Habitat Suitability 1.0
j\j_ base trial 1
1.0
trial 1
'ptional Modifications
Drag a trial of the Chemical Exposure tool to here:
Drag a trial of the Stocking/Harvest tool to here:
BABS model run not possible due to
insufficient data. This could be missing
Hydrology data or missing BASS model files.
C
r
r
25
-------
Figure 3-7 Hydrology bankfull tool.
M Hydrology [bankfull]
About Hydrolociy [bankfull]
Site: Teter Creek
Stream: Teter Creek
County: B arbour
State: WV
Inputs
Channel Bed Slope [ft/ft]:
Left Side Slope
Right Side Slope
f™ Gankfull Flow (cu. ft. per sec.)
Stream Characteristics
^H
Measurement
Flow Depth
Flow Width
Wetted Perimeter
Area
Hydraulic Radius
Velocity
Flow
Units
ft
ft
ft
sq. ft.
ft
ft. per sec.
cu. ft. per sec.
Controls
Regional Options
Predict Stream Characterstics
Manning's Coefficient:
Drainage Area [sq miles]
Physiographic Province
West Virginia Region
.1296
|48.1547
Appalachian Plateaus province
North
Value
6.1604
72.4212
75.3237
370.2406
4.9149
4.9285
1824.7326
26
-------
4 Habitat Suitability and Macroinvertebrates
4.1 Introduction to habitat suitability assessment and benthic indices
Similar to the method used in WHAT IF v.l for stream fishes, habitat suitability index
(HSI) models were developed for stream macroinvertebrates using the most commonly
collected instream habitat measures. WHAT IF v.2 contains models for both the EMAP
developed macrobenthic invertebrate index (MBII) and the more commonly applied EPT
index (Ephemeroptera, Plecoptera and Trichoptera) used by states in the Mid-Atlantic.
Multiple logistic regression was used to develop quantitative relationships (i.e., models)
between selected, measured instream habitat variables and the presence or absence of both
MBII and EPT taxa. These index models calculate a composite score of the suitability of a
few, statistically meaningful predictor habitat variables. For each of the habitat variables,
suitability ranges from 0 (unsuitable) to 1 (fully supporting of the species). An important point
of clarification is that these models are similar to those developed by the US Fish and
Wildlife Service but they are not identical in approach. The approach herein is based on the
assumption of a positive relationship between the suitability index and habitat carrying
capacity, but employs logistic regression as the method of data analysis (USFWS 1981).
Also similar to WHAT IF v. 1, reach-scale habitat variables were used as predictors,
resulting in a stream reach assessment tool for benthic insects. The insect and habitat data
used in this analysis were produced by the USEPA EMAP program for MAH surface waters
during 1993-6 (http://www.epa.gov/emap/html/datal/surfwatr/data/ma9396.html). Only
samples within the boundary of the MAH were included in the analysis, and newly available
WV state data (REMAP) were added to the assessment capabilities of WHAT IF v.2.
4.2 What is the quality of the benthic macroinvertebrate community of my
stream?
Similar to the operations in the preceding section, a manager selects the stream of
interest through the assistance of the Wizard Tool, if necessary, as in Figure 4-1. This results
in the map selection window in Figure 4-2. Note that experienced managers/WHAT IF users
can also select streams using the main pulldown menu "Data," then clicking "Open Site Data
Record," and finally selecting "Search For Site Data Record." This series of actions also
27
-------
produces the map selection window with the default extent and no streams currently selected
as in Figure 4-2.
Figure 4-1 Stream wizard tool for benthic stream selection.
S Guided Steps
What is the quality of the benthic macro in vertebrate community of my stream?
The task is to open the EPT/MBII calculator and observe how changes in stream characteristics change
the predicted EFT or MBII score.
Open a Site Data Record window. First open the site searching required Do it
window, either by choosing 'Open Site Data Record' and 'Search for
Site Record' from the Data menu, or by clicking the 'Do it' link.
required
required Do
required
optional
option 8.1
« Back to Questions «
Selecting "Do It" in Figure 4-1 produces the map selection window in Figure 4-2.
Since we are interested in Teter Creek as an extension to the Bankfull Tool example, we
access the "Database Search" by entering "Teter" into the field and clicking on
the nearby "Search" button. Figure 4-3 is produced that shows the EMAP stream of that name
in addition to the new stream created with the identical name in the Bankfull Tool example.
28
-------
Figure 4-2 Map selection window with default extent.
ID Moditied Record Slreani Description Stale County Data Source Dominant sp mass Dominant sp count Game Impacted Degradation I Location ID Author Read only HS!
V * F
V Emapsites
* Airports
Cities
• Dams
* Gauges
s\/ Roads
/NS Railroads
fv Rivers
>v Streams
O Urbanareas
D Hud 6
D HucS
O Counties
D Physio
V Q States
Adjust layers on
scale change
Apptonimale map height: 6689 km Lat1
Figure 4-3 Map selection window showing Teter Creek and duplicate name
Database Search
Map Seleciion
ID Modified Record Stream D^rnption
State County Data Source Dominant sp mass Dominant sp count Game Impacted Degradation I Location ID Author Read only HS!
818 7/13/2006 Teter Creek Teter Creek Flood reqion=NoEth
Barbour 50200010802 [null] River Chub
[null] 590 USER '• I*
V * Remapsites
V Emapsites
* Airports
Cities
* Dams
» Gauges
O Urbanareas
D Huc18
D Huc6
13 Counties
D Physio
D States
7
Q.
i^ AdjusS layers on
1 scale change
Open Teier Creek (50200010802.2001.1 on 2001-09-24) Cancel
Approximate map height: 69 km Lat.
29
-------
Streams are identified in the database with unique IDs, as shown in the left hand side
of the table view in Figure 4-3. The "Author" of the stream is also noted, whether as the
collection agency or the manager using WHAT IF to create new trials of the various tools.
Figure 4-4 illustrates the identical "Location ID" and "Author" associated with the stream that
is centered on the screen. Note as well that the "EPT" column shows the existence of a default
trial using this tool, as well as several others. Figure 4-4 also shows that the user who might
be also interested in the BASS model for chemical exposure or a trout stocking and harvest
assessment has yet to use the WHAT IF tool to populate these entries in the table view.
Figure 4-4 Map selection window showing details of Teter Creek.
ity Site Search
» Remapsites
V E
* Airports
Cities
Dams
V * Gauges
f*t Roads
v' fv Railroads
iv Rivers
Streams
Urbanareas
D Hud 6
HucS
Adjust layers on
scale change
Open Teter Creek (50200010802,2001.1 on 2001-09-24)
Approximate map height: 93 km Lat:
In this example, the manager/WHAT IF user can select the stream saved in the
previous example, since this is not a "Read Only" site in the default database, and can be
modified as necessary for all trials. The stream of interest is shown as selected by presence of
30
-------
the blue highlighting in Figure 4-4, and the clicking of the highlighted "Open Teter Creek"
button at the bottom of the map selection window. Figure 4-5 is thereby returned displaying
the "General" tab by default.
Figure 4-5 Stream data record for Teter Creek with default General information tab
1^ CVI - WHAT IF?
Data View Window Help
I Guided Stcns
iy Site Data Record: Teter Creek
General | Site Profile ) Tools | BASS Model Files
Site Record Name |Teter Creek
Stream Name JTeter Creek
Author
USER
Last Modified
7/13/200612:39:23 AM
Description:
(this record only)
(could affect other records)
Flood region=North
Site Attributes
Game Fish Present (Smallmouth bass, Largemouth bass. Trout)
onrarlaHnn jndj
Degradation Index (0-100)
Save Changes to Names, Attributes, Description
J
On this "General" information tab is included default stream queries from WHAT IF
v.l, namely the presence of any game fish, and whether a degradation index had been
31
-------
calculated for the stream (if blank, missing data prevented the calculation and no tool is
provided for this). Additional information concerning each stream selected is provided using
Figure 4-5 by clicking on the various tabs. The "Site Profile" tab organizes the majority of
the data of interest; the results of its use are shown in Figure 4-6.
Figure 4-6 Stream data record for Teter Creek showing Habitat and Landscape tab.
r •
HJ CVI - WHAT IF?
£j
30
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Data View Window Help
•
Hit
5P Guided Stens
• iy Site Data Record: Teter Creek
-
-
General Site Profile j
Habitat & Landscape
Tools | BASS Model Files
Biota 1 Location |
• Habitat Data III
Description
Value L units Data source
.*.
^ Fish cover - aquatic macrophytes (areal proportion) 0.0000 proportion WVREMAP
Fish cover - filamentous algae (areal proportion) 0.0000 proportion WVREMAP
Fish cover - large woody debris (areal proportion) 0.0000 proportion WVREMAP , 1
Fish cover - undercut banks (areal proportion) 0.0000 proportion WVREMAP
Percent of reach with fine (silt/clay/muck) substrates 0.0000 percent WVREMAP
Fish cover - overhanging vegetation (areal proportion 0.0409 proportion WVREMAP
Proportion of reach with riparian ground layer cover 0.4023 proportion WVREMAP
Fish cover - boulders (areal proportion) 0.8477 proportion WVREMAP
Fish cover - large wood, rock, undercut bank, or artific 0.8477 proportion WVREMAP
Fish cover - all types (sum areal proportion) 0.8886 proportion WVREMAP
Pmnnrtlnn nf rfiflnh with 3-lflusr rinflrifln nnvpr 09545 nrnnnrtinn WVRFMAP i-ZJ
• Landscape Data 111
Description
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^M
Value units Data source
^ Annual precipitation no data m WVREMAP
Annual runoff no data m WVREMAP
Percent of watershed in agricultural covers (NLCD-81, 82, 18.7000 percent WVREMAP
Percent of watershed in forest covers (N LCD -41 ,42,43) 80.5000 percent WVREMAP
Percent of watershed in human-disturbed covers (urbjot no data percent WVREMAP
Percent of watershed in urban covers (N LCD -21 ,22,23,85 0.0000 percent WVREMAP
Population density no data indAm"2 WVREMAP
Road density 15.2780 m/ha WVREMAP
Site elevation 381.0850 m WVREMAP
Site latitude
l<
39.2089 decimal degr WVREMAP
••••^^H
_J
32
-------
The "Habitat and Landscape" tab shows the entries from both EMAP and REMAP
datasets, as well as the units and description to assist managers with a more complete
background for each stream. Habitat parameters used in WHAT IF v.2 tools are displayed
with a variety of others that may be of interest to managers. Figure 4-7 shows the information
displayed with the "Biota" tab.
Figure 4-7 Stream data record showing Biota tab and associated data fields
r ' • '
|y CVI - WHAT IF?
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^m
Data View Window Help
SP Guided Stens
• Hy Site Data Record: Teter Creek
-
-
-
General Site Profile j Tools )
Habitat^ Landscape Biota
BASS Model Files)
Location |
• Fish Community III
Common Name
^ River Chub
Rock Bass
SmallmouthBass
Mottled Sculpin
Longnose Dace
Northern Hogsucker
Scientific Name Percent Abundance .A
Nocomis micropogon 35.075 i
Ambloplites rupestris 18.657 i
Micropterus dolomieu 16.418 |
Cottusbairdi 10.448 j
Rhinichthys cataractae 9.701 i ;
Hypentelium nigricans 3.731 j ^J
• Fish Metrics III
Description
Value units Data source J A
^ Adjusted proportion of tolerants no data proportion WVREMAP
Calculated adjusted proportion of aliens no data - WVREMAP
IBI score (McCormicketal. 2001) no data - WVREMAP j — I
Number of individuals in sample no data - WVREMAP j
Number of native species 9.0000 - WVREMAP !T|
• Non-fish Metrics III
Description Value
^ .V^^MlK.
^ Chironomids no data
Chiroiwriids no data
Dominant tax no data
units Data source _ A
"^\ percent WVREMAP
percent WVREMAP j J
A WVREMAP |
Ephpmeropte no data pepent WVREMAP |j
Ephemeropte no data patcent WVREMAP j
EpherrtiiPPte no data / WVREMAP 1
Hikpnhn^>ii«L r,n HA^
_^fS . UA/R F M h P 1
!< •• ' >
33
-------
Of note in Figure 4-7 is the non-existent "Non-fish Metrics," which is often an issue
associated with the use of the EPT tool. In this case the manager/WHAT IF user will not be
able to compare predicted to observed EPT values. A manager/WHAT IF user has several
choices, including manually entering the data required for the benthic calculators, opting to
use fish as the stream bioassessment endpoint, or going back to the map selection window and
searching for a nearby stream to Teter Creek that provides benthic insect information. In this
example, the first option will be investigated in the following section. Note that since WV
REMAP contains fish measurements only, this is a limitation of attempting to use other than
EMAP streams. Figure 4-8 illustrates an additional feature for managers/WHAT IF users
with an Internet connection: hotlinks to Mapquest and TerraServer for automated image
retrieval, in the latter case for digital orthophoto display.
Figure 4-8 Stream data record location tab
tW CVI - WHAT IF?
Data View Window Help
1U Site Data Record: Teter Creek
General Site Profile Tools BASS Model Files |
Habitat & Landscape ] Biota Location
State
County
Physiographic Code
Physiographic Section
Physiographic Province
HUCID
Latitude
Longitude
Map It
WV
B arbour
8e
Kanawha section
(Appalachian Plateaus province
150200010802
39.2089
-79.9263
Microsoft TerraServer
34
-------
4.3 What is the quality of the benthic macroinvertebrate community of my
stream? (continued)
Figure 4-9 EPT habitat suitability tool
Data View Window Help
About EPT
Site: Teter Creek
Stream: Teler Creek
County: B arbour
State: WV
Calculation Type
f- EPT
Inputs
Drainage Area (hectares)
Fast Water (?)
Rock Substrate (?)
Fines Substrate (?)
Cobble Substrate (?)
Large Woody Debris (?)
Riparian Cover (?)
Undercut Banks (?)
Conductance
(micro Siemens)
Results
12472
|84.77
Supplementary Field Description
Create New
base trial 1
Rename Trial I Save All Trials Q
MBII
Population Density (persons/'Sq Km)
M in: 0 Man: 77
Min:OMax:1QO
Min:OMax:90
Road Density (meters/hectare) H5.28
Disturbed Watershed Area l%l
Min:OMax:3C
Elevation (meters) 1381.OS
Overhanging Vegetation (?)
Stream Slope {%}
pH
Calculate
Min:8Man:30
Min:4Max:881
As shown in Figure 4-9, the calculator is incomplete, as indicated by the red
exclamation icon near the highlighted calculate button. Moving the mouse over the red icon
produces a message to aid in locating the missing parameters, in this case "Disturbed
Watershed Area." Estimating this value as 10 (percent of agricultural and urban land) and
clicking "Calculate" results in Figure 4-10. Note that there is still an issue, as explained in the
"Results" field, that "Rock Substrate" has a value outside the acceptable range. Rather than
35
-------
truncate values from the WV REMAP database automatically, managers/model users are
prompted to modify an entry, and WHAT IF provides the minimum and maximum values for
each input parameter to assist in this process. Since EMAP data were used to develop the
models, values in the REMAP dataset may also occasionally be outside the acceptable range.
Figure 4-10 EPT tool showing explanatory fields and value outside accepted range.
ItJcVI-WHATIF?
Data View Window Help
About EPT
Trial Manager
Site: Teter Creek
Stream: Teter Creek
County: E arbour
State: WV
Create New
Min:OMax:77
Min:OMax:100
Min:OMax:90
Calculation Type
»- EPT r MEN
Inputs
Drainage Area (hectares)
Fast Water {%)
Rock Substrate {%]
Fines Substrate {%}
Cobble Substrate (%) [545
Large Woody Debris (%} I
Riparian Cover (%} I
Undercut Banks(%) |Q
Conductance
(micro Siemens)
Results
Rock Substrate has an invalid value.
Supplementary Field Description
Generally the percentage of agricultural and urban area.
I base trial 1
Rename Trial
Save All Trials
Calculate
Population Density (persons^Sq Km)
Road Density (meters/hectare) |l528
Disturbed Watershed Area (
Min:OMax:30
E levation (meters) 381.09
Overhanging Vegetation (%}
Stream Slope(%)
pH
Min:8 Max:30
Min:0 Max:75
Min:4 Max:881
Figure 4-11 illustrates the successful completion of the input/setup process, resulting
in a predicted EPT value of 29.5. In this case there is no measured EPT value with which to
compare the predicted value, but the lower and upper confidence bounds of the calculated
value can help with its interpretation. Note that the EPT and MBII tools have a shared
36
-------
interface such that a number of additional input habitat parameter values are displayed but in
shaded format. Managers can continue to modify any of the currently used required model
input parameters, provided the values are within the acceptable ranges.
Figure 4-11 EPT tool with predicted value and confidence limits.
AbouLEPI
Site: Teter Creek
Stream: Teter Creek
County: B arbour
State: WV
Calculation Type
f- EPT
Inputs
D rainage Area (hectares) •) 2472
Fast Water (?)
Rock Substrate (?) 77
Fines Substrate (?) g
Cobble Substrate (?) [5^45
Large Woody Debris (?)
Riparian Cover (?)
Undercut Banks (?) Q
Trial Manager
^iJ^J
[base trial 1
RenameTrial SaveAIIT
r MEN
Population Density (persons/Sq Km)
Min:OMax:77 Road Density (metersAiectare) 1528
Min:OMax:100 D rsturbed Watershed Area (?) -|0
Min:OMax:90 Elevation (meters) pijB
Overhanging Vegetation (?)
Stream Slope(?)
Min:OMax:30 PH
Conductance
(micro Siemens)
Results
Measured EPT = No Measurement
Predicted EPT = 29.52
Upper Confidence Limit = 45.16
Lower Confidence Limit = 0.0
05? Confidence)
Supplementary Field Description
Fish cover consisting of rocks and boulders. Percent of habitat that is made up of boulders. Boulders' size is greate
Create New
rials Calculate
Min:8 Max: 30
Min:0 Max: 75
Min:4 Max:881
than 256mm.
The MBII calculator input/setup and result is shown in Figure 4-12. Trial results can
be saved by creating and renaming each trial. WHAT IF stores all saved trials. An example of
this is shown in Figure 4-13 where road density was doubled, resulting in a decrease of
calculated MBII score to half of its value in Figure 4-12. After completing the needed
37
-------
assessments (trials) using the EPT and MBII tools, managers/model users return to the Tools
Wizard (Figure 4-14).
Figure 4-12 MBII calculator.
Site: Teter Creek
Stream: Teter Creek
County: B arbour
State: WV
Calculation Type
"' EPT
Inputs
Drainage Area (hectares)
Fast Water (%}
Rock Substrate (%}
Fines Substrate (%)
Cobble Substrate (%)
Large Woody Debris [%}
Riparian Cover [%}
Undercut Banks (%)
Conductance
(micro Siemens)
[base trial 1
Rename Trial Save All Trials
Calculate
Population Density (persons/Sq Km)
Road Density (meters/hectare) f-|5_28
Disturbed Watershed Area {%} I
Elevation (meters) I
Overhanging Vegetation [%) [4 gg
Stream Slope (%\ 12.2
pH I
Min:8 Max:30
Min:0 Max:55
Min:0.11 Ma«:4
Results
Measured MBII = No Measurement
Predicted MBII = 58.6
Upper Confidence Limit = 907
Lower Confidence Limit = 0.0
(95% Confidence)
Supplementary Field Description
Fish cover consisting of rocks and boulders. Percent of habitat that is made up of boulders. Boulders' size is greater than 256mm.
38
-------
Figure 4-13 MBII calculator with trial selected.
JWCVI-WHATIF?
View Window Help
fftjEPT
About EFT
Trial Manager
Site: Teter Creek
Stream: Teter Creek
County: B arbour
State: WV
Calculation Type
r EPT
Inputs
Drainage Area (hectares) 112472
Fast Water (%) |
Rock Substrate (%) |
Fines Substrate (?) I
Cobble Substrate (%\ |
Large Woody Debris (?) j
Riparian Cover (?) I
Undercut Banks (?) I
MBII
Conductance |
(micro Siemens)
Results
Measured MBII = No Measurement
Predicted MBII = 26.36
Upper Confidence Limit = 60.32
Lower Confidence Limit = 0.0
(95? Confidence)
Supplementary Field Description
Overhanging Vegetation Cover.
Test trial -- road density doubled
RenarneTrial SaveAIITrials
Population Density (persons/Sq Km)
Road Density (meters/hectare)(T30
Disturbed Watershed Area (?) |~
Elevation (meters)
Overhanging Vegetation]?) [4^09"
Stream Slope (?) [22^
pH I
Min:8 Max:3Q
Min:0 Max: 55
Min:0.11 Max:4
39
-------
Figure 4-14 Benthic tool wizard showing completion of all steps.
iJ CVI - WHAT IF?
Data View Window Help
Guided Steps
What is the quality of the benthic macro in vertebrate community of my stream?
The task is to open the EPT/MBII calculator and observe how changes in stream characteristics change
the predicted EFT or MBII score.
Open a Site Data Record window. First open the site searching
window, either by choosing 'Open Site Data Record' and 'Search for
Site Record' from the Data menu, or by clicking the 'Do it' link.
OK! Doit
In the 'Database Search' panel, select 'EFT' as the search criterion.
Choose a site from the resulting list and click the 'Use Selected Site'
button.
OK!
Select the Tools' tab in the Site Data Record window.
OK! Doit
Open the EPT/MBII calculator by selecting the 'EFT1 item and clicking
the 'Open Tool' button.
OK!
Doit
This calculator performs both EFT predictions and MBII score
predictions. Notice the radio buttons to switch between the two kinds
of calculations. The calculations have different sets of required inputs,
and not all values may be available in the data of this site visit. When
available, the actual scores are shown with the predictions.
optional
Once finished, you may close the tool window.
optional
« Back to Questions «
40
-------
References
CVI. 2002. Mid-Atlantic Highland Action Program. Canaan Valley Institute, Thomas, WV.
Cyterski, M. and C. Barber. 2006. Identification and prediction offish assemblages in
streams of the Mid-Atlantic Highlands, USA. Transactions of the American Fisheries
Society 135:40-48.
McCormick, F.H., D.V. Peck, and D.P. Larsen. 2000. Comparison of geographic
classification schemes for Mid-Atlantic stream fish assemblages. Journal of the North
American BenthologicalSociety 19: 385-404.
McCormick, F.H., R.M. Hughes, P.R. Kaufmann, D.V. Peck, J.L. Stoddard, and A.T. Herlihy.
2001. Development of an index of biotic integrity for the Mid-Atlantic Highlands
region. Transactions of the American Fisheries Society 130: 857-877.
Mohamoud, Y.M. and R. S. Parmar. 2006. Estimating streamflow and associated hydraulic
geometry, the Mid-Atlantic region, USA. Journal of the American Water Resources
Association 42(3):755-768.
Rashleigh, B., M.C. Barber, M.J. Cyterski, J.M. Johnston, Y. Mohamoud, and R. Parmar.
2004. Population models for stream fish response to habitat and hydrologic alteration:
the CVI Watershed Tool. EPA/600/R-04/190, U.S. Environmental Protection Agency,
Athens, Georgia.
Rashleigh, B., R. Parmar, J. M. Johnston, and M.C. Barber. 2005. Predictive habitat models
for the occurrence of stream fishes in the Mid-Atlantic Highlands. North American
Journal of Fisheries Management 25:1353-1366.
Rosgen, D. 1996. Applied River Morphology. Wildlife Hydrology, Pagosa Springs, CO.
41
-------
USEPA. 2000. Mid-Atlantic Highlands Streams Assessment. U.S. Environmental Protection
Agency, Washington, D.C. EPA-903-R-00-015.
USFWS. 1981. Standards for the development of habitat suitability index models for use in
the Habitat Evaluation Procedures. U.S. Department of Interior, Fish and Wildlife
Service, Division of Ecological Services. ESM 103.
42
-------
Appendix A
Appendix B Overview
WHAT IF is software that integrates a number of calculators and tools for assessing
the health of watersheds and streams with an emphasis on fish communities. The software is
built upon a database of stream measurements that provides the inputs to the various WHAT
IF calculators and tools. These calculators and tools vary in complexity, ranging from
regression-based predictions to comprehensive fish population simulation model, the
Bioaccumulation and Aquatic System Simulator (BASS).
The software allows the user to investigate a location on a stream or river by
examining the data that have been collected at the location, and then using that data with the
calculator's tools to compare actual conditions with predicted or possible conditions.
For example, the Species Cluster tool might predict that a stream in this location
would likely support a trout population, while the data show that no trout have been observed.
Then, the Habitat Suitability calculator could be used to examine the scores for trout to see if
an explanation can be found for this observation in the current stream conditions. The user
could then explore what changes could be made to the stream, such as increased overhanging
vegetation or erosion control, to allow trout to thrive. Finally, trout could be "stocked" in the
simulation model to see if they persist and how other species are affected.
All of the data, both original observations and user-generated experimental data, are
stored in the central database that allows access by any WHAT IF tool or calculator. Thus, the
output of one tool can become the input to another; this feature is exploited by some WHAT
IF calculators and tools for certain kinds of data.
Figure Al illustrates a simplified view of the central database. Each stream sampling
site contains information related to its geographic location. A location can also be referenced
by one or more sampling events; these represent multiple visits to a site for data collection.
Each sampling event, in turn, has one or many data groups. A data group is a set of data
associated with a particular tool, calculator, or category. The original field data for a site
43
-------
constitutes one of the data groups. A data group can contain inputs and outputs related to a
WHAT IF calculator or tool. For THE BASS simulation model, the data group includes the
model's input files and can include graphs generated from model output.
Figure A.I A simplified view of the WHAT IF database of stream site data. A location
can have one or more sampling events, and a sampling event one or more groups of data
values.
Sampling Location
Including stream name, latitude, longitude, state,
county, physiographic province, HUC, etc.
Sampling Event
"Sits Data Record"
Including name, ID,
date, description,
location ID, etc.
Sampling Event
Sampling Event
Data Group
"Tool Trial"
Including a
trial ID, tool or
calculator ID,
data name-
value pairs
Data Group
Data Group
Data Group
In a WHAT IF application, the sampling event is referred to as a Site Data Record,
and the data group is called a Tool Trial, or simply Trial. The following table contains the
current number of Trials based on data grouping. EMAP and WVREMAP are the site
observations for the 797 sites.
44
-------
Table 1 Calculators and their respective trial counts.
Calculator, Tool, or Category of Data
BASS model
Clustering [abundance]
Clustering [biomass]
EMAP
EPT
Habitat Suitability
Hydrology
Hydrology [bankfull]
WVREMAP
Number of Trials
476
607
609
669
723
605
540
434
128
Appendix C
Appendix D How WHAT IF Works
There are a set of tutorial questions to guide the user through the process of using
WHAT IF. The window of questions opens when the software is started.
Generally, one starts by searching for a stream site of interest, then opening one of its
Site Data Records. In the window of the Site Data Record, some of the site's data are
displayed in the various window tabs. The Tools tab shows the relevant calculators and tools,
and the Trials that are associated with them. If a tool or calculator has a Trial pre-made, it is
called the base trial. A base trial has been created for any site for which the tool is applicable,
and the site had sufficient data to use as inputs to the tool or calculator. However, a user can
still open a calculator/tool and supply inputs with which to experiment, even if no base trial
has been created. If the calculator/tool does not support the current site, it will inform you
and you can try another site. Usually, when a calculator does not support a site it is due to
some characteristic of the site that is outside the bounds of the acceptable inputs.
To experiment with a site, the user first chooses "Save Site Data Record As..." from
the Data menu. Doing this will create a complete copy of the data associated with this Site
Data Record, but not the location information, that other data records may share. With this
data copy the user can create new Trials for the different calculators and examine different
scenarios for the stream. All data created as Trials are saved to the database.
45
-------
Links to help files near the top of the calculator windows or the Help menu can be
used to find documents that describe the theory and science behind the tools/calculators.
Appendix E Software Architecture
A.3.1 Development tools
Microsoft Visual Studio .NET 2003 is used to compile and build an application.
MySQL 4.1 .x isused to build the database and it is also the server used at runtime. The .NET
libraries are used for XML parsing and transformations. Visual SourceSafe is used to manage
source files. The MySQL Connector/NET from MySQL AB is used as the database driver.
A.3.2 Language and Components
WHAT IF consists primarily of C#.NET source code. It uses several XML files for
externalized information such as SQL, configuration strings, and other text data. See
Appendix B for a list of external files. It uses SQL statements for database access, and the
XML Stylesheet Language (XSL) for transformations of XML data to text or other XML data
formats.
In addition, the following components are included in WHAT IF:
1. The BASS model GUI
The GUI for the BASS model was built as a separate application using C#.NET. The
code has been integrated into WHAT IF, unchanged, by sharing sources between the
BASS GUI standalone and WHAT IF. Parts of the BASS GUI—editor windows for
game fish stocking and chemical exposures—appear in the WHAT IF user interface.
It is possible to switch modes and go into the full BASS GUI while running WHAT
IF.
2. MS Chart control
This COM control is used to draw plots of BASS model output.
3. MapWinGIS ActiveX control 3 .x
This open source (Mozilla Public License 1.1), COM control renders ESRI shapefile
formatted data, among other formats, and draws maps that respond to mouse events.
Along with a map legend COM control, it enables the site searching dialog to allow
site selection by spatial characteristics. See http://www.mapwindow.org
4. MySQL Connector/Net 1.0.4 from MySQL AB
This is an open source (Lesser General Public License), .NET native data provider to
interface with the MySQL database server. See
http://sourceforge.net/projects/mysqlnet
5. CVIDatabaseConnector
A DLL created from a C#.NET project outside of the WHAT IF solution to manage
database connectivity.
46
-------
6. BusinessLogic DLL
This library contains the functions that implement the scientific algorithms and
equations behind the various calculators. It is shared with the Web-based version of
WHAT IF.
Figure A.2 Overall component organization
WHAT IF application
MySQL.NET
BusinessLogic
WHATIF database
MySQL server
MS .NET Framework
MapWinGIS
BASS I/O files
BASS model
MS Windows
A.3.3 Organization of code
Overall, the C#.NET source code can be divided into two sections: the BASS GUI
support code and the WHAT IF application code. The former consists of the following
components:
• BASS_22_GUI_code
• BASS_Controller
• BASS_FORM_LIBRARY
• BASSJnterfaces
• BASSJO
• BASSPlotFish
• DOM_Manager
The remainder of the components are part of the WHAT IF application:
• CVI_App
• CVI_DataManagers
• CVI GUI
• CVI_Interfaces
• CVIJVIiddleware
• GroverUtilities
• MapWinManager
• StepByStepCntl
47
-------
Two other components handle installation tasks, for a total of 17. In general, the
component name is the same as the Namespace of the classes it.
To help the user become oriented towards which parts of the code are responsible for
which tasks, we will briefly describe each component in turn. Further details can be found in
the source code itself, and in the HTML documentation generated from the source.
A.3.3.1 BASS_22 GUI_code
This component contains support code for the BASS GUI. The BASS model accepts
many different kinds of commands, and each type has an editor. The large number of classes
in this component reflects the different BASS commands and related support code.
A.3.3.2 BASS_Controller
The BASS model works on a messaging system. Events from the GUI are turned into
messages and the messages are broadcast to all registered components. Components check to
see if they are the receiver, and if so, will respond to the message. This component contains
the class that brokers this communication, as well as classes to log errors and run the model.
A.3.3.3 BASS_FORM_LIBRARY
The forms of the BASS GUI are contained in this component. Form inheritance is
used in many cases.
A.3.3.4 BASS_Interfaces
This component is USED (imported) by several others. It has interfaces to facilitate
communication among components and avoid circular referencing. It includes a class of
static common variables. In addition, it has the BASS message class and related classes.
A.3.3.5 BASSJO
Classes in this component handle BASS file input and output. This task involves bi-
directional converting between the BASS input file text format and the internal XML
representation of the BASS inputs. XML to text is handled with an XSL transformation.
Text to XML is performed in two stages: parsing of the text to XML, followed by an XSLT
48
-------
transformation to another, more rigorous XML format. There are several different BASS file
formats and command formats.
A.3.3.6 BASSPlotFish
There are forms and classes in this component to plot BASS output. The output file is
a large XML document. Code in this component reads and transforms the XML, which is
then read into a DataSet for extracting arrays for plotting.
A.3.3.7 DOM_Manager
The BASS input files exist in a pre-defined directory structure called the BASS root.
The BASS root directory and all sub-directories are read into an XML Document Object
Model (DOM). Edits are made to this structure. When saving, a node is extracted to write as
a file.
A.3.3.8 CVI_App
This component is the entry point for the application.
A.3.3.9 CVI_DataManagers
There is a large class that handles connecting to the database (using
CVIDatabaseConnector.DLL), submitting SQL statements, and packaging result sets into DataTables.
All but a few SQL statements are stored in SQL.resx.
A.3.3.10 CVI_GUI
The forms for the WHAT IF GUI are here, along with some logic for the help steps
and other support files. Form inheritance is used for the calculators.
A.3.3.11 CVI_Interfaces
Most of this component consists of interfaces to allow indirect access to classes and
methods across components. A few classes requiring visibility are found here along with
static global variables and custom exception classes.
49
-------
A.3.3.12 CVI_Middleware
The classes within this component are concerned with interfacing to the BASS GUI
and preparing and running the BASS model. To run the BASS model, some input files are
exported from the database, where they are stored as XML, and transformed to their text
form. Other BASS files are created from stream data or trial data in preparation for the model
run. These files are written to a temporary directory from which the model is run.
A.3.3.13 GroverUtilities
Classes here manage the MRU (recent menu items lists) functionality.
A.3.3.14 MapWinManager
This component handles all of the functions associated with the MapWinGIS control
and the MapLegend control. Communication from this component to the SiteSearcherPicker
class occurs via a simple interface—IgisHost. The SiteSearcherPicker class accesses a single
public class, MWManagerPublic, in this component.
A.3.3.15 StepByStepCntl
This component defines a custom control that is used as a step panel in the guided
steps window. The step control contains some of the logic of the guided steps function.
A.4. Database description
A.4.1 Overview
For the purposes of the WHAT IF toolset software, a stream site is considered to be
the collection of data necessary to describe the physical, biological, and spatial coordinates
and boundaries of an actual stream reach, along with the data produced and consumed by the
tools of the application. Except for tool configuration parameters, all of the data used by the
application are read and written to the stream site database, and are associated with a
particular site. A major tool of the software toolset is the BASS model, version 2.2. BASS
input files are required to parameterize the model for a particular body of water; these files, if
present, are stored in the database.
50
-------
CSC's goal in database design was to create a flexible and comprehensive database
schema. CSC anticipates that needs will change, and as a result tried to minimize any
assumptions about the data. For example, rather than create a table with columns that
represent the data values of a particular tool, generic tables that hold all data for all tools
were created. The definitions of the data are stored as rows in another table and can be easily
extended to include new data types.
At the time of this writing, WHAT IF version 2.0 uses MySQL version 4.1.11. That
does not use transactions or stored procedures. All tables are of type MYISAM.
The database of WHAT IF (designated cvi_wif_l, but referred-to in this document as
the WHAT IF database) is described next in terms of its tables, relationships, limitations, and
example queries.
The database comprises 24 tables, summarized and described next:
Table 2 CVI database tables and their descriptions.
Table
bass file
bass_plot
Bmp
bmp_adjust
Cluster
cluster filter 6
cvi tool
fish species
Physiographic
sp cluster
Description
Stores BASS model files in XML form.
Stores graphical output as base64-encoded binary data
Holds the name of a Best Management Practice type
Holds adjustment parameter(s) and direction for a given BMP type
Defines a cluster in terms of a number and a name.
Defines fish species allowed in a given 6 digit HUC, and used to filter
results in the Clustering tools.
Defines tools or calculators, or, more broadly, "categories" of data
associated with a stream site.
Defines fish in terms of common name, scientific name, and name used by
BASS. Note that some records define groups, unknown species of some
genus, and hybrids.
Defines physiographic code, province, and section.
Holds a list of fish for the cluster number along with the predicted
abundance.
51
-------
Table
sp_datum_property
sp^group
ss aux att
ss_bass_file
ss fish
ss_geopol
ss_loc
ss_source_huc
ss source site
Streamsite
tool_property
tool_trial
trial_data
trial datum
Description
Links fish_species to trial_datum to define which fields are required by
species, and also provides some fields for storing species-specific
parameters; currently for HSS, but could be used for other tools as well.
(This is no longer in use with the new HSI calculator)
Defines species into groups for HSS
Stores auxiliary attributes for a site; data for which a field doesn't exist.
Link table between streamsite and bass_file for many-to-many relationship.
Link table between streamsite and fish species for many-to-many
relationship. The site's actual community.
Stores geo-political data, e.g. state, county, physiographic code. Sites can
share records in this table.
Currently stores latitude and longitude. Could be replaced with spatially
enabled tables in the future.
Stores HUC ID's matched to geopol ID's.
Stores stream site ID's with foreign ID's, ie, identifiers used in various
source material such as EMAP site ID's.
The main site table. Stores the site name, author, last modified date,
description, and other metadata.
Stores tool configuration parameters.
A row defines a trial for a tool. A trial is a named set of inputs and outputs.
The inputs and outputs for the tools' trials.
Defines an input or output of a tool.
The 24 tables of the WHAT IF database can also be categorized into three types: static
property data, stream site data, and location information. The static properties data tables
define tool-related items such as species, clusters, and tool input definitions. The stream site
data tables store data that can be manipulated, such as site observations, model files, and other
tool and calculator inputs. The location information tables store the geographic coordinates,
geopolitical boundaries, and related information for a stream. Location data may link to
52
-------
multiple site sampling events, and is normally not edited. The following table shows the
WHAT IF database tables in each of these three categories.
Table 3 CVI database tables by category.
Table
bmp
bmp adjust
cluster
cluster filter 6
cvi tool
fish_species
physiographic
sp_cluster
sp datum_property
sp^group
tool_property
trial datum
bass_file
bass_plot
ss_aux_att
ss bass file
ss fish
ss source site
streamsite
tool_trial
trial data
ss^geopol
ss loc
ss_source_huc
Category
Static data
Static data
Static data
Static data
Static data
Static data
Static data
Static data
Static data
Static data
Static data
Static data
Site data
Site data
Site data
Site data
Site data
Site data
Site data
Site data
Site data
Location data
Location data
Location data
See Appendix B for the complete database schema.
53
-------
Data Limitations
The number of sites possible is large; ID's on most tables allow millions to billions of
records.
Some field sizes and other limitations are listed below:
Table 4 Important data fields and their sizes and/or values.
streamsite name, src_emap, src_hydro, src_bio
streamsite description
ss^geopol county, physiographic province & section
ss_geopol stream name
ss_geopol state
species com, sci, and bass names
cvi tool name and version
BASS file name
BASS file size
Graphical output binary
HUCID
Character based attribute or tool datum
streamsite game, impacted, readonly; tool_trial.base
Number of clusters per trial, number of species used per trial,
number of months per datum per trial
Values for streamsite. author
Values for trial_datum.put
Values for trial datum. type
255 characters each
64,000 characters
64 characters each
255 characters
2 characters
255 characters each
32 and 10 characters
255 characters
64,000 characters
16MB
20 decimal digits
255 characters
True or false
256
'CVI', 'EPA', 'USER'
'in', 'out'
'number', 'integer', 'string', 'sp id',
'cluster_num', 'bmp', 'switch',
'feedback', 'periodic', 'list'
A.4.1.1 SQL statements and templates
In order to provide some guidance in using the WHAT IF database, sample SQL
queries are provided here to use as tests or templates by the user. The user may want to
examine the file SQL.resx from the source code for more SQL statements.
54
-------
With some exceptions, data for a stream sampling event (known as a Site Data Record
in the GUI) are stored in the trial_data table and are associated with a specific Trial.
Exceptions include general stream site descriptions (streamsite table), fish communities
(ss_fish), BASS input files (bass_file), and BASS plots (bass_plot). To retrieve the value of a
particular stream variable for a particular Site Data Record, one needs to link/join, at a
minimum, tool_trial, trial_data, and trial_datum, to specify the site, tool category, and
variable name. The user can link/join tool_trial to trial_data using trial_id; trial_datum to
tooljrial using the tool_id; and trial_datum to trial_data using the short_name. After
supplying the ssid and tool_id values, and the user can then read the num_value, int_value, or
str_value field of trial_data, depending on the value of trial_datum's type field.
Figure A. 3 WHAT-IF Database tables illustrating the relationships for an example SQL
query.
tool_trial1
PK
U1
U1
U1
trial id
ssid
tool id
name
base
last_modified
N-Signed Integer
N-Signed Integer
N-Signed Integer
C-Variable Length(64)
N-Signed Integer
T-Auto Timestamp
L ^
trial_data1
PK,FK1
PK
PK
trial id
ndx
short name
num value
str_value
int_value
N-Signed Integer
N-Signed Integer
C-VariableLength(31)
N-Floating Point
C-Variable Length(255)
N-Signed Integer
^
^
^
^
trial_datum1
PK,FK1
PK.FK2
short name
emap_equiv
tool id
gui_name
put
type
description
units
min val
max_val
field_marker
required
uses_ndx
C-Variable Length(31)
C-Variable Length(31)
N-Signed Integer
C-Variable Length(64)
C-Fixed Length(3)
C-FixedLength(11)
C-Variable Length(255)
C-Variable Length(255)
N-Floating Point
N-Floating Point
C-Fixed Length(1)
N-Signed Integer
N-Signed Integer
This example retrieves pH:
select def.description, dat.num_value as "value"
from tool trial "tri" join trial data "dat" using(trial id)
join trial_datum def using(short_name)
where tri.tool id = def.tool id
and def.short_name = 'phstvl'
and tri.ssid = 123
and def.tool id in (select tool id from cvi tool where version
1 original') ;
55
-------
The sub-select will return all of the tool_ids associated with original data (EMAP,
WVREMAP, etc.) but only one will be in use by the given site.
To retrieve multiple values, the tool_trial and trial_data (and possibly trial_datum) tables need
to be joined for each variable, as in this example:
select site.source_site , ss.name, g.state, g.county, didat.num_value as
DI, eptdat.num_value as EPT, mbidat.num_value as MBII,
lo.latitude dd,and lo.longitude dd from streamsite ss; join ss geopol g
using(geopol_id) join ss_loc lo on ( lo.loc_id = g.loc_id);
join ss source site site on (ss.ssid = site.ssid);
left join tool trial di on (di.ssid = ss.ssid and di.tool id = 11);
left join trial_data didat on( di.trial_id = didat.trial_id);
left join tool trial ept on (ept.ssid = ss.ssid and ept.tool id = 14 )
left join trial_data eptdat on( ept.trial_id = eptdat.trial_id and
eptdat.short name = 'ept ptax');
left join tool trial mbi on (mbi.ssid = ss.ssid and mbi.tool id = 14);
left join trial_data mbidat on( mbi.trial_id = mbidat.trial_id and
mbidat.short name = 'MBII score');
having not ( DI is null and EPT is null and MBII is null);
For tool_id 11, only one short_name exists and so it was not needed. The 'having' clause
simply eliminates rows for which none of the selected values existed.
Each record in trial_data should have a value. Here is a check on trial data (should return 0):
select count(dat.short_name)from trial_data dat; join tool_trial tri
using(trial id);
join trial_datum def on(dat.short_name=def.short_name and tri.tool_id =
def.tool_id)
where ( (def.type='number' and dat.num value is null)
or (def.type='string' and dat.str_value is null)
or (def.type='integer' and dat.int value is null)
or (def.type='sp_id' and dat.int_value is null)
or (def.type='cluster_num' and dat.int_value is null));
Which locations have the most site visits?
select g.geopol_id, g.stream_name, count(ss.ssid) as visits
from streamsite ss; join ss_geopol g using(geopol_id)
group by geopol id
having visits = (select count(ss.ssid) as visitNum
from streamsite ss join ss geopol g using (geopol id)
group by g.geopol_id
order by visitNum desc limit 1);
This statement will perform a dump of the trial data for the given site.
select tool.name as 'Tool-Calculator', def.description,
56
-------
if( dat.num_value is not null, dat.num_value,if(dat.str_value is not
null, dat.str_value,dat.int_value)) as value,
dat.ndx as 'index', def.units
from streamsite ss; join tool_trial tri using(ssid);
join trial data dat using(trial id);
join trial_datum def on( def.short_name = dat.short_name and def.tool_id
= tri.tool_id);
join cvi tool tool on ( tool.tool id = def.tool id )
where ss.ssid = 6;
order by tool.name, def.description, dat.ndx;
A.4.1.2 Loading a table from a text file
Use the LOAD DATA INFILE command to batch load tables from text files. The
MySQL manual, http://dev.mysql.eom/doc/refman/4.l/en/ details its use, but here are some
tips.
The defaults are tab delimited fields, one record per line, no delimiters around the field
values. It looks for files in the database's directory if no path is specified. Therefore, the
simplest form is:
LOAD DATA INFILE 'new_recs.txt' INTO TABLE someTable;
The file should have all fields in order, separated by the tab character. Be aware that
Excel may add quotes around fields that contain a comma, and these quotes will become part
of the data.
Enter null values using \N in the field. A caveat is that if the last field in a line is given
\N, it seems to miss that field (perhaps confusing the \N null with the \n newline) and that
field takes on its default value (which may not be null, of course). The workaround is to have
a trailing tab on each line.
The LOAD DATA INFILE command has options for
1. REPLACing records that have matching keys,
2. changing the field delimiter,
3. indicating that fields may be [OPTIONALLY] enclosed in a specified character,
4. skipping lines,
5. and skipping over leading text on each line.
There is also a reciprocal SELECT ... OUTFILE command.
57
-------
It is important to check the table after an import to look for errors.
When using Excel to create a file for importing, it is sometimes necessary to enter,
say, ID's offish species. To do this, first SELECT com_name, sp_id FROM fish_species
order by com_name. Then copy this whole table into your Excel workbook. Finally, use
Excel's VLOOKUP function to look up the com_name and return the ID into the cell where it
is needed. The range you are doing lookups on can be given a defined name to make it easier
to use the function.
A.4.1.3 Copying a site
Stream sites that are loaded into the database probably will be marked as 'readonly',
and the user will have to first make a copy before any editing is allowed.
Records in the following tables will be copied to create a user-editable site:
streamsite (l)(change fields 'readonly' and 'author'; new ssid generated, keep old geopol_id; add old
ssid to src_ssid),
ss_source_site (O..*)(change ssid)
ss_aux_att (O..*)(change ssid),
src_fish (O..*)(change ssid),
tool_trial (0..*) (change ssid, new trial_id generated),
trial_data(0..*) (change trial_id),
bass_plot(0..*)(change trial_id),
bass_file(0..*)(new file_id generated);
new records created in ss_bass_file(0..*)(set ssid, file_id).
ss_geopol, ss_loc, ss_source_huc, share the geopol record with the original.
A.5 Modifying WHAT IF 2.0
This section is intended for software developers who wish to build new calculators or
tools to extend the functionality of WHAT IF. This section will explain the data architecture,
the concept of trials, and a step-by-step example for creating a new calculator.
Readers of this section will benefit from some understanding of C#, Microsoft .Net
development concepts, database technology (SQL and relational databases), and XML. To do
software development, users must have the following software installed:
• The Microsoft .Net framework v. 1.1.
58
-------
• A C# IDE. Ideally, Visual Studio 2003. However, any C# IDE, such as
SharpDevelop or Eclipse (with the appropriate plug-ins), will work.
• MySQL 4.1
Developers will want to use a WHAT IF application to increase familiarity with
WHAT IF operations.
We will explain how to create a simple calculator that adds two numbers. It's a bit
more than 'Hello World', but this document's objective is not to create a brilliant calculator
for WHAT IF, but to explain the mechanics behind creating a tool that can extend and add to
WHAT IF applications.
Each calculator is unique. Methods shown here are not comprehensive. The methods
that are overridden in the following example will most likely be overridden in each
calculator's code; however, additional methods may need further customization to meet
requirements.
A.5.1 Terms
The following are terms used frequently in WHAT IF:
• Calculator - a GUI that takes input, typically numbers and character strings, and uses
the WHAT IF BusinessLogic to compute a result that is returned to the user.
Calculators typically interact with the back end database to draw inputs and outputs;
however, it is not necessary for all calculators to get their inputs from the database.
Specific calculators are subclasses of class Calculator.
• Tool - a GUI that either takes input and stores it in a database for use with another
application or displays data in a database. Specific tools, such as the
GraphicalOutputTool, are subclasses of ToolBase.
• Trial - a set of inputs and outputs for a specific calculation in a specific calculator
associated with an individual site.
A.6 Data
The information in the section on the WHAT IF database will be very helpful in
understanding the operations outlined in this section.
When creating a new streamsite (directly inserted into the database, not created by the
user in WHAT IF), it is best to set the read-only value to 1 (true). Marking initial sites read-
only helps maintain data integrity, since WHAT IF users can do a "Save As" and create their
59
-------
own trial sites; in this way users cannot change developer entered data. Trials that are created
by a developer and marked read-only are commonly referred to as 'base trials'. In the
tool_trial table, a base trial's name field should be blank. The 'base' integer field should
contain a 1.
Assuming that the streamsite data in the database is satisfactory, one is ready to extend
the data to support a new calculator. To do this, follow the nest steps:
1. Define a calculator in the cvi_tool table.
a. This step involves assigning an id, name, and version number to the tool.
b. Example:
INSERT INTO cvi_tool(tool_id,version, name) VALUES (16,
'1.0', 'My Calculator');
(The tool_id is just the next number in the series.)
2. Define inputs for the calculator in the trial_datum.
Table 5 Descriptions of the different columns in trial datum. Note: only the most
relevant columns are covered here.
Column
short_name
emap equiv
GUI_name
put
tool id
type
Definition
The name of an input or output for a
calculator.
The EMAP equivalent of the variable.
A name displayed in the GUI (not as cryptic
as the abbreviation used in the short name)
Indicates whether input or output
Relates the trial datum table to the cvi tool
table.
The type of the data. A developer has a lot of
freedom with this field ( see cluster num).
Example
Xdepth
Thalweg Mean Depth
in
string, double, integer,
cluster num.
60
-------
required
If an input should not be null in the trial_data
table, the trial_datum record should contain a
1, otherwise it should contain a 0.
uses ndx
Some inputs may be multidimensional (such
as tabular data). If this trial_datum is such, its
record should contain a 1, otherwise it should
contain a 0.
Example Code
Insert into trial_datum
(short_name, tool_id,gui_name,put,type,description,required,uses_ndx)
values
('firstvalue',16,'First Value','in','number','First value used in
calculating a sum',1,0);
Insert into trial datum
(short name, tool id,gui name,put,type,description,required,uses ndx)
values
('secondvalue',16,'Second Value','in','number','Second value used in
calculating a sum', 1,0);
Insert into trial_datum (short_name,
tool id,gui name,put,type,description,required,uses ndx) values
('me result',16,'Result','out','number','My Calculator Result',1,0);
3. Create base trials.
a. Do not name the trials. Leave the name field blank, but set the base field to 1.
b. Put the data for each trial in the trial_data table.
i. Use the corresponding trial_id in the tooljrial table as a foreign key in the
trial_data table.
4. Link streamsites to trials and calculators by assigning streamsite id numbers (ssid's) to trial
records.
Example Code
61
-------
insert into tool_trial (ssid, tool id,base) values (782,16,1);
insert into trial_data (trial id, ndx,short name,num value)
values(3616,0,'firstvalue',23.2);
insert into trial_data (trial id, ndx,short name,num value)
values(3616,0,'secondvalue',16.8);
insert into trial_data (trial_id, ndx,short_name,num_value)
values(3616,0,'mc_result',50.0);
A.7 Trials
The previous section contained instructions for loading data into trial_datum,
trial_data, and tooljrial tables. Trial data are information about the inputs and outputs of
each calculator or tool defined in WHAT IF. Each trial has a name (unless it's a base trial)
and a unique trial ID number (trial_id). The concept of a trial in WHAT IF is critical because
trials allow the user/developer to organize information into individual instances. This
includes information that enters into a calculator and information that results from use of a
calculator. Other tools or applications can retrieve this data from the database, thus allowing
calculators to share information.
For example, the Cluster Biomass calculator takes stream depth as an input. The
hydrology calculator calculates stream depth. The Cluster Biomass GUI includes a button
that lets the user retrieve the stream depth value from a calculation performed by the
hydrology calculator in order to estimate the stream depth. Figure A.4 and Figure A.5 show
how a calculator/tool can import values/data from another WHAT IF calculator/tool.
62
-------
Figure A. 4 Trial Importer allows the calculator to choose which inputs to import.
EM Trial Importer
Choose a trial from the tools listed below and click OK to import its information
into B calculator.
- \ Hydrology 1.0
[TC[ base trial 1
OK
Cancel
Figure A. 5 The calculator has successfully imported the stream depth figure from the
Hydrology calculator.
BASS Chemical Exp
"\ BASS Plot 1.0
>\ BASS Stock!ng/Harj
•"^ Clustering [aburtdar
*\ Clustering [biomasr
- -\EPT1.G
U basetnal 1
- "\ Habitat Suitability 1.
[ff basetnal 1
- "\ Hydrology 1.0
[f| basetnal 1
•\ Hydrology [bankfull]
Controls
Get Siie Geo Data
Estimate Stream Depth
Mm:35.2Max:42.6
Latititcie
Longitude
FineGrsr/el (%)
Predicted Assemblage
Mir' 5 M3x:5S
Mean Stream Depth (cm) [207264
63
-------
A.8 Creating Calculators and Tools
A.8.1 Trial Manager
Once you have prepared the data for a tool or calculator, you are ready to begin
extending WHAT IF to create the UI. Calculators and tools are instanced from the
TrialManager class in the CVI_GUI namespace. In the space where your WHAT IF code
resides, go into the xml folder and look for the file named toolfilter.xml. Add a
node to the list of nodes. The text content of these nodes goes into an SQL
statement. That statement gets a list of tools or calculators that are suitable for the
TrialManager class. If your tool is named 'My Calculator', enter the following:
My Calculator
Now, when you open the TrialManager, you should see your tool/calculator in the
treeview list, assuming you have created the tool definition in the database.
A.8.2 Instancing a Tool
Go to the OpenCalculator (int tool ID, int triallD) method. In the
switch statement, add a case statement that contains the concatenated version number and
name of the tool as it appears in the Trial Manager window. If you created a model called
'My Model 1.0', add the following case statement:
case "MyCalculatorl.0":
calc = new MyCalculator (toolID, triallD, out toolDataOK);
break;
Note: if you do this, your code will not be in a buildable state because you have not yet
created a MyCalculator class. To remedy this issue, add a MyCalculator class, go to its class
signature and implement an iPhasellCalculator interface as follows:
public class MyCalculator : System.Windows.Forms.Form, iPhasellCalculator
Your new calculator will be a blank form, but the code will be in a buildable state. If
you wish to test this, rebuild the CVISolution, Run CVI, pick the site with My Calculator
64
-------
trials, select the 'Tools' tab on the Trial Manager, select 'My Calculator 1.0' in the treeview
and click Open Tool. A new blank form window should appear.
A.8.3 Creating a data enabled tool or calculator
A.8.3.1 Building the initial form
In the previous example, creating a simple window class and making it implement the
iPhasellCalculator interface qualified it as a calculator (though it doesn't do anything yet).
This section will step through the transformation of that code into a useful utility.
Open the MyCalculator class and change its signature so that it is a subclass of
CVI_GUI.Calculator. The signature should look like:
public class MyCalculator : CVI GUI.Calculator
and the code modified to look like the following:
using System;
using System. Collections;
using System.ComponentModel;
using System.Drawing;
using System.Windows.Forms;
namespace CVI_GUI
{
public class MyCalculator : CVI GUI.Calculator
{
private System.ComponentModel.IContainer components = null;
public MyCalculator()
{
// This call is required by the Windows Form Designer.
InitializeComponent();
// TODO: Add any initialization after the
InitializeComponent call
65
-------
///
/// Clean up any resources being used.
///
protected override void Dispose ( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose ( disposing );
}
#region Designer generated code
/// Required method for Designer support - do not modify
/'// the contents of this method with the code editor.
private void InitializeComponent ( )
{
components = new System. ComponentModel . Container (
}
#endregion
No need to implement the iPhasellCalculator interface because the
CVI_GUI. Calculator does that. Rebuild the CVI_GUI project and view class in Designer
mode. That should produce a screen such as the one in Figure A.6.
66
-------
Figure A. 6 Form after inheriting from Calculator
Figure A. 7 A form with two textboxes and a label.
First Number
Secjrd Number
Result
-------
bindControlsToTrialDataset (
catch (CVIDataException cde)
{
CVIStaticCommon . handle CVI Data Except ion ( cde) ;
toolOK = false;
}
catch (CVIToolException cte)
{
CVIStaticCommon.handleCVIToolException (cte) ;
toolOK = false;
}
catch (CVIioException cioe)
{
CVIStaticCommon . handle CVIioException (cioe) ;
toolOK = false;
The call to InitializeComponent ( ) renders the GUI controls local to this
form. The initializeTool ( ) call tells the calculator to perform the tasks that make
it ready to handle data such as downloading the data from the database, making that data into
a dataset, and binding that data to the controls in the GUI. toolOK = true signals back
to the TrialManager that the tool opened successfully. If an exception occurs, it is caught by
the CVI exception catch statements. The bindControlsToTrialDataset ( ) binds
the controls to the data from the database. Override this method to bind the data to your
specific controls.
At this point, if the user builds, runs, and opens the calculator, see a form like Figure
A. 8 should appear.
68
-------
Figure A. 8 A form that is data bound. The trial name appears in the
trial control manager.
fU My Calculator
First Number
Second Number
jbase Snal 1
Rename Trial J Save All Trials I
A.8.3.2 Constructing the local data
Now that the local form is constructed, call the methods that build the local data tables
to which the input and output controls are bound. Go into the CVI_DataManagers class and
open the CVIDatabaseManager class. In this class, there is a method called
factoryMakeDataTables (int toolID) . Since the
calculator in the example is called MyCalculator, create a method called
factoryMakeMyCalculatorDataTables(int toolID) .
public DataTable [] factoryMakeMyCalculatorDataTables(int
toolID)
return factoryMakeCommonDataTables(toolID,2);
Go back to the MyCalculator class. Override the method, makeTrialDataset. Add the
following code to the MyCalculator class:
protected override void makeTrialDataSet(int toolID)
try
base.makeTrialDataSet (toolID);
69
-------
this . dsTrialData . Tables .AddRange (base . cvi db manager . factoryMak
eMyCalculatorDataTables (toolID) ) ;
}
catch (NullReferenceException nre)
{
throw new CVIException ( "Error while making the trial
dataset .", nre, false) ;
}
catch (Exception ex)
{
throw new CVIException ( "Error while making the trial
dataset .", ex, false) ;
Next, override the bindControlsToTrialDataset ( ) and the fillTrialDataSet ()
methods.
protected override void bindControlsToTrialDataset (int toolID)
{
base . bindControlsToTrialDataset () ;
this . txtFirstNumber . DataBindings . Clear () ;
this . txtSecondNumber . DataBindings . Clear ( ) ;
this . IblResult . DataBindings . Clear ( ) ;
//bind textboxes to inputs and outputs.
this . txtFirstNumber. DataBindings .Add ( "Text" , dsTrialData . Tables [ "Trial
Inputs"] , "firstvalue") ;
this . txtSecondNumber. DataBindings .Add ( "Text" , dsTrialData . Tables [ "Tria
llnputs"] , "secondvalue") ;
this . IblResult. DataBindings .Add ( "Text" , dsTrialData . Tables [ "TrialOutpu
ts"] , "mc_result") ;
this . txtFirstNumber . DataBindings [ "Text" ] .Format += new
ConvertEventHandler (CalculatorNumber_Format) ; //CalcualtorNumber_Format is
in the superclass.
70
-------
this . txtSecondNumber . DataBindings [ "Text" ] .Format += new
ConvertEventHandler (CalculatorNumber Format) ;
this . IblResult. DataBindings [ "Text"] . Format += new
ConvertEventHandler (CalculatorNumber Format) ;
protected override void f illTrialDataSet (int toolID)
{
base. fillTrialDataSet (toolID);
foreach (DataRow dr in dsTrialData . Tables [ "TrialData" ]. Rows )
{
int trialid =
Convert. Tolnt 32 (dr [ "trial_id" ] .ToString ( ) ) ;
//get the trial from the list that came from the
database and the put is in.
DataRow drlnput =
dsTrialData. Tables [ "Trial Inputs " ] .NewRow( ) ;
foreach (DataRow drlnputs in
dtFillTrialDataSet. Select ("tool_trial_trial_id = " + _trialid + " and put =
'in' ") )
{
drlnput ["trial_id"] =
drlnputs [ "tool_trial_trial_id" ] .ToString ( ) ;
drlnput [drlnputs [ "trial_data_short_name" ] . ToString (
)] = distillRowValue (drlnputs );
//look out for invalid cast exceptions
try
{
//in a case statement incase some variables should be treated
differently than others .
switch (drlnputs [ "trial_data_short_name" ] . ToSt
ring ( ) )
{
case "firstvalue" : //put your data
variable names here.
71
-------
case "secondvalue" :
drlnput [drlnputs [ "trial data short name" ] . ToString ( ) ]
drlnput [drlnputs [ "trial_data_short_name" ] . ToString ( ) ] ;
break;
catch (InvalidCastException)
drlnput [drlnputs [ "trial data short name" ]. ToString ()]
System. DBNull .Value;
if (dsTrialData. Tables [ "Triallnputs " ] . Select ( "trial_id
" + drlnput ["trial_id"] ) .Length == 0)
dsTrialData. Tables [ "Triallnputs"] . Rows .Add (drlnput) ;
//do the output.
DataRow drOutput =
dsTrialData . Tables [ "TrialOutputs " ] . NewRow ( ) ;
foreach (DataRow drOutputs in
dtFillTrialDataSet. Select ("tool_trial_trial_id = " +
_trialid + " and put = 'out'"))
{
drOutput ["trial_id"] =
drOutputs [ "tool_trial_trial_id" ] . ToString ( ) ;
if
(drOutputs [ "trial_data_short_name"] .ToString ( ) . Equals ( "mc_result") )
72
-------
drOutput [ "mc_result"]
drOutputs [ "trial data num value"];
else
drOutput [drOutputs [ "trial data short name"] . ToString ( ) ]
distillRowValue (drOutputs ) ;
if (drOutput ["trial_id"] != System. DBNull .Value)
{ //do not allow rows with no output to go into the
output table.
if
(dsTrialData. Tables [ "TrialOutputs " ] . Select ("trial_id = " +
drOutput ["trial_id"] ) .Length == 0)
dsTrialData. Tables [ "TrialOutputs"] . Rows .Add (drOutput) ;
} //close the outer foreach loop
dsTrialData . AcceptChanges ( ) ;
} //close the method.
Code reviews may reveal that this is not the cleanest approach to populating the
dataset. Future revisions of CVI may afford the opportunity to better design such data
mechanics. For now, this code will properly populate the dataset.
73
-------
Figure A. 9 Diagram of the backend dataset for My Calculator
FK1
firstvalue
secondvalue
trial id
After writing the code, rebuild and run the solution. Open the MyCalculator tool and
the following form should appear with data in the controls.
Figure A. 10 MyCalculator with data in controls.
• m My Calculator
-re
IS
iS
iS
ite
;te
ita
rol
rol
Za
>as
Site: Harmon Creek
Stream: Harmon Creek
County: Brooke
State: W
First Number
Second Number
Result
Trial Manager
(base trial 1
Rename Trial Save All Trials
A.8.3.3 Performing a calculation
Since the calculator now has data in its input fields, it can calculate a result. To do
this, override the method perf ormCalculation (int triallD) with the code below
public override tool performCalculation(int trial id)
74
-------
base . perf ormCalculation (trial id);
//for brevity, I will not add any data validation. Pleae
add data validation here.
decimal fv = Convert . ToDecimal (this . txtFirstNumber . Text );
decimal sv =
Convert . ToDecimal (this . txtSecondN umber .Text) ;
decimal result = fv+sv; //This is where a call to
business logic would go.
DataRow [] drs =
dsTrialData. Tables [ "TrialOutputs " ] . Select ("trial_id = " + trial_id) ;
if (drs. Length != 0)
{
DataRow dr = drs[0];
dr [ "me result"] = result;
else
DataRow dr =
dsTrialData . Tables [ "TrialOutputs " ] . NewRow ( ) ;
dr ["trial_id"] = trial_id;
dr ["mc_result"] = result;
dsTrialData. Tables [ "TrialOutputs"] . Rows .Add (dr) ;
}
dsTrialData . AcceptChanges ( ) ;
return true;
Note: you can modify a base trial, but you cannot save that trial back to the database.
If the 'Create New' trial button is disabled, the site is read-only. Click Data, Save Site Data
Record As ... in the main menu to save the record as a writable site.
75
-------
A.8.3.4 Saving a New Trial
Saving a new trial requires overriding the pushNewTrialToDataBase ( string
newTrialName) method.
protected override void pushNewTrialToDataBase(string newTrialName)
{
base.pushNewTrialToDataBase (newTrialName);
prepDataTransport("TrialInputs");
prepDataTransport("TrialOutputs");
this.tempTriallD =
cvi db manager.InsertNewTrialData(this.dataTransport,newTrialName,toolID);
Note: If the user has string or integer fields in the dataset, he may also have to
override prepDataTransport. Clicking the button on the calculator labeled 'Create New' will
create a new trial and put its data into the database.
A.8.3.5 Updating a Trial
After a trial is created and changes are made, users will save their changes back to the
database. To implement this functionality, override the sendSingleUpdate method as shown
below:
protected override void SendSingleUpdateToDatabase (int TriallD)
try
this . suspendErrorNotification = true;
base. SendSingleUpdateToDatabase (TriallD) ;
DataRow [] drTemp =
dsTrialData. Tables [ "TrialData" ] .Select ("trial_id = " + TriallD);
if (drTemp. Length == 1)
{
DataRow tdr = drTemp [0];
76
-------
DataRow [ ] tdins =
dsTrialData. Tables [ "Triallnputs " ] . Select ("trial_id = " + TriallD) ;
DataRow [] tdouts =
dsTrialData. Tables [ "TrialOutputs " ] . Select ("trial_id = " + TriallD);
cvi_db_manager . UpdateHydroClusterTrial ( tdr , tdins , tdouts ) ;
else
throw new CVIException (drTemp . Length + " rows
were found in the datasetfor trial " + TriallD) ;
JbroadcastCVIDataEvent (TriallD , this . toolID ,getUpdateTrialName (TriallD
) , ClusterCalculator . CVITOOLNAME , TrialManager . CVITOOLNAME , CVI_DataManagers . C
VIDataEventArgs . operationEnum. UpdateTrial) ;
}
catch (Exception ex)
{
throw new CVIException ( "Error occurred while
attempting to save trial .", ex, false) ;
}
finally
{
this . suspendErrorNotif ication = false;
Two important parts of this code to note are the
cvi_db_manager . UpdateHydroClusterTrial (tdr , tdins , tdouts)
and the
broadcastCVIDataEvent (TriallD , this . toolID ,getUpdateTrialName (TriallD) , Clust
erCalculator . CVITOOLNAME , TrialManager . CVITOOLNAME , CVI_DataManagers . CVIDataE
ventArgs . operationEnum. UpdateTrial) .
77
-------
The statement
cvi_db_manager .UpdateHydroClusterTrial (tdr , tdins , tdouts) , though
listed as updating the hydro or cluster trials, will work for generic calculators that only have
number inputs. broadcastCVIDataEvent tells other WHAT IF components about
changes to the data. For instance, when the trial is saved with a new name, the TrialManager
window will refresh with the new trial name under the treeview because it is looking for
CVIDataEvents.
A.8.3.6. Deleting Trials
When deleting a trial, override the deleteTrial method and paste the code below.
protected override void deleteTrial (int _trialID)
try
base. deleteTrial (_trialID) ;
broadcastCVIDataEvent (_trialID, this . toolID, " " , CVITOOLNAME, TrialManage
r . CVITOOLNAME, CVI_DataManagers . CVIDataEventArgs . operationEnum. DeleteTrial ) ;
object o = new Object();
EventArgs e = new EventArgs ( ) ;
TrialData_PositionChanged (o ,e); //the parameters
are just filler
}
catch (Exception ex)
{
throw new CVIToolException ("Error in deleteTrial
for trial id " + _trialID + ".", ex, false) ;
78
-------
Realistic Additions
The example shown was simple. WHAT IF may serve as host to different kinds of
calculators of varying complexity. For example, some may display their results in a label,
while others display their results in data grids. Developers may have to either override
additional methods, or modify the methods in this example more extensively to achieve the
desired results.
79
-------
Appendix F Database Schema
physiographic
PK
code
section
province
C-Variable Length(4) .
C-Variable Length(64)
C-Variable Length(64)
ss_source_huc :
PK
PK,FK1
hue id
aeoool id
N-Decimal(20,0)
N-Signed Integer
•XD-4
ss_geopol
PK
FK1
FK2
aeoool id
state
county
stream_name
physiographic_code
ecoregion 4
loc_id
N-Signed Integer
C-Fixed Length(2)
C-Variable Length(64)
C-Variable Length(128)
C-Variable Length(4)
C-Variable Length(32)
N-Signed Integer
ss aux att
PK
PK.FK1
attribute
ssid
num_value
str_value
C-Variable Length(32)
N-Signed Integer
N-Floating Point
C-Variable Length(255)
ssjoc
PK
loc id
latitude_dd
longitude_dd
N-Signed Integer
N-Floating Point
N-Floating Point
H-
ss_source_site
PK,FK1
PK
ssid
source site
tooMd
visit_date
N-Signed Integer
C-Variable Length(64) :
N-Signed Integer
T-Date
streamsite
PK
FK1
ssid
readonly
impacted
game
name
author
src_ssid
src_hydro
src bio
dominant_sp_id
abundant_sp_id
last_modified
description
geopoHd
N-Signed Integer
N-Signed Integer
N-Signed Integer
N-Signed Integer
C-Variable Length(255)
C-Fixed Length(4)
N-Signed Integer
C-Variable Length(255)
C-Variable Length(255) '
N-Signed Integer
N-Signed Integer
T-Auto Timestamp
C-Large Length
N-Signed Integer
•H-
• H-
ss_fish
PK.FK1
:PK,FK2
'PK.FK3
ssid
tool id
so id
abundance
suitability
N-Signed Integer :
N-Signed Integer
N-Signed Integer
N-Floating Point :
N-Floating Point
• H-O;
ss_bass_file
PK,FK1 ssid
PK,FK2 file id
N-Signed Integer :
N-Signed Integer
tool trial
.PK
FK2,U1
FK1,U1
U1
trial id
ssid
tool_id
name
base
last_modified
N-Signed Integer
N-Signed Integer
N-Signed Integer
C-Variable Length(64)
N-Signed Integer
T-Auto Timestamp
bass_file
PK
file id
name
mod_date
xml
N-Signed Integer
C-Variable Length(255)
T-Date & Time
C-Large Length
bass_plot
PK.FK1
PK
trial id
number
name
extension
description
bindata
N-Signed Integer
N-Signed Integer
C-Variable Length(255)
C-Fixed Length(4)
C-Variable Length(255)
R-Picture
trial_data
PK.FK1
PK
PK.FK2
trial id
ndx
short name
num_value
str_value
int_value
N-Signed Integer
N-Signed Integer
C-Variable Length(31)
N-Floating Point
C-Variable Length(255)
N-Signed Integer
*>
80
-------
-H-
cvi tool
PK
tool id
version
name
N-Signed Integer
C-Variable Length(10)
C-Variable Length(32)
trial datum
PK
PK.FK1
short name
emap_equiv
tool id
gui_name
put
type
description
units
min_val
max_val
field_marker
required
uses ndx
C-Variable Length(31)
C-Variable Length(31)
N-Signed Integer
C-Variable Length(64)
C-Fixed Length(3)
C-Fixed Length(11)
C-Variable Length(255)
C-Variable Length(255)
N-Floating Point
N-Floating Point
C-Fixed Length(1)
N-Signed Integer
N-Signed Integer
-CX
i sp_datum_property
PK.FK2
PK.FK1
PK.FK3
SP id
tool id
short name
coefficientl
coefficient2
coefficients
intercept
rank
N-Signed Integer
N-Signed Integer
C-Variable Length(31)
N-Floating Point
N-Floating Point
N-Floating Point
N-Floating Point
N-Signed Integer
; tool_property
PK.FK1
PK
tool id
property
num_value
str_value
N-Signed Integer
C-Variable Length(32)
N-Floating Point
C-Variable Length(255)
fish_species
PK
SP id
com_name
sci_name
bass name
N-Signed Integer
C-Variable Length(255)
C-Variable Length(255)
C-Variable Length(255)
cluster
PK
number
name
N-Signed Integer
C-Variable Length(64)
-CX
cluster_filter_6
PK
PK.FK1
huc6
SP id
C-Fixed Length(6)
N-Signed Integer
sp_group
PK
PK.FK1
group id
sp id
N-Signed Integer
N-Signed Integer
sp_cluster
PK,FK1
PK.FK2
PK.FK3
SP id
tool id
number
abundance
N-Signed Integer
N-Signed Integer
N-Signed Integer
N-Floating Point
bmp
PK
name
C-Variable
Length(150)
bmp_adjust
FK1
bmpname
increase bool
short_name
C-Variable Length(1 00)
N-Signed Integer
C-Variable Length(1 00)
81
-------
Appendix G External Text Files
CSV Files
ClusterIBI_AbundanceModel . csv
ClusterIBI_BiomassModel.csv
ClusterSpecies_AbundanceModel.csv
ClusterSpecies_BiomassModel.csv
Coefficients_Cluster_AbundanceModel.csv
Coefficients_Cluster_BiomassModel.csv
Coefficients HSS.csv
drainageBasinSpecies.csv
maha maia biota, csv
maha maia relative Abundance, csv
parameters Cluster.csv
parameters HSS.csv
Physio. csv
sub Specie s_HS S . csv
Coefficient matrix for predictor variables and clusters
for Cluster Abundance.
Coefficient matrix for predictor variables and clusters
for Cluster Biomass.
List of fishes, the clusters to which they correspond,
and their abundance.
List of fishes, the clusters to which they correspond,
and their biomass.
Coefficient matrix for predictor variables and clusters
for Cluster Abundance.
Coefficient matrix for predictor variables and clusters
for Cluster Biomass.
Species, Parameter, and Value table for the HSS
calculator predictor coefficients.
True false matrix that tells if a species is either
present in or absent from a HUC.
Biomass density grid for species at the sites where
they were observed.
Relative abundance grid for species at the sites where
they were observed.
Parameter definitions for the Cluster Calculators.
Contains descriptions, equivalent EMAP names,
input range definitions, units, and supplementary
notes. (These notes are not used in the CVI
Applications.)
Parameter definitions for the HSS Calculators.
Contains descriptions, equivalent EMAP names, and
input range definitions.
Map between emap names and physiographic
provinces.
Map between the fish groups and their species.
82
-------
XML Files
bassCommandDefaults .xml
bassFileDefaults.xml
cvi support info .xml
map config.xml
toolfilter.xml
bassCommandNames.xml
bassRootMRU.xml
mru.xml
bassconfig.xml
cvi cnxn.xml
cvi supplements .xml
graphingOptions .xml
tooldoc.xml
Contains default values for select BASS command xml nodes.
Contains xml that defines templates for new bass files. For instance, the
project xml node creates a new BASS project with default BASS
commands.
Contains the release date for WF£AT IF. Has the potential to hold other
support information for WF£AT IF.
Configures the MapWindow GIS control.
Governs which tools appear in the trial manager. So far, all tools appear
in the Trial Manager window, but this file has the flexibility to change
which tools are directly available to the user.
Defines command names for each type of file in BASS. Loads the
command names into the combo box on the file editor.
Stores paths to BASS root directories for the MRU menu.
Stores site names and ID's selected by the user. Data is loaded into the
MRU menu in WHAT IF under Data, Open Site Data Record.
Contains supporting information for both BASS commands and the
BASS application. The first section holds information about BASS
commands, and the corresponding editor needed to use those commands
in BASS. This section also configures the controls on different editors.
For example, a command that takes three parameters is edited with a
data grid. One of the xml nodes contains the definitions for editor
columns and the row values for default parameters.
Contains configuration parameters for the database connection tool. All
database connection inputs are stored here.
todo: write
Holds parameters that tell the plotting tool how to draw different graphs.
This document supports the building of the meta document that will
open when the user clicks the About link. The name attribute will be
appended to the tag of the About Hyperlink on each GUI form in CVI.
The link will take the following form:
[targetdirectory]/tooldoc/index.html
83
-------
XML Schema Files
bass-22.xsd
BASS XML Schema. Governs the structure and allowable
values of the BASS XML DOM.
cvi cnxn schema.xsd
Defines dataset that holds the CVI Database Connection
parameters. Only used with the Connection Editor Window.
dat-file.xsd
Defines the data structure that supports the dat-file editor in
the BASS GUI.
initial conditions .xsd
Defines the data structure that supports the initial conditions
editor in BASS.
plots.xsd
Defines the data structure that supports the dataset behind the
plotting tool. Dataset holds transformed xml data from the
BASS model.
XML Stylesheet Language Files
bassOut2DS.xslt
extractDirView.xslt
stage2Parser.xslt
bass-22.xsl
meta index.xsl
Transforms BASS output xml into that which can be loaded by a dataset through
the dataset LoadXML method.
Constructs a hierarchical view of the BASS DOM so that the files are viewable
in the file hierarchy.
This stylesheet will convert the output of the stage 1 parser to XML that is
compatible with the BASS 2.2 schema. The stage 1 parser is C# code in the
BASS_IO namespace that reads BASS input files and parses them into an
intermediate XML form, which is the input to this transformation code.
Transforms BASS DOM data back to flat files, readable by the BASS model
Transforms the tooldoc.xml file into the meta index.html file.
Other Files
helpdoc.css
Cascading style sheet for the meta document that links to
WHAT IF calculator documents.
basscmdoptions.txt
File contains command line options and descriptions for
configuring BASS runs. Used in BASS application when the
'View Command Line Options' link is clicked.
84
------- |