Alaskan Outdoor Fatality MapJune 28, 2013
The data for this map was collected from akfatal.net. Tagline: "Many people have died in the Alaskan outdoors. Read their stories and learn from them."
The map itself was put together in CartoDB.
LibreOffice is a free software alternative to Microsoft Excel.
The original data is stored in an HTML table. It's prety easy to import the data into Excel or LibreOffice, but after that there's a few inconsistencies to iron out.
The date format is inconsistent across entries, but LibreOffice is pretty good at detecting alternate date formats. In some cases I found it easiest to save the spreadsheet as a CSV file and re-import it in order to clear out the text delimiters (') for unrecognized formats.
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. I was disappointed to find that not only did Topo not run on Linux, it had been discontinued. Turning to Google, I happily 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|
USGS Database Sample
|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|
There's probably a better (regular-expression-y) way 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. In some cases I had to rely on local knowledge to find the nearest fit for points that weren't found in the 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).