# Presentation: 217409

## Analysis tool pack for STORET for biological assessment

***Erik W. Leppo***
***Las Vegas, Nevada***
***November 8, 2001***

**Notes:**
Work for Tetra Tech, Inc. in Baltimore, MD.  One of the leads on the EDAS database.  This is the evolution of the EDAS database.  Redesigning it to be more closely tied to STORET.

## Ecosystem

**Five classes of environmental variables that affect water resource integrity and overall biological condition (modified from Karr et al. 1986).**

**Flow Regime**

**Volume**
**Watershed**
**Characteristics**
**Runoff**
**Velocity**
**High-Low Extremes**
**Precipitation**
**Groundwater**

*Ecosystem*
*Health*

**Chemical Variables**

**Metals**
**Nutrients**
**Dissolved Oxygen**
**Chemical Solubilities**
**Organics**
**Alkalinity**
**Temperature**
**Hardness**
**Turbidity**

**Biotic Interactions**

**Disease**
**Reproduction**
**Competition**
**Predation**
**Feeding**
**Parasitism**

*Biological*
*Condition*

**Stressor**

**Response**

**Status**

**Energy Source**

**Nutrient Availability**
**Organic Matter Inputs**
**Primary and **
**Secondary Production**
**Seasonal patterns**
**Sunlight**

**Habitat Structure**

**Sinuosity**
**Current**
**Riparian Vegetation**
**Substrate**
**Instream Cover**
**Gradient**
**Channel Morphology**
**Bank Stability**
**Canopy**

** **

**Notes:**
What kind of data is collected through biological monitoring programs?  Biological monitoring requires holistic assessments of aquatic communities and the watersheds which drive them.  As such, a full suite of data representing biotic interaction, energy sources, chemical parameters, habitat structure and flow regimes is amassed by biologists both in the office and in the field.  Thus requiring the need for a logical, flexible and organized data management and analysis system.  From this diagram I’m going to focus just on the biological indicators, and specifically, benthic macroinvertebrates.

## Biological Monitoring Data

**Biological Monitoring Data**

- Biological monitoring is becoming more and more widespread in the US in response to heightened emphasis on preserving the biological integrity of the nation’s waters under the CWA.
- Data produced through these monitoring efforts are:
***incorporated into state’s 305(b) reports,***
***used to generate 303(d) lists, and***
***involved in the development and implementation of TMDLs.***

## Use STORET To Hold Your Data

**Notes:**
What happens to this data when the biologists return to the office?  What is the next step?  Analysis and reporting.
But in order to analyze all of this data it needs to be formatted, that is where STORET comes into play.

## Why Analyze Your Data

- Translate raw data into metric values.
* **Hard to communicate species lists.*
- Look for trends over time and patterns within your watershed.
* **Identify new hotspots, and watch for change in water   bodies of known impairment** .*
- Communicate interpreted results of your sampling efforts to the public.
* **Easier to create understandable, graphic displays of metrics than raw data.*
- Allows for QA/QC analysis of data.
*Ensures the reliability of your data.*

## Communicating Results

**12 EPT Taxa**

**78% Impervious**

**Channelized**

**4.2 mg/l D.O.**

**Obvious NPS sources**

**72% Chironomids**

**23 Total Taxa**

**Substrate Availability 13**

**10% Collector-Filterers**

**13% Ephemeroptera**

## Communicating Results

**12 EPT Taxa**

**78% Impervious**

**Channelized**

**4.2 mg/l D.O.**

**Obvious NPS sources**

**72% Chironomids**

**23 Total Taxa**

**Substrate Availability 13**

**10% Collector-Filterers**

**Communicating Results**

**13% Ephemeroptera**

**Notes:**
Even after the data is collected and stored it needs to analyzed and results communicated to your audience, be it managers, other biologists, or the public.

## Displaying Your Data With GIS

**Displaying Your Data With GIS**

**Notes:**
One example of communicating results is through the use of interactive maps.

## Digestible Site Reports

**Digestible Site Reports**

**Notes:**
Another example is a site report.  This is the Florida’s Eco Summary Report.  It takes raw collected data, converts it into a multimetric index, specifically the Stream Condition Index (SCI), translates that score into a narrative score (e.g., Good, Fair, or Poor) and puts it all into an easy to read format.

## Getting To The Analysis Step

- In the past have had to export data from STORET and format it for use in another program.
- The Analysis Tool Pack allows the user to skip formatting and query data directly from STORET.
*Limited to benthic macroinvertebrates (for now).*
*Addresses one of the concerns from end-users of having to use multiple databases (i.e., one for storage and one for analysis).*

## Getting to The Reporting Step

- Data entry.
*Input data into STORET.*
- Organize your data for analysis purposes.
- STORET has no analytical capabilities, it wasn’t designed with any in mind.  Through ODBC (Open Database Connectivity) can link to the data in STORET to do calculations.

**Notes:**
Many users keep their data in different databases making it difficult to analyze all the data for a particular project. STORET can handle most types of data.

## Why STORET?

- More and more funding sources require that data collected be put into STORET.
- There is some resistance due to the view that a user sees little return on his/her investment of time and energies.
- Having an analysis tool can help to change this mindset (along with the import utility – SIM2).

## How the Tool Pack Works

- Through the use of ODBC users will no longer have to spend time exporting their data from STORET to another database, but can access their data directly with the use of this utility.
- Based on Access 2000 this utility connects to a view specific benthic macroinvertebrate-specific view within STORET.
- All data necessary for metric calculations is incorporated into this view.  It will have the same suite of metrics as EDAS, i.e., all of the metrics contained in the Rapid Bioassessment Protocols (2nd Edition).

**Notes:**
Different database have been designed to work with the same types of data as STORET but are more specialized in their analysis approach (e.g., EDAS).

## Parameters for BioView

- An EDAS-BioView within STORET.
- Linked in Access with the following restrictions:
*Activity Community*
- Benthic Macroinvertebrates
*Activity Intent*
- Taxon Abundance
*Result Unit*
- Count
*Result Value Status*
- Final (Can be changed to Preliminary)
*Bio Group Type*
- Multi-Taxon Population Census

**Notes:**
The BioView is based upon the current Bio All View.  With a few additional restrictions on the displayed data.

## Slide 15

**Notes:**
View of a Linked spreadsheet from within Access 2000 of the Bioview from STORET

## Slide 16

**Notes:**
Forms driven within Access.  Utilizes the ODBC link to the STORET view and can run select metric queries and reports. An Advanced User can use Access to write his/her own queries.

## Slide 17

**Notes:**
Several predefined Reports.  This one is a summary of all metrics for each site.  Color coded at different grouping levels for this presentation.

## Future Functionality

- Just as the SIM module will simplify the import of data into STORET, the EDAS Analysis Tool Pack will simplify analyzing data in STORET.
- It is planned to build in the capability to analyze other data types (biological, chemical, and physical).
- Also planned is the ability to allow users to input their biocriteria endpoints and output assessment reports.
- Move toward a more stable platform, Oracle, and away from the constant “upgrades” of MS Access.

## Analysis Tool Pack Advantages

- The Tool Pack will encompass most of the functions that are built into some other databases, e.g., EDAS.
- Data being analyzed will always be current.
- Can easily share data.
- Built in reports based upon data analysis.