FARS and PBcat

23 thoughts on “FARS and PBcat”

1. There a pretty interesting thread discussing bicyclist fatals in a recumbent forum here (but essentially now dated, since it was about trying to ascertain details from the 2009 data):
   http://www.bentrideronline.com/messageboard/showthread.php?t=66443

2. Mainly out of frustration with the web interface to the FARS database…

   The entire raw FARS data is distributed and freely downloadable as a zip file containing a bunch of files, all in .dbf format — each representing a table. nhtsa ftp site or My Local copy.
   These can be read in to a spreadsheet type program (I used OpenOffice) and outputted as (e.g. comma separated) text files.

   I put the 2010 FARS PBType table into a mysql database.
   So this would be nationwide ped and bicyclists MV fatals.
   This table has a row for each ped or bicyclist involved in a fatal collision (the large majority, but not every row is associated with one fatality).
   There are a bunch of other tables, i also loaded up the (entire, not just bike/ped) incident table (which they annoyingly still call accident.dbf, but i will have none of that!); there’s a field called ST_CASE which acts as a unique case identifier across all tables.

   I would guess the most interesting feature would be the breakdown of BikeCrashType/Grp;
   I made a spreadsheet 🙂 !!
   Listing the attribute definitions: http://azbikelaw.org/crashReports/asdm/FARS.xls

   The database is named farsaz, and the password scheme is analogous to asdmadmin setup:
   mysql -h farsaz.db.4318490.hostedresource.com -u farsaz -ppassword -D farsaz
   mysql -h farsaz.db.4318490.hostedresource.com -u farsazuser -puserpassword -D farsaz

   —
   After i did the above, it came to my attention that in addition to the .dbf format data files there are ALSO something called SAS (Statistical Analysis Software?) format files. In particular, this file format10.sas in file 2010\SAS\FSAS2010.zip contains a plain text definitions file that looks like it might come in handy (i had been cutting/pasting 811530FARS2010CodingandValidationManual.pdf which was definitely a p.i.t.a ). The table files end with a .sas7bdat extension; here is a reader for that file format along with some explanatory notes on the files structure.

3. Lighting (LGT_COND); 2010 Data

   Daylight = 332/617 = 54%
   Darkness = (121+132+1)/617 = 41%
   The remaining 4% was dusk or dawn

   Note in the table below, the terminology refers to the condition of the roadway, so e.g. “Dark – Not Lighted” is nothing to do with whether or not the cyclist was lighted.

   SELECT count(*) FROM 2010_incident i WHERE EXISTS (SELECT 1 FROM 2010_person p WHERE p.ST_CASE=i.ST_CASE AND (p.PER_TYP=6 OR p.PER_TYP=7) AND p.INJ_SEV=4 ) ;
   
   SELECT LGT_COND,count(1) FROM 2010_incident i WHERE EXISTS (SELECT 1 FROM 2010_person p WHERE p.ST_CASE=i.ST_CASE AND (p.PER_TYP=6 OR p.PER_TYP=7) AND p.INJ_SEV=4 ) GROUP BY 1 ORDER BY 1 ;

   Light Condition	arizona Only		US
   Daylight	9		332
   Dark – Not Lighted	2		121
   Dark – Lighted	6		132
   Dawn	0		9
   Dusk	2		21
   Dark – Unknown Lighting	0		1
   Other	0		0
   Not Reported	0		0
   Unknown	0		1
   TOTAL	19		617

   The table was extracted using the FARS web front-end, while the SELECT statements above are from my own mysql database; the results do match — and represent an *incident* count (slightly different than a fatality count) where a cyclist was killed (and not just a count of all cyclists in the FARS person database). To get a particular state add on an AND STATE=4 (for for example, arizona) to the query.

4. New Table: farsxref

   Note to self: should also put peds into this table (since the PBType covers both cyclists and peds…

   I have all the FARS plus ASDM data tables loaded up on my own local server in databases named asdm and fars. A new table will be added to asdm.farsxref just three fields: IncidentID, Year, and FarsCase.
   The year is slightly redundant; it is possible to lookup by IncidentID number in all the asdm.YYYY_incident tables until it is found but that seems like a waste.
   CREATE TABLE asdm.farsxref ( IncidentID int PRIMARY KEY, Year int, FarsCase int) engine=MyISAM ;

   So here’s a query to select the data we want from the ASDM tables:

   SELECT IncidentID, IncidentYear FROM asdm.2013_incident i WHERE EXISTS  
   (SELECT 1 FROM asdm.2013_unit u WHERE u.IncidentID=i.IncidentID AND u.eUnitType IN ('PEDALCYCLIST') AND i.InjurySeverity=5 ) 
   ORDER BY IncidentDateTime ;
    -- OR THIS OTHER QUERY SHOULD PRODUCE SAME RESULTS --
   SELECT IncidentID, IncidentYear FROM asdm.2013_incident i WHERE sF_Bicycle AND i.InjurySeverity=5
   ORDER BY IncidentDateTime ;
   

   And here’s what that would look like plugged into an INSERT command (note, though that after this the FarsCase would be NULL:

   INSERT INTO farsxref ( IncidentID, Year ) SELECT IncidentID, IncidentYear FROM 2012_incident i WHERE EXISTS (SELECT 1 FROM 2012_unit u WHERE u.IncidentID=i.IncidentID AND u.eUnitType IN ('PEDALCYCLIST') AND i.InjurySeverity=5 ) ;

   Re-run this command for all the years. Note since IncidentID is a primary key, if you re-run it for the same year, it will get an error (and that’s probably good since we want exactly one row for each incident).

   So I tried to figure out a way to join them together, e.g. i THINK something like this should work (but doesn’t!):
   

   SELECT az_i.IncidentDateTime FROM asdm.2011_incident AS az_i JOIN fars.2011_incident AS fars_i WHERE
   az_i.IncidentDayOfWeek = fars_i.DAY_WEEK AND fars_i.STATE = 4 AND az_i.InjurySeverity=5;

   
   But I gave up… I didn’t know how to rectify the different date/time formats, e.g. in asdm there an entire datetime, and in fars there are various subsets of that, e.g. day, month, hour, minute and so forth.

   So i chickened out and used these two queries, and produced a spreadsheet I eyeballed for correctness and output as the following csv, from whence they can be LOADed asdm.farsxref this will be incorporated into build-asdm.sql :

   
SELECT IncidentID, IncidentYear, IncidentDateTime, sF_Bicycle, sF_Pedestrian FROM asdm.2016_incident i
WHERE (sF_Bicycle OR sF_Pedestrian) AND i.InjurySeverity=5 ORDER BY IncidentDateTime ;
SELECT i.ST_CASE, i.YEAR, i.MONTH, i.DAY, i.HOUR, i.MINUTE, sF_Bicycle, sF_Pedestrian FROM fars.2016_incident i
WHERE (sF_Bicycle OR sF_Pedestrian) AND i.STATE=4 ORDER BY i.MONTH, i.DAY, i.HOUR, i.MINUTE;

   Note this will produce a few false-positives — that is fatal collisions where a bike or ped is only incidentally involved (not killed); these will need to be manually deleted from the .csv during eye-balling.

   An older, now abandoned, scheme involved turning them into INSERT statements:
   “IncidentID”,”IncidentYear”,”ST_CASE”

   —
   anomolies — 2015 was a nightmare:
   http://10.1.1.10/asdmphp/queryAsdmIncident.php?incident=2959511 is coded as a moped in asdm but in fars, it’s a “personal conveyance”
   http://10.1.1.10/asdmphp/queryFarsIncident.php?year=2015&st_case=40108

   http://10.1.1.10/asdmphp/queryFarsIncident.php?year=2015&st_case=40840 says pedestrian acoma/pima but cannot find in asdm at all?!
   AND this one!!
   http://10.1.1.10/asdmphp/queryFarsIncident.php?year=2015&st_case=40841 says ped on “BIA 12” cannot find in asdm at all. one with that date/time was on I40 but that one involved two drivers.
   AND this one!!
   http://10.1.1.10/asdmphp/queryFarsIncident.php?year=2015&st_case=40801 says ped on US-191 cannot find in asdm. There is one at another time that day on us191 but that’s written as a single-vehicle where passenger killed.

   Oh, this is weird, for 2012 adot incident 2624035 is missing from Fars!? According to asdm, this is a ped fatality occuring on 5/30/2012.

5. UPDATE: as of at least Aug 29, 2016 PBCAT is back… “Starting from 2014, Pedestrian and Bicyclist file is included in the file release”
   —
   Well this stinks: on 07/03/2013 NHTSA quietly removed pbcat data entirely, citing unspecified “inconsistencies…”. ug. Here is the text of the notification:

   PBTYPE In 2010, NHTSA added new pre-crash data elements for pedestrians and bicyclists (non-motorist) to the data collected in the Fatality Analysis Reporting System (FARS) and the National Automotive Sampling System (NASS) General Estimates System (GES). These data are intended for countermeasure research and development. However, NHTSA’s National Center for Statistics and Analysis (NCSA) quality control team has identified inconsistencies between the new non-motorist data elements and comparable pre-existing FARS elements that can lead to different results. Consequently, NHTSA has removed the pbtype table from the 2010 and 2011 FARS and NASS GES files while research is conducted on how improvements can be made. This pbtype table includes some non-motorist data (see below). The following data elements have been removed: (all of them, i think)

   Here is an email that explains some more detail; Ug, note timeframe for getting data back isn’t for 2 YEARS (the latter part of 2015).

   ———- Forwarded message ———-
   Date: Wed, Oct 23, 2013 at 5:14 PM
   Subject: RE: FW: NASS redesign and bicyclist injuries

   Sorry for the delayed response, I hope this is clarifying.
   We are working to correct the new Ped/Bike typing application that we introduced in 2010 and used on a trial basis since then (all new data elements).
   Our goal is to have revisions to the typing application in place in time to have confidence to release data it generates during the 2014 data collection year. However, the following Pedestrian Bicycle Elements continue to be available for public access:

   NON-MOTORIST LOCATION AT TIME OF CRASH
   NON-MOTORIST ACTION/CIRCUMSTANCES PRIOR TO CRASH
   NON-MOTORIST ACTION/CIRCUMSTANCES AT TIME OF CRASH
   NON-MOTORIST SAFETY EQUIPMENT
   CONDITION (IMPAIRMENT) AT TIME OF CRASH
   RELATED FACTORS – PERSON (NOT A MOTOR VEHICLE OCCUPANT) LEVEL

   We do not have plans to correct data already generated or already being generated by the typing application (2010-2013). These will serve as internal test data for us.

   We estimate that the 2014 data generated from the typing application (new data elements) should be available the latter part of 2015.

   In reviewing the 2010 -2013 data generated by the typing application, some of the anomalies we noted included the following:

   •We observed apparent inconsistencies, for example, between data coded in the elements above and data generated by the typing application.
   •We observed apparent unsatisfactory inter-coder reliability in the data generated by the typing application.
   •Assumptions and definitions used within the application differ from those understood for the rest of our traditional data elements.

6. sample query with incident joined to person and PBType for cyclists… Note that this “overcounts” in the sense that it yields 676 rows, wheres there were only 640 cyclists in the entire database. There is a solution to this in another comment…

   SELECT
   i.sLighting,pbtype.eBIKECTYPE,count(*)
   FROM ((
   2010_incident as i JOIN 2010_person AS p_bike ON i.ST_CASE = p_bike.ST_CASE)
     JOIN 2010_PBType AS pbtype ON i.ST_CASE = pbtype.ST_CASE)
   WHERE p_bike.ePER_TYP IN ('Bicyclist', 'Other Cyclist')
   GROUP BY 1,2;

   model type breakdown (auto, LTV light truck van, heavy truck, etc):
   SELECT sModel,count(*) FROM 2011_vehicle GROUP BY 1;

   mysql -u root -ppassword -D fars

7. To avoid “overcounting”, add conditions to the join’s ‘on’ clause; for example the following 3 queries all yield 618; one-and-only-one row for each fatality. In the first example, the incident is joined with the (bicyclist)person. Next the vehicle (note there is always a vehicle#1, note that sometimes there are #2, etc but those are ignored. Also of note: cyclists are assigned vehicle#0). And thirdly, the PBType table is joined…

   SELECT count(1)
   FROM (
   2010_incident as i JOIN 2010_person AS p_bike ON (i.ST_CASE = p_bike.ST_CASE AND p_bike.eINJ_SEV LIKE ('Fatal%') AND  p_bike.ePER_TYP IN ('Bicyclist', 'Other Cyclist')  ));
   
   SELECT count(1)
   FROM ((
   2010_incident as i JOIN 2010_person AS p_bike ON (i.ST_CASE = p_bike.ST_CASE AND p_bike.eINJ_SEV LIKE ('Fatal%') AND  p_bike.ePER_TYP IN ('Bicyclist', 'Other Cyclist')  ))
   JOIN 2010_vehicle as vehicle ON (i.ST_CASE = vehicle.ST_CASE AND vehicle.VEH_NO=1 ));
   
   SELECT count(1)
   FROM (((
   2010_incident as i JOIN 2010_person AS p_bike ON (i.ST_CASE = p_bike.ST_CASE AND p_bike.eINJ_SEV LIKE ('Fatal%') AND  p_bike.ePER_TYP IN ('Bicyclist', 'Other Cyclist')  ))
     JOIN 2010_vehicle as vehicle ON (i.ST_CASE = vehicle.ST_CASE AND vehicle.VEH_NO=1 ))
     JOIN 2010_PBType AS pbtype ON (i.ST_CASE = pbtype.ST_CASE AND p_bike.PER_NO=pbtype.PER_NO));

   This next one, to self-join the person table with the driver’s info; doesn’t work as i would expect it, it yields 640 rows but I don’t understand why (640 is, possibly coincidentally, the total number of cyclists in the database including those not killed)… I also noted the count(distinct(i.ST_CASE)) was 615 so I don’t understand that at all, should be 617…

   SELECT count(1)
   FROM ((((
   2010_incident as i JOIN 2010_person AS p_bike ON (i.ST_CASE = p_bike.ST_CASE AND p_bike.eINJ_SEV LIKE ('Fatal%') AND  p_bike.ePER_TYP IN ('Bicyclist', 'Other Cyclist')  ))
     JOIN 2010_vehicle as vehicle ON (i.ST_CASE = vehicle.ST_CASE AND vehicle.VEH_NO=1 ))
     JOIN 2010_PBType AS pbtype ON (i.ST_CASE = pbtype.ST_CASE AND p_bike.PER_NO=pbtype.PER_NO))
     JOIN 2010_person AS p_driver ON (i.ST_CASE = p_driver.ST_CASE AND vehicle.VEH_NO = 1 AND p_driver.PER_TYP=1 ));
   

   Another issue is counting crashes versus counting fatalities; there were 618 cyclist fatalities, but only 617 incidents — in other words, there was one double-fatality and all the others were single fatalities. Check this by selecting count(distinct(i.ST_CASE)) instead of count(1).

8. Fatalities of Pedestrians, Bicycle Riders, and Motorists Due to Distracted Driving Motor Vehicle Crashes in the U.S., 2005–2010, Jim P. Stimpson, et al

   Used FARS data to count “distracted driving” deaths by looking at driver factors. John Allen has a blog post raising some questions… Also, unrelated note: they use something called the USDA continuum codes to classify between rural and urban (“metro”). Not sure how this might vary from the more obvious (to me) ROAD_FNC, the Roadway Function Class. As the author noted to me in a private email, and I would completely agree; the categorization is quite subjective (that was noted, e.g. many years ago in the Cross study where each crash was completely re-categorized on a case-by-case basis).

9. 2012 FARS final data was released 11/14/2013
   ftp download (sans PBCAT data, of course, see comment above).

10. Queries used to gather lists of info to link FARS to ASDM (arizona specific) databases. These queries use the EXISTS scheme to produce lists of incidents; exactly one row per fatal incident regardless of number of fatalities (i.e. they count fatal collisions, not the number of people killed; for cyclists these numbers are almost always equal)…

    Get list of FARS bicyclists killed in AZ sorted chronologically; joins incident and person tables
    SELECT i.ST_CASE,i.MONTH, i.DAY, i.HOUR, i.MINUTE FROM fars.2012_incident i WHERE EXISTS (SELECT 1 FROM 2012_person p WHERE p.ST_CASE=i.ST_CASE AND p.PER_TYP IN ('6', '7') AND i.STATE=4) ORDER BY i.MONTH, i.DAY, i.HOUR, i.MINUTE;

    Get corresponding list of ASDM incidents (1st example uses unit table):
    SELECT IncidentID, IncidentYear, IncidentDateTime FROM 2012_incident i WHERE EXISTS (SELECT 1 FROM 2012_unit u WHERE u.IncidentID=i.IncidentID AND u.eUnitType IN ('PEDALCYCLIST') AND i.InjurySeverity=5 ) ORDER BY IncidentDateTime ;
    Or, a 2nd example yields same result using person table
    SELECT IncidentID, IncidentYear, IncidentDateTime FROM 2012_incident i WHERE EXISTS (SELECT 1 FROM 2012_person p WHERE p.IncidentID=i.IncidentID AND p.ePersonType IN ('PEDALCYCLIST') AND p.InjuryStatus=5 ) ORDER BY IncidentDateTime ;

    To capture pedestrian results, in Fars specify PER_TYP of both 5 and 8 (personal conveyances, typically a powered personal assistive device, arizona codes these as pedestrians).
    Fars: HIT_RUN (is only in the vehicle table)
    asdm: HitAndRunFlag

    This info is normally spread-sheeted in crashStats/FARS.xls ; and also gets put into the official AZ cyclist fatality grid kept public on Google Docs.

11. Good explanation of why the intersection data element in crash data isn’t useful, from Serge Issakov on bicycledriving group:
    There are two widely used definitions for “intersection”. I call them the narrow and broad interpretations. Neither is right or wrong – they’re just different. You do have to understand which is being used in whatever context you’re reading, because they’re meanings are very different.

    The narrow interpretation strictly limits “intersection” to refer to the space that is common to any two intersecting roadways.
    The broad interpretation of “intersection” refers to the vicinity of any place where vehicular travel from different directions can coincide.

    All of the following are intersections per the broad interpretation, but not the narrow interpretation:
    Vicinity of a driveway and a street.
    Vicinity of an alley and a street.
    The area immediately before a cross street.
    The FARS data is based on the narrow definition of an intersection. It ultimately depends on whether the officer at the scene checks the “intersection” box on the form, which the are trained to do only if the collision occurs within the intersecting space of two roadways.

    So if a cyclist riding near a curb is right hooked by a right turning vehicle before the end of the block has been reached, it’s counted as a non-intersection crash. If a cyclist is left-crossed by an oncoming motorist turning left into a mid-block driveway, it’s counted as a non-intersection crash.…more

12. Raw counts for sanity check purposes (counts people killed and not incidents):

      SELECT PER_TYP,ePER_TYP,count(1) FROM 2012_person WHERE INJ_SEV=4 GROUP BY 1 ORDER BY 1  ;
    +---------+---------------------------+----------+
    | PER_TYP | ePER_TYP                  | count(1) |
    +---------+---------------------------+----------+
    |       1 | Driver of a Motor Vehicle |    21394 |
    |       2 | Passenger of a Motor Vehic|     6389 |
    |       3 | Occupant of a Motor Vehicl|       45 |
    |       4 | Occupant of a Non-Motor Ve|       17 |
    |       5 | Pedestrian                |     4743 |
    |       6 | Bicyclist                 |      722 |
    |       7 | Other Cyclist             |        4 |
    |       8 | Pers Convey               |      149 |
    |       9 | Unknown Occupant Type in a|       86 |
    |      10 | Persons In/On Buildings   |       11 |
    |      19 | Unknown                   |        1 |
    +---------+---------------------------+----------+
    can just count up pedalcyclists and peds, respectively with:
    SELECT count(*) FROM 2012_person p WHERE PER_TYP IN ('6', '7') AND INJ_SEV=4  ; ==>> 726
    SELECT count(*) FROM 2012_person p WHERE PER_TYP IN ('5', '8') AND INJ_SEV=4  ; ==>> 4892

    But counting incidents instead will be slightly smaller numbers, since a few of these crashes involve more than one fatality…:

    SELECT count(*) FROM 2012_incident i WHERE EXISTS (SELECT 1 FROM 2012_person p WHERE p.ST_CASE=i.ST_CASE AND p.PER_TYP IN ('6', '7') AND INJ_SEV=4); ==>> 722; and 4804 peds.
    

    So I created a series of flags that split by PER_TYP and reflected them into the incident table:
    sF_Pedestrian, sF_Bicycle, sF_Motorist, and sF_OtherUnk (see 20xx_postproc.sql for definitions. As its name suggests, postproc is run after all the tables are generated; some of these flags rely on other tables).
    There are some others, sF_Motorcyclist is a subset of Motorist. And sModel is reflected up from the vehicle table into the the incident table for convenience (mine!). Here’s a possibly interesting query that reveals counts of fatality types:

    SELECT sF_OtherUnk,sF_Motorist,sF_Bicycle,sF_Pedestrian,sF_Motorcycle,count(1) from 2012_incident group by 1,2,3,4,5;

    Unfortunately, this still doesn’t solve my problem, e.g. when i join the incident and vehicle tables, i *still* get more than one result per incident — in the query below, there are 760 results; one for each motor vehicle involved, rather than one result for each of 724 incidents.

    SELECT i.VE_FORMS,count(1) FROM ( 2012_incident AS i
     JOIN 2012_vehicle as v ON i.ST_CASE =v.ST_CASE) WHERE sF_Bicycle=1 GROUP BY 1;

    Here is a sample modeled after the query used to create the AZ crash map… unfortunately i never figured out how to eliminate the duplicate cases; that’s why the query below yields, 726 results; one for each cyclist fatality rather than 722 results. Note that the “AND i.sF_Bicycle” is redundant:

    SELECT i.ST_CASE, i.sUrbanRural, p_bikeped.eLocation, p_bikeped.ePER_TYP FROM ( 2012_incident AS i  JOIN 2012_person as p_bikeped ON i.ST_CASE =p_bikeped.ST_CASE) WHERE p_bikeped.PER_TYP IN ('6', '7') AND p_bikeped.INJ_SEV = 4 AND i.sF_Bicycle;
    

    Here are the ped/cyclist by Vehicle Model type queries:

    SELECT  i.sModel,count(1)  FROM 2012_incident AS i WHERE  i.sF_Bicycle GROUP BY 1;
    +---------------------+----------+
    | sModel              | count(1) |
    +---------------------+----------+
    | Automobiles         |      279 |
    | Light Trucks        |      323 |
    | Motorcycles         |        6 |
    | Med and Heavy Truck |       61 |
    | Bus                 |       12 |
    | Unknown             |       41 |
    +---------------------+----------+
    SELECT  i.sModel,count(1)  FROM 2012_incident AS i WHERE  i.sF_Pedestrian GROUP BY 1;
    +---------------------+----------+
    | sModel              | count(1) |
    +---------------------+----------+
    | NULL                |       13 |
    | Automobiles         |     2108 |
    | Light Trucks        |     1936 |
    | Motorcycles         |       28 |
    | ATV                 |        1 |
    | Med and Heavy Truck |      284 |
    | Motor Home          |        2 |
    | Bus                 |       64 |
    | Not Reported        |        2 |
    | Other(Vehicle)      |        3 |
    | Unknown             |      372 |
    +---------------------+----------+

13. http://bikeleague.org/content/new-report-every-bicyclist-counts#comment-2946
    full EveryBicyclistCounts .pdf report.
    There are some irregularities; they seem to want to overstate the fraction of strike-from-behind fatalities, claiming they found much higher (40%) vs. FARS 2011 (27%).
    There are a couple of problems, 1) their 40% is really more like 34% (194/567) when computed in the normal way, and 2) their data is a subset and it’s not known what the characteristics of the “missing” crashes are; they gathered their list from, e.g. news stories. This means the characteristics of their sample is potentially different than the true full set in unknowable ways. “We captured 628 fatalities overall and 552 in 2012 alone. In 2012, FARS reported 726 bicyclist deaths”. P.s. in FARS 2010, the figure for rear-end was 25% (154/681). I just so happen to have that factoid in fars.xls

    I left this comment on their blog site:
    It’s customary to total tabular data, i.e. “n=”; it’s also customary when representing percentages that they total to 100% (give or take rounding error) I’m confused about the numbers in Table 1 — i totaled up the ‘#’ column and got 567. (which would make the normal way to report percentages 194/567 = 34%). Did i total it wrong? what is 567 supposed to represent?

14. This is the query i used to pull splits for rear-ends; other splits were eLGT_COND, sLighting, sUrbanRural, eROAD_FNC
    SELECT eBIKECGP, count(1)
FROM (((
2011_incident as i JOIN 2011_person AS p_bike ON (i.ST_CASE = p_bike.ST_CASE AND p_bike.eINJ_SEV LIKE ('Fatal%') AND p_bike.ePER_TYP IN ('Bicyclist', 'Other Cyclist') ))
JOIN 2011_vehicle as vehicle ON (i.ST_CASE = vehicle.ST_CASE AND vehicle.VEH_NO=1 ))
JOIN 2011_PBType AS pbtype ON (i.ST_CASE = pbtype.ST_CASE AND p_bike.PER_NO=pbtype.PER_NO))
GROUP BY 1;

    This all had to do with the LAB paper every bicyclist counts and how it was their number for rear-end was 40% (no one seems to know; lab won’t release data?!)

    You can use that query above to find that there were 154+164=318 (2010+2011) Motorist Overtaking crash group of a total of 618+677=1295. Equals 25%
    (for the curious, that includes 2+2 “other” pedalcyclists, typically trikes)

    Note/reminder on that query: the bit where it joins to the vehicle table isn’t necessarily consistent; it turns out that for 2010 and 11 bicyclists it works fine; but there were some odd cases where there was no veh #1 in pedestrian cases. In other words, there usually, but not always a vehicle #1. I’m not sure why there isn’t always a #1. E.g. in AZ, there is ALWAYS a veh #1. In this slight re-spin of that query, to get driver’s gender needed to also join the person table again, decided arbitrarily to force it to veh #1 so that the quantites would remain the same; i.e. ignore the gender of other drivers when more than one veh is involved:

    SELECT eBIKECGP, p_car.SEX, count(1)
FROM ((((
2011_incident as i
JOIN 2011_person AS p_bike ON (i.ST_CASE = p_bike.ST_CASE AND p_bike.eINJ_SEV LIKE ('Fatal%') AND p_bike.ePER_TYP IN ('Bicyclist', 'Other Cyclist') ))
JOIN 2011_vehicle as vehicle ON (i.ST_CASE = vehicle.ST_CASE AND vehicle.VEH_NO=1 ))
JOIN 2011_PBType AS pbtype ON (i.ST_CASE = pbtype.ST_CASE AND p_bike.PER_NO=pbtype.PER_NO))
JOIN 2011_person AS p_car ON (i.ST_CASE = p_car.ST_CASE AND p_car.ePER_TYP LIKE 'Driver%' AND p_car.VEH_NO=1 ))
GROUP BY 1,2;
    Factoids: males Drivers are ~ 2.5X more often involved with a bicyclist fatality compared to females. Also if you split by sHit_Run, of the drivers *identified*, male drivers are 3-4X more frequent than females. (higher incidence of risky behaviors like impaired driving?)

15. Pingback: ADOT’s Bicycle Safety Action Plan Study | Arizona Bike Law

16. pre-cursor to PBCAT? Pedestrian and Bicyclist Crash Types of the Early 1990’s “A total of 45 distinct bicycle-motor vehicle crash types are identified in the NHTSA Manual Accident Typing (MAT) for Bicyclist Accidents Coder’s Handbook. Each type is characterized by a specific sequence of causal events or bicyclist/driver actions preceding the crash occurrence….” This paper is an update to cross/fisher and other than lumping turns (e.g. right hook) with overtaking into what they called “parallel paths” seems like good stuff.

17. Pingback: Every Bicyclist Counts | Arizona Bike Law

18. Steve Magas asked, with reference to IIHS page
    “It is estimated that 21 percent of bicyclists age 16 and older who were killed in 2013 had blood alcohol concentrations (BACs) at or above 0.08 percent. Imputations for missing BACs were provided by the U.S. Department of Transportation’s multiple imputation model beginning in 1982”

    content that was here has been moved to use-of-alcohol-as-a-risk-factor-for-bicycling-injury

19. There is a handy document
    http://www-nrd.nhtsa.dot.gov/Pubs/F10CH.pdf
    “Below is a list of FARS elements that have substantial changes for 2010… If you have any questions concerning this Coding Manual, please contact NISR, Inc. at: 717-751-2823; or by e-mail at: john@nisrinc.com NEED HELP IN CODING? CONTACT NISR AT: codingquestions@nisrinc.com
    Anyways, i never heard of the National Institute for Safety Research nisrinc.com which appears to be a private contractor involved with coding and training for coding and so forth.

20. The National Bureau of Economic Research has full FARS data.
    In addition to just the data, there’s also lots of interesting stuff here, e.g there’s a description of each variable in each table, e.g. here is the 2014 description of pbtype.
    In previous years, at least, you can get tabular listings of frequency count breakdowns, e.g. here is 2014’s crash time by hour.

21. From AAA Agressive Driving Research Update 2009:
    The following driver-related contributing factors in FARS were taken as indications that crashes may have involved aggressive driving.
    • Following improperly
    • Improper or erratic lane changing
    • Illegal driving on road shoulder, in ditch, or on sidewalk or median
    • Passing where prohibited by …
    • Operating the vehicle in an erratic, reckless, careless, or negligent manner or suddenly
    changing speeds
    • Failure to yield right of way
    • Failure to obey traffic signs, traffic control devices,…
    • Failure to observe warnings or instructions on vehicle displaying them
    • Failure to signal
    • Driving too fast for conditions or in excess of posted speed limit
    • Racing
    • Making an improper turn

    The factors summarized in this list represent FARS driver-related contributing factors 26, 27, 29, 33, 34, 35, 36, 38, 39, 41, 42, 44, 46, 47, and 48.

    Along with the contributing factors analyzed here, FARS also includes a code for Road Rage /
    Aggressive Driving, which was added in 2004. T

22. Fatal bicyclist traffic collisions that are missing from FARS: moved here.

23. ph