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 (or CD) 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. [UPDATE: as of 2018 I have online access and can download the files directly so no longer need to order the DVD/CD]

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

UPDATE: adot re-organized their site and now has a lot of this infor organized on one page at Arizona Crash Report Forms, Resources and Training Events including as of this writing, 2021 crash forms, manuals, and training presentations.

Below is some historical info collected back as far as 2010:

  • 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.

2010

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

2009

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

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

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

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;

2014

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

2015

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…

2019

Beginning 2019 there were some changes —  in the incident table, they changed how the Totals were reported in an odd way, the columns:

TotalMotoristsInjuries,TotalNonMotoristsInjuries
became:
TotalMotorists1,TotalNonMotorists1

the latter (non motorists) seems to be exactly as it was. However, for motorist injuries it’s not. The field now called TotalMotorists1 appears to be simply the number of motorists(?i haven’t verified that).
Can (i’m guessing) compute Total motorist injuries by subtracting non-motorist injuries from TotalInjuries.

All Years, 2009-2020

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 — unfortunately UnitID is no long unique, so it can’t be a primary key.

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

This is current at least through data year 2020.

2001-2003

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

FARS and ASDM

[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.

Examples

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(?)

15 thoughts on “ADOT Traffic Collision Database”

  1. TUTORIAL ON HOW TO DUMP A FULL RECORD
    when an exact date is known…

    Step 1. Determine IncidentID — in this case the date is known, and the time is just before 7AM. The following query finds all bicyclist collisions on a given date (in the year 2010):

    SELECT IncidentID,IncidentDateTime,Onroad,CrossingFeature FROM 2010_incident i WHERE EXISTS (SELECT 1 FROM 2010_unit u WHERE u.IncidentID=i.IncidentID AND u.eUnitType IN ('PEDALCYCLIST')) AND IncidentDate="2010-09-23" ORDER BY IncidentDateTime;

    -> we see that IncidentID of interest is 2446148

    Step 2. Dump the entire record (the -E outputs data “vertically”):

    mysql -E -h mysql.azbikelaw.org -u asdmuser -ppassword -D asdm
SELECT * FROM pretty_2010_incident WHERE IncidentID=2446148;
SELECT * FROM pretty_2010_unit WHERE IncidentID=2446148;
SELECT * FROM pretty_2010_person WHERE IncidentID=2446148;

    [update, nowadays there is a web page to dump a record:
    http://mysql.azbikelaw.org/asdmphp/queryAsdmIncident.php?incident=2446148
    except that as of Jan 2016 this is only available on internal network ]

    Commentary: everything the cyclist told me seemed to line up okay (e.g. sideswipe; driver=unit1; cyclist=no improper), and the outcome looks correct EXCEPT is was a hit and run, and the flag is not set. He forwarded me the PD’s departmental report (but doesn’t have the acr). The police reacted quickly and did a great job of grabbing the kid (high school kid; parked in the lot at school; with a broken right mirror, and everything!). the cyclist told me he declined to press charges and the kid was then cited for 701 (why not 735? but i guess that’s not a big deal). Police apparently treated this as a potentially serious matter — kudos to Scottsdale PD.
    Cyclist said he may have felt somewhat pressured (“sway” was the word he used) by police to 1) decide on the spot (like as he was being tranported away in the ambulance!) whether or not to press charges, and 2) felt somewhat pressured to decide in the negative (which he did).
    One sort of annoyance might be that it appears the driver was allowed to divert out of the citation via traffic school but i guess that’s by the book (it was not really a “serious injury”; so no reason she shouldn’t be eligible.

  2. Here’s a query to select just bicyclist incidents (note the info to figure out is a particular incident involves a cyclist resides in both the unit and person table, therefore both of these are equivalant):

    SELECT IncidentID,OnRoad,CrossingFeature FROM 2012_incident i WHERE EXISTS
    (SELECT 1 FROM 2012_unit u WHERE u.IncidentID=i.IncidentID AND
    u.eUnitType IN ('PEDALCYCLIST'));

SELECT IncidentID,OnRoad,CrossingFeature FROM 2012_incident i WHERE EXISTS (SELECT 1 FROM 2012_person p WHERE p.IncidentID=i.IncidentID AND p.ePersonType IN ('PEDALCYCLIST'));

    That uses a “subquery” instead of a join. In many cases you can convert between the two. Mysql didn’t add subquery support until a later version (not recent, but not an early version, either. Perhaps circa 2003..5.0? despite that subquery support is required by the ANSI SQL standard). At one point I read some comparison of the “development ideology” of mysql and postgresql, I think the claim was
    that “mysql devs will avoid implementing something until they can implement it in an ideal, optimized way”.

    A simple join looks like (and also subsituted the counting/grouping function):

    SELECT u.eUnitType,count(*) FROM 2010_incident i JOIN 2010_unit u ON
    i.IncidentID=u.IncidentID WHERE
    u.eUnitType IN ('PEDESTRIAN', 'PEDALCYCLIST') GROUP BY 1;
-> which by the way yields 1526 peds + 1942 cyclists = 3648 rows

    Note the difference between the two queries:

    . the first only gives output from the incident table;
    . the second gives output from incident and unit, but only for
    cyclists (there may be multiple rows per incident, if multiple
    cyclists are involved).

    The first clause of the “where” condition “lines up” the unit table with the incident table. Otherwise you have count(incident)*count(1) rows (every row of incident combined with every row of unit, aka garbage).

    mysql> select count(1) from 2010_incident,2010_unit;
    -> 21855485600 (yikes, that a big number!)

    If you add “person” into that join, you must use the join condition
    “UnitID=UnitID” (and then IncidentID=IncidentID should be redundant)
    Otherwise, (person2,unit1) will show up in both unit1 and unit2.

    You an also write that as (Note explicit use of “JOIN”):
    SELECT u.eUnitType,count(*) FROM 2010_incident i JOIN 2010_unit u ON i.IncidentID=u.IncidentID WHERE
    u.eUnitType IN (‘PEDESTRIAN’, ‘PEDALCYCLIST’) GROUP BY 1;

    That’s equivalent, but separates the “join” condition into its own place.

    If you do a self-join (because you need to know data from passengers, or both drivers, or both units, or whatever) you probably want a condition like “u1.UnitID!=u2.UnitID”.

    However many tables are in a query, you probably need one fewer “join condition”. So a common 2-vehicle collision you could get most data(excluding any passengers) like:

    —*** BELOW THIS LINE I HAVEN’T UPDATED QUERIES TO FOLLOW THE “new” TABLE NAMES ***—

    SELECT count(1) FROM incident2 i, unit2 u1, unit2 u2, person2 d1, person2 d2 WHERE u1.IncidentID=i.IncidentID AND u2.IncidentID=i.IncidentID AND u1.UnitID!=u2.UnitID AND d1.UnitID=u1.UnitID AND d2.UnitID=u2.UnitID AND d1.PersonID!=d2.PersonID AND i.TotalUnits=2 AND d1.UnitNumber=1;

    Remeber that if you join with (say) “person2 AS cyclist” that you have to specify “… AND cyclist.PersonType=’PEDALCYCLIST'” 🙂

    The “AS” gives gives the table an alias, which is usually optional (as is the word “AS”). Table aliases are required in the case of self joins.

    —

    We could make views for common joins.

    I believe the following should be equivalent:
    SELECT COUNT(1) FROM incident2 i, unit2 u, person2 p WHERE p.UnitID=u.UnitID AND u.IncidentID=i.IncidentID;
    SELECT count(1) FROM incident2 JOIN unit2 USING (IncidentID) JOIN person2 USING (UnitID);

    The small difference is that the 2nd query has only of each of the
    join columns (which means you don’t have to alias the tables, because
    there’s no “collision”).

    “Using” may be non-portable/mysql-specific, but it means to join using
    columns with the same name. “NATURAL JOIN” is shorthand requiring
    that all the columns with the same name have matching values. That
    *almost* works for us; it fails due to HitAndRunFlag, which is in both
    Incident and Unit (presumably it is present in Incident if any Unit
    has it set, and Unit has it set for only units which Ran). The
    following misses rows (units involved in a hit and run which remained
    on scene):
    mysql> SELECT count(1) FROM incident2 NATURAL JOIN unit2 NATURAL JOIN person2;
    +———-+
    | count(1) |
    +———-+
    | 279744 |
    +———-+

    It’s strange (if useful) that UnitNumber is in both the Unit and
    Person table. I just checked, and it seems consistent
    (person2.UnitNumber always matches unit.UnitNumber WHERE
    person2.UnitID=unit2.UnitID).

  3. I loaded the data locally into an Ubuntu (11.10, 64bit i think) VM running as a guest OS under Windows 7 via VMware Player application.
    At some point i had to load something like
    apt-get install mysql-client-5.1 (or something, just try to execute mysql command and it tells you what to load).
    Also loaded mysql browser and administrator (I can’t remember how, i guess I did something like sudo apt-get install mysql-query-browser ). This is from the people at http://www.mysql.com/downloads/ but it looks like their download there is windows-only)

    I also did something similar on my Ubuntu 11.04(32bit) nettop.

    This all worked fine, allowing connection to the database via the internet.

    Later I wanted to have a local mysqld set up — so I installed xampp. Once again, I can’t remember exactly how I installed it. I don’t recall downloading it, it’s pretty big, but maybe i did. Anyway everthing ends up in /opt/lampp and you just do something like ./lampp start to get it going. If apache is already running, do a apachectl stop first.

    Anyway, i had no trouble loading up the database from the text files, e.g. running
    mysql -h 127.0.0.1 -u root user@10.1.1.172 IDENTIFIED BY ‘blah’;
    where 10.1.1.172 is the ip of the remote computer i wanted to connect from and user / blah are the username/password. (instead of specific ip, can use the ‘%’ wildcard. that is a percent sign with single quotes around it)
    Eventually that all worked and i was able to run the php generator from (yet another) VM, this one running win7 (this program is only available for windows); and have it connect to the database on the ubuntu VM.

  4. Below is a response to some requests for clarification; the attachments were:
    http://azbikelaw.org/crashReports/asdm/definiton_tables(fromADOT)May_2012.xls
    and
    http://azbikelaw.org/crashReports/asdm/PUBLIC_RECORDS_REQUEST_PROCEDURES(adot).pdf
    The latter form or whatever, i have been told by someone else is not really used anymore.
    …

    Your email of May 29, 2012 to Mr. Larry Talley has been forwarded to me for response as custodian of records for the agency. In response to your statement of the “first” problem which concerned definitions, I attach a revised definition document. The ADOT database was revised in 2010 and the column headings were reversed but the input from the reports is correct with the revised attached document.

    The crash data is kept in the normal course of business by ADOT for ADOT use and analysis. Human error attributes to some factors as well as the integrity of the data as completed by the various police agencies which is received both in hard copy and electronically by ADOT. The Arizona Crash Report manual is available online and may provide further insight on the report’s data. As required by Arizona public records statute, ADOT must make available data maintained in databases and paper document in the format maintained by the agency, which you were provided. The analysis by third parties cannot be regulated by ADOT. Your email does not request additional public records and therefore the above is responsive to the best of my information. Should you require ADOT public records, I attach a procedure for requesting records.

    Sue Olson
    Deputy Risk Manager
    ADOT Safety and Risk Management

  5. here are some more sample queries (be careful when cutting/pasting!!)

    Count up the number of cyclist crashes on a particular date. E.g. There were 13 crashes on Sept 23, 2010:

    SELECT count(*) FROM 2010_incident i WHERE EXISTS 
(SELECT 1 FROM 2010_unit u WHERE u.IncidentID=i.IncidentID AND u.eUnitType IN 
('PEDALCYCLIST')) AND IncidentDate="2010-09-23" ;

    Count up the number of bicyclist hit-and-runs:

    SELECT count(*) FROM 2010_incident i WHERE EXISTS 
(SELECT 1 FROM 2010_unit u WHERE u.IncidentID=i.IncidentID 
AND u.eUnitType IN ('PEDALCYCLIST')) AND HitAndRunFlag=1;

    To filter on injury, can add ‘AND (InjurySeverity=5 OR InjurySeverity=4)’ to the where clause (where, e.g. a 4 is incapacitating injury)

  7. I have “lost” the old tools I was using last year, see this comment above, it was a separate gui browser and gui adminstration tool, which ran under ubuntu 11.?? (e.g. 11.04 on my netbook, which has died). The newish ubuntu i have running in a VM on my main machine is 12.04LTS and i have loaded up their newfangled “Mysql workbench” which i can’t quite figure out. In particular, the old adminstration tool had a nice concise way of showing database size; I can’t find that in workbench, here is a query to find the overall size… note I’m supposed to keep it under 1G…

    mysql> SELECT table_schema "Data Base Name", sum( data_length + index_length )
     / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES
     GROUP BY table_schema ; 
+--------------------+----------------------+
| Data Base Name     | Data Base Size in MB |
+--------------------+----------------------+
| asdmadmin          |         872.92841244 |
| information_schema |           0.00488281 |
+--------------------+----------------------+

    By the way, the old tools were apparently known as the “Mysql GUI Tools Bundle”; and are supposedly archived but the download links at mysql.com were dead, you can easily find copies on the internet searching for e.g. mysql-gui-tools-noinstall-5.0-r17-win32.zip or mysql-gui-tools-5.0r12-linux-i386.tar.gz. I tried the windows and it’s very straightforward, just unzip and run (no installation); and it works fine.

  8. Arizona crash facts reports the split between URBAN 83,166 / RURAL 20,010
    This data doesn’t appear to be in the ASDM data they release publically. Here is a possible rough approximation of rural crashes; which selects any time the investigating officer is either any sherriff’s office, or DPS AND not in any City (~ “unincorporated”):

    SELECT COUNT(1) FROM 2012_incident WHERE (OfficerNcic LIKE '%00' OR OfficerNcic=799) AND CityID=999;

    -> 15955

    Here, from the MMUCC is what we really want to know, note this field is NOT on any crash form, rather it is derived by linking a database of road types to the crash location…
    RL5. Roadway Functional Class
    Definition: The character of service or function of streets or highways. The classification of rural
    and urban is determined by State and local officials in cooperation with each other and
    approved by the Federal Highway Administration, U.S. Department of Transportation.
    Source: Obtained by linking Crash Location (C6)to the Roadway Inventory data.
    Rural / Urban:
    Principal Arterial-Interstate
    Principal Arterial-Other
    Minor Arterial
    Major Collector
    Minor Collector
    Unknown

  9. old laptop to maybe use as my own mysql server…
    install ubuntu 12.04 LTS 64b desktop on toshiba xxxx laptop
    … the rest of the details are here:
    http://azbikelaw.org/azbikelaw/
    (note, this is a “private” post azbikelaw gets its own server).

  10. ADOT has a version/update scheme for Arizona Crash Facts
    The newest version of any particular year is served. In fact it sort of bothers me a tiny bit that the old versions don’t seem to be available; i had guessed by messing with the url i could have gotten another/other versions but that doesn’t seem to be the case, i.e. where it says ?sfvrsn=2 in
    http://www.azdot.gov/docs/default-source/mvd-services/12crashfacts.pdf?sfvrsn=2
    (e.g. i tried 0, 1, 3; and i always got the Oct 28 version).

    And just to point out what might be obvious: when the next year’s crash facts comes out, it automagically picks up the then most-current data counts for prior years. So, e.g. from 2011 -> 2012 i compared via the 5 year crash history that there were 11 more pedalcyclists crashes in 2011 than was reported in the 2011 crash facts. (1921 versus 1910)

    From: Ed Beighe
    Sent: Tuesday, November 26, 2013 5:36 PM
    Subject: Re: Pedalcycle Crash Numbers (was: PBCAT Questionnaire – Your Input Needed!)

    thx for the clarification.
    right, so the dataset we get is frozen in time to coincide with that June 1 release of crash facts. i suppose that makes sense.

    i guess in some more perfect world i would be able to get subsequent releases/updates but on the other hand i don’t imagine it affects much on a bigger-picture basis.

    there were no “new” fatalities discovered, were there? (e.g. between the june 1 freeze and whenever the fars freezes)… i see fars 2012 is out as of a couple of weeks ago.

    it would be fun to to know what those 20 “new” crashes are? i mean are they coming from the hinterlands? clerical errors (found a crash report at the bottom of a desk drawer)…. did they come from scattered agencies, or is somebody making up the bulk of the tardy reports.

    Sent: Tuesday, November 26, 2013 4:12 PM
    Subject: Pedalcycle Crash Numbers (was: PBCAT Questionnaire – Your Input Needed!)

    … I’ve learned that the total number of pedalcycle-motor vehicle crashes reported here for 2012 – 2,134 – is up to 2,141 . . . (2012 Crash Facts, publication date May 31, 2012 showed 2,121 – matching what is reported here).

    Here’s what I was told: “This will be the case from now on. (ADOT Traffic Records was) required to publish the crash facts by June 1st, per orders from GOHS. Doing this meant using data which did not match FARS. Therefore, when FARS data was “frozen” later in the year, (ADOT Traffic Records) did an updated crash facts report to reflect the correct number of fatalities, and also any other data that was added since the first report was published. So the data you see in the report posted on the website is more accurate, but of course we are always receiving more crashes for different years and when we publish the 2013 crash facts, the data for 2012 will have changed again. In fact, right now (ADOT Traffic Records is showing) 2,141 pedalcycle crashes for 2012, so the data has already changed since we posted the report a month ago.”

  11. I was asked to provide a list of potential areas for improving the ACR, so I am keeping a list here:

    0) number zero isn’t an acr thing, it’s more of an adot “fiddling” (also called fudgery) with data — There are some oddities in PostedSpeed and EstimatedSpeed, both from the unit table. Note that MUTCD requires speeds to be posted in multiples of 5MPH, though theoretically private streets (which seemingly don’t often, if ever, make it into adot’s database) can and do post anything they want. In any event, EVERY pedalcyclist (and also EVERY pedestrian, by the way) has a PostedSpeed of 0.

    There are also perhaps a couple of hundred (out of 150,000) of odd/improbably/likely incorrect pairing of the two data element, e.g. estimated speeds of between 100 and 200mph? really; e.g. weirdo posted speeds like 4mph.

    SELECT PostedSpeed, eUnitType,count(*) FROM 2013_unit group by 1, 2;
SELECT EUnitType, IF(PostedSpeed>10,ROUND(PostedSpeed/5)*5,PostedSpeed), count(*) FROM 2013_unit GROUP BY 1,2 order by 2,1;
SELECT PostedSpeed, EstimatedSpeed,count(*) FROM 2013_unit where PostedSpeed NOT IN (0,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75) group by 1;

    1) Collision Manner
    See “some clouds in the data“)
    Right now; according to the instructions; there is no “right answer” to what the collision manner between a MV and bike should be. *most* LEOs overlook that distinction and pretend the bike is a 2nd MV and code accordingly (i think that is the right answer). There is also some suggestion that ADOT is fiddling with this data field (i mean in the dataset they produce) — it would be good to know exactly what data fiddling is being done by ADOT. Justin has tried asking multiple times for this info and has gotten nowhere.
    Follow that link above to see some email correspondence with USDOT guy who apparently manages the MMUCC.

    2) Helmet
    Currently not on the form (for bicyclists) at all!?

    3) NonMotoristLocation
    It would probably be best if the ped and bike was entirely discombobulated but I doubt that will happen.
    Anyhow — there was that case i asked you about; there didn’t seem to be a “good” answer for the case where a cyclist crashes within an intersection (a “vehicular” cyclist; one riding in the roadway)… i.e. the choices regarding intersection always conflate crosswalk stuff that is irrelevant in that case.
    Also on this field: from the manual “4. Driveway Access Crosswalk – A crosswalk on roadway providing access to property adjacent to a trafficway”. This means driveway, right? I think that LEOs often code cyclists (and peds, i would guess) struck while going along/across a driveway incorrectly as sidewalk (#10). In any event it seems to me #4 and 10 get mixed up a lot and should be clarified in instructions.

    4) Excessive use of ‘Other’ in particular in Violation/Behavior
    This isn’t a crash form issue/fix per se; and I don’t know how to fix this problem; but just let me say… LEOs abuse this choice in bicyclist crash reports (from memory: 30 some percent of bicyclists get tagged with ‘other’). When ‘other’ is checked, from what i understand, it is required that something be written in next to it. I think it is time that ADOT man-up and collect this information (and make it available in the dataset) so it can be analyzed. I would expect some large majority of ACRs are submitted electronically so there would be very little manpower impact by preserving this information.

    5) Bicyclist ‘with’ vs. ‘against’ traffic direction
    So we sometimes theoretically can derive this; but given the large proportion of crashes that involve wrong-way bicyclists:
    A simple ‘with’ vs. ‘against’ (and there would also have to be ‘other’, plus the ever popular ‘unknown’ i guess). p.s. FARS/PBCAT (when there is/was pbtype data in fars, i.e. 2010 and 2011) has a field like this, they call it NM9 Bicyclist Direction

    6) unit direction of travel. i.e. N, S, E, W, NE, NW, etc. Some LEOs use a half-way (e.g. NE) direction when they should use just a single direction (e.g. N) for turning traffic. So, say, a right-hook collision where the MV is (initially anyway) going N and bike is going N; some LEOs code the MV as NE. Its a consistency thing. everybody should code it the same way.

  12. The AZ Governor’s Traffic Safety Advisory Council, GTSAC; is defunct as of late-2000s (perhaps part of severe state budget-cutting due to deteriorating economic conditions after the 2008 Financial Crisis and ensuing “great recession”)
    http://www.gtsac.org/ (now defunct… here’s a wayback machine of gtsac.org from early 2009)
    However, the site seems to be still living at:
    http://www1.azdot.gov/data_improvement/index.asp
    including the tantalyzing “Crash Data” page; in any event i probably knew this due to hunting for ALISS information.
    other keywords and acronyms:
    Traffic Records Coordinating Committee TRCC
    AZ Traffic and Criminal Software (TraCS)
    “Effective January 1, 2009 the new Traffic Records System (TRS) will become functional. On that date,
    your agency will be able to electronically submit crash data through the Electronic Incident Data
    Submission (EIDS) process to the new ALISS database”

  13. Pingback: Do all Crashes “Count”? | Arizona Bike Law

  14. I’m pasting this here to remind myself to check for any collisions in 2014 (there were none 2009-2013; which makes sense because this officer is new) this officer investigates to see if there are any irregularities. It’s unlikely in any event because the MSCO investigates relatively few bike-MV collisions.
    The content of the on-line MCSO Citizen Compliment/Complaint form you submitted is show below for your records.
    Thank you, M.C.S.O. Internal Affairs Division
    Type of Feedback: Complaint
    Officer’s Name/Badge: D. Barno / S1119
    Date of Incident: June 17, 2014
    Time of Incident: 7:30AM
    Address or Location of Incident: Bush Highway and Granite Reef

    Description of Incident:
    I was on a bicycle in the single lane going north (there is a shoulder but it is in disrepair and very bumpy). This is a very steep down hill portion of the road with a blind sharp right hand turn and a no passing zone. Also near the bottom the shoulder narrows significantly. The posted speed limit is 45. I was going 38.5 MPH. The officer came from way back and was the only other vehicle on the road decided to come up on my rear with sirens blazing and lights flashing. Then pulled up on my left and attempted to push me off the road. This was my scariest moment on a bike I have ever experienced. This officer endangered my life. This officer needs training and an understanding of what being on a bike is like when a huge SUV is harassing you. She could have just as easily waited until we rounded the corner and flattened out before attempting to stop me. I certainly could not have out run her. I am still shaking (5 hours since it happened). Shouldn’t safety come first.

Leave a Reply

Your email address will not be published. Required fields are marked *