This blog post was authored by Ginger Ni.
Like any natural disaster, hurricanes can leave behind extensive damage to life and property. The question asked by NGOs, government agencies, and insurance companies is, “How can we predict the locations where a storm will inflict the most damage?” Modern spatial analysis enables us to predict the impact of disasters in high-risk areas and enable these groups to effectively allocate their resources.
Vertica enables you to analyze location data efficiently and accurately in your database. Here we will show you how to gain insight into real-world challenges using your spatial data with Vertica. In this example, we will perform a point-in-polygon analysis to identify properties that fall within the impact zone of a hurricane. This analysis will identify the areas most affected by a hurricane and visualize them on a heat map.
Vertica is compatible with the shapefile data format, a popular geospatial vector data format for geographic information system (GIS) software. You can easily load your location data to Vertica in shapefile format and use the data to perform a spatial analysis. Vertica also enables you to export your results to a shapefile which you can load to other data analysis tools for visualization or further analysis. Vertica integrates easily into your spatial analysis work flow.
In this example, we will identify the areas most at risk for damage from Hurricane Bonnie. We will show you each step of the whole work flow, from downloading the publicly available data sets, to generating a heat map which indicates the impacts of the Hurricane. You need three tools:
The following figure shows the work flow:
To perform a geospatial analysis on shapefile of longitude and latitude data in this example, we use the following Vertica built-in functions
Convert Shapefile Using GDAL
We are using the Hurricane BONNIE(2016) data set from the National Hurricane Center
and the Landmarks data set from the United States Census Bureau TIGER database
. Both data sets are geographic longitude latitude data sets in shapefile format. The BONNIE shapefile is in the SR-ORG7219 spatial reference system and the landmark shapefile is in the NAD83 spatial reference system. We cannot analyze spatial data in different reference systems. The first step, then, is converting them to WGS84 using the GDAL ogr2ogr tool:
# ogr2ogr -f “ESRI Shapefile” -t_srs EPSG:4326 destination.shp source.shp
#Convert hurricane BONNIE polygon data to WGS84
ogr2ogr -f “ESRI Shapefile” -t_srs EPSG:4326 al022016-001_5day_pgn_4326.shp al022016-001_5day_pgn.shp
#Convert landmarks point data to WGS84
ogr2ogr -f “ESRI Shapefile” -t_srs EPSG:4326 tl_2015_09_pointlm_4326.shp tl_2015_09_pointlm.shp –s_srs EPSG:4269
You can download the converted data sets from our GitHub repository
Perform a Geospatial Analysis with Vertica
This analysis identifies areas with high concentrations of properties and landmarks located within the predicted cone of the hurricane.
First, load the shapefile data into Vertica:
-- Load the hurricane BONNIE data set (polygons) to Vertica
-- STV_ShpCreateTable returns a CREATE TABLE statement
\set bonnie ''''`echo /home/shapefiles/`'BONNIE.shp'''
SELECT STV_ShpCreateTable(USING PARAMETERS file=:bonnie) OVER();
-- Create a Vertica table to store the polygon objects
CREATE TABLE bonnie(
gid IDENTITY(64) PRIMARY KEY,
-- Copy from shipfile to Vertica table
COPY bonnie SOURCE STV_ShpSource(file=:bonnie) PARSER STV_ShpParser(); -- 6 Rows Loaded
-- Load the landmars data set (points) to Vertica
\set landmarks ''''`echo /home/shapefiles/`'landmarks.shp'''
SELECT STV_ShpCreateTable(USING PARAMETERS file=:landmarks) OVER();
-- Create a Vertica table to store the point objects
CREATE TABLE landmarks(
gid IDENTITY(64) PRIMARY KEY,
COPY landmarks SOURCE STV_ShpSource(file=:landmarks) PARSER STV_ShpParser(); -- 236268 Rows Loade
-- US states data set from the United States Census Bureau TIGER database
-- Load US states data set (polygons) to Vertica
\set states ''''`echo /home/shapefiles/`'tl_2015_us_state.shp'''
SELECT STV_ShpCreateTable(USING PARAMETERS file=:states) OVER();
CREATE TABLE states(
gid IDENTITY(64) PRIMARY KEY,
COPY states SOURCE STV_ShpSource(file=:states) PARSER STV_ShpParser(); -- 56 Rows Loaded
Then, perform a spatial join analysis with Vertica:
-- Compute points intersecting polygons
-- Count the landmarks affected by Bonnie, group by state and time frame
CREATE TABLE counts AS
FROM bonnie a,
GROUP BY ADVDATE,
Next, create a heat map with Vertica:
-- Compute the clipped areas of each state covered by Bonnie
-- Join the counts withe the clipped areas
CREATE TABLE heatmap AS
FROM counts c,
WHERE b.ADVDATE = c.ADVDATE
AND s.STATEFP = c.STATEFP;
Export Vertica table to shapefile:
-- Export the heatmap table from Vertica to shapefiles
SELECT STV_SetExportShapefileDirectory(USING PARAMETERS path = '/home/shapefiles');
-- 1) Export polygon objects in heatmap table to a shapefile
SELECT STV_Export2Shapefile(* USING PARAMETERS shapefile = 'heatmap.shp', overwrite = TRUE, shape = 'Polygon') OVER()
WHERE st_geometrytype(geom) = 'ST_Polygon'; -- 42 Rows Exported
-- 2) Export multipolygon objects in heatmap table to a shapefile
SELECT STV_Export2Shapefile(* USING PARAMETERS shapefile = 'heatmapMulti.shp', overwrite = TRUE, shape = 'MultiPolygon') OVER()
WHERE st_geometrytype(geom) = 'ST_MultiPolygon'; -- 7 Rows Exported
Visualization with R
Here we use R, the statistical computing language, to generate a visualization based on the shapefile that Vertica created. Heat maps are a great way to show the results of your analysis. You can download the full R script for visualization from our GitHub repository
Based on the analysis and the heat map, Vertica predicts that Bonnie would be most destructive along the mid-Atlantic coast. Historically, Bonnie did indeed cause roughly $360 million in damage in South Carolina, North Carolina, and Virginia. While we may not be able to prevent hurricanes, with geospatial analysis we can be better positioned to limit their impact and to come to the aid of those affected.