Woman, 19, pushing disabled car struck, killed in Phoenix

Note to self to follow up to see how this gets coded in asdm and fars. Conceptually, a pedestrian was killed in a rear-end collision, a collision a driver should have been easily able to avoid if the struck vehicle had lights/flashers (it was at night). Was the disabled vehicle displaying emergency lights, or otherwise lit?

Phoenix file number?

Who is most at fault? For comparison, who is most at fault in the rear-end collision pictured here, the pickup driver or the school bus driver — the Pickup driver rammed a stopped school bus?

Continue reading “Woman, 19, pushing disabled car struck, killed in Phoenix”

Missing 2013 and 2014 Fatalities

A note about data sources

  • FARS. As of this writing the 2013 final is available, and 2014 is preliminary
  • Arizona Crash Facts; published yearly by ADOT in June of the following year
  • ADOT collision database sometimes called ASDM (I’ll refer to it as that, below); released yearly in June of the following year
  • News / Media reports; obviously this is very incomplete and hit-and-miss

Data from all these sources is located centrally on this google docs spreadsheet. which covers each bicyclist fatality occurring from 2009 onward.

Normally these are all in agreement, however there are multiple inconsistencies in both 2013 and 2014 that I cannot resolve. Continue reading “Missing 2013 and 2014 Fatalities”

MMUCC C9 Manner of Crash

Executive Summary: You may have never heard of the MMUCC (Model Minimum Uniform Crash Criteria; a set of federal guidelines), it trickles down into every state’s motor vehicle crash reporting system. It’s somewhat analogous to the relationship between the UVC and state’s vehicle codes. The problem, I should say one problem, is non-motorists tend to get overlooked. One obvious example is delved into here — the “Manner of Crash”, e.g. angle, rear-end, sideswipe, etc. is ONLY defined when it involves two motor vehicles, leaving that data-field undefined when a crash is between a MV and bicyclist. Since bicyclists are vehicle drivers, the MMUCC should reflect that. Read on for a proposed change that’s on the table, and how you can vote/comment officially: Continue reading “MMUCC C9 Manner of Crash”

Use of alcohol as a risk factor for bicycling injury

Skip below if you’ve visited this page specifically to see the Johns Hopkins’ study.

FARS Alcohol Results

The FARS data has a number of alcohol (and drug) fields — the fields ATST_TYP, ALC_RES relate actual test type, and results. To simplify things, I’ve added a derived field sALC_RES to breaks down test results into: negative, .01 through .07, and .08+, or no results. Most fatally injured drivers, cyclists, and pedestrians do get tested. (for those that are not, there are imputed results available from a separate data file, see below).

Note that the ALC_RES field, the numerical result, has changed over the years, before 2015, it was listed as the number of hundredths of a percent BAC, e.g. 0.12 was coded as 12. In 2015 and later, it is coded as thousandths of a percent BAC, so the same result would be listed at 120.  The logic for this is encapsulated in the file 20xx_person.sql in the synthetic value sALC_RES: intox / not intox.

FARS and Drug Testing

The coding for drug results in FARS is similar to the alcohol scheme, except there are no quantitative results, only positive/negative. Also there is no equivalent to the imputation of results for drugs.

FARS coding: positive results for drugs shows up in the field DSTATUS=2 (i.e. “test given”) and DRUGRES1, 2, or 3 have a number up to 999; all in the person table. 0 meand test not given; 1 means No Drugs Reported/Negative. Potentially illicits are in groups generally in hundreds, e.g. 100-295 are narcotics, 300’s are depressants, 600’s cannaboids.  Anything 996 or above are various meanings for unknown.

Examples: Zolpidem (Ambien) is 375. See pages 579-594 of the FARS Coding and Validation Manual.

FARS and Imputation of Alcohol Results

Driving while intoxicated has been recognized as a significant serious safety factor for decades; at the same time, it’s long been recognized that many involved in fatal traffic collisions (mostly drivers but sometimes peds and bicyclists) do not have recorded alcohol test results. This nhtsa report published in 2002 explains most of the deep background and terminology on the scheme to “fill in” missing results: Continue reading “Use of alcohol as a risk factor for bicycling injury”

Non-traffic traffic crashes


This is a placeholder for info for what I am referring to as a non-traffic  (you can click there to get a list of incidents like this) traffic crash. Which I define as some sort of traffic crash that doesn’t get reported in official traffic crash stats. The most common reason these incidents might fall into this category is they occur on private property, like a parking lot, or private streets, like cyclist Robert McCain who was killed in a collision on a private street, or inside a building driver-slams-into-day-spa-1-dead-4-go-hospital. Other reasons, especially for cyclists, is for crashes “count” they have to include at least on motor vehicle in transport; so e.g. a cyclist crashing into a parked vehicle is not counted; nor are cyclists who have “simple” falls, or bike-bike crash, even when resulting in death (for example in 2014 see Karl Gerschutz and Jim Walen fatalities, respectively). Likewise bike-bike, or bike-ped crashes are not reportable as traffic crashes. Continue reading “Non-traffic traffic crashes”

Scottsdale Golf-Cart Shuttle Crash

First off; this has nothing whatsoever to do with a bicycle. This is interesting in light of the pedicab crash last year (golf carts apparently are being used to provide non-taxi shuttles in the immediate downtown area); and also wondering if this will generate an ACR? (Arizona Crash Report). I assume it would but you never know. For example, two of last year’s (see 2013 grid) bicyclist fatalities did not result in an ACR for various reasons; one a bike-MV collision on private streets, and another bicyclist hit-and-run with an ATV. An ACR is generally required if a crash involves at least on motor vehicle on a “trafficway”… see do-all-crashes-count for more. Continue reading “Scottsdale Golf-Cart Shuttle Crash”

A Tale of Five Phoenix Bike-MV collisions.

Fault was assigned to the bicyclist in four of the five reports. In two of those, the bicyclist was doing something obviously illegal/wrong (riding the wrong way in the roadway, and running into a stopped vehicle). However, the other two do not support that finding — in one a motorist violated a bicyclist’s ROW by turning into it, and in the other a bicyclist was struck by a motorist who was attempting to turn right-on-red.

Perhaps the reason Phoenix has a persistently high bicyclist MaF (Most at Fault) rate is the officers are often not investigating bike-MV crashes correctly?

Continue reading “A Tale of Five Phoenix Bike-MV collisions.”

Driver slams into day spa; 1 dead, 4 go hospital

[Updated; as expected/suspected this death and injuries DO NOT appear in official crash records from ADOT, nor will they appear in the FARS when that is released. See this comment for how I checked asdm]

9/27/2013. A story like this, besides being a tragedy, tends to make headlines (even going national,  usatoday.com story) but, Seriously, how often does stuff like this happen? Apparently regularly; like the shopper killed inside a Tucson convenience store in July…. or… 11-year old boy dead in a Phoenix parking lot in May… or … 2 Dead in Phoenix after pickup slams into bus stop  in March… or… 1 Dead at a Peoria Walgreens sitting on the bench in front of store in 2010… This is just what i noticed reading the paper; These were all in the recent past, just in Arizona. This is not a complete list! ha. Continue reading “Driver slams into day spa; 1 dead, 4 go hospital”

GIS, mapping, crash reports vs. ASDM

Some notes on mapping using the latitude/longitude; and the ASDM (Adot Safety Data Mart) dataset.

Here is a detailed breakdown of a crash chosen more-or-less at random (I wanted to choose crashes that were *not* at intersections) that occurred 2012-10-12 at a driveway just east of 51st Ave on Indian School Rd. (if the link doesn’t work use 33.494971/-112.167771, the lat/long specified in ASDM). It is ADOT incident number 2672854, Phoenix file number 12001836231 (though it was listed as 201836231 in ASDM). Continue reading “GIS, mapping, crash reports vs. ASDM”

Collision Manner

[Warning/correction not yet made: in the table below where it says MV-only, that’s not quite correct, it’s really incidents where no pedalscylists are involved. The MV-only calculations should also exclude pedestrians; this can be accomplished by saying u.eUnitType LIKE ‘PED%’  In the query below i corrected it but didn’t correct the table; the percentages don’t really change since there are relatively few ped crashes ]

Here is a breakdown of Collision Manner, and rates, for MV collisions (i.e. one or more MV, and not involving a ped or bicyclists) compared to bike-MV collisions.

The megatrends are that rear end collisions are, by far, the predominant manner of collision for MVonly crashes; wheres for bike-MV crashes this manner is quite infrequent — almost twelve times more frequent. For bike-MV crashes, the predominant manner is angle, i.e. so called “turning and crossing” movements (although left turn is broken out as a separate manner).

The 38% rate of REAR END crashes for MVs actually understates the rate — if you back out the number of SINGLE VEHICLE crashes; you see that nearly half of all multi-car collisions are REAR END(!). 38,499/(101,055 – 18,647) = 47%. Inattention? Does this mean that motorists actually are more attentive to same-direction traffic when it’s a bicyclist, compared to another motorist? hmmm.

| eCollisionManner             | MVonly| MVrate |bikeMV|bikerate|MV:bike ratio|
| REAR_TO_REAR                 |   287 | 0.0028 | NULL |   NULL |        NULL |
| UNKNOWN_99                   |   859 | 0.0085 |   40 | 0.0189 |  0.44973545 |
| REAR_TO_SIDE                 |   895 | 0.0089 |   11 | 0.0052 |  1.71153846 |
| SIDESWIPE_OPPOSITE_DIRECTION |  1244 | 0.0123 |   46 | 0.0217 |  0.56682028 |
| HEAD_ON                      |  1438 | 0.0142 |   45 | 0.0212 |  0.66981132 |
| OTHER_97                     |  3160 | 0.0313 |  404 | 0.1905 |  0.16430446 |
| SIDESWIPE_SAME_DIRECTION     | 10727 | 0.1062 |  124 | 0.0585 |  1.81538462 |
| LEFT_TURN                    | 11888 | 0.1176 |  189 | 0.0891 |  1.31986532 |
| ANGLE_FRONT_TO_SIDE          | 13411 | 0.1327 | 1194 | 0.5629 |  0.23574347 |
| SINGLE_VEHICLE               | 18647 | 0.1845 | NULL |   NULL |        NULL |
| REAR_END                     | 38499 | 0.3810 |   68 | 0.0321 | 11.86915888 |
total num of MVonly crashes = 101,055.  total num of bike-MV crashes = 2,121
source: 2012 ASDM

Continue reading “Collision Manner”

Motorcyclist killed after crash in Phoenix

This appears to be highly typical mode of motorcyclist fatality

A motorist makes a bad left at intersection, striking oncoming motorcyclist.

Had this been a bicyclist-MV collision, it would be a crash type 212 – Motorist Left Turn—Opposite Direction, commonly called a “left hook”. This is a relatively uncommon fatal crash type, just 12 of 617 bicyclist fatalities nationwide in 2010 according to FARS. One supposes that the relative speeds involved make this far more likely to be deadly for motorcyclists than for bicyclists.

One wonders how the police handle such cases; from the description, it appears the motorist should be cited for 28-772 making a bad left, and charged with 28-672. since a death resulted.

Motorcyclist, 20, killed when driver, 84, failed to yield

By Yihyun Jeong
The Arizona Republic-12 News Breaking News Team
Thu Apr 11, 2013 4:03 PM

A motorcyclist died after a car tried to make a left turn in front of the motorcyclist at Cave Creek Road and Union Hills Drive in Phoenix Wednesday, officials said. Police responded to a serious injury collision at the intersection of Cave Creek Road and Union Hills Drive around 4 p.m., Sgt. Steve Martos, a spokesman for the Phoenix Police Department said. Police were told that a motorcyclist was down and his motorcycle was on fire. The motorcyclist, Angelo Wright, 20, was taken to a nearby hospital where he died of injuries, Martos said. The police department’s Vehicular Crimes Unit responded to the scene and determined that an 84-year-old female driver of a Volkswagen Rabbit, was heading east on Union Hills and tried to make a left turn at Cave Creek Road to travel north, Martos said. The driver failed to yield to Wright, who was riding a Harley Davidson motorcycle, and collided with him. Wright was not wearing a helmet.


Was the driver cited and/or charged? Dunno. Would have to get police report and get driver’s name, since police didn’t say, and then do a lookup.


It’s like a war zone out there…

Walk in the crosswalk; get hit with flying debris from a red-light-runner? Seriously, how often does this happen? (note to self — check ASDM for 2013, whenever that becomes available, and see if the peds show up in the collision — my guess is no but i really don’t know what the story is)

azcentral.com: …At about 9a.m. Sunday (3/10/2013), Kaylynn Ruth Kayanie, 25, was driving west on Broadway “at a high rate of speed” when she ran a red light and struck another vehicle that was traveling south on Priest Drive… The driver of the southbound vehicle, a 45-year-old woman, was ejected and taken to the hospital with life-threatening injuries…  Two pedestrians who were in the crosswalk heading south on Priest at the time of the collision were hit by flying debris and taken to the hospital, where they were treated for cuts and bruises and released the same day, she said. Impairment does appear to be a factor in that case and investigators are awaiting drug-test results

The same story notes that the suspected impaired driver Veronica Muckerman made a bad left, killing a motorcyclist Elsa Tovar last week; was apparently driving without a license, due to being revoked in 2011 for another dui.

ADOT Traffic Collision Database

It turns out (who knew?) that ADOT sells their crash database for a nominal sum. I purchased the 2010 version, the latest full-year available (2011 is supposed to be ready in July). This data is either similar to (or synonymous with) something referred to as the Arizona (or ADOT?) Safety Data Mart — thus the acronym asdm sprinkled throughout.

The data is delivered on a DVD which contains three large text files; corresponding to Incident, Person, and Crash -level data. It is also accompanied with 5 photocopied pages of “Column Headings”, and about two-dozen pages of photocopied “Definitions”. [it is really strange that they would distribute this information on paper!?]. I was surprised to find out that very little of the data aligns with FARS/GES, which seems quite strange to me.

Anyway, I pieced together most of the info using those photocopies and assembled it for ready-reference in a spreadsheet adsm.xls , there is one worksheet for columns (fields) and another for defintions (ENUM). I don’t have all the definitions there, some i didn’t care about and were very lenghy, like vehicle color, state abbreviations, and so forth; nearly all of that is avaible at the referece material

Reference Material

  • Background info on the ALISS database,  and related terms: AIDW (Adot Information Data Warehouse), and Safety Data Mart.
  • My spreadsheet adsm.xls; provides a unifying list of fields, and enumerations

If the document links, above, go dead; there are local copies in asdm/.

The Database

azbikelaw.org is making this data available publicly via a MySQL database accessible via the internet. Special thanks to Justin Pryzby for completing this work.

Years 2001-2003, and 2009-Newest
hostname:     mysql.azbikelaw.org
databasename: asdm
username:     asdmuser
password:     Contact us
access via myPhpAdmin currently not available

Crash Map 
See crashmap-data

The tables were loaded from raw text files into tables and then re-processed with to create 3 tables: 2010_incident, 2010_person, and 2010_unit.

Interactive (e.g. myPhpAdmin, or MySQL Query Browser) users will want to refer to views: pretty_2010_incident, pretty_2010_person, and pretty_2010_unit which substitute enumerated fields for the original data fields, and leaves out the description-only fields. For example there are 3 fields all carrying the same information: TravelDirection = 1 (int), TravelDirectionDesc = “NORTH” (text), and a synthetic field eTravelDirection=’NORTH’ (enum). The first two are not in the pretty_unit view, while the unit2 table has all three. The table unit should not be used, and is likely to be dropped to save space.

Furthermore, all database users should rely on the enumerated field, eTravelDirection in the above example, and not on the integer values, as they are liable to change in subsequent years, whereas the enumerations can be kept consistent when subsequent data from a new year is imported.

There are some additional helper tables, such as LOVCity, LOVCounty and county which are handy for looking up things like city codes (e.g. Phoenix has a CityID of 241).

Data Completeness

A word about data completeness: As explained in more detail in this comment below. The data received from ADOT represents data frozen in time mid-year (currently the cutoff date appears to be May 31 of the following year). Data does continue to trickle in, however, and for unknown reasons (why shouldn’t 6 months be enough time for PD’s to send all their corrected data from the preceding year?). ADOT initially releases a version of Crash Facts based on the May 31 data; and then subsequently re-publishes an update a few months later. My dataset is frozen in time at the May 31st (or June 1st) version; which doesn’t make me happy but tends to be statistically insignificant.

Trickling example: The number of pedalcyclist crashes in 2012: 2121 / 2134 / 2141 as of May 31 2013 / Oct 28 2013 / Late Nov 2013. I.e. 2012 crash reports continue to trickle in in Late November of the next year!

GIS issues: a widely varying (from year-to-year) number of incidents have a lat/long of zero. More about that at crashmap-data.

I have noted in a few cases (only fatals?) where the data in asdm seems to be massaged relative to the acr, see comment below about “fiddling” (also called “fudgery”) with the CollisionManner and PostedSpeed.


The first dataset available is 2010, which is the most recent full year available from ADOT.


I went backwards and purchased the 2009 data; I believe this is the oldest dataset available under the current schema — which kindof makes sense as the ACR form was re-vamped for 2009.


2011 data became available in mid-July 2012 ( I received it in the mail from ADOT Risk Management 7/27); and is now ready for querying. This year the data was distributed all on a CD, so thankfully no photocopies of headings and definitions hanging around.

As might be supposed, the tables are named 2011_incident, 2011_person, and 2011_unit ; along with the pretty views. The table structure is identical to 2010.


2012 data became available June 10, 2013, according to an email report. It once again was $15, and a CD was delivered very promptly upon mailing ADOT Risk Management a check. This year the contact was Sarah Greener, Litigation & Public Records Supervisor. The data fields were identical to last (and in fact, all previous) year. I only needed to edit the .sql’s to substitute in 2012_ instead of 2011_ or whatever.


2013 became available in mid-June as usual and the Adot risk mgmt people got it promptly mailed out to me for $17 (2 charge for mail, which apparently was waived or forgotten in previous years. See also arizona-crash-facts-2013 and

One area where data quality has degraded noticeably is with the number of incidents listing a geo-location of 0,0. Many of these also have no OnRoad nor CrossingFeature. This year there are almost 3,000 such incidents; last year there were only 663; and prior years were 2,679, 2,017, and 9,048(! 2009 was a bad year for much inconsistency) respectively. I don’t really see a pattern, e.g. as to particular agencies, in other words they’re sprinkled around the state. The 3,000 include about 50 bicyclist incidents including some with incapacitating injury. I am also pretty shocked to find among the 3,000 forty fatalities and over a hundred incapacitating injuries — this just seems horribly lax.

select count(*) from 2013_incident where Latitude=0 OR Longitude=0;


Everything seemed fine until discovering there’s something wrong with UnitID this year; Up until this year, UnitID (as well as PersonID and IncidentID) have all been unique, even year-over-year. This year, however, there are many (~ 30,000?) “reused” UnitIDs. It’s not clear if this is a bug, or just what… Is the data ok otherwise? who knows. What’s clear is ADOT traffic records people prefer to toil in anonymity and don’t deign to bless us (the general public) with any explanations of their work.

e.g. UnitID=4976526 appears in both 2012 and 2014. Boo. You can test for this condition by running the following (long, it takes over a minute) query; the result should be 0 rows, but it returns some 30,000!:

SELECT UnitID, count(1) c FROM (SELECT * FROM 2014_unit UNION SELECT * FROM 2013_unit UNION SELECT * FROM 2012_unit UNION SELECT * FROM 2011_unit UNION SELECT * FROM 2010_unit UNION SELECT * FROM 2009_unit) x GROUP BY 1 HAVING c>1 ORDER BY 2;

This seems rather odd; I expect to be able to use any of those IDs as a primary key, regardless of year, so that can no longer work. In any event to cover up adot’s short-comings; I’ve added XX0,000,000 where XX is the last two digits of the year to UnitID. For now, just did it to the 2014 tables…

UPDATE 2014_unit SET UnitID = UnitID + 140000000;
UPDATE 2014_person SET UnitID = UnitID + 140000000;

Also, there is an error incident=2935635, is incorrectly listed as 2 bicycle crash; it should be bike-MV (there is no such thing a crash not involving any motor vehicle).


I had failed to notice at the time, but in 2014 four fields were added to the tables (this means, by inference, the Arizona Crash Form was updated, also):

  • incident table: Offset Direction , Secondary Crash Flag (The latter seems to not have a def’n?)
  • unit table: Distracted Driving and Distracted Driving Desc

These fields are added to the “end” (i.e. the far right-hand side) of the respective .csv files. As such, they can be safely ignored, as is presently done for 2014.

I would have thought this would break the “ADOTALL” tables, but it still seems to work, albeit without the new fields…

All Years, 2009-2015

I found it was becoming a drag to re-run queries across year; so in Oct 2014 I created three tables, incident, person, unit that hold all contiguous year in 3 big tables. As new years come by I will add them as well. Complicated queries to these tables need to be somewhat judicious as the amount of data gets larger and larger; mysql.azbikelaw.org is hosted on my home server and is an old laptop!

There is a shell script to build all year’s tables at once, build-asdm-all.sh which is in the ADOTALL folder (analogous to ADOT20xx folders). SEE ABOVE, 2014 FOR A PROBLEM with this approach — unfortuantely UnitID is no long unique, so it can’t be a primary key.

See note, just above, about year 2015 data and new fields.


Justin got these discs from Adot in September 2013 (i.e. these are way out of chronological order)… in any event they still have exactly the same columns/tables layout. The only exception was the values STOP and YIELD in ControlType in the Unit table are reversed; so the definition of eControlType had to be adjusted. (so they are the reverse of 2009-2012 values).


[warning: the table referred to below, farsxref, has fallen out of date] : As of now, FARS (federal, fatality-only data) for years 2010 and 2011 is available with full PBCAT crash typing, see 2010 FARS contains PBCAT data for more info on PBCAT. There’s a somewhat hand-crafted table called farsxref which contains an adot IncidentID, Year, and Fars identifier (the adot IncidentID is unique, for fars, the year and identifier have to be combined to uniquely identify an incident). Anyway, in the table is row for pedalcyclists only (at the moment; there’s not particular reason it shouldn’t have all fatals). Anyways, FARS data will eventually be added to the incident dump query.

Database improvements?

See comment below for a list of suggestions.

Shell Scripts

There are shell scripts to build the databases from raw text input files; build-asdm.sh build-fars.sh and there are also classifying scripts which produce sometimes interesting snapshot views of the tables; classify-asdm.sh and classify-fars.sh

See azbikelaw-gets-its-own-server (the slug is now just azbikelaw) which is password protected; actually it’s a “private” post, it’s invisible (not found) unless logged in.


In no particular order, here are some examples for routine lookups.

By Street Name(s), use OnRoad and/or CrossingFeature.  This finds incidents near Catalina Hwy and Houghton Rd.

select * from 2013_incident where (OnRoad LIKE "Catalina%" and CrossingFeature LIKE "Hough%") OR (OnRoad LIKE "Hough%" and CrossingFeature LIKE "Catalina%");
or to get a feel for dangerousness level, instead select things like:
SUM(TotalMotoristsInjuries), SUM(TotalNonMotoristsInjuries), SUM(TotalMotoristsFatalities), SUM(TotalNonMotoristsFatalities)

By Street Name and Latitude/Longitude:

This example was to find any crashes along Park Ave, in Tucson, between Park Ave and Irvington (on the North) and Valencia (to the south), where a road diet was done sometime in 2014.  Since it’s a north/south road, use Latitude to discriminate. To find Latitudes/Longitudes use a tool such as latlong.net. You can, by the way, form a google maps url if you know lat/long like so:  maps.google.com/?q=32.16334,-110.95618 . In any event, the Latitudes in the query are “opened up” a bit to catch more crashes near the begin/endpoints:

SELECT eCollisionManner, count(*)
FROM 2012_incident AS i WHERE 
(OnRoad LIKE "Park Ave%" OR CrossingFeature LIKE "Park Ave%") AND 
(Latitude > 32.13410 AND Latitude < 32.16334 ) AND
(IncidentYear > 2008 AND IncidentYear < 2013) AND
CityId = 310 GROUP BY 1;

An example of an east/west road, University Drive in Tempe, between Priest Drive and ~ Ash Ave can be found here; (which is more complicated because it only selects MV-Bike crashes, by the way).

Other States

CA: The Statewide Integrated Traffic Records System (SWITRS) is collected and maintained by the California Highway Patrol (CHP). UC Berkeley maintains an elaborate geo-coded front-end to the database they call TIMS (Traffic Injury Managment system) and great in-depth learning tools for the database itself.

OH:  Ohio Traffic Crash Reports as reported to the Ohio Department of Public Safety for the current year and the past five years. I think they are full reports(?)