Transcript Desktop Database and Climate Change

Desktop Database and
Climate Analysis
Steven Burian and Erfan Goharian
Hydroinformatics
Fall 2013
Module Overview
• Goal: Apply hydroinformatics skills to analyze
climate impacts on hydrologic response and
water system reliability in SLC
• Activities:
•
•
•
•
•
Climate Data: CMIP5
Databases: MySQL and TOAD
WEAP hydrologic modeling
WEAP water management modeling
Automation and Analysis using WEAP
Class 20 Learning Objectives
•
•
•
•
Access climate change projection data
Create a desktop database
Load hydroclimate data in MySQL using TOAD
Analyze climate data for trends and changes
with Excel, TOAD, and MySQL
Climate Change
Mean-Annual precipitation
change (%), CMIP3, 1970-1999
to 2040-2069
BCCA CMIP3 Daily Climate Analysis
example - Calendar-day, ensemblemean change in 20-year diurnal
temperature range
Why is Climate Change
Important for Water Managers?
Average water available
average use
Future use?
Future water available?
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
General Circulation Models
• Numerical models representing physical processes
in the atmosphere, ocean, cryosphere and land
surface
• Most advanced tools
currently available
• Three dimensional grid
• Their resolution is
quite coarse to evaluate the
impact on water resources
www.cccsn.ec.gc.ca
Coupled Model Intercomparison
Project phase 5 (CMIP5)
• Archive of fine spatial resolution of climate
projections over US
• Updated archive, May 2013:
• 234 projections of monthly data
• 134 projections of daily data
• Resolution: 1/8° latitude-longitude (~12km by 12 km)
• Time Coverage: 1950-2099
• Variables:
• precipitation
• maximum temperature
• minimum temperature
• http://gdo-dcp.ucllnl.org
Example
High-Resolution Climate Data
With the CI-WATER project, we are increasing the
horizontal resolution of climate model output from 100km to 4-km using a physically-based regional model
(WRF) with an explicit lake and urban irrigation scheme.
Dr. Court Strong
University of Utah
Accessing Climate Projection Data
http://gdo-dcp.ucllnl.org/
Data
Known facts in various types
of text, graphics, images,
sound and videos.
Database
Manages data: saving, sorting,
querying, processing, …
 Collection of data stored and retrieved by database software.
 Data organization
 Database management systems (DBMSs):
 Facilitating data analysis
 Improving reproducibility and capacity for data re-use.
 Definition, creation, querying, update, and administration of
database
Well-known DBMSs:
MySQL, PostgreSQL, SQLite, Microsoft SQL Server,
Oracle, SAP, dBASE, FoxPro, IBM DB2, LibreOffice
Base and FileMaker Pro.
What is MySQL
 SQL: a computer language for accessing data,
both to store and to retrieve it.
 Supports use of SQL to access data
 MySQL  A computer program to handle data - manage,
store and find them as efficiently as possible
 Used by programs that run on websites
 World’s most popular open source relational
database
MySQL Database Software
 A client/server system consists of a multi-threaded SQL
server and supports different backends, several different
client programs and libraries, administrative tools, and a
wide range of application programming interfaces (APIs).
 MySQL client: main interface command line
MySQL 
products
MySQL Server: server that responds to requests and
commands
 MySQL
Workbench:
developers, and DBAs
tool
for
database
architects,
MySQL language
 MySQL is a relational database management system
that use SQL language.
 Structured Query Language
 Used to communicate with a database
 Standard language for RDMSs
SQL
 Statements to update data on a database,
retrieve data from a database, and more
 Standard SQL commands such as "Select",
"Insert", "Update", "Delete", "Create", and
"Drop" can be used to accomplish much that
one needs to do with a database
MySQL Server Setup
http://dev.mysql.com/downloads/mysql/5.0.html#win32
 Full installation of MySQL
 Creating an
administrator password
for MySQL
 MySQL products
installed on the
computer
 Creating database in MySQL
 Create a user
burian is the Username and “burian" is the password
Toad for MySQL
 A freeware development tool for the management of a
MySQL database
 Rapidly
create
and
execute
queries,
automate
database object management, and develop SQL code
more efficiently
 Provides utilities to compare, extract, and search for
objects; manage projects; import/export data; and
administer the database
 Dramatically increases productivity and provides
access to an active user community
Toad helps to

Quickly create and execute queries

Automate database object
management

Develop SQL code more efficiently

Compare, extract, and search for
objects

Manage projects

Import/export data

Administer databases

Increase productivity

Access an active user community
Toad setup
http://www.quest.com/toad-for-mysql/
 The Toad for MySQL main window
Connect Toad to Database
Creating Tables in Toad
Analyzing Data in Toad
Query Data from MySQL
Workbench
Select avg(value_rcp85) from pc_pr
where 1970<=year and year<=1999;
Result: 2.16
This the average precipitation for
Parleys creek between 1970 to 1999
Next Session
• Create a hydrologic model and water supply system model in
WEAP
• Download and install WEAP, review Users Manual, and
complete the “one-hour” tutorial before Thursday’s class
• Homework:
Your task for Homework Assignment #9 is to do for the three
creeks (Parley’s, Big Cottonwood, and City Creek) and then
compare your analysis from MySQL to what you already have
from figure 3 and 4 of “CMIP5 analyzer- example.xls” file.