National Water Information System (NWIS) - Global Updates to Water-Quality File DRAFT In Reply Refer To: September 1, 1988 WGS-Mail Stop 412 OFFICE OF WATER QUALITY TECHNICAL MEMORANDUM 88.05 Subject: National Water Information System (NWIS) - Global Updates to Water-Quality File Introduction This memorandum provides background information and detailed specifications for several global updates to be made to the NWIS Water-Quality File. Updates are to be made for alkalinity, the carbonate species (bicarbonate, carbonate, hydroxide), kjeldahl nitrogen, dissolved lead, 1,2 dibromoethane (EDB), and several gage height, datum, and elevation related parameters codes. The bulk of the updates involve the movement of data from one parameter code to another, especially from 90000 level codes (not recognized by EPA STORET) to regular EPA STORET codes. Most of the discussion, however, deals with alkalinity and the carbonate species. The alkalinity problem has been evolving for several years and the correction process is very complex. In order to help the Division's field personnel understand how the update criteria were developed, the following discussion includes (1) a detailed chronology of the events that produced the problem; (2) a discussion of the basic concepts of the parameter code and emerging method code systems and how misunderstanding and misuse of these systems have contributed to the problem; (3) a discussion of alkalinity and its measurement; and (4) a detailed listing of the criteria to be used to effect the updates. Alkalinity Since 1980, there have been nine Office of Water Quality Technical Memorandums (four of which were NASQAN Implementation Memos) and one WRD Bulletin Series article (Oct - Dec, 1981) which have discussed the subject of alkalinity and the carbonate species. The purpose of those discussions was to try to develop a policy regarding the methodology of determination and the parameter codes to use to store the data in the Water-Quality Data Base. Unfortunately, the result of those efforts has been a very high level of confusion and misuse of the water-quality data base system. The purpose of this memorandum is to (1) put the past eight years into perspective, (2) describe proposed changes to the data base, and (3) establish new policy regarding which parameter codes and method codes to use for storing alkalinity and carbonate species data. The memorandums and article mentioned above are as follows: No. Date Subject 80.27 9/19/80 WATER QUALITY -- New Parameter Codes for pH, Alkalinity, Specific Conductance, and Carbonate/Bicarbonate 10/08/80 Correction to Tech Memo 80.27 81.04 11/12/80 WATER QUALITY -- Assignment of Parameter Codes for pH, Alkalinity, Specific Conductance and Hydroxide/ Carbonate/Bicarbonate Data 82.05 12/11/81 WATER QUALITY -- Provisional Method for Carbonate, Dissolved; Bicarbonate, Dissolved; and Carbonate Alkalinity, Dissolved; Electrometric Titration, Incremental, Field Oct-Dec 1981 WRD Bulletin -- Incremental Field Titration of Bicarbonate by Michael C. Yurewicz, pp 8-13. 82.06 01/22/82 PUBLICATIONS -- Policy on Publishing Constituents with Both Field and Laboratory Values 84.16 09/13/84 PROGRAMS AND PLANS -- National Water- Quality Networks: Fiscal Year 1985-- October 1, 1984, to September 30, 1985 85.19 09/18/85 PROGRAMS AND PLANS -- National Water- Quality Networks: Fiscal Year 1986-- October 1, 1985, to September 30, 1986 86.10 09/30/86 PROGRAMS tND PLANS -- National Water- Quality Networks: Fiscal Year 1987-- October 1, 1986, to September 30, 1987 87.01 11/14/86 WATER QUALITY -- Changes to Parameter Codes for Alkalinity and the Carbonate Species 88.01 10/09/87 PROGRAMS AND PLANS -- National Water- Quality Networks: Fiscal Year 1988-- October 1, 1987, to September 30, 1988 The WRD water-quality data base system has the capacity to store important ancillary information about measured constituents. Some of this information may be relevant to all constituents stored in the record. Thus, the structure provides selected bytes of information at the analysis level in the record, i.e., date, time, medium code, hydrologic condition code, etc. Other information is relevant to specific constituents and is therefore stored with the constituent at the parameter level, i.e., remark codes. method codes, precision codes, and quality assurance codes. The parameter code, which is used to identify each measured constituent in the record, defines a specific set of attributes about the constituent such as its unique chemical species, the sampling matrix (water, sediment, etc.), filter size, analysis recovery (total, recoverable), reporting form,(CaCO3, etc.), and reporting units (mg/L, mg/kg, etc.). The current problem with respect to alkalinity and the carbonate species data in the data base stems from the fact that not all of the above described data base functionalities existed when Tech Memos 80.27 and 81.04 were issued. Some of these features (method codes, precision codes, quality assurance codes, etc.) were added to the QW record structure in March 1983. The implementation of the method code system is just beginning. Parameter code subtleties and the method code system are the key players in the current data base problem. A brief discussion of each follows. Parameter Codes The USGS uses the 5-digit parameter code system developed by EPA in the l96O's for its STORET System. Many other governmental agencies also use this system. EPA has assigned approximately 10,000 codes of which the USGS uses about 4,000. We also use about 5,000 additional 5-digit codes with values greater than 90000. These 90000 level codes are only for internal use by the USGS and thus are neither recognized nor stored by the STORET system. The bulk of the data stored under 90000 level codes are biological data transferred from the Atlanta Laboratory Biological Data System. However, other legitimate data also have been stored under 90000 level codes in the absence of STORET codes. An intensive research effort into the EPA parameter code system by the Office of Water Quality at the request of the USGS Data Standards Committee has revealed serious flaws, inconsistencies, and shortcomings in this system. As a result of this effort, a new protocol for restating the parameter code description has been developed. The new protocol is being applied to all EPA/USGS parameter codes. Each of the parameter codes has also been indexed into a system of sampling medium categories (whole water, dry atmospheric deposition, bulk waste, street debris, etc) ; general physical/chemical categories (nutrients, minor elements, agricultural organics, etc.); and specific physical/chemical categories (flow, calcium, nitrate, halogenated hydrocarbons, etc.). A separate data base is being used for this purpose and the new descriptions are not being implemented in the NWIS Parameter Code Dictionary at this time, but it is anticipated that the redesigned NWIS-II data base will include the new descriptions in its parameter code dictionary. The new protocol does not change the originally intended definition of the parameter code, but in many cases there is a significant clarification of the intended meaning. The result of this clarification will be to give the user more explicit information about the constituent in question - its sampling medium, filter and particle size characteristics, the TOTAL or RECOVERABLE distinction, the reporting form. and the reporting units. When new parameter codes are needed, the requestor will be required to supply more definitive information about the constituent to be stored than has been required in the past. The redescribed parameter codes and the protocol will be submitted for consideration as a USGS data standard for parameter codes. The EPA parameter code system is not an official data standard; however, it has evolved to being a de facto standard. Of the parameter codes available at the time Tech Memos 80.27 and 81.04 were issued, many had descriptions that were to general to meet the unperceived subtleties implied in those memos. Many of the codes needed were not available and a preliminary decision was made to use 90000 level codes to store the data. This meant that data stored under the 90000 level codes would not be transferred to STORET until new codes were requested and received from EPA. Method Codes The method code functionality came along in March of 1983 when the latest version of the AMDAHL water-quality data system came on line. This functionality was brought about by the fact that many constituents could be determined by more than one analytical method. Even if only one method was available for a given constituent, there was an increasing interest in having information about the method stored in the data base. The method code consists of a single alphanumeric character stored with each appropriate parameter code. The Branch of Analytical Services has adopted the combination of the parameter code and the method code as its new laboratory code to eventually replace its older system of laboratory codes that few in the field used or understood. An important result of this is that when fully implemented there will be only one system for identifying a constituent and its method of determination for both the lab and the field. The BAS has been assigning method codes for a couple of years and has been entering them into the QW records sent to the Districts. However, access to and utilization of the method codes in the data base will not be available until a method code data base and changes to the data base software are implemented, possibly before NWIS-II. Preliminary design of the functional specifications for the Method Code System is underway in the Office of Water Quality. Although the method code has been around since 1983, appropriate codes for use with alkalinity and the carbonate species have only been defined in the last year. The method codes for use with alkalinity and carbonate species parameter codes are as follows: Code Definition A Fixed Endpoint Titration B Incremental Titration C Phenolphthalein Indicator (Colorless to Pink in pH range 8.2 - 10.0) D Bromo Thymol Blue Indicator (pH change at: (Red to Yellow in pH range 1.2 - 2.8) (Yellow to Blue in pH range 8.0 - 9.6) E Methyl Orange Indicator (Red to Yellow in pH range 3.2 - 4.4) F Low Level Grans Plot Z Indicates that the analysis was done by fixed endpoint titration, but it is unknown whether it was done in the field or laboratory. This applies to analyses done prior to October 1, 1980. THE PROBLEM The objective of Office of Water Quality Technical Memorandums 80.27 and 81.04 was to 1ntroduce the Division to the incremental titration method of determining alkalinity in both the field and the laboratory. The incremental titration method specifies that the titration is made to the stoichiometric (equilibrium) endpoint of the bicarbonate and/or other proton acceptor species in the solution rather than titration to a fixed endpoint, usually pH 4.5. The term alkalinity is historically defined as the titration to pH 4.5, however, the Office of Water Quality considers alkalinity to be a gross measurement of all acid neutralizing species in a solution by titration with a standard acid regardless of the targeted endpoint - stoichiometric, fixed endpoint, indicator. With the introduction of an additional method of determining alkalinity, it was deemed necessary to make a distinction in the data base between alkalinity values determined by the different methods. At that time the method code concept had not been developed and thus the proposed method of distinction was to make available separate parameter codes for each method. It was proposed that parameter codes to be used for incremental titration determinations of alkalinity use the term 'carbonate alkalinity' in the parameter code description. This is now considered a misuse of the term 'carbonate alkalinity' because the incremental titration method does not necessarily determine the sum of just the carbonate and bicarbonate species in the solution to the exclusion of all other acid neutralizing species, such as hydroxide, phosphates, borates, silicates, organic compounds, etc.that might be present. Several parameter codes, however, were requested from EPA with the term 'carbonate alkalinity' in the description. These codes are now considered to be improperly described and will be either redescribed in the Parameter Code Dictionary or the data moved to more appropriate alkalinity parameter codes. In some cases this redescription process has resulted in redundant codes, e.g. 00410 and 00431; 00417 and 00421: 00416 and 00418. The redundant codes will be removed from the parameter code dictionary and EPA will be notified of the redundancy as well as the need to redescribe several USGS requested codes. One existing EPA parameter code at that time was slated to be used as one of the 'carbonate alkalinity' codes because it appeared to suit the need at that time. However, this code, 00430 (ALKALINITY. CARBONATE, MG/L AS CACO3), was actually defined for a different constituent: carbonate reported as calcium carbonate. Recent research has shown that code 00430 was created by EPA at the same time as codes 00425 (ALKALINITY, BICARBONATE, MG/L AS CACO3) and 00420 (ALKALINITY, HYDROXIDE, MG/L AS CACO3). None of these codes are for alkalinity but rather for the individual ions indicated. Values are reported as CaCO3 rather than as the ion concentrations. Therefore, the use of code 00430 to store the results of an alkalinity titration, which WRD has done for the past several years, is not correct Not all of the codes required to implement Memo 80.27 were requested from EPA at the same time. In fact, several codes have only recently been received. As an interim measure, 90000 level codes were defined internally by WRD to fill the gap. Parameter codes with values greater than 90000 are not recognized by STORET and can be defined and used in any way deemed necessary by individual agencies. Thus, data that normally should have been transmitted to STORET have remained in WATSTORE. During this period, the field offices were also given instructions to perform alkalinity measurements on filtered samples, mainly for the NASQAN and Benchmark programs. Appropriate codes for dissolved alkalinity and the carbonate species were not available and the field offices were instructed to store the data under supposedly unused whole water codes until such time as the proper codes were received. Headquarters would correct the data base when the proper codes were in place. The Fix Although we are able to store the data properly from this point ajustments to parameter codes of previously stored data are needed for consistency. The list of parameter codes in Table 1 is the result of a research effort to identify the appropriate parameter codes and method codes to store alkalinity and carbonate species data. This list of codes differs somewhat from the codes that were suggested in Tech memo 87.01, which was issued not as official policy but only as an indicator of Office of Water Quality plans for the action to be taken subsequent to this memorandum. The Office of Water Quality and the NWIS Program Office have spent the past year developing the criteria and software to make appropriate corrections to the data base. The details of the changes to be made are given in tables 2 through 6 in the attachment to this memo. The proposed list of update criteria is based on responses to Office of Water Quality Technical Memorandums 87.01 and 88.09 and other communications with Division personnel. The criteria are primarily designed to (1) move data stored under 90000 level codes to appropriate EPA codes; (2) move dissolved data stored under whole water codes (00410, 00419, 99430, 00450, 00447, and 71832) to appropriate dissolved codes (39086B, 39086B, 39086B, 00452B, 00453B and 71834B, respectively); and (3) consolidate data under the redundant codes discussed above. The actions proposed under item 2 above are primarily for NASQAN and Benchmark data for fiscal years 1986 and 1987, but it is acknowledged that selected Districts may have stored alkalinity data from whole water as well as dissolved samples at other stations under these codes. Although we have given a great deal of thought and time to this effort, including discussions with the Regional QW Specialists; the Branch of Analytical Services, the National Research Program and District personnel there will invariably be exceptions in individual District data bases that will defeat the purpose of our criteria. It is possible that the criteria, when applied, will produce complications to these data bases. Realistically, there is not much that can be done about this, except for the Districts to make selected updates to their data bases, either before or after the execution of the update program, to conform to the policy being developed for alkalinity and carbonate species. In developing the criteria for making changes to the data base, several assumptions had to be made. Some of these assumptions could have a negative impact on some District data bases because not all Districts followed the instructions in the previously listed memorandums. These assumptions are discussed subsequently. Most of the changes are relatively straight forward and involve moving data stored under 90000 level codes to actual EPA codes and assigning appropriate method codes. Alkalinity Assumptions and Notes o All alkalinity data stored under parameter code 00410 with collection dates prior to October 1, 1980, will be assigned a method code of "Z". Prior to October 1, 1980, parameter code 00410 was used to store alkalinity data by fixed endpoint titration to pH 4.5. The"Z" method code is used for distinguishing the fact that although most of the analyses were done in the laboratory, an indeterminate number were done in the field. Tech Memo 81.04 designated parameter code 00410 for the field determination of alkalinity using the fixed endpoint titration method. The laboratory catalog for FY 1981 officially announced the change to parameter code 90410 for the laboratory determination of alkalinity by fixed endpoint titration. Code 90410 will be replaced by EPA code 00417A. o All alkalinity data stored under parameter code 00410 with collection dates after September 30, 1980, will be assigned a method code of "A", signifying that the data were determined in the field using the fixed endpoint titration method. There should be little data in the data base under this code for this period because Tech Memos 80.27 and 81.04 initiated the policy of doing field alkalinity by incremental titration under parameter code 99430 as of October 1, 1980. Parameter code 99430 will be replaced with STORET parameter code 00419B (water, whole, incremental titration, field, mg/L). o Data stored under code 00430 will be moved to code OO4lOA as will data stored under code 00431 if code OO4lOA is not already occupied (unlikely). Data under redundant code 00421 will be moved to code 00417A, and data under code 00418 will be moved to code 00416B. Codes 00431, 00421 and 00418 will be removed from the Parameter Code Dictionary. As stated above, the proper use of code 00430 is for carbonate data reported as CaCO3 in mg/L. o NASQAN Implementation Memo 85.19 (FY 1986) instructed field offices to filter samples before analyzing for alkalinity using the incremental titration technique (parameter code 99430) and by fixed endpoint titration (parameter code 00410). NASQAN Implementation Memo 86.10 (FY 1987) instructed the field to use code 00419 instead of 99430. Code 00419, however, is for whole water samples and was to be used only temporarily until code 39086 (water, dissolved, incremental titration, field, mg/L) could be assigned by EPA. To our knowledge no unfiltered data should be stored under 00419. Therefore, all alkalinity data stored under parameter code 00419 will be moved to parameter code 39086B, and all data stored under parameter code 99430 with collection dates after September 30, 1985, will be moved to 39086B, if 00419 is not already occupied. For data with collection dates prior to October 1, 1985, data stored under code 99430 will be be moved to 00419B. Parameter code 99430 will be removed from the Parameter Code Dictionary. Likewise, for data with collection dates after September 30, 1985, data stored under code 00410 will be moved to code 39036A (water, dissolved, fixed endpoint, field, mg/L). Bicarbonate Notes o NASQAN Implementation Memos 85.19 and 86.10 provided for using code 99440 in 1986 and code 00450 in 1987 for storing bicarbonate data determined by incremental titration on filtered samples, even though these codes are for whole water samples. Data collected in 1986 and 1987 and stored under these codes will be transferred to STORET code 00453B (bicarbonate, water, dissolved, incremental titration, field, mg/L). Carbonate Notes o Similarly, carbonate data collected and stored under parameter codes 99445 and 00447 during the 1986 and 1987 water years, respectively, will be moved to STORET code 00452B (carbonate, water, dissolved, incremental titration, field, mg/L). Hydroxide Notes o Similarly, hydroxide data collected and stored under parameter codes 99830 and 71832 during the 1986 and 1987 water years, respectively, will be moved to STORET code 71834B (hydroxide, water, dissolved, incremental titration, field, mg/L). Parameter Code List for Alkalinity and the Carbonate Species Table 1 gives the list of parameter codes recommended for use by WRD to store alkalinity and the carbonate species for combinations of LAB, FIELD, FIXED ENDPOINT TITRATION, INCREMENTAL TITRATION, GRANS PLOT, WHOLE WATER SAMPLE, DISSOLVED SAMPLE. A second attachment (Tables 11-14) has been added for references purposes listing all EPA parameter codes for alkalinity, carbonate, bicarbonate, and hydroxide. Codes in the USGS Parameter Code Dictionary are designated by a "2" in the AGENCY column. Codes that are slated to be removed from the PCD are indicated by an 'N' in the 'Recommended for WRD Use' column. The original EPA parameter code descriptions have been restated using the new parameter code description protocol. Notice that many of these code descriptions contain a reference to the method used. This appears to be in conflict with the purpose of the method code and one could make a reasonable argument for excluding the method reference in the description. But the Office of Water Quality, the Branch of Analytical Services, the Branch of Quality Assurance, and the Regional QW Specialists have agreed that the inclusion of the method indicator in the parameter code description for alkalinity and the carbonate species parameter codes is appropriate for the time being because the data base has many examples of multiple alkalinity measurements - fixed endpoint and incremental titration - and the present data base can only acconmodate a single occurrence of a given parameter code in an analysis. Full implementation of the method code system and the ability to store multiple occurrences of a parameter code in a record in NWIS-II will eliminate the need to maintain so many parameter codes. At that time, the number of parameter codes for alkalinity and carbonate species can be significantly reduced. Additional Data Base Changes To Be Performed In addition to the changes to the data base described previously, the following changes are to be performed. As a result of the above changes to the alkalinity and carbonate species parameter codes, changes will also be made to the algorithms used to compute noncarbonate hardness. Tables 7 through 10 list the noncarbonate algorithms for whole water, field: whole water, lab; water, dissolved, field; and water, dissolved, lab, respectively. The algorithms are listed in reverse polish notation, which is used internally by the software, but is presented here to show the priority order of the species that are to be used in the calculation. For xample, in table 7, which describes the calculation of parameter code 00902 (noncarbonate hardness, water, whole, as CACO3, mg/l), CALCIUM, TOTAL (00916) is used in preference to CALCIUM, RECOVERABLE (00918). Calcium and magnesium are required constituents, while strontium and barium used only if available. Also note that either of the bicarbonate and carbonate species combinations are used in preference to one of the three possible alkalinity parameter codes. As described in Office of Water Quality Technical Memorandum 86.06, June 16, 1986, data collected for parameter codes 00623, 00636, and 82451 (nitrogen, ammonia plus organic, dissolved) will be adjusted to the minimum reporting level, 0.2 mg/L. As described in Memorandum 86.07, June 16, 1986, data collected for parameter codes 01049 and 01051 (lead, dissolved and recoverable) will be adjusted to the minimum reporting level, 5 ug/L. EPA has provided parameter codes for the metric form of the constituents described in the following list: Old New Parameter Parameter Code Code Description 99065 30207 Gage Height, Distance above Datum, Meters 99060 30208 Discharge, Cubic Meters Per Second 99061 30209 Discharge, Instantaneous, Cubic Meters Per Second 99019 30210 Water Level, Depth Below Land Surface Datum (LSD), Meters 99020 30211 Elevation Above National Geodetic Vertical Datum (NGVD), Meters Data for the old parameter codes will be moved to the new parameter codes. Data under the old codes will be deleted from the data base. The old codes will be removed from the Parameter Code Dictionary. The Office of Water Quality and the Branch of Analytical Services have verified that EDB (1,2 dibromoethane and ethylene dibromide) data have been stored under the wrong code. All EDB data from the Central Lab has been stored under parameter code 39082. This code is actually for 1,2 dibromoethylene (1,2 dibromoethene and acetylene dibromide are synonyms), which is a different compound. The CAS number for EDB is 106934 and the CAS number for 1,2 dibromoethylene is 590125. All data stored under parameter code 39082 will be moved to parameter code 77651 (1,2 DIBROMO ETHANE, WATER, WHOLE, TOTAL, UG/L). The Central Lab will begin using code 77651 at the beginning of the new fiscal year. Although code 77651 is for a "TOTAL" analysis, in actuality the analysis is a "RECOVERABLE" analysis. The reconciliation of the TOTAL vs RECOVERABLE problem in the parameter code system will be addressed in a subsequent data base update. Data Base Update Schedule After the update software has been modified to incorporate as many District exceptions as possible it will be run against test data bases for final refinement. The software will then be sent to each District for execution against the District QW data base. Once the Districts have updated their data bases and made a transfer to the AMDAHL WATSTORE data base, similar update software will be run against the AMDAHL data base to pick up missed historical data that the Districts may not have resident on their local data bases. All contacts regarding this project should be made to Jim Schornick of the Office of Water Quality staff (EDOC JCSCHORNICK or FTS 959-6867) ; Dave Maddy of the NWIS Program staff.(EDOC DVMADDY or FTS 959-5689) ; or John Briggs of the NWIS Program staff (EDOC JBRIGGS or FTS 959-5624). It is recognized that data base actions of this type may cause some hardship on the District's data base management program, but the integrity of the data base is part of WRD's primary mission. As new problems in the data base are recognized and researched, update criteria and software will be developed by the Office of Water Qua1ity and the NWIS Program Office to effect appropriate changes. A considerable amount of effort is being put into the design of NWIS-II, the next generation of the WRD data base system, to provide the data base users with sufficient functionality to insure that our hydrologic data are stored under the correct identifiers with the least amount of ambiguity. Your cooperation and contributions in these efforts are welcomed and encouraged (unsigned) David A. Rickert Attachments WRD Distribution: A, B, S, FO, PO Key Words: Water Quality, National Water Information System (NWIS), data base, updates, alkalinity. carbonate, bicarbonate, hydroxide This memorandum supplements Office of Water Quality Technical Memorandum 87.01.