Transcript Slat Lake City water supply system and demand modeling

A relational model and Data
Management for Salt Lake City
water supply and demand System
Erfan Goharian
Department of Civil and Environmental Engineering
Philip Stoker
Department of City and Metropolitan Planning
October 16th 2012: CEE-6930
Introduction
 Water resources and hydrological modeling projects
typically include simulating systems made up of many
component parts.
Distribution
Access
Source
Demand
Introduction
 Utah’s water resources play an integral role in the life of every Utahn.
 Scarcity in Utah’s semi-arid climate is often overlooked. This reality
must be fully recognized.
 Good employment opportunities, a pleasant climate, beautiful
scenery, and a broad range of other opportunities will continue to
drive growth and prosperity in Utah.
 The population of the Wasatch Front will grow to nearly four million
people by 2040, about 1.5 million more than in 2010. This translates
into an average of about 50,000 new residents, 15,000 new housing
units, and 25,000 new jobs each year.
Salt Lake City Water System
Parleys Water coming to SLC
Maps of the Case area
Research Goals
 Developing an integrated model which can be used in order to describe
the state of water supply system (Parleys Creek).
 Develop and validate water demand models that examine the effects of
the climate, demographics, and the built environment on water use in
Salt Lake City
 Determining the current role and maximum potential of Parleys Creek in
order to supplying SLC demand water.
 This study can help local leaders meet the many challenges they face as
they try to satisfy the needs of the growing population within their
communities.
Study Framework (Supply)
 Systems dynamic modeling approach is used to
simulate the system behavior through time at an
integrated fashion.
Drawing
the
reference
diagrams
Creating a
flow diagram
for the model
Drawing the
cause and
effects
diagram
Defining the
relationship
s between
the variables
Defining the
boundaries of
study
Model
Calibration
Running
model
Study Framework (Demand)
 The water use is a function of climate, demographic, and
built environmental variables.
 Using SPSS, The variables mentioned above will be tested
in the new database and their relationship to water use.
 Calibration
Demographic
s
Built
Environment
Climate
Water Use
Data Life Cycle
Planning
Data Collection
Assure
Metadata
Preserving Data
Data Model
Relational databases and data management software
Planning
 Characteristics of needed data:
 Collected data: Streamflow, reservoir storage, treated water, water drawn from reservoirs,
temperature, and precipitation. Water use, built environment variables, demographic data.
 Measuring methods: Gage height methods, sensors and estimating infrastructure characteristics by
designed methods. Census, Public Utilities, Tax Assessors data.
 Appropriate data formats/standards: All data are tabulated in Excel, Access, and GIS files in United
States customary units format.
 Using data: The data used in format of the used software and mostly in *.xls, *.sav, .dbf and
*.dat format.
 Sharing data: Data files shared and send with e-mails and compact discs by utility department.
 Results and data after this research will be shared with utility department, iUTAH modeling
federation, and shared among CI-Water project universities.
Data Life Cycle
Planning
Data Collection
Assure
Metadata
Preserving Data
Data Model
Relational databases and data management software
Data Collection
 5 streamflow monitoring site
 2 reservoir storage monitoring
site
 2 weather stations:

Air Temperature

Relative Humidity

Solar radiation

Precipitation

Snow depth
Demand Data Collection
 Data are gathered from four main sources to build a database :
 These data identified by latitude
and longitude and will be addressed
Match and join together in GIS.
Salt Lake City
Public Utilities
database
Salt Lake County
Assessors
Database
•monthly water use by
household
•household locations
•type of building
•Characteristics of the
household
•Characteristics of the
commercial buildings
Infogroup USA
National
Household
Database
Prism Climate
Group and
DAYMET Data
•demographic
information
•surface temperature
•precipitation
Data Life Cycle
Planning
Data Collection
Assure
Metadata
Preserving Data
Data Model
Relational databases and data management software
Assure
 Strategies for preventing errors from entering datasets
 Pre-specification of formats, units, etc.
 Activities to ensure quality during collection
 Automated range checks for data
 Activities to “clean” collected data
 Graphical and statistical summaries; remove missing values
Assure
• Out of range values
• Sensor drift
• Missed data observation
Data Life Cycle
Planning
Data Collection
Assure
Metadata
Preserving Data
Data Model
Relational databases and data management software
Metadata
Example
1
Suicide Rock
The adjusted CFS is calculated using the product of the
observed
Salt Lake City Utility department
Adjusted flow
Cfs
2008-01-01
40°42'34.67“
Channel
Metadata
Structural
Heterogeneity
Database
Example
Water Use
Ccf
Hundred Cubic Feet
Unit of Analysis
Parcel, Household, Condo i.e.
not equal
Per unit use for each parcel by
land use.
Entities
Database
Example
Water Use
Ccf
Hundred Cubic Feet
Unit of Analysis
Parcel, Household, Condo i.e.
not equal
Per unit use for each parcel by
land use.
Time frame
2011
We only use data for 2011.
Brief Metadata Information
Observation
Value
Type
Variable
Title
Description
Location
Time Period
covered
Precipitation
Rainfall over the Parleys
watershed
Little dell
Temperature
Quantity of water stored
in Mt. Dell Reservoir in
units of AC-FT each day
Reservoir volume
Stream Inflow
.
.
.
Monitorin
g site
Unit
Data Source
Method
Subje
ct
Scale
Unit
Conversion
convention
Station
Collector
Time
Pure Data file
1980 - 2011
Precipitati Time
on
series
daily
in
in
Mountain
Dell
Reservoir
CBRFC
10/1/201
2
Rain gauge
little dell
1980 - 2011
Temperat Time
ure
series
daily
F
F
Mountain
Dell
Reservoir
CBRFC
10/1/201
2
Sensor
Quantity of water stored
in Mt. Dell Reservoir in
units of AC-FT each day
Little dell
Reservoir
1980 - 2011 STORAGE
Time
series
daily
Ac-FT
cfs
Little dell
Reservoir
SLC 10/15/20
Utility
12
Sensor
Parleys Creek
Little dell res
upstream
cfs
Dell 16'
contracted
rectangular
weir
SLC 10/15/20
Utility
12
Gage height
Variable
Adjusted Time
1980 - 2011
flow
series
gage
daily
heigth
…
Data Life Cycle
Planning
Data Collection
Assure
Metadata
Preserving Data
Data Model
Relational databases and data management software
Preserving Data
 All data used in models and also the source files of models
will be preserved in this research.
 All files preserved in different places including: Desktops,
Drop Box and SQL server.
 In order to back up the data all final models, data and
result are saving in University of Utah network and in
specific external disks.
 Only specific (persons) researchers, students, faculties and
etc. and also for specific period time have the right to
share or use from the database.
Data Life Cycle
Planning
Data Collection
Assure
Metadata
Preserving Data
Data Model
Relational databases and data management software
Data Model
Reservoir
Storage
Streamflow
Snow depth
Treated water
Rainfall
Temperature
Relational databases and data
management software
Data Model for
Assessors Data
Data Life Cycle
Planning
Data Collection
Assure
Metadata
Preserving Data
Data Model
Relational databases and data management software
Relational databases and data
management software
 Advantages of using the ODM Data Loaders
 Designed specifically for ODM
 Validate data against the business logic of ODM
 Protect the consistency of data and avoid errors
Load metadata using the ODM Data
Loader
 Loading metadata to any table in ODM – e.g., Sites,
Variables, Methods, etc.
Methods
Sources
Sites
Load metadata using the ODM Data
Loader
Methods
Variables
Load observations using the ODM
Streaming Data Loader
 Focused on loading data values
 Crosstab data
 Single site, multiple variables
 Date in one column, each variable in one column
 Datalogger files from field sensors
Visualize Data Using ODM Tools
1.
Connect to the database using a client software
application
2. Visualize and manage data using the software tools
Data Management Challenges
 Organization
 Find and identify your data at any step in an analysis
workflow
 Reproducible Analyses
 Track data through the entire analysis
 Match results with the exact inputs that generated them
 Lost values during joins and merges
 Versioning
 Quality control
 Derived datasets
Conclusion And next steps
We are not computer scientists
But…
 The way we store and manage data can either enhance or inhibit our analyses.
 Management, visualization and analysis of large datasets can be difficult and require
specialized software.
 You will need to share data and be able to reproduce results.
 Data will be fed to the SQL server management in order to querying data.
 We anticipate generating accurate and valid models of supply and demand for Salt
Lake City
Conclusion And next steps
Any Questions?