For the more information about water resources in the National Park Service, please visit http://www.nature.nps.gov/water/.


Vital Signs Water Quality Data Management and Archiving

Introduction | What is STORET? | Why STORET? | Details | Getting Started | Downloads | Contact

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 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.

↑ TOP OF PAGE

What is STORET?

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, 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.

↑ TOP OF PAGE

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.

↑ TOP OF PAGE

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.

Data Flow Diagram

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 where the data will be accessible to the states, other entities, and the public on the Internet.

↑ TOP OF PAGE

Getting Started

If you are writing a Network's water quality monitoring protocol and/or SOP you should consult the vital signs guidance. 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 over 389,000 characteristics (also found in tblDef_TSRCHAR in NPSTORET as of 10/19/2008). 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) 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)

    Choices Description
    DRY No visible water in stream (typical of dry period for an ephemeral/intermittent stream).
    NO FLOW Discrete pools of water with no apparent connecting flow (at surface).
    LOW Base flow for a stream or flow within roughly 10% to 20% of base flow condition.
    NORMAL When stream flow is considered normal (greatest time that stream is characterized by this in terms of flow quantity, level, or general range of flow during a falling or rising hydroperiod, but above base flow).
    ABOVE NORMAL Bank full flow or approaching bank full (generally within upper 20% of bank full flow condition).
    FLOOD Flow extends outside normal bank full condition or spreads across floodplain.
  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 in or absorbed to material that passes through the filter during the process of sample filtration.
    Non-Filterable The result-producing portion of the analyte is extracted from the liquid medium by 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.

↑ TOP OF PAGE

Downloads

NPSTORET v.1.86 (July 2, 2014)

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—or export to the Water Quality Exchange (WQX) format. User's can enter Projects, Stations, Metadata, and Results on their respective templates. A Reports & Statistics template allows users to generate reports, statistics, graphics, exports, and Google Earth display 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 and characteristic 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 is intended for use in Microsoft Access 2007, 2010, or 2013. The program requires references to the following (or more recent) versions of these libraries:

  • Visual Basic for Applications
  • Microsoft Access 12.0 Object Library
  • Microsoft Excel 12.0 Object Library
  • Microsoft Office 12.0 Object Library
  • Microsoft Word 12.0 Object Library
  • OLE Automation
  • Microsoft ActiveX Data Objects 2.8 Library
  • Microsoft DAO 3.6 Object Library
  • Microsoft ADO Ext. 2.8 for DDL and Security
  • Microsoft XML, v6.0
  • GdPicture Pro 5 - Imaging Toolkit
  • ExTree 1.0 Control Library

NPSTORET Notes

  • NPSTORET is supported in Access 2007, 2010, and 2013. Be sure to review the caveats concerning security, cloud storage, reports, and pictures in these versions of Access. NPSTORET can still be run in Access 2003 by manually changing the Office, Access, Excel, and Word Object Library References to 11.0 post-installation.
  • 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 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.
  • For video demonstrations of NPSTORET, visit NPSTORETVideos.htm.

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.

  • Open NPSEDD1.1.ZIP in WinZip
  • Click the Extract icon
  • 'Extract to:' C:\
  • Check 'User folder names'
  • Click the 'Extract' button
Winzip

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.

NPSCol2Row v.2.21

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:

  1. Open NPSCol2Row in WinZip
  2. Click the Extract icon
  3. 'Extract to:' C:\
  4. Check 'Overwrite existing files'
  5. Check 'User folder names'
  6. 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 caveats, step-by-step instructions, and information on using NPSCol2Row in Access 2007.

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/2003 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 has only been tested in Access 2002/2003 and 2007.

Part E Draft Guidance on Data Reporting and Archiving in STORET

This guidance is part of the Draft Guidance on Water Quality, Contaminants, and Aquatic Biology, Vital Signs Monitoring under the Natural Resource Challenge Long-Term Water Quality Monitoring Program.

Tutorial on 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.

↑ TOP OF PAGE

Contact

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

↑ TOP OF PAGE

Last Updated: September 26, 2014