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 wasnt 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
  didnt 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 departments
  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_at_des.state.nh.us
• [phone]