DEVELOPMENT OF A DATA WAREHOUSE FOR RIVERINE AND COASTAL FLOOD RISK MANAGEMENT

In New Brunswick flooding occurs typically during the spring freshet, though, in recent years, midwinter thaws have led to flooding in January or February. Municipalities are therefore facing a pressing need to perform risk assessments in order to identify communities at risk of flooding. In addition to the identification of communities at risk, quantitative measures of potential structural damage and societal losses are necessary for these identified communities. Furthermore, tools which allow for analysis and processing of possible mitigation plans are needed. Natural Resources Canada is in the process of adapting Hazus-MH to respond to the need for risk management. This requires extensive data from a variety of municipal, provincial, and national agencies in order to provide valid estimates. The aim is to establish a data warehouse to store relevant flood prediction data which may be accessed thru Hazus. Additionally, this data warehouse will contain tools for On-Line Analytical Processing (OLAP) and knowledge discovery to quantitatively determine areas at risk and discover unexpected dependencies between datasets. The third application of the data warehouse is to provide data for online visualization capabilities: web-based thematic maps of Hazus results, historical flood visualizations, and mitigation tools; thus making flood hazard information and tools more accessible to emergency responders, planners, and residents. This paper represents the first step of the process: locating and collecting the appropriate datasets. * Corresponding author.


INTRODUCTION 1.1 Flood Risk
In New Brunswick (NB) the chance of flooding due to snow melt, ice jams, and heavy rainstorms (Environment Canada, 2013) is a concern as many residential areas and extensive infrastructure have been developed close to rivers.With observed changes in the earth's climate, local weather patterns are changing (IPCC, 2013).Effects of changing weather patterns in New Brunswick include, increasing frequency of winter thaws, intense precipitation, and rising sea level (approximately 30 cm over the past 100 years, and by 2100 a further rise of 50-60 cm is forecasted) (GNB, 2007).Given this variability in weather patterns it is becoming increasingly important for municipal and other levels of government to perform accurate risk-assessments in flood prone and coastal communities.There are three types of vulnerabilities to be considered: structural, functional, and inherent vulnerabilities (Poulin et al., 2012).To assess and mitigate these potential impacts, it's necessary to have both an understanding of the potential event and also the characteristics of the built environment which will be impacted (Sawada et al., 2013).Identifying these regions of vulnerability is important for disaster planning, mitigation and recovery, and preparedness response (Nastev and Todorov, 2013).
To minimize social and economic impact, it is important for municipalities have in place a flood risk mitigation strategy.Flood mitigation strategies include: (a) avoidance (not building in a flood prone area); (b) water regulation (e.g.Levees, dams, or seawalls); and (c) community development guidelines (i.e.structures must be of certain construction material or certain height above flood elevation levels).Because flood generating phenomena generally have no defined upper bounds, the design criteria of a structure are defined to minimize risk (Watt et al., 1989 Mitigation spending has a significant positive return on investment (Public Safety Canada 2014a).In the 1960s' $63.2 million was invested in the Manitoba Red River Floodway with an estimated $8 billion in potential damages and recovery cost savings.Beyond Canadian savings, across the globe benefit cost ratios of flood prevention measures have been reported in Australia, USA, and the United Kingdom to be 3:1, 4:1, and 5:1 respectively.).Pre-emergency planning is therefore important as a savings measure and when you see the water rising -it is too late for planning (Willis, 2010).
The Government of Canada has developed a national disaster mitigation strategy as a collaborative effort to develop sustainable disaster resilient communities across Canada (Schneider et al., 2014).The method being adopted by Natural Resources Canada (NRCan) to respond to this issue is to adapt the Federal Emergency Management Agency (FEMA) Hazus Multihazard (Hazus) software (Nastev and Todorov, 2013).Hazus is a standardized methodology for estimating potential losses from natural disasters (FEMA, 2009).
To identify flood risk areas, local inventory data and flood hazard information must be input to Hazus (FEMA, 2009).The process to locate inventory data across government organizations and manipulate the files into a compatible format for use in Hazus is tedious and time consuming.Additionally, flood hazard information may only be available for a few return periods (e.g.; 100 year flood and 20 year flood).The aim is therefore to develop a multi-use data warehouse to store this data .Furthermore CADYRi software (recently developed by INRE-ETE) will be tested and validated as a tool which can interpolate flood levels between common return periods, thus allowing the user the ability to model a greater range of flood hazard.
In 1998 Golfarelli suggested that decision support systems would become one of the leading users of data warehouses (DW), as the need for quickly obtaining summary information from the system is required in planning and decision making.A DW is thus being proposed to facilitate the storage and analysis of relevant flood prediction data.The DW will support multiple operations: (a) Hazus users will be able to access this data for loss estimation and export Hazus results; (b) discover unexpected dependencies and historical correlations of flood prone areas thru On-Line Analytical Processing (OLAP) and knowledge discovery; (c) visualization of flood risk areas through web maps.

Flood History in New Brunswick
A study carried out by the Saint John River Basin Board in 1973 showed that between 1887 and 1971 damages resulting from floods cost in excess of one million dollars (Franklin and Cardy, 1976).Between 1971 and 1976 when Franklin and Cardy published their report on flood management in NB, another $17 million in flood damages had occurred.This increase is partly due to better flood records and more detailed assessments, but also a result of the extensive development on flood plain land.Specifically, in the Fredericton-Maugerville area, development along the Saint John River increased 30% between the 1960s and 1970s.As people continued to settle along the Saint John River flood plain new infrastructure has been built.Records of flood events have continued, with Public Safety Canada (2014b) recording 35 flood events in NB since 1900 and costing in excess of $133 million.Only 40% of events have recorded loss values, thus true expenditures are expected to be much larger.Following the 2012 flood in Perth-Andover $5 million dollars were paid by the provincial government in response to disaster financial assistance claims, with a total damage estimate of $25 million (GNB, 2014).Floods occur across the province along river flood plains as shown in Figure 1.In addition to damages due to riverine flooding, coastal flooding resulting from storm surge and rising sea level is also a concern in the province.
The structure of this paper is as follows; in section 2 the criteria for and site selection details are outlined.Hazus loss estimation software is briefly described in section 3 and section 4 contains details of the data collection procees.In section 5 results from the data collection as well as the next steps 1. St.John 2. Quispamsis 3. Jemseg 4. Fredericton 5. Perth-Andover 6. Edmundston 7. Campbelton

SITE SELECTION
A comprehensive database of flood events dating back to the 1600s is available on the Government of New Brunswick (GNB) web site (GNB, 2014).The flood database records indicate that many communities situated along the Saint John River floodplain have experienced flooding (Table 1).Those in Table 1 represent the communites situated along the Saint John River floodplain which were considered for this study.The criteria for selection included a location in a floodplain, experience of flooding in recent years, and contain a mix of private and municipal infrastructure.The City of Fredericton is the capital of New Brunswick, and as a result, there are a significant number of government offices and service locations in the city.The community of Fredericton is the third largest in the province with a population of 94,000 (Census, 2011) and approximately 22,000 households (Lantz, 2011).There is therefore a mix of public (municipal, governmental) and private infrastructure at risk of flooding.In addition, the downtown core of Fredericton contains a number of historically significant buildings: City Hall, the Legislative Assembly, Wilmot United Church, York County Gaol, the Justice Building, the Soldiers' Barracks and Officers' Quarters, Christ Church Cathedral and Old Government House (1828) (Canadian Encyclopedia, 2013).Beyond these government buildings, some residences (especially in the eastern section of town) date back to the late 1700s.

Candidate
The Saint John River flows through the city, dividing the 'North' and 'South' sections.Flooding occurs in Fredericton when the river level exceeds flood stage, which is 6.5m above sea level.A number of flood events have been reported in Fredericton, dating back to the 1750s (GNB, 2014).The highest flood levels ever recorded were in 1973, when the water level was 2.04 m above flood stage.In 2008, the second highest water levels were recorded, 1.86 m above flood stage.The water level in 2008 was so high that many roads in the downtown core were inundated with water, and many buildings were flooded, Figure 3.

HAZUS MULTIHAZARD (HAZUS)
Originally developed in the 1990s as a toolset aimed at getting a regional understanding of the impact of hazards, Hazus has evolved to offer community level results (FEMA, 2009).Direct economic losses with respect to buildings calculated by Hazus include structural repair and replacement and costs related to contents damage.Other calculated direct economic losses include time dependent income losses such as relocation expenses, wage loss, rental income loss and capital related income loss.While other loss estimation software been limited to repair and replacement costs, Hazus extends its output to include consequential losses (FEMA, 2009).These consequential losses give an indication of the impact of the flood on the community.This combination gives a greater approximation to the overall impact of the flood.For a detailed description of the Hazus methodology and software the reader is referred to http://www.fema.gov/hazus,and to http://hazus canada.ca/for the Canadian application of Hazus.

Canadian Adaption
In Beyond the program installation limitations, the default datasets used by Hazus are required to be found locally on a machine.This restriction to use of a local dataset can lead to inconsistencies of analysis between users within a single organization and across multiple agencies.
There are many assumptions made in the processing algorthims and data sets in Hazus, which may or may not be correct.Also, there are over 800 damage curves developed by the US Army Corps of Engineers based on US surveys.With the adoption of Hazus, a series of validation tests are being run to determine the accuracy of the loss estimations.These validation tests will be run using a historic water level and the loss estimates generated by Hazus will be compared to recorded expenditures resulting from the flood event.Adjustments of the damage curves or other default assumptions may be made in order to have the Hazus results better approximate real expenditures.
Additionally, the proprietary nature of Hazus precludes usage by the open-source community (Mofftatt and Laefer, 2010).While the program is peer-reviewed and freely available, the logic that supports the software code is deeply embedded, and Moffatt and Laefer (2010) note several independent groups have identified extensibility as a major effort.

DATASETS
To complete an analysis in Hazus flood model, a number of inventory datasets must be populated.In addition to the inventory data, Hazus also requires a user to input a flood depth grid for the analysis.
The data warehouse will contain this data as well as additional data which are used by Hazus.These data include historical water levels, temperature and precipitation,and geological data.
A preliminary search of data to import into the DW shows the data are fractured across a number of local, municipal, and provincial government organizations (  On the City of Fredericton website, in their Data Catalog a shapefile which contains the digitized boundaries of all buildings in Fredericton exists.In this building shapefile all of the structures have been digitized from aerial photography, this includes houses, commercial buildings, as well as garages, and sheds.Hazus does not have an occupancy classification for garages and sheds and therefore does not calculate the losses to these structures.These were therefore deleted from the building file.While there is no distinct classification for garages, the residential occupancy classification (and therefore the loss valuation algorithm) does indirectly calculate loss to a garage.The building shapefile contains no additional information about the buildings, beyond its geographical location and shape.From this polygon feature, the area of each building was calculated to determine the buildings squrare footage and a point feature was calculated based on the polygon centroid.
Though a data sharing agreement with Service New Brunswick (SNB) an assessment file for York County (Fredericton is within York county) was accessed.This file was downloaded as a dBase file.The assessment file contains fields for property codes and descriptions which are used to determine the Hazus occupancy classification.The field 'Year First' has been chosesn to represent the building age.Fields for the building address and Parcel Identifier (PID) are also in this file.To geographically display the assessment file, it was joined with the digital property map shapefile.
The digital property polygon shapefile was downloaded from the SNB GeoNB Data Catalog (http://www.snb.ca/geonb1/e/DC/catalogue-E.asp).This shapefile contains all the individual owned parcels in York County.A Join was created in ArcMap to combine the digitial property feature geometry with the PID from the assessment database.
Another join was needed to combine the City of Fredericton building data to the assessment and digital property data.Since the city of Fredericton building data does not contain any common attributes, a spatial join was performed in ArcMap.
The data described does not contain the first floor height of the building nor its foundation type.To populate these attributes a combination of Google Maps Street View and field inspection were completed to populate these fields.This was not done on the whole of the city (to date) however, buildings within a city block of the 2008 flood boundary were populated with these values.The true first floor height and foundation type (for previously flooded buildings) does exist in a provincial database, but privacy concerns regarding this data has resulted in this information not being released.
Once the geometry and attributes were compiled, this file was merged with a building template and attributes copied to ensure the field types, length, and values were consistent with the Hazus data structure.Figure 4 represents the processes described to find, join, and merge building data into one file which is in a Hazus acceptable format.Fredericton has only one hospital to input into the emergency center facilities.The coordinates for Dr. Everett Chalmers Regional Hospital were determined using the'What's Here?' function of Google Maps.Additional attributes, height of first floor, type, age, etc. were populated based on a site visit.
Public school information was available from the New Brunswick Department of Education and Early Childhood Development website.This public school information was downloaded and saved as a tab delimited text file.The public school file contains information about the school including the address, however, geographic coordinates were not included.Again the 'What's Here?' function within Google Maps was used to determine the latitude and longitude for each of the schools.This database was then imported into ArcMap feature class.As with previous files, this was merged with a schools template file and the field calculator function within ArcMap was used to populate the Hazus appropriate fields.
Essential facility data for police and fire stations were downloaded from the Data Catalogue on the City of Fredericton website.The police station file was a KMZ file.The KMZ file was imported to a feature class in ArcMap, and merged wth the police station template file.To populate the attributes the ArcMap Field Calculator was used.Additional information regarding the building type and height of first floor were entered based on Google Maps Street View images.Fire stations from the city of Fredericton website were downloaded in shapefile format.To input to Hazus, the data needed to be converted to ArcMap feature class and converted to geographic coordinates from the NB Double Stereographic projection.The fire station file was then merged a template and attributes calculated to populate the appropriate fields, types and lengths required for Hazus.
The care facilities for Hazu include small hospitals and medical clinics.As with the hospital, a database of these facilities was not available.A combination of Google search and Yellow pages was completed to populate this feature class.

Lifeline Utility Systems
Lifeline utility system Hazus include potable water, waste water, oil, natural gas, electric power and communication systems (FEMA, 2009).
The water and wastewater information is not posted on the City of Fredericton website, but is available upon request.The data was provided in feature class format.The files obtained from the City of Fredericton contain: lift stations, sanitary lines, water lines, wells, reservoirs.The point feature classes (lift stations), were merged with the Hazus wastewater facility template.The stations feature class included boosters, wells, and reservoirs and was merged with the water facilities template.The sanitary lines and water lines were merged with the Hazus templates potable lines and water lines respectively.
From the Departnment of Natural Resources data sharing agreement with the University of New Brunswick, a pipeline file was downloaded.This file was converted to geographic coordiantes and NAD83 datum to match all other Hazus datasets.
Communication facilities assembled, thus far, include the location of cell towers from Canadian communication providers (Bell Aliant and Rogers).This file was acquired from the City of Fredericton in shapefile format.As with other files this was merged with a Hazus template and the field calculator was used to transfer attrbutes.

Transportation Systems
The city of Fredericton website data catalog contains the street centreline shapefile.The attribute details include civic address range, lanes, class, speed limit, and truck route and weight restrictions.This file is converted and imported to Hazus, however loss estimations are not computed on roadways.
There are three bridges in Fredericton which cross the Saint John River.Two of these bridges are for vehicle transportation, Westmorland Street and Princess Margaret Bridges, while the third is a former railway bridge which is now used as a pedestrian walkway.Attributes for these bridges have been requested from the NB Department of Transportation and Infrastructure.Other bridges across Nashwaak River and Nashwaksis Stream were incorporated into the bridge feature class.
Bus system information is available as a series of Transit files (GTFS).This includes information about the location of bus stops, but no indicators regarding the type of building type, or location of fuel faculties.These bus stop locations were merged with the bus station template.
Other transporation systems used by Hazus include railways, ferrys, airports, and ports.There are no active rail lines in Fredericton nor are their ports.Fredericton does have an international airport, however it is technical in Lincoln,NB and at this time, is not included in the analysis extent.

Agriculture Inventory
From the SNB assessment parcels of land are identified s At this time, the details of crops harvested has not been completed, and therefore this dataset is not yet complete.

Vehicle Inventory
Hazus classifies vehicles into three categories: car, light truck and heavy truck.Inventory of vehicles is used in loss estimation and it considers the number of vehicles, time of day of the flood (as vehicles are allocated in different locations between day and nightnumber of vehicles, value of vehicles, and depth of flood.
Vehicle inventory has not yet been secured.We are working with SNB regarding access to this data.

Flood depth grid
The flood hazard required by Hazus is an ESRI grid file.The grid file contains the flooding extent and the water depth per cell.The flood hazard file may be supplied by local government, calculated using HEC-RAS, or CADYRi, or through the Flood Information Tool (FIT) in Hazus.In this research, a series of flood depth grids will be acquired from the New Brunswick Department of Environment.Three of these grids will be imported into the newly developed CADYRi tool.
CADYRi was developed as a methodology of mapping flood risk in urban areas at INRE-ETE as part of the partnership agreement with INRS-ETE.CADYRi is able to interpolate between depth grids and is able to generate flood depth grids at user defined intervals.The results of the CADYRi developed flood depth grids will be compared for accuracy against the provincial models.The provincial NBDELG depth grid will be regarded as the 'truth' and the geographic extent and flood depth values across the study region as calculated by CADYRi will be compared to this file.This will give a better understanding of the quality and accuracy of the CADYRi output.This method of comparison will be raster overlay.Additionally, following the work of Colby and Dobson (2010), a diagnostic method of comparing the horizontal extent of the water surface across a number of randomly stratified transect lines, perpendicular to the Saint John river, will be analyzed to determine if the differences are statistically significant.
A depth grid using the FIT will also be generated if the required inputs are available, to test the validity of these results.FIT is an ArcGIS extension which uses user input, digital elevation model, floodplain boundary, and river cross sections to generate a grid of extent, depth and elevation of flooding across the floodplain boundary.

Other data
Other data which will be used to validate the Hazus loss estimation include historic flood loss information.This historic flood loss information includes data from the Disaster Financial Asssistance (DFA) dbase, provided by Public Safety New Brunswick.The DFA contains records of all loss claims.Additional data which will be used outside of Hazus include climatological data and geologic data which will come from the Governemnt of Canada, Environment Canada and Government of NB respectively.

CONCLUSION
Canada has developed a national disaster mitigation strategy and is adapting the USA Hazus tool to support this strategy.To run Hazus local inventory data are required.As described in section 4, data required for loss estimation comes from a number of different municipal, provincial agencies.These agencies include the City of Fredericton, Service New Brunwsick, NB Dept of Education and Early Childhood Development, NB Department of Environment, and Public Safety NB.As well open souce data from Google Maps and Yellow pages have been used when no govenment dataset wasn located .A number of steps are required to locate, translate, and combine the datasets into loss estimation software.The next step is to test the data format and structure.At present, the Hazus flood model has not been released.A preliminary test of importing the data into the earthquake model is planned.The attributes between the earthquake and flood differ, but it's expected with little modification this test will give a preliminary result for compatibility.

Figure 1
Figure 1 Distribution of areas prone to major flood damage in New Brunswick, adapted from Franklin and Cardy, 1976.Rivers, Streams, Lakes and county boundaries source: Service New Brunswick.
Figure 2 illustrates the city layout, indicating the exposure, density, Saint John River, and 2008 flood boundary.

Figure 2 ,
Figure 2, The City of Fredericton, with St. John River splitting the city and digitized flood boundary from 2008 flood
Users of Hazus are able to: (a) identify vulnerable areas; (b) estimate potential losses; (c) assess how ready they are to respond to a natural disaster; (d) decide how to allocate resources; and (e) prioritize mitigation measures.Hazus includes models for estimating potential losses from earthquakes, hurricanes, and floods as basic models, and the ability to model storm surge along the coast as a combination of the hurricane and flood models.This is accomplished through the use of GIS technology to estimate the physical, economic, and social impacts of the disaster (FEMA, 2009) .

Figure 4
Figure 4 Process of assembling building data from source datasets into Hazus format

Figure 5
Figure 5 Buildings, essential facilities, transportation network, and 2008 flood extent in Fredericton.Source: City of Fredericton and Service New Brunswick, GeoNB

City # floods in past 10 years Damage amount (thousand $)
including: (a) capital of the province; (b) open data policy; the (c) aging Mactaquac Dam; and (d) the recorded flood history.
August of 2011, the Hazus adaption and co-development agreement was signed by Natural Resources Canada (NRCAN), Defence Research and Development Canada (DRDC), and FEMA.At present (March 2014) the Canadian earthquake model is fully operational, the hurricane and storm surge models are still to come while the flood model continues to be revised (Nastev and Todorov, 2013).The latest information indicates a release date of summer 2014 for the flood model.While Hazus has been selected above other loss estimation programs, there are limitations inherent in the program.As the program is an extension of ArcGIS, a license of ArcGIS and Spatial Analyst are required (Spatial Analyst is required for the flood module only).Secondly, the current version of Hazus (Version 2.1, as of May 2014) is compatible with version 10.0 of ArcGIS -which is not the current version (current version 10.2.1, ESRI, 2014).Furthermore, Hazus will only run on XP or Windows 7 operating systems.
comes with Hazus) includes the 2011 Canadian census data.The census data are used to determine data, such as the population at risk and shelter needs, and aggregated buildings, including seven occupancies (residential, industrial, commercial, educational, governmental, agriculture and religious)(Nastev and Todorov, 2013).It is recommended that users import their own local level building inventory to Hazus, if available.
These datasets include (a) building stock; (b) essential facilities; (c) lifeline utilities; (d) transportation systems; (e) agriculture inventory; and (f) vehicle inventory.The Canadian default inventory default inventory (which Table2).Fredericton open data policy facilitated locating many of the appropriate datasets.Other sources for this data include GeoNB, which is a collaborative effort of the Government of New Brunswick, and the Univeristy of New Brunswick.Beyond these resources, Public Safety Canada (PSC) was very helpful with arranging meetings and facilitating the availability of datasets which had greater privacy restrictions.

Table 2
Datasets and the department or municipality which hosts the inventory data to be used in flood loss estimation fields are primarily used in the calculation of loss estimations, these are the building foundation type, height of the first floor, and age of the structure.When converting the losses to monetary values, attributes such as building occupancy classification, square footage, valuation, construction class, number stories, and age.