Custom Software Apps & SharePoint Consulting

Managing Railroad Crossing Safety with TIBCO Spotfire Analytics

Imagine that you are a intensely focused on managing railroad crossing safety by improving rail-highway crossings that are the site of accidents involving trains and road vehicles or pedestrians. While all crossings may be of interest, you want to triage the 500,000 crossing in the US to determine those that most urgently need attention to safety conditions.

We want our dashboard information system to answer these questions at a minimum:

  1. What are the Rail-Highway crossings that have an unusual number of Incidents?
  2. At which crossings are rates of incidents increasing?
  3. What are the crossings with fatalities or injuries?
  4. Are the rates at a crossing increasing or decreasing over the years?
  5. Are the incidents associated with vehicles stopped on the tracks or other causes?

For instance, here we see grade crossing incidents in the Houston area with 3 or more incidents in 6 years, or with 2 or more showing fatalities.   We select the second bar in the Fatality plot showing 3 fatalities in 2014, see that it is Fondren Road.   From the map, it is At US90 and In the table we see highlighted that it occurred Sept, 6, 2014 at 18:38, Vehicle going 15 mph, Train going 35, the passenger was killed, driver injured.

Spotfire Railroad Incident Visualization

The Visualization displays here are constructed in Tibco Spotfire 6.5.   The real public domain data used in the analysis comes from  the Federal Railroad Administration – Office of Safety Analysis [2].   There are six tables with data from 2009 to 2014.

LAYOUT OF THE DASHBOARD

Let’s start near the end — at the main decision dashboard, “Map Crossings(2)”,  and understand what is displayed and how it answers our key questions interactively.

The dashboard below shows Rail-Highway Crossings in the central Houston Area that have had at least two incidents in the time frame: 2009-2014.

Spotfire Railroad Incident Visualization b

The upper left is a map chart with two marking layers, zoomed into the Houston area.   One layer, CrossLoc Index, are the small blue dots scattered across the map.   These are the Rail-Highway Grade Crossings from the  GCIS database — They follow the rail lines and indicate every place where a railroad crosses, or crossed at one time, a highway.   The other layer, CrossLoc Count, is the active layer of the map and highlights the crossings of interest.   Bubble size is the number of incidents in the time period, Color is the type of grade crossing, with Orange being the guarded public crossings, the Red being less guarded crossings on private, railroad, or industrial property.   The labels are the highway or street names at the grade crossing.

The upper right is a horizontal bar chart showing on the vertical, category axis the ID of the Grade Crossing, the horizontal, value axis is the total number of incidents in the time period.   The stacked bars are the number of incidents at that grade crossing by year, with red for the older reports, green-to blue for the most recent.

The lower panel is a details extraction of the Rail Hwy 2009_2014  data table for the selected and highlighted grade crossing where you can learn the day and time of the incident, how fast the vehicle and train were going, which direction, how much damage, number of injuries or fatalities and a text summary of what happened.

INTERACTIVITY OF THE DASHBOARD PANELS

Let us refine the search and look at only 2012-2014 incidents.
Filter the IYR to (12, 13, 14).   In the Right Hand Bar chart, Select the bars with at least 2 in the Row Count (X-axis).   There are seven such crossings that we see names on the Map and each incident listed in the table at the bottom.

Spotfire Railroad Incident Visualization c

Here we “brush” the table record for the Sherwin June 10, 2013 accident, Crossing ID = 758528K, where we see a “Train Struck an 18-Wheeler” where “the occupant was not in the Vehicle at the time of the accident.”   It happened at 6:55 am, weather and visibility good, the vehicle was stopped, the train going 15 MPH, no injuries but $75,000 in damage.

Now let’s check the entire network of BNSF.    Goto the Map Crossings, Filter on Railroad =”BNSF”.   Select all the crossings.   Goto the Map Crossings(2).  choose years 2012-2014  (IYR=(12, 13, 14)) and select the bars with 3 or more incidents.

Bookmark: BNSF 2012-2014, marked with 3+ incidents, then changed the filter to all years.  In this view, we have the all year count, but we highlight only those with three or more in the past three years.

Spotfire Railroad Incident Visualization dMachine generated alternative text: Cover Map Eq Damage Rail Shapes Map Crossings 1 AVE Map Crossings (2) All RR Bar Map Cas Map Fatalityies Page (3) WEATHER Page (4) Page (5) Page (2) Page (6) Map Ops Map ops (2) Data limited by: . Marking Data table: * Rail Hwy 2009 Marking: . Marking (2) Color by: YEAR Data limited by: . Marking (2) Data table: * Rail Hwy 2009_2... Marking: . Marking (2) Filters Filtering scheme tc search filters rail I CrossLoc RailEq200g 2014 Rail cas2009 2014 Rail Hwy 2009 2014 AMTRAK @ (Empty) IMO Type to search in list (All X 21 values RAILROAD Type to search in list 383 ACE( ACWR AERC AGCR INCOTNO Type to fitter by text IYR2 Type to fitter by text IM02 Type to fitter by text Type to fitter by text INCOTN02 103 columns Rail Hwy200g 2014 Otta CrossLocC01_nt Data limited by Data table * Cross Loc Marking . Marking Marker by CrossingID Color by TypeXing Shape by (None) All values Size by count(GXID) CorssLoc Data limited by . Marking Data table * Cross Loc Marking . Marking Marker by Railroad Hwy Crossings Count of Rail Hwy Incidents by Crossing ID Sorted by number ofincldents 883401 F -47 0254229 883399G 02SE17CÆ4 014S02G 014992" 48 020E47X 02S430G 92E7E4U 20 02SE17C- 4 02E47EY 08SS33A 093389B 38 2750131N8 -17 011717R40 012080E 40 12121G40 023204B 48 024887x.s 02S017AÆ4 025422P- 02E42EKÆ4 02 SE90V 02SS03T 027ssou 028400A 028888u 080419',' 48 Layers SOC km SOO m KANSAS AVE THOMAS 27TH AVENUE aETHANY HOME NARR2 g, AT PEREA RD NG SECOND UNI TRNSPD VEHDMG CASTILIA ST ca 27 IYANalTO 43RD & CAMELBACK TIC'.".'ELL KWAY S DAY YEAR TIMEHR TIMEMIN 24 so 42 30 so 10 31 30 so AMPM AM AM AM AM AM AM AM AM Nassau Havana CAYMAN ISLANDS TOTKLD DRIVAGE ( Row Count) NARRI TRAIN 3 OPERATING wry LOCOMOTIVES Ell 71-E/84- VEHICLE APPROACHED TRACK AT HGH RATE OF SPE 41 STOPPED aETvVEEN GATES DRIVER AGE UNKNOWN 41: PRECEDED LGHTS DRIVER AGE UNKNOWN DRIVER AGE UNKNOWN DRIVER AGE UNKNOWN aelnu»an HIGHWAY PEREA RD CR 27 r,'ANarro PEREA RD 43RD AND CAME 43RD AND CAME 43RD & CAMEL3 43RD AND CAME 43RD AND CAME 43RD & CAMEL 43RD & CAMEL3 27TH AVENUE 27TH AVENUE VISIBLTY VEHSPO 030 Rail Hwy 2009 2014 024887X 024887X 024887X 02S422P 02S422P 02S422P 02S422P 02S422P 02S422P 02S422P 02S430G 02S430G RAILROAD ATK aNSF aNSF aNSF aNSF aNSF aNSF aNSF aNSF aNSF aNSF aNSF MONTH TYPAcc 55 marked Online Details... 12424 of 12424 rows

Castilia St and Parkway Dr S in Memphis, Tenn seem to be a hot spot. If we zoom into the Memphis area, we see these two intersections, within a mile of each other, have 12 accidents in the past three years.

Spotfire Railroad Incident Visualization eMachine generated alternative text: Cover Map Eq Damage Rail Shapes Railroad Hwy Crossings Layers C km Rail Hwy 2009 2014 Map Crossings MEMPHIS PAR KVVAY S Map Crossings (2) GTON CASTILIA ST SHEI_ay All RR Bar Map Cas VISIBLTY Map Fatalityies CrossLocC01_nt Data limited by Data table * Cross Loc Marking . Marking Marker by CrossingID Color by TypeXing Shape by (None) All values Size by count(GXID) CorssLoc Data limited by . Marking Data table * Cross Loc Marking . Marking Marker by Page (3) VEHDMG Page (4) Page (5) Page (2) Page (6) Map Ops Map Ops (2) Count of Rail Hwy Incidents by Crossing ID Sorted by number ofincldents 883401 F —47 883399G 8834071" —47 863397T —47 863400M —47 83437 N -47 8834841" —47 885982M. ssse93A- s 670885 N -47 6738091" —47 9281 SS p —47 NARR2 NARR3 Data limited by: . Marking Data table: * Rail Hwy 2009 Marking: . Marking (2) Color by: YEAR Data limited by: . Marking (2) Data table: * Rail Hwy 2009_2... Marking: . Marking (2) Filters Filtering scheme tc search filters rail I Cross Loc RailEq2009 2014 Rail cas200g 2014 Rail Hwy 2009 2014 AMTRAK @ (Empty) IMO Type to search in list (All X 21 values RAILROAD Type to search in list 383 ACE( ACWR AERC AGCR INCOTNO Type to fitter by text IYR2 Type to fitter by text IM02 Type to fitter by text Type to fitter by text INCOTN02 103 columns Rail Hwy200g 2014 ( Row Count) NARRI 4 VEHICLE STALLED ON TRACK TIMEHR TIMEMIN so 31 40 40 AMPM AM AM AM AM AM AM AM AM HIGHWAY PA R QVAY DR S CASTILIA ST CASTILIA ST CASTILIA ST CASTILIA ST CASTILIA ST CASTILIA ST CASTILIA ST CASTILLIA ST CASTALIA ST CASTILIA STREET PRIVATE VEHSPD coo 003 TOTKLD DRIVAGE TRNSPD TYPAcc WEATHER AD YEAR MONTH Online Details... DAY 41 UNOCCUPIED VEHICLE PRECEDED GATES UNOCCUPIED VEHICLE UNOCCUPIED VEHICLE DRIVER AGE UNKNOWN 4 41 DROV 20 marked 4 WRECKER FOULING XING VEHICLE UNOCCUPIED AT TIME OF IMPACT 12424 of 12424 rows

Here we have modified the upper right bar chart to show the number of incidents together with the total injuries and total killed (included in injuries).   Castilia St is at the top of the list with 3 accidents in 2014 with one fatality, 10 pm on New Year’s Eve.

Spotfire Railroad Incident Visualization fMachine generated alternative text: Cover Map Eq Damage Rail Shapes Railroad Hwy Crossings Layers ES ILL Map Crossings MEMPHIS Map Crossings (2) GTON All RR Bar Map Cas VEHSPD Map Fatalityies Cross Loc Comt Data limited by Data table * Cross Loc Marking . Marking Marker by CrossingID Color by TypeXing Shape by (None) All values Size by count(GXID) CorssLoc Data limited by . Marking Data table * Cross Loc Marking . Marking Marker by (None) Page (3) WEATHER Page (4) Page (5) Page (2) Page (6) Map Ops Map Ops (2) Count of Rail Hwy Incidents by Crossing ID Sorted by number ofincldents Total Killed NARR2 Data limited by: . Marking Data table: * Rail Hwy 2009 2.. Marking: . Marking (2) Color by: YEAR Data limited by: . Marking (2) Data table: * Rail Hwy 2009_2... Marking: . Marking (2) Filters Filtering scheme tc search filters rail I RailEq200g 2014 Rail cas200g 2014 Rail Hwy 2009 2014 AMTRAK @ (Empty) IMO Type to search in list (All X 21 values RAILROAD Type to search in list ACE( ACWR AGCR AGR INCOTNO Type to fitter by text Type to fitter by text IM02 Type to fitter by text Type to fitter by text INCOTN02 103 columns Rail Hwy200g 2014 Total Injured Total Killed 883401 F -47 883399G 883400M —47 8834071"-47 88341 S N —47 ssse93A 670885 N -47 6738091" —47 TOTKLD Number Incidents 234 s s Number Incidents DRIVAGE Total Injured NARRI PAR S ROZELLE ST CASTILIA ST DEMOCRAT DAY ERGON EAST CARGIL CORN EAST C km Rail Hwy 2009 2014 YEAR MONTH SHEI_ay TIMEMIN 40 so 40 SHEI_ay O BRANCH TRNSPD VEHDMG 1000 TIMEHR AMPM AM AM AM AM AM AM VISIBLTY HIGHWAY ROZELLE ST CASTILIA ST CASTILIA ST CASTILIA ST CASTILIA ST CASTILIA ST CASTILIA ST DEMOCRAT RD DEMOCRAT RD SHELay JAMES MILL RD 863400Y 863407',"' 863407',"' 86341SN s€sg78J RAILROAD aNSF aNSF aNSF aNSF aNSF aNSF aNSF aNSF aNSF aNSF aNSF TYPAcc 41 UNOCCUPIED VEHICLE PRECEDED GATES UNOCCUPIED VEHICLE DRIVER AGE UNKNOWN 4 DRIVER AGE UNKNOWN 41 PRECEDED GATES DRIVER AGE UNKNOWN UNOCCUPIED VEHICLE 21 marked Online Details... 6,378 of 12424 rows

IN CONCLUSION

The objectivity of a tool like this is critical to health & safety planners. Imagine how it could be applicable for local governments and safety-conscious industries as well. With more data sources, including urban development and traffic information, you could even start to build out more predictive analytics to determine which areas will become higher risk in the future, and plan for safety improvements there.


[2] Data Source:  http://safetydata.fra.dot.gov/OfficeofSafety/publicsite/on_the_fly_download.aspx — As of January 25, 2016.   Data downloaded by year for the years 2009 through 2014 for the following tables:

  • Rail-Highway Crossing Locations
  • Rail-Highway Casualties
  • Rail-Equipment Incidents
  • Rail-Operations
  • Further, we downloaded the tables from the GCIS, Grade Crossing Information System, and used the Grade Crossing Locations table: CI_LocationAndClassification.csv (120 MB)

[3] North American Railroad Network – ArcGIS Shapefile

http://www.arcgis.com/home/item.html?id=476e6223627944bc8e8ecfddfff16bd2

Share this post with your friends

Skip to content