Tag Archives: ADOT

Funding proposal would add BLs on Chandler Blvd over I-10

Chandler Blvd is vastly wide, and lacks bike lanes west of ~ 54th St.

This is pretty interesting for those of us in the Ahwatukee / Chandler area…

City of Chandler has made a request for TA/CMAQ (see also  alameda-drive-tempe-bicycle-pedestrian-proposal for more about TA/CMAQ) funding to add BLs on Chandler blvd between where they stop now (~ 54th St) and continue them to the “far” side of the bridge (City of Phoenix jurisdiction). It’s about .4 miles and the bottom line cost estimate is ~ 600,000 (details are in the link below). The main money driver here is some of the area needs to have curb/gutter/sidewalk relocated in order to make room. See the area on Google Maps. Continue reading Funding proposal would add BLs on Chandler Blvd over I-10

Bicycling Prohibitions on Highways/Freeways

From time to time, the question about bicycling on freeways (a.k.a. highways, or more officially “controlled access highways”) comes up. Just what are the rules? This ADOT page has a good summary:

…Of course, not all roads are open to pedestrians and bicyclists – pedestrians are prohibited from walking along all controlled-access highways. Bicyclists are permitted by ADOT policy to ride on the shoulders of controlled-access highways, except where prohibited…

Let’s dig a little deeper… By statute, the state (ADOT) or local authorities may prohibit bicyclists’ use of any part of a controlled-access highway: Continue reading Bicycling Prohibitions on Highways/Freeways

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 synonmyous 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).

ADOT’s Bicycle Safety Action Plan Study

ADOT’s Bicycle Safety Action Plan Study ( the study’s home-page link is now dead ) is a multi-phase plan to assess and improve bicycle traffic safety, with emphasis on Arizona state highways. In urban areas that often means the interchanges. Here are direct download links to final reports:

During the five-year study period “There were a total of 9,867 bicycle crashes statewide in Arizona…  crashes that occurred on state highways were extracted from the statewide data set. There were 1,089 bicycle-motor vehicle crashes reported on state highways between January 1, 2004 and December 31, 2008” from this a focus area was determined, within the focus area there were 746 bicycle crashes.  PBCat was used to crash-type all 746 of these collisions.

Thus this data set accounts for a small minority of bike-MV crashes, around 11%. But Working Paper 1,  table 15 offers a useful comparison between the studied data and all statewide data. For example, we see the same suspiciously-high percentage (24 to 25%) of “other” fault ascribed to bicyclists as with other studies. As I’ve written before, the “other” fault is generally the result of a poor/improper crash investigations that tends to wrongly faults cyclists who are doing nothing illegal (see Understanding Collision Summaries) — this is statistical proof of poor-quality investigations are a statewide problem for bicyclists. This is a shortcoming of the crash reports, and not the BSAP;  in Working Paper 1, figure 20, something they call “primary contributing factor” by crash group is assigned overwhelmingly to motorists (67%), and only 24% to bicyclists.

Press coverage

There was a lengthy, front page A1,  Arizona Republic article by Sean Holstege on Sept 17, 2011 which perhaps was intended to be about the plan but did wander, understandably, to general topics. For example they make great hay out of the per capital fatality stats, without any discussion of how to interpret them — e.g. how weather probably affects them, with Arizona being more of a year-round cycling state; or a higher per capita usage, e.g. Arizona has significantly higher (than US) percentage of commuters (according to census figures, see Working Paper 1, Table 1 — Arizona is 0.9% versus 0.5% nationwide).

The story, as many “bicycle safety” stories do, lacks context of traffic in general. So, for example, there was a chart of the number of bike-MV collisions (about 2,000/year total). There is no mention of the fact that that represents only a tiny fraction of all MV collisions ( which ran well over 100,000/year over the study period). And though it mentions the number of fatalites, say 25 in 2008 — it never mentioned the total number of traffic fatalities (it runs around, and lately something under, 1,000 per year).

So here are some hard numbers, over the five year period 2004 – 2008 there were 681,466 MV crashes, of which 9,730 were bike-MV — a little less than 1.5% (taken from the historical overview in the 2009 Bicyclist Fatality study, which were gleaned from AZ Crash Facts — note that the numbers a slightly different in the BSAP, but I don’t know why). The number of fatalities is 4,943 total, 132 bicyclist; or 2.67% — so bicyclist fatalities were somewhat over-represented but not dramatically so.

Note that the ADOT plan by design is aimed at the small percentage of bike-MV crashes that occur on the state highway system. “The majority of bicycle crashes in Arizona (approximately 90 percent) occur on local, city, and county roadways outside of ADOT jurisdiction”

Also, by the way, the article inaccurately stated that the BSAP recommends a mandatory taillight law. That was in an earlier draft but was since removed — I don’t believe there is adequate evidence to support the additional burden on cyclists. The article does correctly mention that the BSAP recommends state-level sidewalk law clarifications, which seem like a worthy endeavor, given the huge proportion of sidewalk-related collisions, along with the current legal murky morass that currently exists when cyclists who cycle on the sidewalk subsequently collide with vehicles in crosswalks and driveways.


The raw data from the 746 crashes studied can be viewed and mapped at this google fusion table.  I also read the data into a mysql database, bsap, password access available upon request using a scheme analogous to asdm and presently only available on mysql.azbikelaw.org, and not on the godaddy server.

The crashes studied were full-blown pbcat 2.0 with crash types, groups, cyclist’s direction; that’s the good news. The bad news is it’s not clear how that relates to bicyclist crashes in general.

Fatal vs. non-fatal: The dataset mentioned above, the 746 crashes includes only 4 fatalities and is data only from the focus area. The BSAP report has several charts and graphs that refer to 24 fatalities. The distinction is that the total 24 is over the entire SHS (state highway system); whereas the 4 is only the focus area. I don’t have the raw pbcat data for the 24 (or rather , the other 20).

BSAP-esqe Data

Here is a bunch of data extracted via query from ASDM data  — currently covering years 2009-2013. In other words, it is an attempt to automate the types of data presented in the BSAP but applied to all Arizona bike-MV crashes, not just state highway system.

Summary of PBCAT results

Many of the motorist drive out’s (from stop signs, or performing a right-turn on red) involve counter-flow sidewalk cyclists. Out of the 746 crashes studied here are the top 5:

Table 5 – Top 5 Crash Types 
Percentage of SHS Focus Area Crashes Crash Type Description 
103 13.8% Bicyclist Ride Through ‐ Signalized Intersection
 83 11.1% Motorist Drive Out ‐ Sign‐Controlled Intersection
 76 10.1% Motorist Drive Out ‐ Right‐Turn‐on‐Red
 71 9.51% Motorist Drive Out ‐ Commercial Driveway / Alley
 61 8.17% Motorist Drive Out ‐ Signalized Intersection
746       Total SHS Motor Vehicle‐Bicycle Crashes

Also interesting title of this PBIC presentation How to Create a Bicycle Safety Action Plan: Planning for Safety. It’s from the toole design people so, as expected, is filled with nacto and facilities stuff.

ADOT 2010 Crash Facts

ADOT’s 2010 Motor Vehicle Crash Facts has just been released.

Highlights are the total number of fatalities continued to fall; there were a total of 762 persons killed in 2010, a 5% decrease from the year before.

There were 19 bicyclists killed on Arizona’s road in collisions with motor vehicles in 2010, which compares favorably with the 25 killed in 2009. That means there are two (possibly three) missing from this tally for 2010.

The MOST COMMON DRIVER VIOLATION is (remains) Speed too fast for condition

There were 106,177 crashes in total, of which 1,914 were bike-MV crashes.

Dangerous by Design

[updated regularly; the one release in May 2014 can be found at smartgrowthamerica.org I don’t think anything much has changed Phoenix and Arizona still rank “high” (bad) ]

While we’re on the subject, t4america.org released the latest version of their recurring report Dangerous By Design 2011; where metro-Phoenix has a recurring, starring role as a particularly dangerous place for pedestrians — the 8th worst rate in the US. The only places significantly higher are basically several (!) metro areas in Florida.

Bad for pedestrians tends to translate into bad for motorists and bicyclists, as well — in other words, we’re all in this together.  Arizona’s motorist fatality “VMT rate  is over twice as deadly as Massachusett’s. The disparity in per capita rate, since Arizonans drive more miles, is even worse…. more

But you are not likely to hear anything about how or if or why Arizona isn’t closing the gap; or even that a gap exists! — rather that deaths overall have merely fallen. Here is a typical new-release-style story: azfamily.com story

Back to the DbyD report, they have this concept called PDI, the Pedestrian Danger Index; Phoenix-metro at 132 is many times worse than, for example, Boston-metro at 21.6.

And just to throw out a factoid, for the year 2009 (the most recent year for which detailed stats are available) there were more bicyclists killed within the City of Phoenix (9) than were killed in the entire state of Massachusetts(6).

The population of Phoenix is 1.5M versus State of Massachusetts having 6.5M…. The C.O.P., accused rightly as being an enormous-sprawling place covers 516 square miles, the state of Massachusetts 7,840 square miles of land area.

John Allen’s blog reflecting upon the fact that in the DbyD report, the Boston-metro area came in dead last (SAFEST!) of all large metro areas in US — “Strange, isn’t it — the Boston area has repeatedly been derogated as supposedly having the nation’s craziest drivers”.

Arizona’s Rural Highway Traffic Safety Problem

A couple of days after the data was released, and somewhat to my chagrin, the arizonarepublic/news/articles/2011/09/02/20110902arizona-deadly-rural-roads.html did a fairly long and detailed piece on what ADOT is doing to identify and address rural highway problems… though, interestingly, the latest Crash Facts shows a steeper decline in rural as opposed to urban fatalities.

So far, no one that I know of, has said or suggested that Arizona’s high rate of rural fatalities is what accounts for Arizona’s overall high traffic fatality rate. Perhaps that is so?

As mentioned in the article, rural fatal crashes tend to be single-vehicle — though that is a little misleading because a bike-MV, or ped-MV crash is defined as a single-vehicle.

Here are the number of fatal crashes split by urban/rural for 2009 and 2010:

Peds fatal crashes, total/urban/rural: 156 / 102 / 54 ( 2009: 121 / 77 / 44)

cyclists killed, total/urban/rural:         19 / 17/ 2 ( 2009: 25 / 17 / 8 )

(all inclusive) Number of fatal crashes, total / urban / rural: 698 / 354 / 344 (2009: 709 / 299/ 410)

Here is some discussion of the 2010 National results: early-estimate-of-motor-vehicle-traffic-fatalities%C2%A0in%C2%A02010/

Arizona bicyclist fatalities 2003-2006

How did I miss this one?

Should State DOTs Prefer Bicycle Lanes or Wide Curb Lanes? A.L. Dennison, 2008 [.pdf] This report was produced for ADOT in cooperation with US DOT/Federal Highway Authority.

Bicycle facility advocates have long debated the respective merits of bicycle lanes (BLs) and wide curb lanes (WCLs); this report investigates their claims… This study found no apparent relationship between fatal bicycle/motor vehicle collisions and type of bike facility… A significant handicap to any analysis of bicycle travel or safety is the paucity of reliable data.

Of great interest to me was the categorization of bicyclist fatalities over a four year (2003-2006) period, based on police reports. Somehow I missed this report entirely even as I echoed its complains about the “paucity of reliable data” for cyclist/traffic collisions while researching Manner and Fault in Bicyclist Traffic Fatalities: Arizona 2009.

According to my (from ADOT’s Arizona Crash Facts) records there were 15, 27, 35, 30 fatals in 2003, 2004, 2005, and 2006, respectively. This totals 107, but the report says that “We obtained 85 (97%) of 88 microfilmed fatal bicyclist/motorist crash reports submitted to AzDOT by police agencies in Arizona between 2003-2006”. The missing 3 (88-85) are explained in a footnote. But one wonders, where are the other 19? (=107 – 88). Does that mean that not all fatalities are submitted to ADOT? … so the answer i am told is that it covers the time period 17-Oct-2003 to 25-Sept-2006, which makes sense.


New Crash Forms / ALISS database

ALISS — stands for Accident Location Identification Surveillance System… it apparently feeds the ADOT Safety Data Mart.

As of Jan 1, 2009(?); Arizona has new forms. The old “Arizona Traffic Accident Report” will now be an “Arizona Crash Report” (as an aside, if you don’t know why that is significant, please see here). You can see what’s on the new form here, in a presentation by Rick Turner; includes the tantalizing bullet point “Customers Will Be Able to Query, Analyze and Retrieve Their Own Crash Data”. Continue reading New Crash Forms / ALISS database