Focus On Water Division
Nebraska Department of Environment and Energy
Standard Operating Procedure

Water Quality Division
Water Quality Assessment Section - GW

Standard Operating Procedure Number: GW-150
Title: Data Management
Written Date: October 1, 2000
Approved Date:


Purpose: To document and describe the procedures in handling and storing the data gathered and how to assure the data are quality. This document also gives guidelines for evaluating data.

Equipment/Materials Needed:
  1. Laboratory analysis reports
  2. Field data sheets completed as per SOP # GW-110
  3. Well construction, registration, and geologic logs
  4. Historical and background ground water quality data
  5. Computer
  6. Calculator
Procedures:

1. Data Compilation
1.1. As soon as data is returned to the project manager (Program Specialist) from the laboratory, perform a cursory review for obvious errors by scanning the results and comparing them to the field measurements recorded on the field data sheet.
1.1.1. Flag any errors that may possibly be from the laboratory and discuss with lab personnel at the earliest opportunity.
1.2. Enter lab results (from both wells sampled and the QA/QC samples) into a computer spreadsheet or data base. QA/QC results may be entered on the same spreadsheet or a different one. Other data may be entered at this time or may optionally have been previously entered. This “other” type of data includes (but is not limited to):
  • date well completed
  • type of well (irrigation, domestic, municipal, etc.)
  • location of well (latitude and longitude and legal location)
  • geologic information available
  • well owner and address
  • registration number
  • past water quality data
  • other important items from the field data sheet
1.2.1. Have a second NDEQ staff person (usually another Program Specialist, but an intern or clerical staff could perform this task as well) check the spreadsheet entries against the lab sheets to ensure accurate data entry.
1.3. Store spreadsheet file on the hard disk of an appropriate computer, usually the project manager’s. The project manager should also retain a back up copy on diskette or other medium; this backup should be updated periodically.
1.4. After data entry is confirmed and obvious errors are eliminated, share data with the NRD(s) cooperating in the study. This is prior to final report issuance and is for the NRD’s information, so they can share the test results with the farm operator or well owner.
1.5. Present data in the final report in tabular form, usually in the Appendices. Legal locations to quarter quarter section (or better) should be used for location. Owner’s name and address are not included in the final report for reasons of anonymity.
2. Data Analysis
2.1. Perform the following calculations/test/analyses on all well sampling results data:
2.1.1. Holding Times
Compare holding times attained on project samples to the appropriate values in the tables in Appendix A. These tables list maximum holding times for particular preservations, methods, and analytes. If holding times have not been met, notify the lab, flag the lab result, and decide whether the data can be used or not.
2.1.2. Piper Diagrams
Piper diagrams, otherwise known as trilinear diagrams, will help to show distinct water quality populations. If appropriate, prepare trilinear diagrams for the project dataset using the data file previously prepared (section 1.0) and appropriate computer software program (e.g. Grapher, HydroChem, etc.).
2.1.3. Ionic Balances
Calculate ionic balances for the project dataset using the computer file of lab results (section 1.0). Milli-equivalents per liter, percent ionic difference, and ionic balance can be calculated using a BASIC computer language program (meqcaco3.bas) or in a spreadsheet program. The conversion factors are as follows:


Result in mg/l multiplied by
This factor
NO3-N-
0.0161
Cl-
0.0282
SO4-2
0.0208
HCO3- (not alkalinity)
0.02
Na+
0.0435
Mg+2
0.0822
Ca+2
0.0499
K+
0.0256
After the results are converted into milli-equivalents per liter, the milli-equivalent anions are summed (NO3-N- + Cl- + SO4-2+ HCO3-) and the milli-equivalent cations are summed (Na+ + Mg+2 + Ca+2 + K+). The percent difference is calculated by taking the difference between the cations and the anions and dividing that by the sum of the cations and the anions multiplied by 100.
cations - anions
cations + anions X 100 = % ionic balance difference
(A good reference for this activity is Study and Interpretation of the Chemical Characteristics of Natural Water, 3rd ed., John Hem, USGS WSP 2254). In general, an ionic balance difference of ~10% or less indicates data, sampling, and lab analysis are adequate. Samples with ionic balance difference of greater than 10% will be reviewed for possible problems. Some possible problems include (but not limited to):
  • Lab analysis error
  • Data entry error
  • Missing analysis of chemical constituent
  • Point source effects
  • Others
2.1.4. Correlation Matrix
Construct a correlation matrix for the major water constituents; correlation coefficients between constituents can be generated using most common computer statistical packages. This will help to identify possible point sources of contamination. For example, a strong correlation (near 1) between nitrate and chloride, nitrate and sodium, and sodium and chloride may indicate an animal waste point source.
2.1.5. Descriptive Statistics
Use any available computer statistical package (or, if desired, use a hand calculator) to generate descriptive statistics for the project dataset. Traditional univariate statistics are used to get a feeling for the range and composition of the water quality data. At a minimum, the following statistics are calculated:
  • mean (average)
  • median
  • range
The following statistics may be calculated (optional):
  • variance
  • standard deviation
  • coefficient of variance
  • skewness
  • kurtosis
  • 25th percentile
  • 75th percentile
2.2. Perform the following calculations/test/analyses on QA/QC sample results:
2.2.1. Holding Times - see section 2.1
2.2.2. Relative Percent Differences (RPDs)—This is a measure of the agreement between the analytical results for a given sample and its duplicate obtained as per SOP GW-061. A relative percent difference of ~ 5% or less between the sample and its field duplicate or laboratory duplicate (all in mg/l) indicates good precision and accuracy. This is done according to the following formula (commonly in a spreadsheet):
(sample concentration - duplicate concentration )
RPD = X 100
(sample concentration + duplicate concentration)/2
2.3. Perform the following calculations/tests/analyses on nitrate-nitrogen results (other water quality parameters may have these actions performed on them as well, based on the project manager’s knowledge of the study area and the anticipated problems for the recommendations / conclusions):
2.3.1. Histogram - a histogram will help visualize the range, mean, and median of the data, and will help identify any data outliers. This can be done in the spreadsheet or other computer statistic program, as listed above.
2.3.2. Map of Sampled Wells and Nitrate Results--either by county, other subdivision, or study area. This map will help to visualize the data and identify subareas or hot spots in the study area. This can be done by hand on a base map or by use of computer and computer software, such as Surfer or ArcView.
2.4. Possible Point Source Influences
Review the data, field data sheet, the above mentioned tests and calculations, and other geologic or well construction information to determine if one or more point sources of contamination may have influenced the water quality results. Much of this determination is made from professional experience and knowledge of ground water properties. However, some indicators of point source problems may include (but are not limited to):
  • Outlier values
  • Strong correlation values (see Sec. 2.1.4)
  • Poor ionic balance (see Sec. 2.1.3)
  • Atypical pH, conductivity, total dissolved solids (if analyzed for) values in field
  • Anomalously high values (as compared with nearby samples)
Review of field data sheets, combined with the above list may more strongly indicate a point source problem. Some of the field data sheet indicators are (but are not limited to):
  • Dead/sprayed weeds around wellhead (evidence of spraying herbicides near well)
  • Well located at low end of field (surface runoff ponding near well could infiltrate around well)
  • Well located near feedlot, barnyard, manure storage facility, especially abandoned feedlots or facilities
  • Well located near septic systems (domestic wells), ag chemical mixing areas
  • Well located near reuse pit (more likely to have pesticide detections)
3. Evaluation and Interpretation
Evaluate and interpret data gathered either from sampling activities, literature search, database search, historical records retrieval, or other sources for the recommendations and conclusions in the final study area report. The majority of the interpretation and evaluation is done by the project manager. He/she may consult with other ground water professionals, such as the University of Nebraska, Conservation and Survey Division geologists or other NDEQ geologists, either in the Ground Water Unit or in other sections, as applicable.

Much of the evaluation and interpretation will be based on experience with this type of study and Nebraska geology and ground water. The following list may be used as a guideline for types of determinations necessary for the particular study.
3.1. Water Quantity/Water Quality Relationships
  • Surface water-ground water system and connections
  • Climatic influences on hydrologic cycle
  • Pumping influences on ground water and surface water
3.2. Hydrochemical Analysis
  • Ground water-aquifer material interactions
  • Ground water-bedrock interactions
  • Ground water-vadose zone interactions
  • Land use impacts on ground water quality
  • Historical water quality
3.3. Nonpoint Source Contamination
  • Spatial distribution of nitrate (and/or other parameters, such as pesticides)
  • Vertical distribution of nitrate (if known, from (for example) vadose zone sampling or multi-level wells)
  • Well distribution in study area and associated land use
  • Surface water irrigation (irrigation from a surface water diversion as opposed to a ground water source)
4. Reporting
The GWMA final report for the study area will include the data collected for the study, as specified above. These data will be presented in tables, maps, graphs, histograms, or other appropriate manner. Data will be used to back up the recommendations of the report; see SOP # GW160
The QA/QC sample results will be discussed in the final report. The QA implications of any inadequacies will be discussed in the report as well.
5. Storage
A hardcopy version of the data will be contained in the final report. This is stored with the file for the study. All records are retained according to the Department’s record retention policy.

Computer files will be stored on disk either in the project manager’s office and/or in the file, as specified above.

The project report and data will be presented on NDEQ’s web page (www.deq.state.ne.us), as agency staff time allows for publication.

All water quality information will be submitted to the University of Nebraska-Lincoln’s Water Sciences Laboratory for inclusion in the statewide Agrichemical Contaminant Database for Nebraska Ground Water. This database, which includes data which has been rated for QA/QC by University scientists, includes analyses for nitrate and pesticides. The database is accessible to the general public on the Nebraska Department of Natural Resources web page (www.dnr.state.ne.us).

Appendix A. Tables of appropriate holding times for various analytes.