Alaskan Outdoor Accident MapJune 28, 2013

Data Sources

Tab-Separated Table (TSV File)
Spreadsheet (ODS Format)
Google Earth (KML File)
Geo Data (GeoJSON)
Geo Data (TopoJSON)
USGS AK Placenames (TXT)
USGS AK Placenames (XML)

The data for this map was collected from akfatal.net:

“Many people have died in the Alaskan outdoors. Read their stories and learn from them.”


Technical Notes

Data Cleanup

The original data is in an HTML table without geographic coordinates. In some cases I had to rely on local knowledge to find the nearest fit for points that weren't found in the USGS Geographic Place Names database. I'd say 95% of the points are accurate to within 20 miles or so, but a few errors definitely slipped through (largely due to repeated place names within the state).

Time Formats

The date format is inconsistent across entries, but parseable with spreadsheet software.

Geo-Referencing

Note: The CartoDB online platform has changed since 2013.

CartoDB has a built-in georeferencing feature to extract latitude & longitude coordinates from place names. However, it's not very good at recognizing features like the rivers, glaciers, and peaks that make up most of the dataset. My first thought to get around this limitation was to try and hack National Geographic's Topo software to spit out a list of coordinates, but it has been discontinued. I discovered the USGS Placename Information System, which was probably where the Topo data came from anyway. I downloaded the Alaska dataset and imported it into a new sheet.

Accident Database Sample

Name Date Location Activity Cause of Death
Redacted 1920-04-28 Bird Railroad Avalanche
Redacted 1920-04-28 Bird Railroad Avalanche
Redacted 1920-04-28 Bird Railroad Avalanche

USGS Database Sample

FEATURE_ID FEATURE_NAME FEATURE_CLASS STATE_ALPHA STATE_NUMERIC COUNTY_NAME COUNTY_NUMERIC PRIMARY_LAT_DMS PRIM_LONG_DMS PRIM_LAT_DEC PRIM_LONG_DEC SOURCE_LAT_DMS SOURCE_LONG_DMS SOURCE_LAT_DEC SOURCE_LONG_DEC ELEV_IN_M ELEV_IN_FT MAP_NAME DATE_CREATED DATE_EDITED
247074 Pacific Ocean Sea CA 6 Mendocino 45 391837N 1235041W 39.3102778 −123.8447222 0 0 Mendocino 01/19/1981 05/16/2011
1397640 Cape Hinchinbrook Cape AK 2 Valdez-Cordova (CA) 261 601405N 1463830W 60.2347222 −146.6416667 0 0 Cordova A-8 01/01/2000 02/05/2009

Accuracy

There's probably a better way (regular expressions?) to look up the geo-coordinates, but I just used VLOOKUP(C2,Places.$B$2:$K$35126,9,0) and did a bit of manual correction.