A Recharge-Based Nitrate-Dilution Model for New Jersey,

MS Excel Workbook, version 6.1

Updated 2009

METADATA

1. IDENTIFICATION INFORMATION

1.1 CITATION

Hoffman, J.L. and Canace, R.J., 2002, A recharge-based nitrate-dilution model for New Jersey, MS Excel workbook, version 6.1: N.J. Geological Survey Digital Geodata Series DGS02-06, Trenton, updated 2009.

1.2 DESCRIPTION

1.2.1 ABSTRACT

Nitrate (NO3) is a constituent found in the effluent from individual on-site wastewater disposal systems (septic tanks.) Ensuring that nitrate concentrations in ground water do not exceed targets should be one goal when planning or reviewing proposed developments that will use septic tanks. The method presented here combines a model of nitrate dilution (based on Trela and Douglas, 1978) with one of ground-water recharge (Charles and others, 1993). The goal is to estimate the minimum lot size needed to provide enough recharge to dilute nitrate to a specified target. This method is intended to be a guide for estimating the impact of nitrate from septic tanks on ground-water quality.

Trela and Douglas (1978) presented a model of nitrate dilution in ground water. That model required inputs of per capita water demand, residents per household, nitrate concentration in effluent, recharge on the lot, and nitrate ground-water target. This model has been modified to require estimates of residents per household, net per capita NO3 loading rate from the septic tank, and nitrate target.

The New Jersey Geological Survey's (NJGS) method for estimating ground-water recharge (Charles and others, 1993) is a water-budget approach applicable only to New Jersey. It requires knowledge of the municipality, soil, land use, and basin factor. With these four variables it can estimate ground-water recharge on a particular site. In this spreadsheet the user inputs municipality, soil type, and basin factor.

Combining these two methodologies allows a determination of how much area per disposal system is needed to generate recharge to dilute the nitrate to the specified target. Because this requires an estimation of nitrate in ground water under 'as built' conditions, and the recharge is a function of the amount of pervious cover which is dependent on the average lot size, the methodology must solve the two equations simultaneously. Hoffman and Canace (2004) describe in more detail how these two underlying methodologies are combined.

This methodology is not intended to predict nitrate concentrations downgradient of an individual septic tank. It is intended to estimate net impact of a number of homes on ground-water nitrate concentrations at a point downgradient of the homes.

This approach assumes that all of the assumptions in the underlying Trela-Douglas nitrate dilution model and the NJGS ground-water recharge model are appropriate to the site being analyzed. One important limiting factor is that the NJGS ground-water recharge methodology does not apply to wetlands, surface water or hydric soils. This is not a limitation since these areas are unlikely spots for an individual on-site wastewater disposal systems.

The NJGS now recommends using a basin factor of 1.0, instead of 1.3 as used by Charles and others (1993). This is based on a calibration of basin-wide recharge volume from this methodology to revised stream baseflow estimations (Hoffman, 1999a). This factor should not be changed without a site-specific study. The basin factor is used in the spreadsheet to calculate recharge but is not explicitly a variable the user can alter.

This digital product consists of an Excel 2003 workbook which implements this methodology. When first opened, this file displays a calculation window. The user enters, via pulldown pick lists in the cells colored yellow, the parameters needed by the Trela-Douglas nitrate dilution and NJGS ground-water recharge models.

1.2.2. LIST OF FILES AND BRIEF DESCRIPTION

NJ_NO3_DILUTION_V6.1.XLS is an MS Excel 2003 workbook which contains the calculations necessary for solving the modified Trela-Douglas nitrate dilution and NJGS ground-water recharge models.

This file contains 7 worksheets:

Calculation Window - A worksheet in which the user enters the required input parameters. Clicking the 'SOLVE' button runs a macro which calculates the minimum land area needed to capture enough recharge to dilute the nitrate in the effluent to the target. Clicking on the 'Print Results' button prints a summary sheet to the computer's printer. Clicking on the 'Read Me!' button jumps to the ReadMe worksheet.

Metadata - This metadata file.

Municipality codes - A list of all municpalities in New Jersey with a code assigned just for this report. This worksheet contains the originally drived climate factor for each municipality as given in Appendix 6 of Charles and others (1993). This workbook also contains revised average annual climate facors and drought climate factors (Hoffman and French, in press).

New Soil - Old Soil - This worksheet lists those soils in SSURGO not in the county reports and the assumed equivalent soil. See section 2.10 for a description of this.

Printout - A series of cells formatted for printout of the results.

ReadMe - A shortened, one screen, list of instructions. A button on the Calculation Window worksheet jumps to this page. A button on this page returns to the Calcualtion Window worksheet.

Soil codes - A list of all soils in New Jersey with a code assigned just for this report. This worksheet includes the recharge factor and recharge constant for each soil. These data are from Appendix 5 of Charles and others (1993). This appendix has also been expanded to include the updated SSURGO soils (Soil Survey Staff, Natural Resources Conservation Service, 2005).

1.2.3. LIST OF KEYWORDS

nitrate, dilution, septic tanks, recharge, dilution model, ground-water recharge, land use, land cover, soils, New Jersey

1.3. GEOGRAPHIC EXTENT

State of New Jersey

1.4. CONTACT INFORMATION

Jeffrey L. Hoffman, Research Scientist

New Jersey Geological Survey

Land Use Management

New Jersey Department of Environmental Protection

PO Box 427

Trenton, NJ 08625

phone: (609) 984-6587

email: Jeffrey.L.Hoffman@dep.nj.gov

www.njgeology.org

2. DATA CREATION & QUALITY INFORMATION

2.1 NAME OF DATA

NJ_NO3_DILUTION_V6.1.XLS

2.2 DESCRIPTION OF THE TYPE OF DATA

The data consist of an EXCEL spreadsheet which incorporates the equations necessary to estimate the land area needed to provide enough recharge to dilute nitrate coming from a septic tank to target. The equations come from the Trela-Douglas nitrate dilution model (Trela and Douglas, 1978) and the NJGS ground-water recharge methodology (Charles and others, 1993).

This spreadsheet also includes a calculation worksheet which allows the user to input the necessary variables. The method is specific to a municipality and soil.

2.3. DATA SOURCES

The underlying nitrate dilution methodology is based on equations are from Trela and Douglas (1978). The ground-water recharge methdology is from Charles and others (1993) with an update to the climate factors used by the ground-water-recharge methodology byHoffman and French (in press). Hoffman and Canace (2004) document how these two approaches are integrated into the site-specific nitrate dilution application implemented in this Excel workbook.

2.4 DATA ORGANIZER

Jeffrey L. Hoffman

2.5 DATE OF COMPILATION

September 2000 (version 2.0)

May 2001 (version 3.0)

June 2001 (version 3.1)

July 2001 (versions 4.0 and 4.1)

May 2002 (version 5.0)

January 2007 (version 5.1)

May 2008 (version 6.0)

October 2009 (version 6.1)

2.6 PREVIOUS VERSIONS

Version 2.0 was the first version officially released. Earlier versions were circulated within the DEP for comment.

Version 3.0 modifies the nitrate loading portion to more explicitely account for nitrate mass loading directly and to not account for volume of septic effluent in diluting the nitrate.

Version 3.1 added a few minor reporting lines relating to impervious surface cover and reduction in average recharge.

Version 4.0 removed the term 'carrying-capacity' from the title and descriptions. This version also included some more minor changes in layout and reporting. Version 4.1 modified the way the basin factor is handled.

Verson 5.0 corrects a mathematical error made in the equations. The impervious surface cover modification was erronously applied twice in previous versions. Version 5.0 was published in May 2002.

Version 5.1 was prepared in January 2007. It corrects an error which had no impact on any calculation. In the explanation of the solution technique the incorrect equation was cited. The explanation, which appears in cells Q3:U4 incorrectly added a Rmax term to the denomiator of the left hand side of the equation. The correct equation is:

(4.4186HM)/(CqA(1-0.179A^-0.5708)) = Rmax

Version 6.0 added new climate factors based on a smoother interpolation of data from meterological sites to municipalities. This process is described in more detail in Hoffman and French (in press). The previous and revised climate factors are listed in the 'Municipality Codes' worksheet. The revised climate factors are presented for both normal and drought conditions. The revised climate factors for normal conditions are used in this workbook. Contact the authors for more information on how to use the climate factors appropriate for drought conditions.

Version 6.0 includes new SSURGO soils (Soil Survey Staff, Natural Resources Conservation Service, 2005). Section 2.9 below describes how recharge factors and recharge constants were assigned to the new SSURGO soils.

Version 6.0 also includes an update of the workbook to Excel 2003.

Version 6.1 released October 2009. This corrects the internal flag that incorrectly indicated some udorthent and urban land series were soils for which a recharge could be estimated. The ground-water-recharge methodology cannot be applied to urban or hydric soils. Many thanks to the user who found this error.

2.7. NECESSARY SOFTWARE SETUP

This spreadsheet was developed using the EXCEL spreadsheet software. Use of brand, commercial or trade names is for identification purposes only and does not constitute endorsement by the New Jersey Geological Survey.

The file NJ_NO3_DILUTION_61.XLS is an EXCEL2003 spreadsheet. When Excel starts to load the file it will indicate that the spreadsheet wants to run macros. The user must indicate that this is acceptable by clicking the 'ok' button. This is because the spreadsheet calculates the minimum lot size by running a macro. Not allowing macros to execute will prevent the spreadsheet from performing as desired.

Additionally, the calculations require access to a special solver routine. The program must have access to the file SOLVER.XLA. This is an add-in file to EXCEL 97 that must be activated by issuing the following commands:

1) On the 'Tools' menu pick the 'Add-Ins' option.

2) Check off the box in front of 'Solver Add-In.' (If this is not an option,

use the browse command to locate the file SOLVER.XLA

and pick this file.)

3) Click the 'OK' button to activate the Solver Add-In.

The spreadsheet cannot perform the mathematics necessary if this add-in is not accessible.

Users have discovered that the SOLVER.XLA file is dependent upon the version of Excel. The SOLVER.XLA file for Excel 97 is not the same as SOLVER.XLA for Excel 2000. It is important to install the version of the solver add-in that is compatible with the Excel program.

The calculation window does not automatically update the appropriate lot size as parameters are entered (as it does for the municipality and soil). The user must click on the 'SOLVE' button to do this. This runs an Excel macro which calculates the appropriate area. The macro responds with a command box titled 'Solver Results.' If the solver finds an acceptable solution the user should indicate in this command to "Keep Solver Solution" and then click on the "ok" button.

Clicking on the 'Print results' button prints a summary sheet of what is on the screen on the system's printer. If the 'SOLVE' button is not clicked before printing results then the minimum recharge areas showed on the printed page may not be a result of the input parameters shown.

2.8. SOLUTION TECHNIQUES

The spreadsheet uses an iterative technique to solve two nonlinear equations. This is documented in cells Q1- U27 of the Calculation Window. In some cases changing the initial guess shown in cell T30 may help the solution technique arrive at an answer.

2.9. SSURGO SOILS

The National Resources Conservaton Service produced a new soil coverage for New Jersey called the Soil Survey Geographic (SSURGO) Database (Soil Survey Staff, Natural Resources Conservation Service, 2005). This replaces the older county-oriented soil descriptions. The SSURGO coverage included includes a number of soil names that are not in the older county soil reports.

A geographical information system was used to compare the SSURGO soils with the older county soils. Each new SSURGO soils was matched up with an older soil from the individual county coverages. This equivalency is given in the worksheet 'New Soil - Old Soil.' The new soils were then added to the list of soils in the work sheet 'Soil Codes' and the recharge factors and recharge constants from the equivalent county soil were transferred to the new SSURGO soils.

3. DISTRIBUTION INFORMATION

This file consists of one Excel spreadsheet. This product may be distributed with proper attributation and if it is unchanged.

4. METADATA REFERENCE INFORMATION

4.1 PUBLICATION DATE

May 2002

Latest update October 2009

4.2 AUTHORS

Jeffrey L. Hoffman and Robert J. Canace

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

5. AUTHOR NOTES

The ground-water recharge methodology (Charles and others, 1993) was developed in the early 1990s and applies only to New Jersey. Visit the NJ Geological Survey's web site (www.njgeology.org) for information on ordering a paper copy of this report.

A digital copy of Charles and others (1993) is available at:
http://www.njgeology.org/pricelst/gsreport/gsr32.pdf

Please report any errors in this spreadsheet to the authors.

Check the NJGS website for possible additional information.
www.njgeology.org

A spreadsheet implementing the method of Charles and others (1993) is available for downloading from the NJGS website as Digital Geodata Series product DGS 99-2 (Hoffman, 1999b).
http://www.njgeology.org/index.html

A manual describing in detail the methodology (Hoffman and Canace, 2004) is available from the DEP Maps and Publications Sales Office.

Please report any errors in this spreadsheet to the authors.

6. REFERENCES

Charles, E.G., Behroozi, Cyrus, Schooley, Jack and Hoffman, J.L., 1993, A method for evaluating ground-water-recharge areas in New Jersey: N.J. Geological Survey Report GSR-32, Trenton, 95p.

Hoffman, J.L., 1999a, Basin factor calibration for ground-water-recharge estimation: New Jersey Geological Survey Technical Memorandum 99-2, Trenton, 2p.

Hoffman, J.L., 1999b, Ground-water recharge calculations for New Jersey, spreadsheet: N.J. Geological Survey Digital Geodata Series DGS 99-2, Trenton.

Hoffman, J.L. and Canace, R.J., 2004, A recharge-based nitrate-dilution model for New Jersey: N.J. Geological Survey Open-File Report OFR 04-1, 27p.

Hoffman, J.L., and French, M.A., in press, Ground-water recharge estimates in the New Jersey Highlands region: N.J. Geological Survey open-file report, in press.

Soil Survey Staff, Natural Resources Conservation Service, United States Department of Agriculture, 2005. Soil Survey Geographic (SSURGO) Database for New Jersey: Available online at http://soildatamart.nrcs.usda.gov.

Trela, J.J. and Douglas, L.A., 1978, Soils, septic systems and carrying capacity in the New Jersey Pine Barrens: paper presented at the First Annual Pine Barrens Research Conference, Atlantic City, May 22, 1978, 34p.

7. EPIGRAM

Throughout the history of literature, the guy who poisons the well has been the worst of all villains.
-- Author Unknown