Development of a database-driven system for simulating water temperature in the lower Yakima River main stem, Washington, for various climate scenarios

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. 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.