9. Investigating Climate Impacts on Streamflow and Water Management Using Desktop Database and WEAP

Learning Objectives

1. Access climate change projection data
2. Create a desktop database and load hydroclimate data in MySQL using TOAD
3. Analyze climate data for trends and changes with Excel, TOAD, and MySQL
4. Use the WEAP system to set up the schematic, enter data, obtain results, and define + analyze scenarios
5. Automate WEAP analysis by providing access to climate projections

Computer and Data Requirements

1. Climate model projection results from Coupled Model Intercomparison Project phase 5 (CMIP5) website (http://gdo-dcp.ucllnl.org Links to an external site.)
2. Excel file containing the calculations for analyzing the monthly climate results
3. MySQL: open-source relational database management system (RDBMS) (http://www.mysql.com/)
4. TOAD: Productivity Software for Database Developers, DBAs, and Analysts (http://www.quest.com/toad/)
5. WEAP: download and install following the directions contained in Class 20 Pre-class instructions

The Problem

Recent investigations suggest increases in global mean temperature and resulting changes in precipitation amount and distribution in many parts of the world. It is expected that global average surface air warming continues during the 21st century, and the frequency of hot extremes, heat waves and heavy precipitation events will continuously increase. The Salt Lake City Public Utilities (SLCPU) wishes to know how future projected climate scenarios may impact their water supply system. Recent research shows the Wasatch Front, north central part of Utah, may face wetter winters and dryer summers with higher temperatures in the future. This may cause changes in snowmelt timing and streamflow rates possibly leading to deficit volumes and expanded periods when existing sources cannot meet demands.

Your Assignment

Specifically, SLCPU requests you to (i) explain future trends and changes in temperature and precipitation in their water supply watersheds, specifically Parley’s Creek, City Creek and Big Cottonwood Creek, (ii) create a database and a modeling framework to store the climate results and data, access to the data, and analyze them, and (iii) present results of the analysis to help them understand possible future climate projection changes to local streamflow and water supply.

To meet this request, you shall download and analyze the projected climate change (precipitation and temperature) for scenario bcc-csm1-1 at three future periods (2011-40, 2041-2070, and 2071-2100) relative to a recent historical period (1971-2000). The steps to complete this are described in “Class 20 Post-Class Handout – Climate Projection Data Analysis”. Use the Excel file provided (“CMIP5 analyzer- example.xls”) to help complete the analyses to describe the projected time series of temperature and precipitation, changes in period-mean annual conditions between future and past, and the relationship between changes in mean annual precipitation and temperature.

After analyzing the climate data, create a MySQL database to store all of the downloaded data from CMIP5 (for three watersheds and two variables – in other words you need six tables). Load the data into the database. To provide for greater flexibility and efficiency in future analyses you shall write a script to operate on the MySQL database to complete the same analyses as the provided spreadsheet (the steps used to create Figures 3 and 4). You will not be able to create the figures, but only write a script to check the numbers. Check your script to show it works and is ready for future use. Note: the precipitation data you have imported to MySQL are in (mm/day), but the analysis in the Excel file for precipitation is done for (in/month). You can see how the conversion is done in the Calcs sheet in CMIP5 Analyzer Excel file. You first add a new table or tables (like column N) and write a new script to convert them to (in/month) in MySQL.

The next step in this project is to create a WEAP model for the three watersheds following the information provided in Class 21. Execute a simulation for the historical period (Class 21) and provide automation to execute the simulation for the future projected periods (Class 22). Analyze the results to provide a summary (using plots from WEAP and written explanations in your report) of the impacts of climate change on streamflow (Parley’s Creek, City Creek, Big Cottonwood Creek – see Class 21), volume in reservoirs (Little Dell and Mountain Dell), and reliability of the SLC water supply system at three locations (Murray, Midvale, Salt Lake City, and Agriculture – see Class 21). 

Deliverable

Submit a one-page briefing sheet that (1) introduces the analyses made for each figure in the CMIP5 Analyzer Excel files and explains them. Explain and conclude from the analysis the possible future climate change impacts on the three creeks, and (2) explains the MySQL database creation and script to perform calculations contained in the Excel spreadsheet

Also submit a second one-page briefing that describes the WEAP modeling analysis results. The conclusion should clearly identify the potential impacts of climate change on SLC water supply system based on a synthesis of all the analyses.

You should also include appendices to provide:

• Figures 3 and 4 from the Excel spreadsheet for each Creek (six figures)
• Your SQL script that automates the calculation of the numbers shown in Figures 3 and 4
• Figures and descriptive text summarizing the WEAP model results

Submit all parts (two one-page briefs and appendices) as one organized file.

A pdf version of this assignment is available here. A Word version of the Rubric is here Download here