Transcript hazuscanada.ca

FLOOD RISK ASSESSMENT IN
FREDERICTON USING MS EXCEL
SPREADSHEET
January 21, 2015
CANHUG Meeting
H. McGrath, PhD Candidate, University of New Brunswick
E. Stefanakis, Ph.D., PEng Associate Professor (supervisor)
M. Nastev, Ph.D, Natural Resources Canada, Geological Survey of
Canada (co-supervisor)
1
Components of flood risk assessment
2
The appeal of this tool:
• Hazus
• Works well for users familiar with GIS and Spatial Analyst, have
access to flood depth grid, and inventory data
• Limitations:
• Need to install
• Need older version of ArcGIS
• License of Spatial Analyst
• Training and experts knowledge of GIS are required
• Data manipulation to get local data or flood hazards into Hazus
• Data manipulation to present desired outputs
• Social losses related to aggregate analyses only
• Cannot be fully adapted for the Canadian hazard and exposure settings
• Costly updating
3
Goals
• Develop loss estimation capabilities in a program familiar
to the non-expert public safety community and population
living in hazard prone areas
• e.g.: Microsoft suite of products
• No training required
• No (to minimal) user input
• Quick and easy estimates of exposure and damages
• User friendly tool
• Easy updating
4
Microsoft Excel: Flood Loss Estimation
• 3 worksheets
• Welcome page
• Single Occupancy
• Multiple Occupancy
• Drop down menus
• Key-in values
• Hyperlinks
• Auto-generated plots
/tables with results
5
(a) Single Occupancy
6
(a) Single Occupancy
7
8
Calculations
• Uses a number of underlying Hazus tables in calculations
Description
Hazus table
Occupancy classifications
BldgStrDmgFn
Depth Damage functions
BldgStrDmgFn, BldgContentsDmgFn
Replacement costs ($/sqft)
hzReplacementCost, hzRes1ReplCost
Square foot factors (e.g. Single family
residence: 1600sf)
hzSqftFactors
Depreciation factors
DepFunction
Exposure Contents percentages
hzExposureContentGBldgTypeB
Basement status
BldgStrDmgFn, BldgContentsDmgFn,
hzRes1ReplCost
Garage valuation
hzRes1GarageAdjustment
9
100
Select Depth Damage Curves
90
Estimated Damage (percent)
80
70
60
50
40
30
20
10
0
-4
-3
-2
-1
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Water Depth (ft)
One floor, no basement
Apartment Multi-Dwellings, 10-19
Two floors, w/ basement
Average Retail, Structure
Split level, w/ basement
10
hzRes1ReplCost
R11N
R11B
R12N
R12B
R13N
R13B
R1SN
R1SB
R11N
R11B
R12N
R12B
R13N
R13B
R1SN
R1SB
R11N
R11B
R12N
R12B
R13N
R13B
DmgCrv
105
106
107
108
109
110
111
112
105
106
107
108
109
110
111
112
105
106
107
108
109
110
SchemeId
Economy
Economy
Economy
Economy
Economy
Economy
Economy
Economy
Average
Average
Average
Average
Average
Average
Average
Average
Custom
Custom
Custom
Custom
Custom
Custom
HeightClass
1 story
1 story
2 story
2 story
3-story
3-story
Split level
Split level
1 story
1 story
2 story
2 story
3-story
3-story
Split level
Split level
1 story
1 story
2 story
2 story
3-story
3-story
AverageCost
$67.91
$67.91
$72.13
$72.13
$72.13
$72.13
$66.46
$66.46
$94.84
$94.84
$92.15
$92.15
$96.49
$96.49
$86.96
$86.96
$116.91
$116.91
$114.91
$114.91
$118.99
$118.99
Finished
BasementMod
$21.3
$21.3
$13.1
$13.1
$13.1
$13.1
$15.9
$15.9
$26.05
$26.05
$17.55
$17.55
$14.35
$14.35
$20.45
$20.45
$41.55
$41.55
$24.9
$24.9
$18.8
$18.8
UnFinished
BasementMod
$9.1
$9.1
$6.65
$6.65
$6.65
$6.65
$7.5
$7.5
$10.45
$10.45
$7.45
$7.45
$6.25
$6.25
$8.5
$8.5
$17.45
$17.45
$11.2
$11.2
$8.85
$8.85 11
hzRes1ReplCost
R11N
R11B
R12N
R12B
R13N
R13B
R1SN
R1SB
R11N
R11B
R12N
R12B
R13N
R13B
R1SN
R1SB
R11N
R11B
R12N
R12B
R13N
R13B
DmgCrv
105
106
107
108
109
110
111
112
105
106
107
108
109
110
111
112
105
106
107
108
109
110
SchemeId
Economy
Economy
Economy
Economy
Economy
Economy
Economy
Economy
Average
Average
Average
Average
Average
Average
Average
Average
Custom
Custom
Custom
Custom
Custom
Custom
HeightClass
1 story
1 story
2 story
2 story
3-story
3-story
Split level
Split level
1 story
1 story
2 story
2 story
3-story
3-story
Split level
Split level
1 story
1 story
2 story
2 story
3-story
3-story
AverageCost
67.91
$67.91
72.13
72.13
72.13
72.13
66.46
66.46
94.84
$94.84
92.15
92.15
96.49
96.49
86.96
86.96
116.91
$116.91
114.91
114.91
118.99
118.99
Finished
BasementMod
21.3
$21.3
13.1
13.1
13.1
13.1
15.9
15.9
26.05
$26.05
17.55
17.55
14.35
14.35
20.45
20.45
41.55
$41.55
24.9
24.9
18.8
18.8
UnFinished
BasementMod
9.1
$9.1
6.65
6.65
6.65
6.65
7.5
7.5
10.45
$10.45
7.45
7.45
6.25
6.25
8.5
8.5
17.45
$s17.45
11.2
11.2
8.85
8.85 12
Building Exposure
𝐸𝑥𝑝𝑘 = (𝑛𝐴𝑏𝑙𝑑_𝑘 [(𝑖𝑤𝑖 )𝐶𝑤𝑖 ]+[𝑛𝐴𝑏𝑙𝑑_𝑘 𝑏𝐶𝑤𝑏 ] + 𝑛𝐺𝑤𝑔 𝐶𝑤𝑔 ) 𝐴𝑔𝑒
𝑘
Occupancy Classification
𝐸𝑥𝑝𝑘
𝑛
Total estimated valuation for any given occupancy class (k)
Number of buildings
Total floor area for given (k)
Means construction class (building quality), 1=economy,
2=average, 3=custom, 4=luxury
Means construction class weighting factor
Basement status (yes [1] or no [2])
Means replacement cost
Cost per square foot for a basement
Garage combination (1 car, 2 car, etc.)
Weighting factor for garage type
Additional replacement cost for a given garage type
Age weighing factor
𝐴𝑏𝑙𝑑_𝑘
𝑖
𝑤𝑖
b
𝐶𝑤𝑖
Cwb
G
Wg
cwg
Age
*only single family residential (RES1) include means construction class & weighting factor (iwi)
13
Contents Exposure
• 𝐶𝑒𝑥𝑝𝑘 = 𝐸𝑥𝑝𝑘 ∗ 𝐶𝑘
𝑘
Occupancy Classification
𝐸𝑥𝑝𝑘
𝐶𝑘
Total estimated valuation for any given occupancy class (k)
Exposure Contents percentage
14
Estimates of damage
• Structure Damages = ([𝑛𝐴𝑏𝑙𝑑_𝑘 𝑆𝑑𝑐 [WD] 𝐶𝑖𝑗𝑙 ]+ 𝑛𝑤𝑔𝑘 𝐶𝑖𝑚 𝑆𝑑𝑐
[WD])* 𝐴𝑔𝑒
• n = number of buildings
• 𝐴𝑏𝑙𝑑_𝑘 = building square footage
• 𝑆𝑑𝑐 = structure damage, per depth damage curve
•
•
•
•
• [WD] = water depth
C = Means replacement cost per sqft
• i = construction class, j = number of stories , l = basement status
Cim = Means replacement cost for garage per construction class
wg,k = garage status
Age = building age factor
• Contents Damages = 𝐸𝑥𝑝𝑘 *[ExpC] * Cdc[WD]
• ExpC = exposure contents damages percentages
• Cdc = Contents damage, per depth damage curve
• WD = water depth
15
Single Family Dwelling, 1story, finished basement, 1960
Estimated Damages in Canadian Dollars
$250,000
$200,000
$150,000
$100,000
$50,000
$0
0
1
2
Economy
3
4
5
6
Water Depth in Feet
Average
Luxury
7
8
9
10
Custom
16
Damage Estimates for Single Family Residence (Structure and Contents)
Estimated Damage in Canadian Dollars
$170,000
$150,000
$130,000
$110,000
$90,000
$70,000
$50,000
$30,000
$10,000
0
1
2
3
4
5
6
Water Depth (in feet)
7
8
9
1 story, with No Basement and carport (2000)
1 story, with UnFinished Basement, 1-car garage (1960)
2 story, with Ffinished Basement and 1 car garage (1960)
3 story, with No Basement, 3 car garage (1985)
10
Split Level, with Finished Basement, 1 car garage (1985)
17
Test 1
Water level 1ft
Hazus
EvaRISK
Percent
Total
$83,239
$81,426
2.18%
Building
$62,464
$61,293
1.87%
Contents
$20,775
$20,133
3.09%
18
(b) Multiple Occupancy
• E.g.: Simulate a census block
19
(b) Multiple Occupancy
20
Test against Hazus: Multi-Occupancy
14 buildings total
12: 1 story
2: 2 story
*in thousands of dollars
Building Value
Total Losses
Hazus
EvaRISK
Percent
$3,523
$3,486
1.04%
$989
$986
0.34%
21
What’s next…
• Integration of the developed algorithm with online
mapping capabilities
• Bldg. by bldg. or aggregate data analysis
• User provided or archived historic water level or water levels
computed on demand (0D, 1D)
22
Multi-Occupancy
Draw polygon to select properties of interest
Draw on Map to
select area of
interest
Adjust slider to
change water
level or Select
Historic Water
Level
23
Multi-Occupancy
Draw polygon to select properties of interest
Draw on Map to
select area of
interest
Adjust slider to
change water
level or Select
Historic Water
Level
24
25
FLOOD RISK ASSESSMENT IN
FREDERICTON USING MS EXCEL
SPREADSHEET
January 21, 2015
CANHUG Meeting
Available online: http://www2.unb.ca/~hmcgrat1/index.html
H. McGrath, PhD Candidate, University of New Brunswick – [email protected]
E. Stefanakis, Ph.D., PEng Associate Professor (supervisor)
M. Nastev, Ph.D, Natural Resources Canada, Geological Survey of Canada (co-supervisor)
26