Identifying Affected Customers in a Disaster
When a disaster strikes, many businesses rush to identify affected customers and react. For example, some may offer no-fee loans, reduce or remove late fees, or take other actions to assist customers in a time of need. Identifying affected customers is essential for businesses. As I was looking for information on areas most hit by disasters like hurricanes, tornados or earthquake in the United States, it occurred to me that there must be an easy way to do this within minutes. In this age of rich and abundant data, we can practically ask and answer questions before, during, and after disasters strike. In this article, I will walk you through some of my best practices for doing this analysis and share tips and tricks I’ve learned on the way.
TLDR; The React and D3 sample code for the below examples can be found on GitHub and the interactive app can be found as a static site on AWS.
Hurricane Sandy
In this article, I am going to focus on a single hurricane, Hurricane Sandy, that struck the Eastern Seaboard on October 2012. The best place to start when looking for hurricane data is the National Hurricane Center (NHC) of the National Oceanic and Atmospheric Administration (NOAA). Their site contains data on hundreds of hurricanes going back many years across many regions of the United States.
It is not immediately obvious how to match the more familiar hurricane names (Hurricane Sandy) with their naming conventions (al182012), but it is relatively easy once you understand the pattern. The first letters of the filename relate to the region where the hurricane struck, i.e. Atlantic, Eastern North Pacific, or Central North Pacific. The following two digits relate to the order in which the hurricane struck that year, i.e. 03 would be the third named storm of that year. It is also worth noting that the storms are named alphabetically (skipping the letter Q), so the 18th storm would start with the letter “S”. The last four digits relate to the year in which the storm hit, i.e. 2012.
Preparing the data
There are many tools out there for quickly visualizing the features of a shapefile, but I find that Mapshaper.org offers the best free one. Simply drag and drop the US Census Bureau county and Hurricane Sandy zip files from your desktop onto their website and within seconds you will have a visual of the spatial files.
From the Mapshaper visuals (and our own knowledge), it is clear that the hurricane affected many counties in its destructive path. Now what’s needed is to find a geospatial tool that can quickly identify those counties and how hard they were hit. Many people swear by QGIS or ArcGIS, which are both incredible for the job, but I’m going to focus on using the built-in query functionality of Postgres and more specifically PostGIS.
To get started, follow the detailed instructions from the Boston GIS group to install Postgres and PostGIS. With this installed and running, you should have all the tools needed to perform the spatial intersections.
The first step is to create a new database and ensure PostGIS is added to the database. Use the commands below
#!/bin/bashcreatedb gis_db
psql -d gis_db -c "CREATE EXTENSION postgis"
The next step is to download and import the shapefiles directly into a Postgres table using shp2pgsql.
Once the files are in the database as PostGIS tables, I wrote a query that finds the intersection between each step of the hurricane’s path and the affected counties and returns a human-readable and valid GeoJSON file.
Visualizing the newly created data
Armed with the affected counties and the time at which the hurricane hit those counties, you can now utilize React and D3 to create and deploy an animated and interactive map of this hurricane, and others, for executive and strategy teams.
If you are interested in the full source code, it is up on GitHub with detailed instructions on how to get the app up and running locally.
Next Steps
After doing this analysis for Hurricane Sandy, I noticed that the NHC does a great job of standardizing their data sets. With this in mind and these powerful geospatial tools, it is easy to drop a new shapefile of a future (or historical) hurricane, tornado, or earthquake into this analysis and within minutes have a beautiful and interactive data visualization that is ready to be shared with any audience.
Christopher Lanoue is a Data Visualization Engineer and Director of Engineering and Innovation at Graphicacy with a focus on designing and building innovative and creative solutions for clients all over the world.
