# Presentation: 243825

## Utah Department of Environmental QualityDivision of Water QualityModern STORET Implementation

**Why Move From a Working Database To STORET:**
- Originally a Dept. of Health Wang Application
- Database Housed on Dept. of Health Hardware
- Paid Dept. of Health $--- per Year for Support
- Application Support Provided Dept. of Health
- Only Two Appx Programmers in the State
- Incumbent Programmer Left Dept. of Health
- Application Software Outdated
- Biological Data not Stored in Database
- Chronic Standards Not Stored or Reported
- Data Retrieval Limited Access

**Notes:**
The Division of Water Quality was under outside political as well as inside motivation to replace a twenty year old custom software application.  Some of the reasons we needed to replace the application are listed above.

## Utah Department of Environmental QualityDivision of Water Quality Modern STORET Implementation

- Utilize and Enhance Modernized EPA STORET
- Pro’s:
- Easy Transfer of Data to EPA
- Application Follows WQ Business Process
- DEQ Supported Database
- EPA Support, Updates and Enhancements
- Con’s:
- Does Not Support DEQ Unique Needs
- Data Entry is Awkward
- Data Transfer from Appx

**Notes:**
Some of the Pro’s and Con’s of moving to STORET are listed above.  For better or worse we made a recommendation to management that we convert to the STORET software. 

## Utah Department of Environmental QualityDivision of Water Quality Modern STORET Implementation

**First Accomplishments**
- Historical Data Migrated to Modernized STORET
- STORET Input Module Developed
- DEQ/WQ, Region VIII, Headquarters: Cooperative

**Notes:**
Installing the software was easy,  Migrating the historic data was not.  It took about 9 months before it was complete.  When I was first introduced to modernized storet, I believe the first question I asked of the STORET development team was “how can I transfer and migrate data electronically?”   We could not have adopted the STORET software without the STORET Import Module (SIM).  The State of Utah helped with the development of that software.  

## Utah Department of Environmental QualityDivision of Water Quality Modern STORET Implementation

- Using SIM as a major import tool for:
- Field/Multi-probe data
- Chemical Data
- Macroinvertebrate Data
- Periphyton/Phytoplankton Data
- Habitat Assesment Data
- Batch update process is used for lake profile data

**Notes:**
We use SIM to migrate our Field, Chemical, Macrinvertabrate, Periphyton, Phytoplankton and Habitat Assesment data.  We use the Batch Update process for lake profile data.
The field, chemical, and macroinvertabrate data is manipulated electronically after adequate QA/QC review.
Periphyton, phytoplankton, and habitat assessment data are currently hand entered into spreadsheets before migration into STORET.
Lake profile data is also handled electronically.
If you would like further details on SOP’s, formats, data file attributes, please ask me for one of my handouts, and I will discuss them with you then.

## Utah Department of Environmental QualityDivision of Water Quality Modern STORET Implementation

**Notes:**
As an example this slide shows a Hydrolab file that has been reviewed. We wrote a small software application that reformats this data such that each result is on a single line.

**Utah Department of Environmental Quality****Division of Water Quality****STORET Modernization**

- Tables Used in Reformating Multi-probe Data

**Notes:**
Of course we had to build a couple of tables in able to reformat the data.

## Utah Department of Environmental QualityDivision of Water QualitySTORET Modernization

- Reformat Multi-Probe Data for SIM2

**Notes:**
This is a picture of the application screen we developed.  A nice feature of this of software is that it also questions or prompts the reformater for some metadata that is not included in the hydrolab file.
Similar software has been developed for the Chemical and lake profile files, and similar philosophies are used on other files.

## Utah Department of Environmental QualityDivision of Water Quality Modern STORET Implementation

- DEQ/WQ STORET Enhancements
- Standards Module
- Trip Module
- Data Export/Reporting Module

**Notes:**
Now that we have a means of adding data to STORET, We have to provide state specific reports for our data users.  We sorted our objectives into three categories. They were the Water Quality Standards module, a trip module, and a catch all category called export/reporting enhancement. The Standard module was necessary because each State has independently developed numeric water quality criteria for 305b reporting.  We knew that STORET was not capable nor was it ever intended to incorporate state specific standards.

## Utah Department of Environmental QualityDivision of Water QualitySTORET Modernization

- STORET Database Comprised of Tables
- 	Tables Owned by User STORET  Read Only
- 		Characteristics, Units of Measure, Field Gear,
- 		Hydrological Units
- 	Tables Owned by User STORET1 - Read/Write Ability
- 		Stations, Station Visits, Trips, Samples,
- 		Results, Field Measurements/Observations
- 	Tables Owned by SIM2 - Data Entry Transition Tables
- 		Stations, Station Visits, Samples, Results
- 	Tables Owned by UTAHDWQ - Read/Write Ability
- 		Utah Water Quality Specific Tables
- 			Water Quality Standards, Use 				Designations, Transition Tables

**Notes:**
The Hydrolab reformat tables were the first tables we introduced to you that were owned by UTAHDWQ.
We are now going to introduce some more tables that are owned by DEQ

## Utah Department of Environmental QualityDivision of Water Quality Modern STORET Implementation

**Notes:**
These are the tables we built to house the waterbody classifications and there associated numeric water quality standards.

## Utah Department of Environmental QualityDivision of Water Quality Modern STORET Implementation

**Notes:**
This is an example of the input screen for numeric criteria, please note both acute and chronic values can be entered.  Ranges can be entered, which is useful, and mathematical formulas can be entered for criteria that is dependent on other characteristics.

## Utah Department of Environmental QualityDivision of Water Quality Modern STORET Implementation

**Utah Department of Environmental Quality****Division of Water Quality**** Modern STORET Implementation**

**Notes:**
This is an example of one of the reports that we produce using the water quality standards.  This is perhaps my favorite report because I believe this data is useful.  Please note the N02+NO3 characteristic, I find this kind of data at least interesting if not alarming, note the mean of all samples is 27 mg/l.

## Utah Department of Environmental QualityDivision of Water Quality Modern STORET Implementation

**Utah Department of Environmental Quality****Division of Water Quality**** Modern STORET Implementation**

**Trip Module**

- Our monitoring strategy consists of “runs” that are repeated
- All our projects are built into these comprehensive runs
- We share and coordinate software with our Chemical Laboratory

**Notes:**
The trip module brings stability to our monitoring efforts.  We review and change our “strategic monitoring plan” on a yearly basis, and during the year we perform runs that sample all the sites and appropriate characteristics for each site for all our projects in a watershed.  We also coordinate with the laboratory and provide them this data electronically.

## Utah Department of Environmental QualityDivision of Water QualitySTORET Modernization

**Notes:**
These are the tables we built to store the data needed for a monitoring run.  You see the storet table on the far left, and the tables we built above and on the right.

## Utah Department of Environmental QualityDivision of Water QualitySTORET Modernization

**Notes:**
This is an example of an input screen for the stations associated with the Bear River watershed run.  Note the order of the sites ie sequence number, the project the site is associated with, and again we have bells and whistles like comments for the monitoring crew.

## Utah Department of Environmental QualityDivision of Water QualitySTORET Modernization

**Notes:**
This is an example of an input screen for the bottles associated with a site on a this run

## Utah Department of Environmental QualityDivision of Water QualitySTORET Modernization

**Notes:**
This would be an example of the paperwork the monitoring crew could take into the field.  Note the paperwork on the right tells monitors which sites to do and in what order, and the paperwork on the left is basically a site analyses plan for this visit.  So if monkeys could read English I could get them to fly, excuse me travel through a watershed and collect everything necessary with at least a smaller chance of mistakes.  We are still implementing some software that will put pictures of sites on the backside of the site sheet with directions, and coordinates.

## Reporting Module

- We have built about 20 reports to meet our programs needs
- Reports can be run against station groups
- Reports can exported in a variety of electronic formats

**Notes:**
The reporting module has been a very successful and fairly easy to administer.  If you give your programmer specific requirements, these custom reports are easy to build.  Two of the robust highlights are the ability to run these reports against groups of stations, and that the reports can be exported in a variety of formats, text, pdf, excell, etc.  Not all these reports are very useful to the average data user, several of them were specifically built to meet Quality Assurance needs.

## Utah Department of Environmental QualityDivision of Water QualitySTORET Modernization

**Notes:**
This is an example of a lake profile report.  Metadata is in the header area, and the actual data is listed below.  Please note the export data button that allows you to download this data in a variety of formats.

## Utah Department of Environmental QualityDivision of Water QualitySTORET Modernization

**Notes:**
This is another example of a report we call the facility report.  After we sample a permitee we usually send them the results in this form letter.  The responsible permitting authority is required to review and sign the letter before it is sent.  The permitting authority is given the option of checking a box that would indicate non-compliance.

## Conclusion

- The STORET database does not meet the needs of individual programs.  Because monitoring program methods and goals are as diverse as our country, it would be impossible to meet the needs of every individual program.  STORET does provide the basic tables and appropriate element relationships necessary for any program to store data.  The STORET database provides an excellent “starter” database that can be customized to meet the requirements of individual programs

**Notes:**
The STORET database does not meet the needs of individual programs.  Because monitoring program methods and goals are as diverse as our country, it would be impossible to meet the needs of every individual program.  STORET does provide the basic tables and appropriate element relationships necessary for any program to store data.  The STORET database provides an excellent “starter” database that can be customized to meet the requirements of individual programs