Protecting populations from extreme temperatures

By Romain Fardel

A project created for the Insight Data Engineering Fellowship, Fall 2020 session (20C)

Heatwave can be deadly for populations. For instance, over 30,000 people died from the heatwave that swept Europe during the summer 2003. The issue affects the whole world, and the problem will keep increasing with global warming.

To protect populations, researchers need to study how mortality is affected by temperature. The problem is that there is currently not a single source of data available to researchers. For the US, researchers need to retrieve vital statistics from the CDC and historical weather data from NOAA. Combining these datasets in challenging for a few reasons:

• Geographical mismatch: mortality is reported by county, whereas temperature is reported by weather station.

• Temporal mismatch: mortality is reported by day or by month, where temperature is reported at variable intervals depending on the data year and station location.

• Schema evolution: the format of mortality data evolves every few years, and data is provided in a non-delimited format, where knowledge of the position of each field is needed to extract the data.

• County evolution in time: county limits have changed over the last 50 years.

This project addresses that need by creating a pipeline to combine these datasets and make them available in a GIS-enabled (Geographic Information System) database that the end user can query with SQL.

1. Raw data is stored in Amazon S3
   • Weather data is readily available in a NOAA S3 bucket
   • Mortality data is ingested by downloading from FTP, unzipping and saving text files to S3
2. Raw data is processed in Apache Spark. Each dataset is extracted, filtered, and aggregated separately
3. Data is loaded to PostgreSQL with the PostGIS extension
4. Auxillary datasets (weather station, county definitions) are loaded to PostGIS and the data is joined
5. Final table is queried on demand and displayed in Dash.

NOAA Global Historical Climatology Network Daily (GHCN-D), available in an Amazon S3 bucket.

CDC - Vital Statistics Online Data Portal, under Mortality Multiple Cause Files, U.S. data (.zip files).

CDC - Public Use Data File Documentation, in PDF format.

ghcnd-stations.txt from NOAA bucket

County Boundaries of the United States, 1990

A Crosswalk for US Spatial Data 1790 - 2000, Fabian Eckert and Michael Peters. ZIP file