Open data on Canadian railway accidents

Fire following July 2013 derailment and explosion in Lac Mégantic, Quebec. Source:   Sûreté du Québec, Wikimedia Commons

Fire following July 2013 derailment and explosion in Lac Mégantic, Quebec. Source: Sûreté du Québec, Wikimedia Commons

The federal government’s open data portal is slowly getting more useful for journalists, as departments and agencies post additional datasets in accordance with the government’s open data directive. The directive affords lots of wriggle room to not release data, or release only selected fields, but open data sites do give instant access to data, and you can always go back to the agency to ask for more.

A good example of a useful dataset is the Transportation Safety Board’s dataset of railway accidents and other occurrences. It’ll tell you where and when the incident happened, if it was a derailment, which railway was involved, how many people died, the mileage on the rail subdivision where it occurred, and more, in 23 fields. The data dictionary is here.

There are a shade more than 17,000 records in the flat-file dataset, so it’ll be easy to use in Excel, Google Sheets and other straightforward programs. For that, the dataset earns a high easyness rating. The data also looks pretty clean, which is nice if you’re trying for the quick turnaround.

The TSB keeps more, of course, and the database is live, so journalists will often want to contact TSB media relations to see what else is available, or to get up-to-date data if immediacy is a concern. But there’s more than a little bit here. I learned, for example, that the devastating Lac Mégantic explosion in 2013 was one of eight incidents involving explosions reported to the TSB since 2004.
It’s easy to see how this dataset could be useful not only to reporters working on deadline, but as good source material for a deeper look into railway safety into Canada.

You can explore the accident with explosions below, in the sample. Use the column visibility to turn fields on and off. You can also download the sample in different formats.

OCC_NO
OCC_YEAR
OCC_DATE
CLASS_ID
OCC_TYPE
ACC_INC_TYPE
PROVINCE
SUBD_MILEAGE
SUBD_NAME
SUBD_OWNER
DERAILED_RS_IND
DG_CARS_INVOLVED_IND
DG_RELEASED_CAR_IND
DG_RELEASED_NOT_FROM_DG_CARS_IND
FIRE_IND
EXPLOSION_IND
EVACUATION_IND
TOTAL_FATAL_INJURIES
TOTAL_SERIOUS_INJURIES
XING_TYPE
AWD_TYPE
PRODUCTION_DT
R05Q0010 2005 23/02/2005 3 ACCIDENT MAIN-TRACK TRAIN DERAILMENT QUÉBEC 86.41 DRUMMONDVILLE CN – CANADIAN NATIONAL RAILWAY CO. YES YES NO N/A YES YES YES 0 0 N/A N/A 2015-11-16T15:00:27
R07V0213 2007 04/08/2007 3 ACCIDENT NON-MAIN-TRACK TRAIN COLLISION BRITISH COLUMBIA 462.4 PRINCE GEORGE CN – CANADIAN NATIONAL RAILWAY CO. YES YES NO N/A NO YES NO 0 0 N/A N/A 2015-11-16T15:00:27
R07W0241 2007 04/11/2007 5 ACCIDENT FIRE ONTARIO 23.46 REDDITT CN – CANADIAN NATIONAL RAILWAY CO. NO NO N/A N/A YES YES NO 0 0 N/A N/A 2015-11-16T15:00:27
R09W0033 2009 13/02/2009 3 ACCIDENT MAIN-TRACK TRAIN DERAILMENT ONTARIO 108.23 ALLANWATER CN – CANADIAN NATIONAL RAILWAY CO. YES YES NO N/A YES YES NO 0 0 N/A N/A 2015-11-16T15:00:27
R09W0252 2009 05/12/2009 3 ACCIDENT MAIN-TRACK TRAIN DERAILMENT SASKATCHEWAN 222 RIVERS CN – CANADIAN NATIONAL RAILWAY CO. YES YES YES N/A YES YES NO 0 0 N/A N/A 2015-11-16T15:00:27
R13D0054 2013 06/07/2013 2 ACCIDENT MAIN-TRACK TRAIN DERAILMENT QUÉBEC 0.35 SHERBROOKE MONTREAL, MAINE & ATLANTIC RAILWAY YES YES YES N/A YES YES YES 47 0 N/A N/A 2015-11-16T15:00:27
R13E0142 2013 19/10/2013 3 ACCIDENT NON-MAIN-TRACK TRAIN DERAILMENT ALBERTA 58.1 EDSON CN – CANADIAN NATIONAL RAILWAY CO. YES YES YES N/A YES YES YES 0 0 N/A N/A 2015-11-16T15:00:27
R14M0002 2014 07/01/2014 3 ACCIDENT MAIN-TRACK TRAIN DERAILMENT NEW BRUNSWICK 152.6 NAPADOGAN CN – CANADIAN NATIONAL RAILWAY CO. YES YES YES N/A YES YES YES 0 0 N/A N/A 2015-11-16T15:00:27