Archive Files for Major River Basin 7 (MRB7) NHD Version 2 SPARROW Models # # METADATA DATE: xx/xx/13 # METADATA CONTACT: Daniel Wise # United States Geological Survey # Oregon Water Science Center # 2130 SW 5th Avenue # Portland, OR 97201 # voice: 503-251-3213 # fax: 503-251-3270 # email: dawise@usgs.gov # # SECONDARY CONTACT: Henry Johnson # United States Geological Survey # Oregon Water Science Center # 2130 SW 5th Avenue # Portland, OR 97201 # voice: 503-251-3472 # fax: 503-251-3270 # email: hjohnson@usgs.gov # This archive provides the data needed to reproduce the total nitrogen (TN) and total phosphorus (TP) SPARROW modeling results obtained by the USGS for Major River Basin 7 (MRB7; hydrologic region 17) using the NHD Version 2 stream network (Wise and Johnson, 2013). This archive contains three folders: 1) fluxmaster These are the SAS programs and data files that were used to estimate the TN and TP calibration loads for the MRB7 SPARROW models. Version 1.2 of Fluxmaster (March, 2012) was used to estimate these loads. Potential calibration sites for the MRB7 SPARROW NHD models were selected by considering the adequacy of their water-quality record and their proximity to a USGS gages with at least two years of streamflow record that included calendar year 2002. The criteria used to select potential TN and TP calibration sites were the same criteria used to select calibration sites for the previous set of SPARROW models developed for MRB7 using the RF1 stream network(Wise and Johnson, 2010): 1) the minimum number of samples was 20, 2) there were at least 3 samples in each season, 3) there were at least two years of water-quality data; and4) the last sample was collected no later than 09/30/95 if there were at least 5 years of data or the last sample was collected no later than 09/30/99 if there were less than 5 years of data. Differences between the calibration stream loads used in the PNW NHD SPARROW models and those used in the PNW RF1 SPARROW models were a result of closer examination of the water-quality and streamflow data that were used to estimate the loads and, in one case, by including a water-quality monitoring station that was located on an NHD stream reach not represented in the RF1 network. The folder wq_compilation contains all of the water-quality data obtained through querying federal, state, and local data bases (all_water_quality_data.zip) and the SAS programs used to process those data. There are four data compilation programs that need to be run in sequence. The prefix for each program indicates its order in the sequence. The SAS program “zz1. . .” reads in the data sets stored in “all_water_quality_data.zip”. There is a tab delimited text file (wq_q_matches.txt) that contains a list of the calibration sites used in the models. This file is used by the SAS program “zz4. . ” to create a SAS data set that contains only the water-quality data for the sites listed in “wq_q_matches.txt”. The output from this process is the water-quality data file that was used in Fluxmaster. The file fluxmaster_wq_input.zip contains two SAS data sets: a) the site file that was used in Fluxmaster (mrb7_site_file) and b) the water-quality data file that was used in Fluxmaster (final_flux_data.sas7bdat). The file fluxmaster_q_input.zip contains the streamflow files used in Fluxmaster. The file fluxmaster_mrb7_control_file_apr_10.sas is the Fluxmaster control file for that was used to estimate the calibration TN and TP loads. The file fluxmaster_results_05_12.sas7bdat contains the results from the Fluxmaster model. 2) sparrow_input This folder contains a modified version of the SAS program (data1_input_compilation_v2.sas) used to compile the file used as input for the MRB7 SPARROW models (nhd_data1.SAS7bdata). This version of the program is not a working SAS program. Rather, it contains the SAS code and other information that is needed to reproduce the compilation of the nhd_data1.SAS7bdata file used as input for the MRB7 SPARROW models. This SAS program reads in geospatial data related to the NHDPlus stream network, nutrient loading to land, point-source nutrient loading to streams, land use data, and landscape physical properties. Most of the geospatial data related to the NHDPlus stream network was included with the NHD Version 2 package for MRB7 (Horizon Systems, 2013). Some of the network data, however, was compiled by the MRB7 SPARROW team. These file are included in this folder and are as follows: a) compacted_node_values: the “compactified” to and from node values from the NHD network created by the MRB7 modeling team that are needed to run the SPARROW model; b) newfrac: the frac values estimated by the MRB7 modeling team; c) waterbody: the water body characteristics estimated by the MRB7 modeling team; The data1_input_compilation_v2.sas program imports the TN and TP results produced by Fluxmaster (fluxmaster_results_05_12.sas7bdat) and a file that indicates whether each site met the screening criteria for inclusion for TN and TP (nhd_load_data.sas7bdat) and combines both data sets. The data1_input_compilation_v2.sas program imports the landscape nutrient source data and the land to water delivery data (the program includes source for each data set) and performs some additional processing that is specific to the MRB7 SPARROW model . The data1_input_compilation_v2.sas program creates the nhd_data1.SAS7bdata file and performs some additional processing of the final data set that is specific to the MRB7 SPARROW models. 3) sparrow_control_ files This folder contains the two control files used to run the MRB7 SPARROW models for TN (MRB7_TN_NHD_model_V2.sas) and TP (MRB7_TP_NHD_model_V2.sas). The control files are configured to run SPARROW in the nonlinear least squares (NLLS) mode without adjusting the predicted loads at calibration stations. The TN model calibration needs to be run in two steps for the model to converge. The first step is to run the calibration with all delivery variables constrained to zero. The second step is the run the calibration/prediction with no constraints on the delivery variables and having the model obtain the initial estimates for the model coefficients from the previous model run. Literature Cited Horizon Systems, 2013, NHDPlusV2Data: Horizon Systems database, accessed March 18, 2013, at http://www.horizon-systems.com/nhdplus/ Wise, D.R., and Johnson, H.M., 2013, Application of the SPARROW model to assess surface-water nutrient conditions and sources in the United States Pacific Northwest: U.S. Geological Survey Scientific Investigations Report 2013-5103, 32 p.