Vital Signs Water Quality Data Management and Archiving

Introduction

The water quality component of the Natural Resource Challenge (NRC) requires that Vital Signs Networks archive all physical, chemical, and biological water quality data collected with NRC water quality funds in the National Park Service's STORET database maintained by the NPS' Water Resources Division (WRD).  Vital Signs Networks are encouraged to also archive any other aquatic-related data collected with base Vital Signs funding in STORET.  To facilitate archiving NPS data in STORET the WRD has developed a series of Access-based templates (called NPSTORET), patterned after the Natural Resource Data Templates (http://science.nature.nps.gov/im/apps/template/), for Networks to use to enter their water quality data in a STORET-compatible format. A STORET Electronic Data Deliverable (NPSEDD) specification has also been developed for those Networks that decide to not use NPSTORET. Vital Signs Networks will send their data from NPSTORET or from their own data system following the NPSEDD specification to the WRD on an annual basis for quality assurance and upload into the WRD's copy of STORET and the Environmental Protection Agency's (EPA) STORET National Data Warehouse.

What is STORET

STORET (http://www.epa.gov/storet/) originated in the Department of Interior in 1964 and was moved to the EPA after its formation in 1970. "Old" STORET, which now exists as the STORET Legacy Data Center(http://www.epa.gov/storpubl/legacy/gateway.htm), was billed as the EPA's oldest and largest data system.  Legacy STORET contains the results of water quality monitoring activities conducted by states and other entities through 1998. In the late 1990s the EPA unveiled modernized STORET, a robust, scaleable Oracle-based water quality data system.  Many states, federal agencies (including the NPS), and others migrated their data from legacy STORET to new STORET to take advantage of the improved architecture, metadata, better Internet accessibility, and other reasons.  STORET users forward their local copies of STORET to EPA periodically for inclusion in the STORET National Data Warehouse (http://www.epa.gov/storet/dw_home.html).

Why has the NPS Adopted STORET

The EPA has developed and maintained STORET as a database to house ambient water quality data collected by states, federal agencies, volunteer monitoring groups, and other entities.  Under the Clean Water Act, states are responsible for ensuring that waters within their boundaries (including waters in National Park units) meet or exceed their designated beneficial uses.  Many states use STORET as their primary or secondary database and seek data from other entities in STORET for performing statewide assessments (e.g. 305b Assessment Reports) and taking regulatory and/or enforcement actions (e.g. 303d Impairment Lists, Total Maximum Daily Load development, etc.).  As a consequence, STORET is integral to implementation of the Clean Water Act as a national repository of data in a common format accessible to all on the Internet.  Beyond its importance in managing water quality, modernized STORET conforms to the recommended metadata standards promulgated by the National Water Quality Monitoring Council.  EPA spends more money developing, supporting, and enhancing the STORET database than the NPS spends on actual water quality monitoring.  The NPS made a significant investment in archiving a wide variety of historical data in legacy STORET and these data have now been migrated to modernized STORET.  The Natural Resources Management Guideline (NPS-77) states that the NPS should provide water quality monitoring data to STORET as the national water quality repository (http://www.nature.nps.gov/rm77/freshwater/WaterResources.htm).

The Details:

Vital Signs Monitoring Networks are responsible for ensuring that any physical, chemical, and biological water quality data collected with NRC water quality funding are archived in the copy of STORET operated by the NPS Water Resources Division.  Data collected by other agencies or entities in cooperative or contractual agreement with the NPS that may be entered in that agency's or entity's copy of STORET or other water quality database must also be entered in the NPS' copy of STORET so that the NPS can fully account to Congress for the expenditure of NRC water quality funds. To avoid having duplicate sets of data in the EPA's STORET National Data Warehouse, WRD will only transmit to EPA results flagged as 'Final.' Networks should flag data that should not go into the EPA's STORET National Data Warehouse as 'Preliminary.'

To facilitate this effort, the WRD has created a series of Microsoft Access-based templates, collectively referred to as NPSTORET, to enable Vital Signs Monitoring Networks to enter their water quality data in a STORET-compatible format with all relevant metadata and supporting documentation (e.g. Water Quality Monitoring Plan, Quality Assurance Project Plan, etc.).  NPSTORET includes templates for entering information about Projects, Stations, Metadata, and Results.  Using NPSTORET, Vital Signs Monitoring Networks will document the "Who, What, Where, When, Why, and How" of their water quality monitoring activities. NPSTORET also includes a Reports & Statistics template for generating reports, analyzing the data, producing graphics, exporting the data, and generating SIM-compatible import files.

For data entry, NPSTORET includes a module for importing stations and results from other user databases, labs, and other sources. EPA Legacy and Modern STORET and U.S. Geological Survey National Water Information System (NWIS) stations and results can be explicitly imported. Taxa lists can be imported for automatic characteristic generation.

For those Networks electing not to use NPSTORET for their water quality data, the STORET NPSEDD specification provides direction on the types, formats, and structure of data files that must be produced to satisfy the NRC STORET reporting requirement.

Although it is the responsibility of Vital Signs Monitoring Networks to ensure that all their NRC funded water quality data are entered in the WRD's copy of STORET, WRD staff will be assigned to assist each Network in this task.  Before the initiation of monitoring and/or data entry, WRD staff will advise each Network on how to configure NPSTORET to reflect accurately their water quality monitoring activities.  Vital Signs Networks will perform their own data entry. Annually, Vital Signs Networks will send their NPSTORET database or the STORET NPSEDD files to their WRD staff contact who will quality assure the data and, in consultation with the Network, make any edits/modifications necessary to upload the data into the WRD's copy of STORET in Fort Collins using the STORET Import Module (SIM). If necessary, a revised version of a Network's NPSTORET database will be returned to the Network.  On a monthly basis, the WRD's copy of STORET will be transmitted to the EPA for inclusion in the STORET National Data Warehouse (www.epa.gov/storet/dw_home.html) where the data will be accessible to the states, other entities, and the public on the Internet.

Where to Begin:

If you are writing a Network's water quality monitoring protocol and/or SOP you should consult WRD's Vital Signs Guidance (http://www.nature.nps.gov/water/VitalSignsGuidance). For Data Managers, the best place to begin would be to download and evaluate NPSTORET and the STORET Electronic Data Deliverable (NPSEDD) file specifications and determine which process best fits the Network’s data management strategy. Regardless of whether you choose to use NPSTORET or generate SIM compatible STORET NPSEDD files, there are certain common data elements that Networks can begin documenting immediately in their Phase 3 reports and/or protocols/SOPs. These include:

Station Location Data

STORET requires that every water quality monitoring station location must have an assigned latitude and longitude coordinate. Also, the horizontal datum to which these coordinates are referenced (typically North American Datum 1983 or World Geodetic System 1984) and the method by which they were obtained (e.g. GPS, Map Interpolation, etc.) must be provided.

Characteristic/Parameter Definitions

STORET enforces standardized nomenclature for what is sampled, measured, and observed. Networks must match their characteristics/parameters with the official standardized EPA list of 389,035 characteristics (found in tblDef_TSRCHAR in NPSTORET as of 10/10/2006 or at http://nrdata.nps.gov/Programs/Water/storetcharacteristics/storetcharacteristics.zip ). Performing this match to the EPA’s standardized characteristic list when protocols/SOPs are being written will provide sufficient lead time for the NPS to request EPA add a missing characteristic. Data Managers should ensure water quality protocol writers provide all the information below. Should you not find a characteristic/parameter that your Network will be monitoring in the EPA’s list, please contact Dean Tucker (970-225-3516 or at Dean_Tucker@nps.gov) so he can formally request that EPA add it. Some common standardized (spelling is exact) characteristics that many Networks will be monitoring include:

(1) pH
(2) Dissolved oxygen (DO)
(3) Specific conductance
(4) Temperature, water
(5) Flow
(6) Flow, severity (choice list)

(7) Turbidity
(8) Fecal Coliform
(9) All ITIS taxa

In addition to itemizing what standard STORET characteristics are being monitored, Networks must explicitly define the characteristics (as appropriate) in terms of:

1. Medium (water, air, biological, sediment, soil, etc.)
2. Sample Fraction
   
   

   STORET Sample Fraction	STORET Description
   Total	The total of all fractions of an analyte.
   Dissolved	The result-producing portion of the analyte is found in a liquid medium - it cannot be removed by filitration.
   Suspended	The result-producing portion of the analyte is suspended in the sample medium, either as, or absorbed to, particles that are more or less uniformly dispersed within the medium.
   Settleable	The result-producing portion of the analyte is found either in or absorbed to that part of the sample that has fallen or settled (i.e. fallen out of suspension) to the bottom of the sample container.
   Non-Settleable	The result-producing portion of the analyte is in or absorbed to particles that remain in suspension in the sample container.
   Supernate	The result-producing portion of the analyte is found in the liquid layer above a precipitate produced from a solution of a liquid.
   Fixed	The result-producing portion of the analyte is found in the liquid layer above a precipitate produced from the sample.
   Vapor	The result-producing portion of the analyte exsists in a gaseous state - under ordinary conditions it is a liquid or solid.
   Non-Volatile	The result-producing portion of the analyte is in a liquid or solid state under normal temperature and pressure.
   Volatile	The result-producing portion of the analyte evaporates readily at normal pressures and temperatures.
   Filterable	The result-producing portion of the analyte is extracted from the liquid medium by filtration.
   Non-Filterable	The result-producing portion of the analyte is in or absorbed to material that passes through the filter during the process of sample filtration.
   Acid-Soluble	The result-producing portion of the analyte becomes dissolved within the sample following treatment with an appropriate acid.
   Pot. Dissolved
   Free Available
   Total Residual
   Total Recoverable
   Comb Available

   
   
3. Unit of Measure
4. Value Type (actual, calculated, estimated)
5. Field or Lab Measured
6. Statistic Type (mean, max, min, mode, MPN, etc.)
7. Duration (# of hours or days)
8. Weight Basis (wet, dry, ash-free dry)
9. Temperature Basis (5 to 95 degrees C in 5 degree increments)
10. Particle Size Basis
11. Detection Limit (MDL or similar)
12. Lower Quantification Limit (PQL or similar)
13. Upper Quantification Limit
14. Analytical Procedure and/or Equipment
15. Sample Collection Procedure
16. Sample Handling Procedure
17. Lab Sample Preparation Procedure
18. Name/Contact Information for the Lab
19. Is Lab EPA Certified for the Characteristic?

The analytical procedure and/or equipment used to produce a result should include a reference along the lines of “ASTM D1688(C) Copper in Water by GFAA”, “EPA 245.1 Mercury in Water by CVAA”, “USGS B0051 Fecal Coliform Bacteria-Presumptive Test-MPN Method”, or “Hach 8156 pH in Water” where the procedure/method is a recognized standard. For “non-standard” analytical procedures/method, a thorough description is required. For characteristics that required the extraction of a sample, a sample collection procedure must be specified.

All this metadata should be captured in the Phase 3 report and/or protocols/SOPs. Be sure to review the report and/or protocols/SOPs to ensure that the protocol writers included this information. For documentation purposes, it might be useful to create a spreadsheet with the characteristics/parameters as row headers and the characteristic attributes (a. through s. above) as column headers. Have the water quality protocol writers fill in each attribute for each characteristic as appropriate so you have your metadata in one location. NPSTORET allows you to enter this information on its Metadata Template.

Downloads:

(1) NPSTORET v.1.54 (April 17, 2008))

NPSTORET is a series of Microsoft Access XP templates that comprise a database allowing users to document water quality monitoring projects in a manner compatible with the National Water Quality Monitoring Council's core water quality data elements in a format amenable for upload to the Environmental Protection Agency's STORET database - which is the NPS' water quality data archive.  User’s can enter Projects, Stations, Metadata, and Results on their respective templates.  A Reports & Statistics template allows users to generate reports, statistics, graphics, and exports of entered data.  Data import routines can be used to import users’ own stations and results, as well as stations and results from the Environmental Protection Agency's STORET Data Warehouse, Legacy STORET, and the U.S. Geological Survey's National Water Information System.  Taxa lists can also be imported for automatic characteristic definition.  Context sensitive on-line help, overview videos, and other detailed documentation help users learn how to use NPSTORET.

Use the NPSTORET setup program (NPSTORET Setup.exe) to install NPSTORET on your computer.  Be sure to download and follow the installation details. If you will be copying data from a previous version of NPSTORET (before v.1.50) into this version, be sure to read about how NPSTORET handles data migration to the new activity types.

NPSTORET was developed using Microsoft Access 2002 and Visual Basic for Applications.

The program requires references to the following (or more recent) versions of those libraries:

Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft Excel 10.0 Object Library
Microsoft Office 10.0 Object Library
Microsoft Word 10.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DOA 3.6 Object Library
Microsoft ADO Ext. 2.8 for DDL and Security

• You must be running Access 2002 or 2003 (not Access 2007).
• Be sure that your desktop screen resolution is set to 1024x768 or higher.
• You must be an "Administrator" or "Power User" to install and have write permission to the installation directory during use.
• To start the program, double click on NPSTORET.MDB or start Access 2002 or 2003 and open NPSTORET.MDB.
• Be sure to read the file: FirstTimeUser.doc for a quick introduction to how NPSTORET works or Workshop.doc for a more elaborate overview.
• Consult the file: DataImportInstructions.doc for detailed information on how to import existing data into NPSTORET.

(2)  NPS STORET Electronic Data Deliverable File Specifications v.1.1 (March 15, 2007)

The NPS STORET Electronic Data Deliverable (NPSEDD) file specifications provide a data-reporting format that parks, Networks, and other groups may use as one of the means (NPSTORET provides the other way) to satisfy the Natural Resource Challenge-Water Quality STORET reporting requirement.  NPSEDD Version 1.1 is a set of Excel spreadsheets that contains required and supplemental data fields that can be processed by the STORET Import Module (SIM).  After data providers populate the spreadsheets and send them to their Water Resources Division (WRD) contact, WRD will quality assure the files and upload the data to WRD’s STORET database with SIM.

Other federal and state agencies may have different versions of STORET data deliverable specifications that have been used to transfer data from field offices, labs, and other data sources to STORET.  We have attempted to adopt the best aspects of these other agencies’ specifications into the NPS’ own customized Electronic Data Deliverable format specifications.  NPSEDD1.1 is presently designed to accept field and laboratory results determined from water, sediment, soil, and air media.  Some biological (multi-taxon population census and tissue analysis) results can also be entered into STORET with the NPSEDD1.1 format.  Support for other types of biological monitoring data will be included in future versions of NPSEDD.

Installation:

Note:  If you have data in an earlier version of NPSEDD that you want to preserve (not be overwritten by the installation of a newer version of NPSEDD), be sure that your WinZip Extract screen does not contain a check mark for ‘Overwrite existing files’ or simply rename the existing NPSEDD directory before extracting the newer version.

a)Open NPSEDD1.1.ZIP in WinZip
b) Click the Extract icon
c)‘Extract to: C:\’ Check ‘
d) Use folder names’
e) Click the ‘Extract’ button

This will create the following directory structure:

C:\NPSEDD1.1
C:\NPSEDD1.1\Documents
C:\NPSEDD1.1\Examples
C:\NPSEDD1.1\Projects
C:\NPSEDD1.1\Reference Tables
C:\NPSEDD1.1\Results
C:\NPSEDD1.1\Stations

The file structure above is based on extracting to the C:\ drive; however, NPSEDD1.1.ZIP can be extracted to other drives and folders.  NPSEDD1.1 was designed in Microsoft Excel 2002.  Some of the reference files were written in Microsoft Word 2002.

Review the NPSEDD1.1.DOC in the compressed file for more information on the structure and features of NPSEDD1.1.

(3) NPSCol2Row v.2.12

NPSCol2Row is a general purpose Microsoft Access-VBA utility that converts 'column-major' tables to 'row-major' tables. The 'column-major' input tables can be in Microsoft Access or Excel formats and the 'row-major' output tables can be opened in Access, Excel, and/or ASCII formats. NPSCol2Row also includes a myriad of options allowing the user to customize this general 'column-major' to 'row-major' transformation, particularly insofar as creating an output file compatible with the Environmental Protection Agency's (EPA) STORET Import Module.

To install NPSCol2Row:
a) Open NPSCol2Row in WinZip
b) Click the Extract icon
c) 'Extract to:' C:\
d) Check 'Overwrite existing files'
e) Check 'User folder names'
f) Click the 'Extract' button

This will create (or overwrite) the following directory structure:

C:\NPSCol2Row
C:\NPSCol2Row\Exports
C:\NPSCol2Row\Imports
C:\NPSCol2Row\Microsoft Data Access Components

NPSCol2Row must be installed on the C:\ drive in a directory named C:\NPSCol2Row.

NPSCol2Row will now automatically update the Windows Registry to ensure the optimal settings for ImportMixedTypes (Text) and TypeGuessRows (0) are used for reading and converting Excel Spreadsheets. Alternatively, NPSCol2Row will do the column to row conversion from a backup version of the Excel Spreadsheet where all data have been converted to text format.

Refer to NPSCol2Row.DOC for step-by-step instructions.

NPSCol2Row.MDB contains all the input forms and code modules (the user interface). NPSCol2Row_BE.MDB contains all the tables that support the application, including four tables (prefaced with the name tblDef_) that are directly from STORET v.2.0. See the lower right corner of the opening NPSCol2Row ‘Splash’ Screen for the version date of these four STORET tables.

NPSCol2Row was developed using Microsoft Access 2002 and Visual Basic for Applications. The program requires references to the following (or more recent) versions of these libraries:

Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft Excel 10.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft DAO 3.6 Object Library

Note: NPSCol2Row only works in Acess 2002 and 2003.

Note: MDAC_TYP.EXE is included in NPSCol2Row.ZIP in case you need to install the latest references for Microsoft ADO. Alternatively, you can download the latest version of Microsoft Data Access Components (MDAC) from http://www.microsoft.com\downloads by searching for “MDAC”. Download and install the latest version of MDAC and any bug-fixes or security patches.

(4) "Part E Draft Guidance on Data Reporting and Archiving in STORET" part of the WRD's "Draft Guidance on Water Quality, Contaminants, and Aquatic Biology, Vital Signs Monitoring under the Natural Resource Challenge Long-Term Water Quality Monitoring Program"

(5) "Tutorial Retrieving National Park Service Water Quality Data from the STORET Data Warehouse"

The tutorial contains step-by-step instructions on how to retrieve Station Descriptions, Regular Results, and Metadata from the STORET Data Warehouse and import the data into Microsoft Excel.

Contacts:

For assistance, questions, comments, and/or suggestions, please contact:

Dean Tucker National Park Service Water Resources Division 1201 Oak Ridge Drive Suite 250 Fort Collins, CO 80525 Tel. (970) 225-3516 Fax. (970) 225-9965 E-M. Dean_Tucker@NPS.GOV

Mike Matz CSU/NPS Research Associate Water Resources Division 1201 Oak Ridge Drive Suite 250 Fort Collins, CO Tel. (970) 225-3514 Fax. (970) 225-9965 E-M. Mike_Matz@Partner.NPS.GOV