Visualizing Percentage of Storms Resulting in Injuries/Deaths by State (1996 - 2014)

Starting in 1996, National Weather Service directive 10-1605 mandated the reporting of 48 different types of weather events and their effects on the impacted community. This tutorial aims to create a visualization focusing on the percent of storms resulting in injuries/deaths reported by state, and to identify which states
have been more fortunate than others. The data is provided by NOAA in .csv files organized by individual year. This tutorial requires a working knowledge of the command line, PostgreSQL/PostGIS, GDAL/ogr2ogr, LeafletJS, Python, and JavaScript.

The first step is to create the tables in PostgreSQL and load the data. A new database is created and the following code executed to enable PostGIS:

Included are the attributes we need for this application, along with additional fields we may opt to use in the future for further analysis.

To create a surrogate primary key called id for this table, the following code is executed:

ALTER TABLE storm_events ADD COLUMN id SERIAL PRIMARY KEY;

To load the data into the table, several options are available. One option, though not the most efficient, is outlined below:

1) A python script is written to read from each year’s .csv file in our details_raw folder and creates newly formatted .csv files, excluding the unnecessary data. It is then executed in the appropriate directory using the command line.

Now, to create views that allow us to analyze the data you have several options but below are a few simple ones that isolate the info we are interested in. They also allow room for growth should you choose to add aggregates of other attributes:

A simple query to validate the data and identify the states with the highest percentage of storms resulting in injury/death:

SELECT *
FROM map_storm_data
ORDER BY percent DESC;

To generate a shapefile we can use to visualize our data, download from TIGER the shapefile needed, and then use command line to convert it to the desired projection and import it into postgres. In this case, I entered:

To create the map for our application, we will use the same process as described in this Leaflet tutorial. The end result is a thematic map with a legend and an info window describing the data as you select each state.