Water resources are one of the vital signs monitored in parks. Measurements like the temperature of the water, toxins, and aquatic species can all help determine the condition of a park's resource. As with any scientific research and data collection, this information provides resource managers the tools necessary to plan and take action to fulfill the mandates of the NPS mission.
The following applications can be used by data managers to report the state of their water resource:
Vital Signs Networks are encouraged to also archive any other aquatic-related data collected with base Vital Signs funding in EQuIS so those data can also be shared with the public in the STORET National Data Warehouse and the Water Quality Portal.
To facilitate archiving NPS data in STORET via EQuIS and the Water Quality Exchange (WQX), the WRD has developed a series of Microsoft Access-based templates (called NPSTORET), for Networks to use to enter their water quality data in an EQuIS/STORET/WQX-compatible format.
An NPS EQuIS WQX Electronic Data Deliverable (EDD) 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 mapped to the NPS EQuIS WQX EDD specification to the WRD on an annual basis for quality assurance and upload into the service-wide EQuIS database and automatic transmission to EPA's STORET National Data Warehouse and the NWQMC's Water Quality Portal.
STORET originated in the Department of Interior in 1964 and was moved to the EPA after its formation in 1970. "Old" STORET, which still 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 the new modernized STORET to take advantage of the improved architecture, metadata, better Internet accessibility, and other reasons. Modernized STORET users forwarded their Oracle back-ends to EPA periodically for inclusion in the STORET National Data Warehouse. In late 2009, the EPA decided to no longer support the distributed Oracle database version of STORET and developed the Water Quality Exchange (WQX) - an XML-based schema for transmitting water quality data between disparate systems. All subsequent data submissions to the STORET National Data Warehouse had to be done via WQX rather than Oracle .dmp files. This made the STORET National Data Warehouse accessible to data providers who operated different data systems.
The move to STORET/WQX obviated WRD's Oracle-based modernized STORET database. A replacement system that could flow data to the EPA's STORET National Data Warehouse via WQX was sought. WRD ultimately acquired EarthSoft's Environmental Quality Information System (EQuIS), a widely used data management system that can house a diverse array of environmental data and create WQX exports for automatic ingestion by the STORET National Data Warehouse. All NPS data in WRD's Oracle-based modernized STORET database were migrated into EQuIS.
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 service-wide EQuIS database hosted by WRD so they can be automatically transmitted to the EPA STORET National Data Warehouse via WQX. 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 water quality database (e.g. the U.S. Geological Survey's National Water Information System) must also be entered in the service-wide EQuIS database 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 or the Water Quality Portal, WRD will only transmit to the EPA STORET National Data Warehouse 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 an EQuIS/WQX/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 can 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 EQuIS and STORET/WQX-compatible EDD files.
For data entry, NPSTORET includes a module for importing stations and results from other user databases, labs, and other sources. EPA Legacy STORET, STORET National Data Warehouse, U.S. Geological Survey National Water Information System, Water Quality Portal, and Ambient Water Quality Monitoring System 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 NPS EQuIS WQX Electronic Data Deliverable (EDD), a series of Microsoft Excel worksheets, for documenting salient attributes of the projects, locations, activities, results, and other aspects of water quality monitoring, must be produced to upload the data to EQuIS and the STORET National Data Warehouse. Networks will need to translate their data to match the NPS EQuIS WQX EDD schema and business rules.
Although it is the responsibility of Vital Signs Monitoring Networks to ensure that all their NRC funded water quality data are entered in EQuIS, WRD staff can assist in this task. Before the initiation of monitoring and/or data entry, WRD staff can advise on how to configure NPSTORET to reflect accurately their water quality monitoring activities or how to populate the NPS EQuIS WQX EDD to make data submission easier in subsequent years. Vital Signs Networks will perform their own data entry. Annually, Vital Signs Networks will send their NPSTORET database or their populated NPS EQuIS WQX EDD to their WRD contact who will quality assure the data and, in consultation with the Network, make any edits/modifications necessary to upload the data into EQuIS using the Electronic Data Processor included in EQuIS Professional and EQuIS Enterprise. If necessary, a revised version of the Network's NPSTORET database or EDD will be returned to the Network. Once Network staff are comfortable with the dataflow they will have the opportunity to import the data into EQuIS on their own. On a weekly basis, any updates to the NPS' service-wide EQuIS database will be automatically 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. The data will also automatically transfer from the STORET National Data Warehouse to the Water Quality Portal for one-stop querying of water quality data.
For Data Managers, the best place to begin would be to download and evaluate NPSTORET and the NPS EQuIS WQX EDD and determine which process best fits the Network's data management strategy. Regardless of whether you choose to use NPSTORET or generate NPS EQuIS WQX EDDs, there are certain common data elements that Networks can begin documenting immediately in their protocols/SOPs. These include:Station Location Data
STORET/EQuIS/WQX requires that every water quality monitoring station must have assigned latitude and longitude coordinates. 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. Every station must have a station ID. Station IDs should begin with the four digit park code followed by an underscore (e.g. ROMO_VM_GC1 could be the station ID for volunteer monitoring location #1 on Glacier Creek in Rocky Mountain National Park). NPSTORET and the NPS EQuIS WQX EDD will indicate which fields are required.Characteristic/Parameter Definitions
STORET/WQX and EQuIS enforce standardized nomenclature for what is sampled, measured, and observed. The NPS EQuIS database generally follows the names used by EPA for STORET/WQX. Networks must match their characteristics/parameters with the official standardized EPA characteristic domain list. 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 acharacteristic/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:
- Dissolved oxygen (DO)
- Specific conductance
- Temperature, water
- 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.
- Fecal Coliform
- All ITIS taxa
*For characteristics that have choice or pick-lists (like "Flow, severity (choice list)" above), consult this EPA domain reference table to see the valid choices.
In addition to itemizing what standard STORET characteristics are being monitored, Networks must explicitly define their characteristics (as appropriate) in terms of:
- Medium (water, air, biological, sediment, soil, etc.)
- Sample Fraction
**Note: The most common fractions are: (1) 'Unfiltered' or 'Total' to indicate samples that weren't filtered and (2) 'Dissolved', 'Filtered, field', or 'Filtered, lab' to indicate samples that were filtered prior to analysis.
STORET Sample Fraction STORET Description Acid Soluble That portion of the analyte which becomes dissolved within the sample following treatment with an appropriate acid. Bed Sediment Bedload Bioavailable The extent to which a substance is / becomes available or absorbed by a specific tissue or organ. Comb Available The Concentration of a analyte or other analyte derivatives in a water which is combined with other derivatives yet is still available to oxidize organic matter. Dissolved That portion of the analyte found in the liquid medium. Cannot be removed by filtration. Extractable An analyte that can be removed from a mixture using a specific method or technique Filterable That portion of the analyte which is in or absorbed to material which passes through the filter during sample filtration. Filtered, field Performed in field. +/ Pertains to constituents in a water sample passed through a filter membrane of specified pore diameter, 0.45 µm or less for inorganic analytes and 0.7 µm for organic analytes. Filtered, lab Performed in lab. +/ Pertains to constituents in a water sample passed through a filter membrane of specified pore diameter, 0.45 µm or less for inorganic analytes and 0.7 µm for organic analytes. Fixed That portion of the analyte found in the liquid layer above a precipitate produced from the sample. Free Available The concentration of residual substance present as dissolved gas, and not combined with any substance or in other less readily available form. Inorganic Inorganic content of sample Non-Filterable (Particle) That portion of the analyte which is extracted from the liquid medium by filtration. Non-settleable That portion of the analyte which is in or absorbed to particles remaining in suspension in the sample container after a settling process. Non-volatile That portion of the analyte which is in a liquid or solid state under normal temperature and pressure. None None or Not Applicable Organic Organic content of a sample Pot. Dissolved Potentially Dissolved is the portion of a constituent measured from the filtrate of a water and suspended sediment sample that was treated with nitric acid prior to sample filtration. Semivolatile The portion of the analyte which semi-easily enter the air as gases from some solids or liquids. Settleable That portion of the analyte which is found in or absorbed to that part of the sample which has settled (fallen out of suspension) to the bottom of the sample container. Strong Acid Diss Strong Acid Dissociable, analyte that is released from analyte containing compounds under highly acidic conditions. Supernate That portion of the analyte found in the liquid layer above a precipitate produced from the sample. Suspended That portion of the analyte which is suspended in the sampled medium, either as, or adsorbed to, particles which are more or less uniformly dispersed within the medium. Total The total of all fractions of the analyte. Total Recoverable The analyte concentration available for analysis in an unfiltered sample following solubilization. Total Residual The amount of available analyte remaining after a given contact time. The sum of the combined available residual analyte and the free available residual analyte. Total Soluble The total of all fractions of the analyte / The sum of all of the different forms of the analyte present which becomes dissolved within the sample. Unfiltered Pertains to the constituents in a representative water-suspended-solids sample. (whole) Vapor That portion of the analyte which exists in a gaseous state and that under ordinary conditions is liquid or solid. Volatile That portion of the analyte which evaporates readily at normal temperature and pressure. Weak Acid Diss Weak Acid Dissociable, analyte that is readily released from analyte containing compounds when the pH is lowered.
- Unit of Measure
- Value Type (actual, calculated, estimated)
- Field or Lab Measured
- Statistic Type (mean, max, min, mode, MPN, etc.)
- Duration (# of hours or days)
- Weight Basis (wet, dry, ash-free dry)
- Temperature Basis (5 to 95 degrees C in 5 degree increments)
- Particle Size Basis
- Detection Limit (MDL or similar)
- Lower Quantification Limit (PQL or similar)
- Upper Quantification Limit
- Analytical Procedure and/or Equipment
- Sample Collection Procedure
- Sample Handling Procedure
- Lab Sample Preparation Procedure
- Name/Contact Information for the Lab
- 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. The National Environmental Methods Index may be helpful for identifying common analytical methods. For "non-standard" analytical procedures/methods, a thorough description is required. For "non-standard" analytical procedures/methods, start the procedure ID with the four character park or Network code. For characteristics that required the extraction of a sample from the environment, a sample collection procedure must be specified.
All this metadata should be captured in the Network's protocols/SOPs. Be sure to review the 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 (1 through 19 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.
NPSTORET is a complete water quality database management system that allows users to enter information about their water quality monitoring Projects, Stations, Metadata, and Results in a Microsoft Access database in a manner compatible with the National Water Quality Monitoring Council's (NWQMC) core water quality data elements. Users 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 displays of entered data. Data import routines can be used to import users' own stations and results, as well as stations and results from the EPA's STORET National Data Warehouse, Legacy STORET, the U.S. Geological Survey's National Water Information System, the NWQMC's Water Quality Portal, and the Ambient Water Quality Monitoring System. Taxa and characteristic lists can also be imported for automatic characteristic definition. Export files can also be created for import into the NWQMC's Water Quality Portal using the Water Quality Exchange (WQX) format or EarthSoft's EQuIS database using the NPS' EQuIS WQX EDD.
Use the NPSTORET setup program (NPSTORET Setup.exe) to install NPSTORET on your computer. Be sure to download and follow the installation details.
NPSTORET requires references to the following (or more recent) versions of these libraries:
- Visual Basic for Applications
- Microsoft Access 15.0 Object Library
- Microsoft Excel 15.0 Object Library
- Microsoft Office 15.0 Object Library
- Microsoft Word 15.0 Object Library
- OLE Automation
- Microsoft ActiveX Data Objects 2.8 Library
- Microsoft Office 15.0 Access database engine Object Library
- Microsoft ADO Ext. 2.8 for DDL and Security
- Microsoft Windows Common Controls 6.0 (SP6)
- Microsoft Internet Controls
- Microsoft Scripting Runtime
- TeeChart Pro ActiveX Control v2019
- NPSTORET is supported in Access 2013 and 2016. 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 2010 by manually changing the Office, Access, Excel, and Word Object Library References to 14.0 post-installation.
- Be sure that your desktop screen resolution is set to 1024x768 or higher.
- You must be an "Administrator" on your computer to install NPSTORET and have write permission to the installation directory during use.
- To start the program, double click on NPSTORET.accdb or start Access and open NPSTORET.accdb.
- Be sure to read the file FirstTimeUser.docx for a quick introduction to how NPSTORET works or Workshop.docx for a more elaborate overview.
- Training overview videos covering the various templates and other functions of NPSTORET can be accessed as MP4 or WMV files from here.
- Consult the file: DataImportInstructions.docx for detailed information on how to import existing data into NPSTORET.
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 the general 'column-major' to 'row-major' transformation.
To install NPSCol2Row:
- Open NPSCol2Row in WinZip
- Click the Extract icon
- 'Extract to:' C:\
- Check 'Overwrite existing files'
- Check 'User folder names'
- Click the 'Extract' button
This will create (or overwrite) the following directory structure:
- C:\NPSCol2Row\Microsoft Data Access Components
NPSCol2Row must be installed on the C:\ drive in a directory named C:\NPSCol2Row.
NPSCol2Row will 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 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 2003, 2007, 2010, and 2013, but the code was tweaked in July 2016 to enable it to operate in both 32-bit and 64-bit Office environments.Tutorial on Retrieving National Park Service Water Quality Data from the STORET National Data Warehouse
The tutorial contains step-by-step instructions on how to retrieve Station Descriptions, Regular Results, and Metadata from the STORET National Data Warehouse and import the data into Microsoft Excel.
Last updated: April 14, 2021