National Water Quality System (NWIS) Water-Quality Software?Errors in Small Magnitude, Negative Values

In Reply Refer To:  
Mail Stop 412
                                             November 26, 2001



Office of Water Quality Technical Memorandum 2002.04

Subject:
   National Water Quality System (NWIS) Water-Quality Software?Errors in
   Small Magnitude, Negative Values

The purpose of this memorandum is to describe the scope and ramifications
of a problem with the storage of small magnitude, negative values in the
NWIS Water-Quality System and the procedure being developed to correct it.
A change was made in the NWIS water-quality software, version 3_1, released
in September 1998 that introduced a data-storage error.  The software
change in version 3_1 caused small magnitude, negative values to be stored
in the database differently than identical values had been stored
previously.  The problem was discovered after the release of the 4_1
software.  Correcting the problem will require some effort on the part of
District personnel.


Scope:  Values retrieved from the NWIS water-quality system may (or may
not) be incorrect by two orders of magnitude, depending on when the result
was stored in the system.  The problem affects only values in the range of
0 > X > -1.  Few results are open to question due to this problem since
only negative values are affected, and NWIS only allows storage of negative
values for 503 of the 10,621 NWIS parameter codes (NWIS User's Manual,
Appendix H).  An exploratory investigation of several NWIS systems (table
1) confirms that the number of affected results is small: 0.0006 % of the
stored results.

|--------------+---------+--------+---------+--------------|
| System       |  No. of | Samples|  Results| Negative-valu|
|              | Database| stored |  stored | e            |
|              |    s    |        |         | problem      |
|              |         |        |         | results      |
|--------------+---------+--------+---------+--------------|
| Oklahoma     |     2   |  166,97| 2,306,00|       0      |
|              |         |       9|        4|              |
|--------------+---------+--------+---------+--------------|
|  Arizona     |    9    | 118,484| 2,296,07|      2       |
|              |         |        |        5|              |
|--------------+---------+--------+---------+--------------|
|  Texas       |    3    | 228,021| 4,482,28|      11      |
|              |         |        |        6|              |
|--------------+---------+--------+---------+--------------|
|  Alaska      |    2    |  43,560|  729,021|      16      |
|--------------+---------+--------+---------+--------------|
| NAWQA        |     2   |  309,87| 9,608,26|       88     |
| aggregate    |         |       2|        5|              |
|--------------+---------+--------+---------+--------------|




Ramifications:  The next aggregation of data for release on NWISWeb,
scheduled for late 2001, will exclude data in the affected range so the
aggregation will not be delayed.  A procedure to identify and correct the
problematic results is being developed and tested.  This procedure will use
data files that were created during the NWIS 4_1 transfer.  These files are
stored in the directory /usr/opt/nwis/data/ascii and are named
"qw_tran4.qwfile_DB.gz", where DB is an NWIS database number.  Districts
should immediately ensure that needed data files are not deleted, and will
be available for use with the correction procedure.  If the files have been
deleted, they will need to be retrieved from backups.  The correction
procedure produces a table of results that will need verification and it
prepares prototype batch-input files for use in correcting the results.
Districts will need to make staff time available in December to run the
procedure, verify the results against original records, and make necessary
corrections in the batch file.  This process should take from less than 1
to 2 days, depending on the size of the database and the number of results
that need to be checked.  Instructions for making the data corrections will
be distributed in early December 2001.

Questions can be emailed to phoenix@usgs.gov.


                                        Stephen K. Sorenson /s/
                                        Acting Chief, Office of Water Quality

This memorandum does not supersede any other Office of Water Quality Technical Memorandum.

Distribution:  All WRD Employees


(See attached file: tech02.04.1fn.fix.htm)