Development of a Database-Driven System for Simulating 

Water Temperature in the Lower Yakima River Main Stem, 

Washington, for Various Climate Scenarios

Open-File Report 2013–1010

U.S. Department of the Interior

U.S. Geological Survey

Development of a Database-Driven System for 

Simulating Water Temperature in the Lower Yakima 

River Main Stem, Washington, for Various Climate 

Scenarios

By Frank Voss and Alec Maule

Open-File Report 2013–1010

U.S. Department of the Interior

U.S. Geological Survey
U.S. Department of the Interior

KEN SALAZAR, Secretary

U.S. Geological Survey

Marcia K. McNutt, Director

U.S. Geological Survey, Reston, Virginia: 2013

For more information on the USGS—the Federal source for science about the Earth,

its natural and living resources, natural hazards, and the environment—visit 

http://www.usgs.gov or call 1–888–ASK–USGS

For an overview of USGS information products, including maps, imagery, and publications,

visit http://www.usgs.gov/pubprod

To order this and other USGS information products, visit http://store.usgs.gov

Suggested citation:

Voss, Frank, and Maule, Alec, 2013, Development of a database-driven system for simulating water 

temperature in the lower Yakima River main stem, Washington, for various climate scenarios: U.S. 

Geological Survey Open-File Report 2013-1010, 20 p. 

Any use of trade, product, or firm names is for descriptive purposes only and does not imply 

endorsement by the U.S. Government.

Although this report is in the public domain, permission must be secured from the individual 

copyright owners to reproduce any copyrighted material contained within this report.
iii

Contents

Abstract ......................................................................................................................................................... 1

Introduction.................................................................................................................................................... 2

Methods......................................................................................................................................................... 2

Project Background.................................................................................................................................... 2

Problems with Running the SNTEMP Model in Series............................................................................... 7

Results......................................................................................................................................................... 11

Discussion ................................................................................................................................................... 17

Summary ..................................................................................................................................................... 19

Acknowledgments ....................................................................................................................................... 20

References Cited......................................................................................................................................... 20

Figures

Figure 1. Map showing extent of modeled reach of the lower Yakima River main stem, Washington, 

with model endpoints and model output locations. ........................................................................................ 3

Figure 2. Schematic diagram showing model for the Yakima River main stem with all modeled 

inflows and outflow for all seven model sections between Roza Dam and West Richland, Washington. ...... 4

Figure 3. Graph showing monthly air temperature increases applied to the Current Conditions 

scenario to create the adjusted air temperatures for the Plus 2 and Plus 1 climate scenarios. ..................... 5

Figure 4. Diagram showing logic flow for Model Automation Program....................................................... 10

Figure 5. Graph, created with first graphing tool, showing difference in mean monthly water 

temperature between the Plus 1 and Current Conditions scenarios for all output locations on

the modeled Yakima River main stem, Washington, May, June, and July.................................................. 12

Figure 6. Graph, created with first graphing tool, showing difference in mean monthly water 

temperature between the Plus 1 and Current Conditions scenarios for all output locations on the 

modeled Yakima River main stem, Washington, August, September, and October. ................................... 13

Figure 7. Graph, created with second graphing tool, showing difference in mean monthly water 

temperature between the Plus 1 and the Current Conditions climate scenarios for selected output 

locations on the modeled Yakima River main stem, Washington. ............................................................... 14

Figure 8. Graph, created with third graphing tool, showing difference in simulated mean daily 

water temperature between Plus 1 and Plus 2 scenarios and the Current Conditions scenario for 

the site at a distance of 194.47 kilometers upstream of the mouth of the Yakima River, Washington......... 14

Figure 9. Graph, created with third graphing tool, showing difference in simulated mean daily 

water temperature between Plus 1 and Plus 2 scenarios and Current Conditions scenario for the 

site at a distance of 184.25 kilometers upstream of the mouth of the Yakima River, Washington............... 15

Figure 10. Graph, created with third graphing tool, showing difference in simulated mean daily 

water temperature between Plus 1 and Plus 2 scenarios and Current Conditions scenario for the 

site at a distance of 168.42 kilometers upstream of the mouth of the Yakima River, Washington............... 15

Figure 11. Graph, created with third graphing tool, showing difference in simulated mean daily 

water temperature between Plus 1 and Plus 2 scenarios and Current Conditions scenario for the 

site at a distance of 88.57 kilometers upstream of the mouth of the Yakima River, Washington................. 16

Figure 12. Graph, created with third graphing tool, showing difference in simulated mean daily 

water temperature between Plus 1 and Plus 2 scenarios and Current Conditions scenario for the 

site at a distance of 50 kilometers upstream of the mouth of the Yakima River, Washington...................... 16
Tables

Table 1. Typical input files for one Stream Network Temperature Model..................................................... 6

Table 2. Stream Network Temperature Model input files that had to be swapped in and out of the 

processing directory for each of the 25 years in a simulation period ............................................................. 7

Table 3. Stream Network Temperature Model input files that had to be swapped in and out of the 

processing directory for each of the seven segments.................................................................................... 7

Table 4. Average daily mean water temperature for Kiona, Washington 2002–05.................................... 11

iv

Conversion Factors and Datums

Conversion Factors

Multiply By To obtain

Length

mile (mi) 1.609 kilometer (km)

kilometer (km) 0.6214 mile (mi)

Temperature in degrees Celsius (°C) may be converted to degrees Fahrenheit (°F) as follows:

°F=(1.8×°C)+32

Datums

Vertical coordinate information is referenced to the North American Vertical Datum of 1988 (NAVD 88).

Horizontal coordinate information is referenced to the North American Datum of 1983 (NAD 83).

Altitude, as used in this report, refers to distance above the vertical datum.
1

Development of a Database-Driven System for

Simulating Water Temperature in the Lower Yakima 

River Main Stem, Washington, for Various Climate 

Scenarios

By Frank Voss and Alec Maule

Abstract

A model for simulating daily maximum and mean water temperatures was developed by 

linking two existing models: one developed by the U.S. Geological Survey and one developed by 

the Bureau of Reclamation. The study area included the lower Yakima River main stem between 

the Roza Dam and West Richland, Washington. To automate execution of the labor-intensive 

models, a database-driven model automation program was developed to decrease operation costs, 

to reduce user error, and to provide the capability to perform simulations quickly for multiple 

management and climate change scenarios. Microsoft© SQL Server 2008 R2 Integration Services 

packages were developed to (1) integrate climate, flow, and stream geometry data from diverse 

sources (such as weather stations, a hydrologic model, and field measurements) into a single 

relational database; (2) programmatically generate heavily formatted model input files; (3) 

iteratively run water temperature simulations; (4) process simulation results for export to other 

models; and (5) create a database-driven infrastructure that facilitated experimentation with a 

variety of scenarios, node permutations, weather data, and hydrologic conditions while 

minimizing costs of running the model with various model configurations.

As a proof-of-concept exercise, water temperatures were simulated for a “Current 

Conditions” scenario, where local weather data from 1980 through 2005 were used as input, and 

for “Plus 1” and “Plus 2” climate warming scenarios, where the average annual air temperatures 

used in the Current Conditions scenario were increased by 1degree Celsius (°C) and by 2°C,

respectively.

Average monthly mean daily water temperatures simulated for the Current Conditions 

scenario were compared to measured values at the Bureau of Reclamation Hydromet gage at 

Kiona, Washington, for 2002–05. Differences ranged between 1.9° and 1.1°C for February, 

March, May, and June, and were less than 0.8°C for the remaining months of the year. The 

difference between current conditions and measured monthly values for the two warmest months 

(July and August) were 0.5°C and 0.2°C, respectively. The model predicted that water temperature 

generally becomes less sensitive to air temperature increases as the distance from the mouth of the 

river decreases. As a consequence, the difference between climate warming scenarios also 

decreased. The pattern of decreasing sensitivity is most pronounced from August to October.
2

Interactive graphing tools were developed to explore the relative sensitivity of average 

monthly and mean daily water temperature to increases in air temperature for model output 

locations along the lower Yakima River main stem.

Introduction

A methodology for linking and operating models using readily available and inexpensive 

software is described in this report. The methodology was applied to link water temperature 

models in series and to perform multiple simulations to predict daily maximum and mean water 

temperatures in the lower reach of the Yakima River basin for three climate scenarios. The 

predicted water temperatures from the model were used as input to other models in a decision 

support system developed for potentially managing resources in the Yakima River basin. The 

study area is located in the lower valley of the Yakima River basin in south-central Washington.

The lower valley is in the rain shadow of the Cascade Mountains and only receives 10 cm of 

annual precipitation which mostly occurs between late autumn and early spring. As a result, the 

agricultural land in the valley is irrigated with water that is stored in a series of reservoirs in the 

upper basin. For a more detailed description of the study area see Voss and others (2008).

Methods

Project Background

Two existing water temperature models were linked for the project: (1) a U.S. Geological 

Survey (USGS) model extending on the Yakima River main stem from the Roza Diversion Dam 

to the Prosser Dam, and (2) a Bureau of Reclamation (Reclamation) model extending from the 

Prosser Dam to the 224 bridge at West Richland, Washington (fig. 1). The development of the 

USGS model is documented by Voss and others (2008), and the development of the Bureau of 

Reclamation model is by Payne, Bremm, and Monk (2001). The Stream Network Temperature 

Model (SNTEMP) was used in both studies to simulate water temperatures.

SNTEMP is a mechanistic heat transport model that predicts water temperature as a 

function of stream distance and environmental heat flux. SNTEMP has been used successfully in 

documented water temperature studies and has many useful features. For example, the model 

executable file is free to download, it has a fast run-time, and it uses readily available input data 

(such as local weather data for climate inputs and topographic maps and field measurements for 

stream geometry). SNTEMP also can simulate stream networks of any size or order and can use 

simulation time steps ranging from 1 day to 1 month. The model’s documentation states that 

SNTEMP is capable of simulating water temperatures to within 0.5 C° of measured mean 

temperatures, given representational data for a user-selected time step. SNTEMP also is supported 

with a web site that provides a downloadable user’s manual, a free online water-temperature 

modeling course, a bibliography of SNTEMP study reports, a Frequently Asked Questions section, 

and a publication on how to conduct water temperature modeling studies (Bartholow, 2000).

SNTEMP software and documentation can be downloaded from a USGS Fort Collins Science 

Center web site (USGS, 2012).

The heat transport model in SNTEMP is based on the dynamic temperature-steady flow 

equation that assumes that all input data for meteorological and hydrological variables are average 

values for the simulation time step used. To obtain a daily time step, the length of a model had to 

equal the distance that a volume of water would travel in a single day (that is, travel time) under 

steady-flow conditions. Because high water temperature usually is of most concern during periods 
3 

of low flow and high air temperatures, the USGS and Reclamation models were developed to 

simulate water temperatures during the growing season, when temperatures are warmest and flows 

are regulated for crop irrigation. Travel time for the study reach was estimated to be 7 days based 

on field measurements made by the USGS (Voss and others, 2008). Therefore, to model the entire 

main stem, seven reach models needed to be linked in series to produce the required daily values 

for maximum and mean water temperatures. 

Figure 1. Map showing extent of modeled reach of the lower Yakima River main stem, Washington, with 

model endpoints and model output locations.

The model structure for the main stem between Roza Dam and Prosser Dam is the same as 

was documented by Voss and others (2008). Model structure for the main stem between Prosser 

Dam in West Richland was changed by splitting the original model into two models to generate a 

daily time step: (1) one model extended from Prosser Dam to Kiona, Washington, and (2) one 

model extended from Kiona, Washington, to the 224 bridge at West Richland, Washington. A 

schematic view of the entire model structure is shown in figure 2. Each model segment represents 

1 day of travel time during mid-summer low-flow conditions. The column on the left contains all 

modeled streams and returns flowing into the Yakima River main stem, and the column on the 

right contains all modeled diversions from the main stem.
4 

Figure 2. Schematic diagram showing model for the Yakima River main stem with all modeled inflows and 

outflow for all seven model sections between Roza Dam and West Richland, Washington. 
5 

In addition to data for characterizing stream-channel geometry and shading, SNTEMP 

requires three sets of input data to run simulations: climate data, flow data, and stream 

temperatures of all inflowing waterways. Climate data for the first scenario (“Current Conditions” 

scenario because it simulated conditions unaffected by climate change) used data compiled from 

two sources: the National Oceanic and Atmospheric Administration’s National Weather Service 

station at the Yakima Air Terminal (National Oceanic and Atmospheric Administration, 2012, 

station ID = KYKM), and the Washington AgriMet weather station in Harrah, Washington 

(Agrimet, 2012, station ID = HRHW). Climate data for the second scenario (“Plus 1”) were the 

same as data used for the Current Conditions scenario except that the average daily air 

temperatures were adjusted 1°C higher using a method described in Mastin (2008). Climate data 

for the third scenario (“Plus 2”) also were the same as data used for the Current Conditions 

scenario, but with the average daily air temperature adjusted 2°C higher, again using the method 

described in Mastin (2008). The increases in air temperature were simulated by applying monthly 

adjustments (that averaged a 1 or 2°C annual increase), as shown in figure 3. 

0 

0.5

1 

1.5

2 

2.5

3 

J F M A M J J A S O N D

Air Temperature (C)

Monthly air temperature increases applied to the Current Conditions 

Scenario to create Plus 2 and Plus 1 Climate Scenarios air temperatures

PLUS 2

PLUS 1

Figure 3. Graph showing monthly air temperature increases applied to the Current Conditions scenario to 

create the adjusted air temperatures for the Plus 2 and Plus 1 climate scenarios.

SNTEMP does not have hydrologic modeling capabilities; therefore, all flows used in the 

model must be created from historical flow measurements or flow predicted by hydrologic 

models. Flow data for the three climate scenarios were generated using a watershed model 

developed by the USGS, coupled with a river and reservoir-management model developed by 

Reclamation (Mastin and Sharp, 2006). 

The watershed model used is the Precipitation Runoff Modeling System (PRMS) model.

PRMS is a deterministic, distributed-parameter, watershed model developed to evaluate the 

impacts of various combinations of precipitation, temperature, and land use on streamflow and 

general basin hydrology. Each hydrologic component used to generate streamflow is represented 

within PRMS by a process algorithm based on a physical law or an empirical relationship with 

measured or calculated characteristics. Distributed-parameter capabilities of PRMS are provided 

by partitioning a basin into Hydrologic Response Units (HRUs), using characteristics such as 
6 

slope, aspect, elevation, vegetation type, soil type, and precipitation distribution. These HRUs are 

assumed to be homogeneous with respect to their hydrologic response. For each HRU, a water 

balance is computed each day, and an energy balance is computed twice each day. PRMS is 

conceptualized as a series of reservoirs (impervious zone, soil zone, subsurface, and groundwater) 

whose outputs by HRU combine to produce the daily basin response. 

Output from the PRMS model was used as input to the reservoir-management model 

developed using RiverWare software. RiverWare is a model-building tool used to develop waterdistribution models for operations and planning. The model for the Yakima River consists of a 

network of reservoirs, diversions, river reaches, confluences, and other components. Operations 

policy and rules associated with reservoirs or other system components within the basin are used 

to control flows throughout the system. Output data from the RiverWare model were used as input 

to the SNTEMP model for all three climate scenarios.

Initial daily mean water temperatures for all inflowing waterways (fig. 2) in the model 

were estimated using empirical equations developed for the existing USGS model. The exceptions 

were initial water temperatures for Satus Creek, which were provided by a model developed by the 

Columbia River Inter-tribal Fish Commission.

SNTEMP requires nine input files (table 1) to run a simulation for a single reach. The files 

are fixed-format text files and their file structure is described in the SNTEMP user’s manual 

(Theurer and others, 1984).

Table 1. Typical input files for one Stream Network Temperature Model. 

File name File function

Jobfile.txt Controls simulation operation

Metdat.txt Contains input climate data for simulation

Hyddat.txt Contains input flow and water temperature data for simulation

Timedat.txt Defines time step for simulation

Skelton.txt Defines stream network structure of model

Geom.txt Defines stream geometry of model

Study.txt Defines locations for generating simulation output

Hydnode.txt Defines hydrology structure of model

Shade.txt Defines amount and type of shading occurring along the river

Although SNTEMP can span a multiple-year simulation period when percent cloud cover 

is used as input to the climate data file (as a surrogate input for solar radiation), SNTEMP only can 

span a 1-year simulation period when directly measured solar radiation is used as input. This 

limited capability probably is a result of SNTEMP being developed at a time when long records of 

daily solar radiation measurements were not available. However, because only direct solar 

radiation data were available from the weather stations used for this study, the study required that 

a climate scenario’s 25-year simulation period encompass 25 separate simulations with a unique 

climate input file for each year for each segment of the model. Therefore, generating daily values 

for seven segment models linked in series required a total of 525 SNTEMP model runs for three 

climate scenarios with 25-year simulation periods. 
7 

Problems with Running the SNTEMP Model in Series

Achieving the project objective of generating daily maximum and mean water 

temperatures for the three climate scenarios required running 525 separate SNTEMP simulations

(7 segments×3 scenarios×25 years). However, SNTEMP was not designed to be run in series, and 

the setup of the SNTEMP model runs is manual, time-consuming, and prone to error. Project 

requirements presented the following challenges:

Challenge 1: Various combinations of the nine input files must be manually swapped in and out of 

the execution directory for each year and segment. 

Running multiple simulations requires that different combinations of input files be 

swapped in and out of processing directories, and if the correct set of files is not present in the 

right directory when the model is run, this could result in erroneous output that might not be 

detected in the analysis phase of the project. Keeping track of the appropriate files to manually 

swap in and out of the processing directory is a tedious accounting process for 525 model runs and 

is highly prone to error as a manual process. The following files (in table 2) had to be swapped in 

and out of the processing directory for each of the 25 years in a simulation period: 

Table 2. Stream Network Temperature Model input files that had to be swapped in and out of the 

processing directory for each of the 25 years in a simulation period.

File Description

Metdat.txt Contains input climate data for simulation

Hyddat.txt Contains input flow and water temperature data for simulation

Timedat.txt Varies by whether or not the year is a leap year

The following files (in table 3) also had to be swapped in and out of the processing directory for 

each of the seven segments:

Table 3. Stream Network Temperature Model input files that had to be swapped in and out of the 

processing directory for each of the seven segments.

File Description

JobFile.txt Controls simulation operation

Skelton.txt Defines stream network structure of model

Geom.txt Defines stream geometry of model

Study.txt Defines locations for generating simulation output

Hydnode.txt Defines hydrology structure of model

Shade.txt Defines amount and type of shading occurring along the river

Challenge 2: Input data must be manually integrated from various sources into input files with 

complex logic and structure. 

SNTEMP is not designed to interact with a database. All data input to SNTEMP must be 

managed through text files. As designed, to complete one model run for a single river segment, 

data must be downloaded from databases, spreadsheets, XML, and flat files and then manually

assembled with a text editor in the complex format specified in the SNTEMP user’s manual. Input 

files contain data dependencies in which the value of one parameter determines the existence of 

other parameters within the same input file or other input files. Learning to construct correctly the 
8 

input file formats is time-consuming, and manual assembly of input files is prone to data entry 

errors that might be detected by abnormal error termination of the model run. Otherwise, input 

data errors might not be detected at all, leading to erroneous conclusions. 

In addition to integrating input data from various sources, because SNTEMP was not 

designed to be run in series, the output of one model run would have had to be used as input to the 

next model run so that output files could be manually deconstructed and reconstituted into 

complex input file formats. Without a model automation program, this time-consuming and errorprone process would have had to be performed manually 525 times to meet project objectives.

Challenge 3: SNTEMP model does not interact with databases.

Large amounts of input data cannot easily be associated with output data when these data 

are stored in files rather than in a relational database that allows queries to be easily constructed 

for data analysis. Because data are stored in files rather than in a relational database, flexible 

analysis tools (such as generation of dynamic graphs based on user-selected criterion) cannot be 

easily developed.

Solution

To overcome these three challenges, we used Microsoft© SQL Server’s Integration 

Services (SSIS) to automate database-driven execution of the model. SSIS was used to develop 

programs to: 

1. Import data from various sources into a unified database, 

2. Automate extraction of data from the database to create model input files, 

3. Execute the model and control the flow of files in and out of the processing directory, 

4. Import model results into a relational database, 

5. Create the next input file from the output of the previous run, and 

6. Generate dynamic graphs of simulation results based on user-selected criterion . 

SSIS consists of tasks (or units of work) and program control flow and data integration 

tasks that can be arranged on a user-interface and programmed using Dot Net Code, SQL 

statements, and stored procedures. These graphical units of work can be manually manipulated 

within the SSIS interface to easily refine and revise process work flows to meet changing project 

requirements.

Process Flow

The process flow for model execution was analyzed, as shown in figure 4. The looping 

starts by selecting data for all the nodes in the first segment for the first year of the simulation 

period, and writing them to the appropriate SNTEMP input files. The model is then executed and 

the results are written back to the database. The process is repeated for the remaining segments 

where the output from an upstream segment is used as input to the next downstream segment. 

These steps are then repeated for each year of the simulation period for each of the three scenarios. 

Design of Database and Model Run Automation Program

Design objectives for the model automation program included the ability to easily add 

scenarios, years, segments, and stream nodes to the model without changing underlying code for 

the automation program. This objective was accomplished by creating a database design that 
9 

would drive execution of the automation program. The database consisted of tables where 

scenario, years, segments, and stream nodes could be added easily as data, and the program was 

designed to use this configuration data to control execution of various tasks. Tasks controlled by 

this data included: 

• Swapping files in and out of the processing directory based on year and segment. See 

Challenge 1 for a description the issues associated with manual file swapping. 

• Automation of input files generated from data stored in the database. This eliminated 

the error-prone and time-consuming processes described in Challenges 2 and 3. 

• Import of output data into a relational database. This allowed for easy analysis of the 

data and for automated construction of input files from output of a previous run. Importing 

data from the output files into the database allowed input data to be associated easily with 

output data (see Challenge 3), and analysis tools could be developed to meet various 

analysis needs. The dynamic graphs (created using SQL Server Reporting Services) 

included in this report are examples of the types of tools that can be created if data are in a 

relational database.

Additionally, program automation created a testable and repeatable process that can be 

used to verify the accuracy of the model and to refine iteratively the input data and processes. 

Program automation also saved project money because files did not need to be manually 

assembled nor did complex accounting need to be performed to determine which input files to 

move into the processing directory. Program automation also improved the accuracy of the model 

runs. 

Generation of Input Data into the Database

A series of calculations were performed for preparing model input data. Air temperatures 

for the two climate scenarios were calculated by applying monthly adjustment factors shown in 

figure 2. Flow data for all the scenarios were imported from the Reclamation RiverWare model 

and daily flows were calculated for all ungaged inflowing and outflowing streams. Daily initial 

water temperatures for all inflows to the model were then generated using regression equations 

that related water temperature to air temperature or to air temperature and flow. The initial water 

temperatures for Satus Creek from the Columbia River Inter-Tribal Fish Commission (CRITFC) 

model were added to the database.
10

Start

Import climate data into database and calculate climate data for climate change scenarios

Import flow data from BOR into database and calculate flow for laterals

Calculate initial water temperatures using empirical equations

Import water temperatures into database for Satus Creek from the CRITFC model

Another 

Scenario?

Another 

Year?

Leap 

Year? Select files for leap year

Select files for non-leap year

Another 

Segment?

Create an intermediate file to hold hydrology input data for model

Create a meteorology file for the segment

Another 

Node?

Retrieve data for node from database and write to hydrology file

Run model and read results into database

Create file for other DSS model

End

No

No

No

No

Figure 4. Diagram showing logic flow for Model Automation Program. 
11

Results

The two linked models were used without making modifications to their stream geometry 

data. Both models had been calibrated and validated when they initially were developed but recalibrating and validating the linked models were beyond the scope of this study. Simulation data 

were compared to data measured at a Reclamation Hydromet gage to obtain a general indication of 

model performance. Table 4 shows monthly average mean daily measured and simulated water 

temperatures for 2002–05, measured at the Reclamation Hydromet gage at Kiona, Washington 

(Bureau of Reclamation, 2012, station ID = KIOW). 

Table 4. Average daily mean water temperature for Kiona, Washington 2002–05. 

Month

Average measured mean 

daily water temperature 

(degrees Celsius) 

Average simulated

mean daily water 

temperature (degrees 

Celsius) 

Current Conditions 

scenario

Average simulated

mean daily water 

temperature (degrees 

Celsius) 

Plus 1 scenario

Average simulated

mean daily water 

temperature (degrees 

Celsius) 

Plus 2 scenario

JAN 4.3 5.2 5.4 5.5

FEB 5.4 7.3 7.5 7.8

MAR 9.3 11.1 11.2 11.4

APR 13.9 14.4 14.7 15.1

MAY 17.3 18.4 18.8 19.0

JUNE 20.2 21.8 22.3 22.6

JULY 24.4 25.0 25.2 25.3

AUG 23.2 23.5 23.7 23.9

SEPT 19.1 18.9 19.1 19.5

OCT 13.3 12.9 13.1 13.4

NOV 7.1 6.9 7.0 7.2

DEC 4.6 4.9 5.3 5.6

The difference between average monthly mean daily water temperatures predicted for the 

Current Conditions scenario and measured values at the Kiona gage was between 1.9° and 1.1OC 

for February, March, May, and June, and less than 0.8°C for the remaining months of the year.

The difference between current conditions and measured monthly values for the two warmest 

months (July and August) were 0.5°C and 0.2°C, respectively.
12

Programs were developed for creating interactive graphs for visualizing simulation results 

using SQL Server Reporting Services (SSRS). SSRS was used to create a graphing tool that would 

allow users to select various criteria (distance, scenario, months, etc.) to meet their analysis needs.

SSRS is a server-based report generating software that is integrated with the SQL Server 2008 R2 

database. The user can graph the differences in mean monthly water temperature between the 

Current Conditions and either the Plus 1 or Plus 2 scenarios at selected distances along the main 

stem for selected months. Water temperature differences between the Plus 1 and Current 

Conditions scenarios for the entire modeled main stem for May, June, and July are shown in figure 

5. Water temperature differences between the Plus 1 and Current Conditions scenarios for the 

entire modeled main stem for August, September, and October are shown in figure 6. 

0 

0.2

0.4

0.6

0.8

1 

1.2

0 50 100 150 200 250

Water Temp (C)

Distance from the mouth of the Yakima River (km)

Difference in mean monthly water temperature between the Plus 1 and Current 

Conditions climate scenarios for all output locations for May, June, and July

MAY

JUN

JUL

Figure 5. Graph, created with first graphing tool, showing difference in mean monthly water temperature 

between the Plus 1 and Current Conditions scenarios for all output locations on the modeled Yakima River 

main stem, Washington, May, June, and July.
13

0 

0.1

0.2

0.3

0.4

0.5

0.6

0.7

0.8

0.9

1 

0 50 100 150 200 250

Water Temp (C)

Distance from the mouth of the Yakima River (km)

Difference in mean monthly water temperature between the Plus 1 and Current 

Conditions climate scenarios for all output locations for August, September, and 

October

AUG

SEP

OCT

Figure 6. Graph, created with first graphing tool, showing difference in mean monthly water temperature 

between the Plus 1 and Current Conditions scenarios for all output locations on the modeled Yakima River 

main stem, Washington, August, September, and October.

A second graphing tool was developed to allow users to graph the differences in mean 

monthly water temperature between the Current Conditions and either the Plus 1 or Plus 2 

scenarios for all months in a year for any combination of output sites. Water temperature 

differences between the Plus 1 and Current Conditions scenarios for all months of the year for the 

output sites at distances of 194.47, 184.25, 168.42, 88.57, and 50 km upstream of the mouth of the 

river is shown in figure 7. Water temperature generally becomes less sensitive to air temperature 

with decreasing distance from the mouth of the river (fig. 7). 
14

0 

0.2

0.4

0.6

0.8

1 

1.2

J F M A M J J A S O N D

Water Temp. (C)

Difference in mean monthly water temperature between the 

Plus 1 and Current Conditions climate scenarios for selected 

output locations on the Yakima River main stem

50

88.57

168.42

184.25

194.47

Figure 7. Graph, created with second graphing tool, showing difference in mean monthly water 

temperature between the Plus 1 and the Current Conditions climate scenarios for selected output locations 

on the modeled Yakima River main stem, Washington. 

A third graphing tool was developed to allow users to graph the differences in daily water 

temperatures between the Current Conditions and the Plus 1 or Plus 2 scenarios at selected output 

sites. Figures 8, 9, 10, 11, and 12 are screenshots showing the differences in mean daily water 

temperatures between the Plus 1 and Plus 2 scenarios and the Current Conditions scenario for sites 

at distances of 194.47, 184.25, 168.42, 88.57, and 50 km upstream of the mouth of the river, 

respectively, for July 1–August 31, 1993 compared to 1993 current conditions. 

0 

0.5

1 

1.5

2 

2.5

3 

7/1 7/8 7/15 7/22 7/29 8/5 8/12 8/19 8/26

Water Temp. (C)

Difference in mean daily water temperature between the 

(Plus 2 and Plus 1) and Current Conditions scenarios for river 

distance 194.47 km for July and August, 1993

Plus 1

Plus 2

Figure 8. Graph, created with third graphing tool, showing difference in simulated mean daily water 

temperature between Plus 1 and Plus 2 scenarios and the Current Conditions scenario for the site at a 

distance of 194.47 kilometers upstream of the mouth of the Yakima River, Washington. 
15

0 

0.2

0.4

0.6

0.8

1 

1.2

1.4

1.6

7/1 7/8 7/15 7/22 7/29 8/5 8/12 8/19 8/26

Water Temp. (C)

Difference in mean daily water temperature between the 

(Plus 2 and Plus 1) and Current Conditions scenarios for river 

distance 184.25 km for July and August, 1993

Plus 1

Plus 2

Figure 9. Graph, created with third graphing tool, showing difference in simulated mean daily water 

temperature between Plus 1 and Plus 2 scenarios and Current Conditions scenario for the site at a distance 

of 184.25 kilometers upstream of the mouth of the Yakima River, Washington. 

0 

0.2

0.4

0.6

0.8

1 

1.2

1.4

7/1 7/8 7/15 7/22 7/29 8/5 8/12 8/19 8/26

Water Temp. (C)

Difference in mean daily water temperature between the 

(Plus 2 and Plus 1) and Current Conditions scenarios for river 

distance 168.42 km for July and August, 1993

Plus 1

Plus 2

Figure 10. Graph, created with third graphing tool, showing difference in simulated mean daily water 

temperature between Plus 1 and Plus 2 scenarios and Current Conditions scenario for the site at a distance 

of 168.42 kilometers upstream of the mouth of the Yakima River, Washington. 
16

0 

0.1

0.2

0.3

0.4

0.5

0.6

0.7

0.8

0.9

7/1 7/8 7/15 7/22 7/29 8/5 8/12 8/19 8/26

Water Temp. (C)

Difference in mean daily water temperature between the 

(Plus 2 and Plus 1) and Current Conditions scenarios for 

river distance 88.57 km for July and August, 1993

Plus 1

Plus 2

Figure 11. Graph, created with third graphing tool, showing difference in simulated mean daily water 

temperature between Plus 1 and Plus 2 scenarios and Current Conditions scenario for the site at a distance 

of 88.57 kilometers upstream of the mouth of the Yakima River, Washington. 

0 

0.1

0.2

0.3

0.4

0.5

0.6

7/1 7/8 7/15 7/22 7/29 8/5 8/12 8/19 8/26

Water Temp. (C)

Difference in mean daily water temperature between the 

(Plus 2 and Plus 1) and Current Conditions scenarios for 

river distance 50.00 km for July and August, 1993

Plus 1

Plus 2

Figure 12. Graph, created with third graphing tool, showing difference in simulated mean daily water 

temperature between Plus 1 and Plus 2 scenarios and Current Conditions scenario for the site at a distance 

of 50 kilometers upstream of the mouth of the Yakima River, Washington. 
17

The daily graph tool provides users with a more detailed view of water temperature 

response to air temperature at the output sites. The series of graphs show that water temperature 

becomes less sensitive to air temperature with decreasing kilometer distance from the mouth of the 

river.

Discussion

This paper describes a cost-effective approach for running multiple simulations. The 

database-driven design made the model flexible and extendable to different scenarios, node 

permutations, new weather information, and hydrology attributes. Database-driven design also 

eliminated the need to manually assemble files, to manually set up parameters, and to perform 

other labor-intensive processes. Once the system was in place, the simulations for the three climate 

scenarios ran efficiently and successfully without the need of other user interventions. 

The single relational database developed for the project proved effective for storing and 

managing all project data. Input data from many sources and in many formats were imported into 

the database. The entire database easily can be backed up and sent to other researchers for their 

analysis. Integrated database tools are available for performing complex queries and for 

performing data mining to detect and characterize patterns in the input data and simulation results

for potential future projects. Database tools also are available for providing reports, charts, and 

data on the Internet if there is an interest.

The SSIS packages accessed model configuration data (such as scenarios, years, segments, 

and stream nodes) from a relational database and produced model input files for 525 simulations 

without introducing errors into the model runs. The packages were designed so that a new user 

also can run simulations without having to learn the SNTEMP file formats. Because the system is 

database-driven, users can add data for numerous scenarios to the database, and the SSIS package 

will automatically adapt to the new changes so that no SNTEMP file manipulation is necessary.

Because all input and output data are in a relational database, cross comparisons between different 

scenarios are relatively easy to make by querying the database. 

A comparison of Current Conditions scenario results with measured data at the Hydromet 

gage located at Kiona, Washington, showed that the model had the best agreement with measured 

values from mid-summer through late autumn. This result was expected because both of the linked 

models initially were developed to simulate conditions for the growing season when air 

temperatures are warm and the main stem flows are relatively low and stable. The more 

divergence from growing season conditions, the more error is introduced into the model and the 

more care should be taken in interpreting the simulation results.

For this study, the model was used to explore the relative sensitivity of water temperatures 

to changes in air temperature between locations along the Yakima River main stem. However, the 

model also could be used to test water temperature sensitivity to other variables, such as changes 

in flow or shading. 
18

The method described in this report can be applied to creatively combine a wide range of 

models, regardless of the coding language in which they are written. These models can easily be 

integrated into a decision-support system or loosely linked to a set of models for analysis. Possible 

opportunities for future work could include the following:

• Link a Geographic Information System (GIS) to the system. A GIS could be 

programmed so that a user could set the locations on the main stem of a river where output 

was desired on a map, and the GIS would calculate characteristics of the site (such as river 

mile or elevation) and write the data to an SNTEMP stream geometry input file. A GIS 

also could be linked so that it could automatically display simulation results using timeaware animation to illustrate seasonal water temperature patterns.

• Develop a user interface. Many types of interfaces could be developed to help users 

interact with SNTEMP. For example, options could be added to allow a user to increase or 

decrease daily flows for all the segments in a linked series of segment models by a 

percentage during a simulation period to perform sensitivity analysis of water temperatures 

to flow.

• Add analysis tools. By storing all model input and output data in a relational database, a 

user can take advantage of the analytic and reporting tools within the database. For 

example, SQL Server 2008 R2 has a suite of algorithms and tools for performing data 

mining to explore patterns in data. Tools also exist to generate many types of charts that 

can give the user an intuitive understanding of the distributions of data for variables of 

interest.

• Develop a Web interface. SQL Server 2008 R2 also has tools for linking the relational 

database to the Internet. Projects can be developed for allowing the public to explore

recently run simulation results by creating and downloading interactive reports and charts 

and to download input data and simulation results in a wide variety of file formats, such as 

Microsoft© Excel, XML, pdf, and delimited text files.

• SSIS also can be used to link any model to other models. Output from one model can be 

used as input to another model, and loops also can be created so models feed back to each 

other.
19

Summary

Selecting the methodology for linking and operating models is an important consideration 

when designing a decision-support system. This often overlooked topic can have a substantial 

impact on the cost of setting up and operating a decision-support system. It also can affect the 

system’s capacity to integrate models with complex input or output file formats, thereby limiting 

the creative combinations of models that can be used.

The information in this report outlines a database-driven approach for automating laborintensive models for multiple simulations. Two existing water temperature models were linked in 

series to simulate daily maximum and mean water temperature at user-specified locations on the 

Yakima River main stem between Roza Dam and the 224 bridge at West Richland, Washington. 

Data flows for automating simulations were defined and diagrammed. A relational database was 

designed, and data from numerous sources and formats were imported to its tables. SQL Server 

2008 R2 Integration Services was used to develop packages to create SNTEMP input files, to run 

SNTEMP, and to process simulation output over multiple simulation runs.

SNTEMP was successfully linked to the relational database. The database-driven approach 

used made it possible for different management and climate scenarios to be run without the user 

having to perform any file manipulation. All data management for the model was handled using 

database management tools.

Water temperatures were simulated for a “Current Conditions” scenario (1980–2005) and 

for “Plus 1” and “Plus 2” climate warming scenarios where the average annual air temperatures 

used in the Current Conditions scenario were increased by 1degree Celsius (°C) and 2°C,

respectively. Monthly mean daily water temperatures (2002–05) predicted for the Current 

Conditions scenario were compared to measured values at the Hydromet gage at Kiona, 

Washington. Differences were between 1.9° and 1.1°C for February, March, May, and June, but 

less than 0.8°C for the remaining months of the year. The difference between simulated current 

conditions and measured monthly values for the two warmest months were small (0.5°C for July 

and 0.2°C for August).

Interactive graphing tools were developed for exploring the relative sensitivity of average 

monthly and mean daily water temperature to increases in air temperatures. The model predicted 

that water temperature generally becomes less sensitive to air temperature increases the farther the 

location is downstream of Roza Dam, and that the difference between climate warming scenarios

also decreases with downstream distance from the dam. The model predicted that the largest

changes in sensitivity occur from August to October.

Much work has been done to create useful models for many disciplines ranging from 

hydrology to sociology to economics. Using a data-driven approach with a relational database 

technology makes it possible to develop creative and flexible decision-support systems for 

investigating management options for complex systems.
20

Acknowledgments

The authors would like to acknowledge Mark Mastin and Cindi Barton of the USGS 

Washington Water Science Center in Tacoma, Washington, for their support for this project.

References Cited

Agrimet, 2012, The Pacific Northwest Cooperative Agricultural Weather Network, Harrah, 

Washington AgriMet Station (HRHW), accessed on December 5, 2012, 

http://www.usbr.gov/pn/agrimet/agrimetmap/hrhwda.html

Bartholow, J.M., 2000, The stream segment and stream network temperature models—A selfstudy course: U.S. Geological Survey Open-File Report 99–112, 276 p. (Also available at 

http://pubs.er.usgs.gov/publication/ofr99112.)

Bureau of Reclamation, 2012, Yakima Hydromet Real-Time (DAYFILES) Data Access, accessed 

12/5/2012, http://www.usbr.gov/pn/hydromet/yakima/yakwebdayread.html

Mastin, M.C., 2008, Effects of potential future warming on runoff in the Yakima River Basin, 

Washington, U.S. Geological Survey Scientific Investigations Report 2008–5124, 12 p. (Also 

available at http://pubs.er.usgs.gov/publication/sir20085124.)

Mastin, Mark, and Sharp, Warren, 2006, Comparison of simulated runoff in the Yakima River 

Basin, Washington, for present and global climate-change conditions: Presentation by U.S. 

Geological Survey at the Climate Impacts Group, Climate and Water Resource Forecasts for the 

2007 Water Year Conference, Kelso, Washington, October 3, 2006, 25 p.

National Oceanic and Atmospheric Administration, 2012, Climate Data Online, accessed 

12/5/2012, http://www.ncdc.noaa.gov/cdo-web/

Payne, T.R., Bremm, D.J., and Monk, P.A. 2001, Evaluating the Columbia River Pump Exchange 

Project using the Stream Network Temperature [SNTEMP] model: Report prepared by Thomas 

R. Payne and Associates, Arcata, Calif., for Kennewick Irrigation District, Kennewick, Wash. 

U.S. Geological Survey, 2012, Stream Network and Stream Segment Temperature Models 

Software: U,S, Geological Survey Fort Collins Science Center web site, accessed October 24,

2012, at http://www.fort.usgs.gov/products/software/SNTEMP/.

Theurer, F.D., Voos, K.A., and Miller, W.J., 1984, Instream water-temperature model—Instream 

flow information Paper 16: U.S. Fish and Wildlife Service, FWS/OBS-84/15, 335 p.

Voss, F.D., Curran, C.A., and Mastin, M.C., 2008, Modeling water temperature in the Yakima 

River, Washington, from Roza Diversion Dam to Prosser Dam, 2005-06: U.S. Geological 

Survey Scientific Investigations Report 2008–5070, 43 p. (Also available at 

http://pubs.er.usgs.gov/publication/sir20085070.)
Publishing support provided by the U.S. Geological Survey

Publishing Network, Tacoma Publishing Service Center

For more information concerning the research in this report, contact the

 Director, Washington Water Science Center

U.S. Geological Survey

934 Broadway, Suite 300

Tacoma, Washington 98402

http://wa.water.usgs.gov
Printed on recycled paper

Voss and Maule— Development of a Database-Driven System for Simulating Water Temperature in the Lower Yakima River Main Stem, 

Washington, for Various Climate Scenarios—Open-File Report 2013–1010