This is a google fusion table, like the multi-year bike and ped crashmaps; but will include ALL types of crashes. Because of google limitation of 100,000 rows — yeah there are a lot of crashes! — this set will be limited to Maricopa County only for one year, 2013. There are almost 80,000 crashes per year in Maricopa County.
I’m not real happy with it. And i haven’t bothered to do much with the styling; but it may be useful/handy for running filters. Ideas for impovement are I thought it might be neet to somehow used different icons for MV, Bike, Ped crashes… so far, they just use the injury-severity based color coded icon.
The Data
The data is the publicly-released ASDM crash database, as of this writing, it will be 2013, the latest full year available.
Because I am not very good at mysql, I hacked through the queries as explained in this comment. I made a new table called 20xx_helper that contains just the PersonID (and it excludes passengers; so it’s either a driver, a pedalcyclist or a pedestrian) and UnitID of unit 1 and unit 2 (so, four fields), as well as a couple of flags to say whether or not any particular crash involved any pedestrian, or any bicyclist.
Then there’s a view, called 20xx_incidentHelper that simply joins these six new fields together with the usual incident table. This is all a one-to-one correspondence; i.e. there are exact same number of rows and the primary key is identical.
So in this particular dataset limiting to Maricopa county, there were 76,340 crashes; and I excluded 1,264 that had no GIS data.
Workflow
See workflow, here. Except this scheme is simpler because there are no “duplicate” rows that need to be eliminated. The only manual step really is creating the icon column based on injury severity.
The Big Query
SELECT i.IncidentID, i.IncidentDateTime, i.IncidentYear, LOVCounty.name County, LOVCity.name City, OffNcic.name Oncic, ExtNcic.name Encic, i.OfficerId, i.OfficerNcic, i.ExtendedNcic, i.isBike, i.isPed, i.eCollisionManner, i.eLightCondition, i.TotalUnits, i.TotalNonMotorists, i.eInjurySeverity, i.InjurySeverity, i.HitAndRunFlag, i.OnRoad, i.CrossingFeature, i.Latitude, i.Longitude, i.eTrafficWayType, i.eIntersectionType, i.eJunctionRelation, i.eWeather, p_unit1.eNonMotoristLocation NMLoc1, p_unit1.eSex Sex1, p_unit1.Age Age1, p_unit1.eViolation1 Violation1_1, p_unit1.eSafetyDevice SafetyDevice1, p_unit1.ePersonType PersonType1, u_unit1.eLane Lane1, u_unit1.eTravelDirection Direction1, u_unit1.eUnitAction Action1, u_unit1.PostedSpeed PostedMph1, u_unit1.EstimatedSpeed EstMph1, p_unit2.eNonMotoristLocation NMLoc2, p_unit2.eSex Sex2, p_unit2.Age Age2, p_unit2.eViolation1 Violation1_2, p_unit2.eSafetyDevice SafetyDevice2, p_unit2.ePersonType PersonType2, u_unit2.eLane Lane2, u_unit2.eTravelDirection Direction2, u_unit2.eUnitAction Action2, u_unit2.PostedSpeed PostedMph2, u_unit2.EstimatedSpeed EstMph2 FROM (((( 2013_incidentHelper AS i LEFT OUTER JOIN 2013_person AS p_unit1 ON i.P1 = p_unit1.PersonID) LEFT OUTER JOIN 2013_unit AS u_unit1 ON i.U1 = u_unit1.UnitID) LEFT OUTER JOIN 2013_person AS p_unit2 ON i.P2 = p_unit2.PersonID) LEFT OUTER JOIN 2013_unit AS u_unit2 ON i.U2 = u_unit2.UnitID ) LEFT OUTER JOIN LOVCounty ON i.CountyId = LOVCounty.id LEFT OUTER JOIN LOVCity ON i.CityId = LOVCity.id LEFT OUTER JOIN LOVNcic AS OffNcic ON i.OfficerNcic = OffNcic.id LEFT OUTER JOIN LOVNcic AS ExtNcic ON i.ExtendedNcic = ExtNcic.id WHERE i.CountyID=13 AND i.Latitude > 0 ORDER BY i.IncidentID;
Here is the styled info window (for the fusion table):
<div class='googft-info-window'>
<b>IncidentID:</b> <a target="_blank" href="http://azbikelaw.org/crashReports/asdmphp/queryAsdmIncident.php?incident={IncidentID}">{IncidentID}</a><br>
<b>IncidentDateTime:</b> {IncidentDateTime}<br>
<b>City:</b> {City}<b> County:</b> {County}<br>
<b>OfficerNcic:</b> {OfficerNcic}
<b> ExtendedNcic:</b> {ExtendedNcic}<br>
<b>CollisionManner:</b> {eCollisionManner}<br>
<b>LightCond:</b> {eLightCondition}
<b>TotalNumUnits:</b> {TotalUnits}<br>
<b>InjurySeverity:</b> {eInjurySeverity}<br>
<b>HitAndRunFlag:</b> {HitAndRunFlag}
<b>OnRoad:</b> {OnRoad}
<b> Cross St:</b> {CrossingFeature}<br>
<b>JuncRelation:</b> {eJunctionRelation}<br>
<hr>
<center><b>Unit #1 Info</b></center>
<b>NonMotLoc:</b> {NMLoc1}<br>
<b>Age:</b> {Age1}/{Sex1}<br>
<b>Violation1:</b> {Violation1_1}<br>
<b>Lane:</b> {Lane1}
<b> TravelDir:</b> {Direction1}
<b> Posted:</b> {PostedMph1} mph<br>
<b>UnitAction:</b> {Action1}
<hr>
<center><b>Unit #2 Info</b></center>
<b>NonMotLoc:</b> {NMLoc2}<br>
<b>Age:</b> {Age2}/{Sex2}<br>
<b>Violation1:</b> {Violation1_2}<br>
<b>Lane:</b> {Lane2}
<b> TravelDir:</b> {Direction2}
<b> Posted:</b> {PostedMph1} mph<br>
<b>UnitAction:</b> {Action2}
</div>
...