This is an “INVISIBLE” cyclist — meaning I had missed the news story at the time and only found it later by searching on location; the incident occurred 2/13/2013. Continue reading “Bicyclist hit by dump truck in Phoenix, killed”
Tag: ASDM
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
Data Collection of Bicyclist Helmet Use in Crashes
I have some questions/concerns/misgivings about helmet usage as it relates to bicyclist safety and crash reports. It seems to me that it is not well-reported… (update; sometime in the late 2010s this was updated; skip below to the 11th edition…
Arizona Crash Report (10th Edition)
Curiously, given the hoopla intense interest surrounding bicyclist helmet usage, there is NO place on the ACR to report whether or not a bicyclist was using a helmet. There is a block for each traffic unit(4dd) and passenger(5c), SafetyDevice lists things like helmet/airbag/seatbelt and so forth. However, this block is always supposed to be coded 0/Not Applicable, and is explicitly never to be 1/None Used, or 5/Helmet Used for pedalcyclists. See p.23, 26 of 2010 Arizona’s Crash Report Forms Instruction Manual. (this was block 4ff)
Clearly this is often coded not according to the manual, see below for some live data e.g. from 2010. We might guess that the 10% or so that say Helmet Used probably does mean those bicyclists had helmets, and likewise probably the 35% that said None Used probably means not helmet. But the other 55% is (from the data) anybody’s’ guess.
There is likewise no place on the ACR to code for nighttime crashes whether or not a bicyclist was using required lighting equipment (a front headlight or a rear taillight/reflector).
Arizona Crash Report (11th, 12th & 13th Edition)
the helmet business was changed and made explicit. Some of these changes were in the 11th edition (I have only a draft), below is what is in the 13th Edition, Revised August 2023: here are the salient bits, with my emphasis added:
5ff ‐ Safety Devices - The restraint equipment in use by the occupant, or the helmet use by a motorcyclist, at the time of the crash. 0. Not Applicable - Use for driverless/parked vehicles or pedestrians. Do not use this code for motorcycle operators or pedalcyclists. 1. None Used - Any occupant of the motor vehicle (driver, passenger) did not use a device or in the case of a motorcycle or pedalcyclist, did not use a helmet. 5. Helmet Used - This attribute applies to helmets used by drivers and passengers of all motorized cycles (motorcycles, mopeds, minibikes, motor scooters, and all-terrain vehicles) This should also be used for non-motorists such as pedalcyclists or a pedestrian on a skateboard
So that’s that. This field shows up in the database in the person table as SafetyDevice. Although there are still hundreds of “NOT APPLICABLE” for pedalcyclists despite being explicitly wrong, per the manual. At a few hundred, there are also more UNKNOWNS thank I would like to see. There are also a couple dozen apparent oddballs like seat belts, even one airbag(!). See comment below for sample data dump from 2010 and 2024.
SELECT SafetyDevice, eSafetyDevice, count(*) FROM 2021_person WHERE ePersonType LIKE 'PEDAL%' GROUP BY 1, 2 ORDER BY SafetyDevice;
I didn’t check into the other issue regarding lighting, there’s some other fields, but only in the fatal suppleement regarding non-occupant safety devices: things like reflective clothing, lights; in any event that doesn’t get captured into the (state) database, though it presumably gets passed along into FARS.
FARS
For 1994 through 2009; all persons including bicyclists and other pedalcyclists used the P10 Restraint/Helmet Use field. The column was called REST_USE in the person table.
From 2010 and later, this info was moved to a whole new dataset, the SafetyEq table. the column name is MSAFEQMT; and it’s slightly annoying because it’s indexed by ST_CASE (i.e. the case number), and person number. There can be any number of records for any given person.
The field is called NM13 Non-Motorist Safety Equipment “This element indicates the safety equipment that was used by the non-motorist
involved in the crash”, in the FAR Manual. In the FAR Validation Manual, there’s an elaborate confusing explanation of differentiating between Not Reported and Unknown. Allowable values are:
- None
- Helmet
- Reflective Clothing (jacket, backpack, etc.)
- Protective Pads Used (elbows, knees, shins, etc.)
- Lighting
- (not used)
- Other Safety Equipment
- Not Reported
- Unknown if Used
For reference, you can see mmucc.us‘s field P23 Non-Motorist Safety Equipment from (MMUCC is a nhtsa-funded group that sets standards for data collection on traffic crash reports). This is consistent with FARS definition; except that mmucc says only two may be selected for any person, and FARS allows any number to be selected (theoretically, the vast majority have either one or two selected).
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. Continue reading “ADOT Traffic Collision Database”
Arizona Agency NCIC Numbers
This info will be only of interest for those working with the ADOT Data Safety Mart database.
There are a couple of places on the ACR form for NCIC numbers. That stands for National Crime Information Center; and the actual number in question apparently is called an Originating Agency Identifier (ORI) and it’s keeper is the FBI. Below I will refer to this number only as the “NCIC number”.
I found it surprisingly difficult to find a list. The only place I found it was in a 12 year old(!) AZ Crash Manual (“Manual of Instructions for use with State of Arizona Traffic Accident Report Forms” published by ADOT dated December 2000), so the info regarding Agency name should be suspect.
It is plain to see that some of it is easily verifyable and correlates to any of the “big” cities/jurisdictions: Phoenix PD is 0723, DPS is 0799, Tucson PD is 1003, etc. Beyond a couple of dozen, though, things get pretty sketchy.
Of more interest is the meaning of the distinction between data fields ExtendedNcic, and OfficerNcic — the field on the ACR is marked simply NCIC No. (Block 1e), which i imagine maps to ExtendedNcic; however I can’t find a block on the ACR that might correspond to OfficerNcic. They are usually, but by no means always, the same. There’s another thing called Officer ID No., Block 1f, but that maps to OfficerID in table incident.
This info is also in my famous catch-all spreadsheet adsm.xls; and will undoubtedly either turn into enumerations, or probably its own table.
| Arizona NCIC Numbers | |||||
| National Crime Information Center number is a code that uniquely identifies each law enforcement agency. Numbers are assigned by the Federal Bureau of Investigation. (See pages 66 through 68 [of the year 2000 version of AZ Crash Manual] for a complete list of Arizona NCIC Numbers.) | |||||
| ExtendedNcic, OfficerNcic’s value/count data from ADOT safety data mart year 2010. Agency name list from pages 66 -68 of the year 2000 version of AZ Crash Manual | |||||
| ExtendedNcic | OfficerNcic | From 2000 AZ Crash Manual | |||
| value | count | value | count | agency name | value |
| 100 | 474 | 100 | 40 | Apache County S.O. | 100 |
| 101 | 28 | 101 | 28 | Eagar | 101 |
| 103 | 18 | 103 | 18 | St. Johns | 103 |
| 105 | 12 | 105 | 10 | Springerville | 105 |
| Whitemountain Apache Res. (Apache) | 162 | ||||
| 189 | 73 | Navajo Reservation (Apache) | 189 | ||
| 200 | 693 | 200 | 224 | Cochise County S.O. | 200 |
| 201 | 89 | 201 | 55 | Benson | 201 |
| 203 | 4 | 203 | 1 | Bisbee | 203 |
| 205 | 41 | 205 | 37 | Douglas | 205 |
| 207 | 7 | 207 | 8 | Huachuca City | 207 |
| 209 | 756 | 209 | 763 | Sierra Vista | 209 |
| 211 | 1 | Tombstone | 211 | ||
| 213 | 39 | 213 | 32 | Willcox | 213 |
| 300 | 1539 | 300 | 223 | Coconino County S.O. | 300 |
| 301 | 1909 | 301 | 1762 | Flagstaff | 301 |
| 302 | 1 | Hualapai Reservation (Coconino) | 302 | ||
| 303 | 6 | Fredonia | 303 | ||
| 307 | 97 | 307 | 43 | Williams | 307 |
| 308 | 21 | 308 | 21 | Page | 308 |
| 310 | 195 | 310 | 197 | Sedona | 310 |
| Hopi Reservation (Coconino) | 365 | ||||
| 389 | 34 | Navajo Reservation (Coconino) | 389 | ||
| Northern Arizona University | 397 | ||||
| 400 | 484 | 400 | 134 | Gila County S.O. | 400 |
| 401 | 172 | 401 | 178 | Globe | 401 |
| 403 | 2 | 403 | 1 | Hayden | 403 |
| 405 | 13 | 405 | 13 | Miami | 405 |
| 406 | 142 | 406 | 139 | Payson | 406 |
| 407 | 1 | 489 | 4 | Winkelman | 407 |
| Whitemountain Apache Res. (Gila) | 465 | ||||
| San Carlos Reservation (Gila) | 489 | ||||
| 500 | 124 | 500 | 38 | Graham County S.O. | 500 |
| 501 | 3 | Pima | 501 | ||
| 503 | 105 | 503 | 95 | Safford | 503 |
| 505 | 45 | 505 | 49 | Thatcher | 505 |
| San Carlos Reservation (Graham) | 562 | ||||
| 600 | 60 | 600 | 13 | Greenlee County S.O. | 600 |
| 601 | 6 | 601 | 3 | Clifton | 601 |
| 603 | 4 | Duncan | 603 | ||
| 700 | 5242 | 700 | 3036 | Maricopa County S.O. | 700 |
| 701 | 1044 | 701 | 890 | Avondale | 701 |
| 703 | 405 | 703 | 256 | Buckeye | 703 |
| 704 | 56 | Cave Creek | 704 | ||
| 705 | 3516 | 705 | 3007 | Chandler | 705 |
| 707 | 321 | 707 | 311 | El Mirage | 707 |
| 709 | 20 | Gila Bend | 709 | ||
| 711 | 2378 | 711 | 2250 | Gilbert | 711 |
| 713 | 4822 | 713 | 4492 | Glendale | 713 |
| 715 | 939 | 715 | 635 | Goodyear | 715 |
| Ft. McDowell Reservation | 716 | ||||
| 717 | 6130 | 717 | 4744 | Mesa | 717 |
| 719 | 193 | 719 | 131 | Paradise Valley | 719 |
| 721 | 2237 | 721 | 1855 | Peoria | 721 |
| 723 | 29065 | 723 | 21442 | Phoenix | 723 |
| 725 | 3529 | 725 | 3329 | Scottsdale | 725 |
| 727 | 1027 | 727 | 904 | Surprise | 727 |
| 729 | 6659 | 729 | 4084 | Tempe | 729 |
| 731 | 366 | 731 | 237 | Tolleson | 731 |
| 733 | 93 | 733 | 89 | Wickenburg | 733 |
| 735 | 13 | Youngtown | 735 | ||
| 739 | 300 | Guadalupe | 739 | ||
| 744 | 3 | ||||
| 753 | 41 | ||||
| 755 | 116 | ||||
| 756 | 97 | Fountain Hills | 756 | ||
| 760 | 15 | Carefree | 760 | ||
| Gila Bend Reservation | 762 | ||||
| Tohono O’Odham Res. (Maricopa) | 763 | ||||
| Gila River reservation (Maricopa) | 764 | ||||
| 789 | 1 | Salt River Reservation | 789 | ||
| Arizona State University | 797 | ||||
| 799 | 25587 | Dept. of Public Safety | 799 | ||
| 800 | 1140 | 800 | 326 | Mohave County S.O. | 800 |
| 801 | 537 | 801 | 478 | Kingman | 801 |
| 804 | 629 | 804 | 632 | Hualapai Reservation (Mohave) | 802 |
| 805 | 683 | 805 | 684 | Lake Havasu City | 804 |
| 806 | 18 | 806 | 18 | Bullhead City | 805 |
| Colorado City | 806 | ||||
| Kaibab-Paiute Reservation | 860 | ||||
| 862 | 3 | Ft.Mohave Reservation | 862 | ||
| 900 | 647 | 900 | 141 | Navajo County S.O. | 900 |
| 901 | 60 | 901 | 41 | Holbrook | 901 |
| 902 | 19 | Hopi Reservation (Navajo) | 902 | ||
| 903 | 164 | 903 | 167 | Show Low | 903 |
| 905 | 64 | 905 | 81 | Snowflake | 905 |
| 907 | 19 | Taylor | 907 | ||
| 909 | 134 | 909 | 117 | Winslow | 909 |
| 913 | 118 | 913 | 124 | Pinetop/Lakeside | 913 |
| 962 | 72 | Navajo Reservation (Navajo) | 962 | ||
| 989 | 2 | Whitemountain Apache Res. (Navajo) | 989 | ||
| 1000 | 4424 | 1000 | 3324 | Pima County S.O. | 1000 |
| 1001 | 70 | 1001 | 69 | South Tucson | 1001 |
| 1003 | 9718 | 1003 | 9058 | Tucson | 1003 |
| 1004 | 192 | 1004 | 157 | Sahuarita / Green Valley (both same code??) | 1004 |
| 1007 | 454 | 1007 | 462 | Oro Valley | 1007 |
| 1009 | 916 | 1009 | 679 | Marana | 1009 |
| San Xavier Reservation | 1062 | ||||
| 1089 | 220 | Tohono O’Odham Res. (Pima) | 1089 | ||
| 1097 | 117 | University of Arizona | 1097 | ||
| 1100 | 1779 | 1100 | 703 | Pinal County S.O. | 1100 |
| 1101 | 853 | 1101 | 796 | Casa Grande | 1101 |
| 1103 | 172 | 1103 | 179 | Coolidge | 1103 |
| 1105 | 149 | 1105 | 97 | Eloy | 1105 |
| 1107 | 81 | 1107 | 111 | Florence | 1107 |
| 1109 | 5 | 1109 | 4 | Kearney | 1109 |
| 1111 | 4 | 1111 | 4 | Mammoth | 1111 |
| 1112 | 2 | 1112 | 1 | Superior | 1112 |
| 1113 | 417 | 1113 | 377 | Apache Junction | 1113 |
| 1117 | 215 | 1117 | 211 | ||
| 1164 | 7 | Tohono O’Odham Res. (Pinal) | 1164 | ||
| Maricopa Reservation | 1165 | ||||
| 1189 | 345 | Gila River Reservation (Pinal) | 1189 | ||
| Central Arizona College | 1197 | ||||
| 1200 | 294 | 1200 | 75 | Santa Cruz County S.O. | 1200 |
| 1201 | 342 | 1201 | 313 | Nogales | 1201 |
| Patagonia | 1203 | ||||
| 1300 | 1378 | 1300 | 283 | Yavapai County S.O. | 1300 |
| 1301 | 1 | 1301 | 1 | Clarkdale | 1301 |
| 1303 | 244 | 1303 | 237 | Cottonwood | 1303 |
| 1305 | 6 | 1305 | 5 | Jerome | 1305 |
| 1307 | 760 | 1307 | 749 | Prescott | 1307 |
| 1311 | 539 | 1311 | 536 | Prescott Valley | 1311 |
| 1312 | 87 | 1312 | 87 | Chino Valley | 1312 |
| 1313 | 92 | 1313 | 72 | Camp Verde | 1313 |
| 1314 | 1 | ||||
| 1358 | 11 | ||||
| Hualapai Reservation (Yavapai) | 1363 | ||||
| 1400 | 716 | 1400 | 484 | Yuma County S.O. | 1400 |
| 1403 | 26 | 1403 | 31 | Somerton | 1403 |
| 1405 | 1891 | 1405 | 1849 | Yuma | 1405 |
| 1407 | 7 | Wellton | 1407 | ||
| 1408 | 137 | 1408 | 139 | San Luis | 1408 |
| 1410 | 5 | ||||
| 1497 | 1 | Arizona Western College | 1497 | ||
| 1500 | 330 | 1500 | 32 | La Paz County S.O. | 1500 |
| 1501 | 33 | 1501 | 31 | Parker | 1501 |
| 1503 | 35 | 1503 | 18 | Quartzite | 1503 |
| Colorado River Reservation | 1506 | ||||
| Sums → | 106301 | 106301 | |||
| Below are listed Federal Parks and Monuments, and US Military – it is not clear how, or even if, these codes (from 2000) map to the Adot data, which is all numeric; and perhaps doesn’t even cover “federal” investigations? | |||||
| Canyon De Chelly National Monument | I007 | ||||
| Casa Grande Ruins National Monument | I012 | ||||
| Chiricauha National Monument | I013 | ||||
| Glen Canyon National Monument | I003 | ||||
| Montezuma Castle National Monument | I014 | ||||
| Navajo National Monument | I009 | ||||
| Organ Pipe Cactus National Monument | I015 | ||||
| Petrified Forest National Park | I004 | ||||
| Saguaro National Monument | I005 | ||||
| Sunset Crater National Monument | I010 | ||||
| Tonto National Monument | I016 | ||||
| Tumacacori National Monument | I017 | ||||
| Tuzigoot National Monument | I018 | ||||
| Walnut Canyon National Monument | I019 | ||||
| Wupatki National Monument | I011 | ||||
| Davis Monthan AFB | F001 | ||||
| Ft. Huachuca Army Base | USA0 | ||||
| Luke AFB | F003 | ||||
| Yuma Proving Grounds Army Base SA02 | SA02 | ||||
Most at Fault vs. NCIC
Most at Fault is defined in the Arizona Crash Form Manual
Traffic Unit #1 is the vehicle, pedestrian, pedalcycle that caused the collision or was most at fault.
Police determine or decide who is most at fault, by assigning #1 to that person/operator when filling out the Arizona Crash Report; note that there is no defined way to indicate that investigators find it impossible to determine fault; there must be a unit #1.
(The stats quoted can be found in this comment below)
It can be illuminating to study who, the bicyclist or the motorist, was most at fault (MaF) in a Bike-MV collision. All things being equal, we would expect a 50:50 split, because in the vast majority of collisions there is one bicycle operator, and one MV operator.
The MaF data is available in the yearly collision database from ADOT, a.k.a. the ASDM; the vehicle/person/bicyclist listed as Unit #1 is always the MaF, in the determination of the investigating officer.
Reassuringly, overall the MaF rates are indeed fairly close to 50:50 — for example, the seven year period 2009-2015 the split was 51:49, indicating bicyclists were every so slightly more likely to be found at fault that the driver they collided with. Deviations from this nominal rate might indicate something is amiss; perhaps bicyclists in one community are more likely to break the law, or perhaps police are misinterpreting laws in someone’s favor…
The NCICs associated with the city of Phoenix has a particularly high bicyclist MaF rate: e.g. 68% in 2010 — compare this to, e.g. Scottsdale where it was only 48%. I find it pretty unlikely that bicyclists in Phoenix behave significantly different than Scottsdale; though without looking at a lot of ACRs it’s not possible to tell. On the other hand, 2010 seems to have been anomalously high that year, 2011 and 2012 were 61 and 60%, respectively; so perhaps just a data glitch. On the other hand Tempe, at 68% in 2012, and seems persistantly somewhat high. Yuma, a small city, had a persistently very high bicyclist MaF rate, as high as 80%!, this may be changing after the local ordiance restricted & clarified sidewalk use rules in 2015.
Here are some queries; note that similar results are used using either OfficerNcic as ExtendedNcic. The first is very fancy, computing the percentages and everything!
SELECT sum(atfault)/count(1), Name, sum(atfault), count(1) FROM LOVNcic, (SELECT ExtendedNcic, u.eUnitType='PEDALCYCLIST' atfault FROM 2012_incident i, 2012_unit u WHERE i.IncidentID=u.IncidentID AND EXISTS (SELECT 1 FROM 2012_unit u2 WHERE u2.IncidentID=i.IncidentID AND u2.eUnitType='PEDALCYCLIST') AND UnitNumber=1) x WHERE ID=ExtendedNcic GROUP BY ExtendedNcic HAVING count(1)>20 ORDER BY 1;
Here is how to select the total number of bike crashes by ncic, and then the number of those where bicyclist is MaF
SELECT ExtendedNcic,count(1) FROM 2012_incident i WHERE EXISTS (SELECT 1 FROM 2012_unit u WHERE u.IncidentID=i.IncidentID AND u.eUnitType IN ('PEDALCYCLIST')) GROUP BY 1 ORDER BY 1 ASC;
SELECT ExtendedNcic,count(1) FROM 2012_incident i WHERE EXISTS (SELECT 1 FROM 2012_unit u WHERE u.IncidentID=i.IncidentID AND u.eUnitType IN ('PEDALCYCLIST') AND u.UnitNumber=1 ) GROUP BY 1 ORDER BY 1 ASC;
…