Using STORET as a Database Design Model

1. Using STORET as a Database Design Model Deb Soule Watershed Management Bureau New Hampshire Department of Environmental Services

2. Agenda • Background on NHDES monitoring data • Development of our own database based on STORET • Web access to database • Electronic data submittal process

3. Prior to 2003, how would one obtain NHDES monitoring data? Pick through overflowing file cabinets. Pick through disks and databases of various formats. Know someone who knew where the data was.

4. Realizing this wasn’t a solid approach to environmental data management, we did the following about 4 years ago… • Reviewed commercial databases to see if they would work for us. (Expensive option.) • Asked other states if they would share their database with us. (Cheap option but not very fruitful.) • Reviewed STORET as a possible database since it met reporting requirements. (Later deemed it didn’t fit all of our needs.) • Formed a department wide committee to develop a database. (Initially a no-go since developing consensus was difficult and painful.) • Began development of own database within Watershed Management Bureau based on STORET.

5. Development Process • Bureau met every 2 weeks for several months to determine what they collectively wanted to see in a database. (Providing home cooked food always brings them in.) • STORET structure and requirements were used as guidance. • Program needs were incorporated • Included program specific columns • Complaints module • ADB linkability • Integration of inspection data with monitoring data

6. Everything went smoothly… Everyone was in complete agreement. The process was approached with full disclosure and openness. Change was welcomed.

7. Database Development • Took approximately 4 months for one developer to build core Oracle database (called the Environmental Monitoring Database (EMD)) in-house • Testing was done as soon as each section of the database was completed. • Old bureau data was seamlessly and effortlessly imported into the new database…

8. The Environmental Monitoring Database (EMD) • Went live March 2003. • Accepts air water, soil, product etc. field and lab data. • Handles physical/chemical now – biological later. • Handles QA/QC samples. • Has automatic nightly imports from state and limnology labs. • Soon will have ability to capture data logger files. • Fulfills beach reporting requirements.

9. The Environmental Monitoring Database (EMD) (cont.) • Integrates shellfish, beach, and complaints inspection data with monitoring data. • As of 10/2004 had 84 projects, 8100+ stations, & 300,000 activities. • Creates SIM compatible export files for upload to STORET. • Linkable to Assessment Database (ADB) and will be developing ability this next year to create automatic assessments based on EMD data.

10. How about the rest of NHDES? • Once they saw we had a functioning database with dedicated resources, they wanted to join in. • Developed department wide database committee to collectively create changes to the database. • Other bureau data being incorporated as time and staff resources permit. Now besides Watershed data, have Superfund, Site Remediation, and Geological Survey data streaming in. Public drinking water data next on the horizon. • Database becomes data warehouse for department’s data.

11. Beyond NHDES • Goal is to make the database a warehouse for all NH environmental monitoring data. • Now have Great Bay Coast Watch, Green Mountain Conservation Group, and Upper Merrimack Monitoring Group data in database. • Data from UNH, EPA, and White Mountain National Forest to follow soon. • Outside agencies more willing to share data with us since we will export it to STORET for them and make it available on the web.

12. EMD Data on the Web • Went live June 2004. • Only “final” data is available via flat file created each night. • Can query by: Organization Project Name Station ID Station Type Town County State Waterbody Name River Name Designated River HUC 12 Name Analyte Medium Beach Name • Data returned via email in zipped/unzipped, Excel/pipe delimited format complete with your query parameters. • Excel has column and row limitations – separate worksheets for project station data vs result data. • http://des.nh.gov/OneStop/Environmental_Monitoring_Query.aspx

13. Electronic Data Submittal Process • Needed a way to receive monitoring data electronically from consultants, outside labs, volunteers, cooperating agencies etc. • Developed Excel spreadsheets to import station and activity data separately (with XML planned for the future). • Developed web registration and submittal forms with data checker.

14. Electronic Data Submittal Process (continued) • Developed interim table to capture what was being submitted, by who, when etc. for data integrity purposes. • Piloted process with select WMD consultants and labs during summer/fall 2004. • Currently incorporating Drinking Water Program elements into template to create one NHDES wide data submittal template.

15. Our next steps? • Continue mining state lab database for data to import into EMD. • Continue bringing in more bureaus and outside agencies into EMD. • Enhance EMD web site – including a GIS component. • Put electronic data submittal templates into production. • Add a biological component to EMD. • Incorporate surface and drinking water standards.

16. Questions/Suggestions? Contact Information: Deb Soule Watershed Management Bureau New Hampshire Department of Environmental Services dsoule@des.state.nh.us (603) 271-8863