danrademacher
commented
4 years ago OK, "progress." Insanely, this SQL gets is the data we think we want, just for 2019, to test in this Google Sheet. The Sheet includes some minor QA around making sure the fractional data add up to the un-split totals for peds, cyclists, motorists killed or injured. At first, they did not, by as much as 10%, and that turned out to be because a lot of crashes have no value for any vehicle type, so I went back and lumped those in with _other and then renamed those _other_unspecified, and now everything lines up precisely.
Which is different from saying this is accurate to real life. I am pretty confident it is internally consistent, which is the first hurdle. Now, off to the client for her consideration.
If this survives scrutiny, I am sure we'll have to find ways to make the SQL more sane, since I wrote it! But it seemed impossible to specify outside of iteratively testing each part of the query until it came out about right. Now we can get the allocations Christine's been looking for all with SQL, preview in Sheets change it if we need it, then figure out if that leads to changes upstream.
Note that unlike other things I have been previewing in Sheets, this is not a live call to the CARTO API. This is simply too long to encode in a URL. Again, yet another reason the query will have to change, but still I think worth having this giant hairball of a query to show the draft results before we start trying overhauling ETL or the user facing applications.
-- final working query!
SELECT
--FOURTH Query aggregates into monthly buckets
year_month,
SUM(d.cyclist_injured) as cyclist_injured,
SUM(d.cyclist_killed) as cyclist_killed,
SUM(d.motorist_injured) as motorist_injured,
SUM(d.motorist_killed) as motorist_killed,
SUM(d.pedestrian_injured) as pedestrian_injured,
SUM(d.pedestrian_killed) as pedestrian_killed,
SUM(d.persons_injured) as persons_injured,
SUM(d.persons_killed) as persons_killed,
SUM(d.cyclist_injured_bybike) as cyclist_injured_bybike,
SUM(d.cyclist_killed_bybike) as cyclist_killed_bybike,
SUM(d.motorist_injured_bybike) as motorist_injured_bybike,
SUM(d.motorist_killed_bybike) as motorist_killed_bybike,
SUM(d.pedestrian_injured_bybike) as pedestrian_injured_bybike,
SUM(d.pedestrian_killed_bybike) as pedestrian_killed_bybike,
SUM(d.persons_injured_bybike) as persons_injured_bybike,
SUM(d.persons_killed_bybike) as persons_killed_bybike,
SUM(d.cyclist_injured_byscooter) as cyclist_injured_byscooter,
SUM(d.cyclist_killed_byscooter) as cyclist_killed_byscooter,
SUM(d.motorist_injured_byscooter) as motorist_injured_byscooter,
SUM(d.motorist_killed_byscooter) as motorist_killed_byscooter,
SUM(d.pedestrian_injured_byscooter) as pedestrian_injured_byscooter,
SUM(d.pedestrian_killed_byscooter) as pedestrian_killed_byscooter,
SUM(d.persons_injured_byscooter) as persons_injured_byscooter,
SUM(d.persons_killed_byscooter) as persons_killed_byscooter,
SUM(d.cyclist_injured_bymotorcycle) as cyclist_injured_bymotorcycle,
SUM(d.cyclist_killed_bymotorcycle) as cyclist_killed_bymotorcycle,
SUM(d.motorist_injured_bymotorcycle) as motorist_injured_bymotorcycle,
SUM(d.motorist_killed_bymotorcycle) as motorist_killed_bymotorcycle,
SUM(d.pedestrian_injured_bymotorcycle) as pedestrian_injured_bymotorcycle,
SUM(d.pedestrian_killed_bymotorcycle) as pedestrian_killed_bymotorcycle,
SUM(d.persons_injured_bymotorcycle) as persons_injured_bymotorcycle,
SUM(d.persons_killed_bymotorcycle) as persons_killed_bymotorcycle,
SUM(d.cyclist_injured_bybusvan) as cyclist_injured_bybusvan,
SUM(d.cyclist_killed_bybusvan) as cyclist_killed_bybusvan,
SUM(d.motorist_injured_bybusvan) as motorist_injured_bybusvan,
SUM(d.motorist_killed_bybusvan) as motorist_killed_bybusvan,
SUM(d.pedestrian_injured_bybusvan) as pedestrian_injured_bybusvan,
SUM(d.pedestrian_killed_bybusvan) as pedestrian_killed_bybusvan,
SUM(d.persons_injured_bybusvan) as persons_injured_bybusvan,
SUM(d.persons_killed_bybusvan) as persons_killed_bybusvan,
SUM(d.cyclist_injured_bycar) as cyclist_injured_bycar,
SUM(d.cyclist_killed_bycar) as cyclist_killed_bycar,
SUM(d.motorist_injured_bycar) as motorist_injured_bycar,
SUM(d.motorist_killed_bycar) as motorist_killed_bycar,
SUM(d.pedestrian_injured_bycar) as pedestrian_injured_bycar,
SUM(d.pedestrian_killed_bycar) as pedestrian_killed_bycar,
SUM(d.persons_injured_bycar) as persons_injured_bycar,
SUM(d.persons_killed_bycar) as persons_killed_bycar,
SUM(d.cyclist_injured_bysuv) as cyclist_injured_bysuv,
SUM(d.cyclist_killed_bysuv) as cyclist_killed_bysuv,
SUM(d.motorist_injured_bysuv) as motorist_injured_bysuv,
SUM(d.motorist_killed_bysuv) as motorist_killed_bysuv,
SUM(d.pedestrian_injured_bysuv) as pedestrian_injured_bysuv,
SUM(d.pedestrian_killed_bysuv) as pedestrian_killed_bysuv,
SUM(d.persons_injured_bysuv) as persons_injured_bysuv,
SUM(d.persons_killed_bysuv) as persons_killed_bysuv,
SUM(d.cyclist_injured_bytruck) as cyclist_injured_bytruck,
SUM(d.cyclist_killed_bytruck) as cyclist_killed_bytruck,
SUM(d.motorist_injured_bytruck) as motorist_injured_bytruck,
SUM(d.motorist_killed_bytruck) as motorist_killed_bytruck,
SUM(d.pedestrian_injured_bytruck) as pedestrian_injured_bytruck,
SUM(d.pedestrian_killed_bytruck) as pedestrian_killed_bytruck,
SUM(d.persons_injured_bytruck) as persons_injured_bytruck,
SUM(d.persons_killed_bytruck) as persons_killed_bytruck,
SUM(d.cyclist_injured_byother) as cyclist_injured_byother,
SUM(d.cyclist_killed_byother) as cyclist_killed_byother,
SUM(d.motorist_injured_byother) as motorist_injured_byother,
SUM(d.motorist_killed_byother) as motorist_killed_byother,
SUM(d.pedestrian_injured_byother) as pedestrian_injured_byother,
SUM(d.pedestrian_killed_byother) as pedestrian_killed_byother,
SUM(d.persons_injured_byother) as persons_injured_byother,
SUM(d.persons_killed_byother) as persons_killed_byother
FROM
--THIRD Query multiplies out all the blame allocation for each assigned vehicle type
(SELECT
year_month,
cyclist_injured,
cyclist_killed,
motorist_injured,
motorist_killed,
pedestrian_injured,
pedestrian_killed,
persons_injured,
persons_killed,
hasvehicle_bicycle,
hasvehicle_scooter,
hasvehicle_motorcycle,
hasvehicle_busvan,
hasvehicle_car,
hasvehicle_suv,
hasvehicle_truck,
hasvehicle_other_unspecified,
blame_factor,
bike_blame,
--now all the different fields, for every mode
--hasvehicle_bicycle
CASE WHEN (bike_blame is TRUE AND hasvehicle_bicycle is TRUE) THEN cyclist_injured_allocated ELSE 0 END AS cyclist_injured_bybike,
CASE WHEN (bike_blame is TRUE AND hasvehicle_bicycle is TRUE) THEN cyclist_killed_allocated ELSE 0 END AS cyclist_killed_bybike,
CASE WHEN (bike_blame is TRUE AND hasvehicle_bicycle is TRUE) THEN motorist_injured_allocated ELSE 0 END AS motorist_injured_bybike,
CASE WHEN (bike_blame is TRUE AND hasvehicle_bicycle is TRUE) THEN motorist_killed_allocated ELSE 0 END AS motorist_killed_bybike,
CASE WHEN (bike_blame is TRUE AND hasvehicle_bicycle is TRUE) THEN pedestrian_injured_allocated ELSE 0 END AS pedestrian_injured_bybike,
CASE WHEN (bike_blame is TRUE AND hasvehicle_bicycle is TRUE) THEN pedestrian_killed_allocated ELSE 0 END AS pedestrian_killed_bybike,
CASE WHEN (bike_blame is TRUE AND hasvehicle_bicycle is TRUE) THEN persons_injured_allocated ELSE 0 END AS persons_injured_bybike,
CASE WHEN (bike_blame is TRUE AND hasvehicle_bicycle is TRUE) THEN persons_killed_allocated ELSE 0 END AS persons_killed_bybike,
--hasvehicle_scooter
CASE WHEN (bike_blame is TRUE AND hasvehicle_scooter is TRUE) THEN cyclist_injured_allocated ELSE 0 END AS cyclist_injured_byscooter,
CASE WHEN (bike_blame is TRUE AND hasvehicle_scooter is TRUE) THEN cyclist_killed_allocated ELSE 0 END AS cyclist_killed_byscooter,
CASE WHEN (bike_blame is TRUE AND hasvehicle_scooter is TRUE) THEN motorist_injured_allocated ELSE 0 END AS motorist_injured_byscooter,
CASE WHEN (bike_blame is TRUE AND hasvehicle_scooter is TRUE) THEN motorist_killed_allocated ELSE 0 END AS motorist_killed_byscooter,
CASE WHEN (bike_blame is TRUE AND hasvehicle_scooter is TRUE) THEN pedestrian_injured_allocated ELSE 0 END AS pedestrian_injured_byscooter,
CASE WHEN (bike_blame is TRUE AND hasvehicle_scooter is TRUE) THEN pedestrian_killed_allocated ELSE 0 END AS pedestrian_killed_byscooter,
CASE WHEN (bike_blame is TRUE AND hasvehicle_scooter is TRUE) THEN persons_injured_allocated ELSE 0 END AS persons_injured_byscooter,
CASE WHEN (bike_blame is TRUE AND hasvehicle_scooter is TRUE) THEN persons_killed_allocated ELSE 0 END AS persons_killed_byscooter,
--hasvehicle_motorcycle
CASE WHEN (hasvehicle_motorcycle is TRUE) THEN cyclist_injured_allocated ELSE 0 END AS cyclist_injured_bymotorcycle,
CASE WHEN (hasvehicle_motorcycle is TRUE) THEN cyclist_killed_allocated ELSE 0 END AS cyclist_killed_bymotorcycle,
CASE WHEN (hasvehicle_motorcycle is TRUE) THEN motorist_injured_allocated ELSE 0 END AS motorist_injured_bymotorcycle,
CASE WHEN (hasvehicle_motorcycle is TRUE) THEN motorist_killed_allocated ELSE 0 END AS motorist_killed_bymotorcycle,
CASE WHEN (hasvehicle_motorcycle is TRUE) THEN pedestrian_injured_allocated ELSE 0 END AS pedestrian_injured_bymotorcycle,
CASE WHEN (hasvehicle_motorcycle is TRUE) THEN pedestrian_killed_allocated ELSE 0 END AS pedestrian_killed_bymotorcycle,
CASE WHEN (hasvehicle_motorcycle is TRUE) THEN persons_injured_allocated ELSE 0 END AS persons_injured_bymotorcycle,
CASE WHEN (hasvehicle_motorcycle is TRUE) THEN persons_killed_allocated ELSE 0 END AS persons_killed_bymotorcycle,
--hasvehicle_busvan
CASE WHEN (hasvehicle_busvan is TRUE) THEN cyclist_injured_allocated ELSE 0 END AS cyclist_injured_bybusvan,
CASE WHEN (hasvehicle_busvan is TRUE) THEN cyclist_killed_allocated ELSE 0 END AS cyclist_killed_bybusvan,
CASE WHEN (hasvehicle_busvan is TRUE) THEN motorist_injured_allocated ELSE 0 END AS motorist_injured_bybusvan,
CASE WHEN (hasvehicle_busvan is TRUE) THEN motorist_killed_allocated ELSE 0 END AS motorist_killed_bybusvan,
CASE WHEN (hasvehicle_busvan is TRUE) THEN pedestrian_injured_allocated ELSE 0 END AS pedestrian_injured_bybusvan,
CASE WHEN (hasvehicle_busvan is TRUE) THEN pedestrian_killed_allocated ELSE 0 END AS pedestrian_killed_bybusvan,
CASE WHEN (hasvehicle_busvan is TRUE) THEN persons_injured_allocated ELSE 0 END AS persons_injured_bybusvan,
CASE WHEN (hasvehicle_busvan is TRUE) THEN persons_killed_allocated ELSE 0 END AS persons_killed_bybusvan,
--hasvehicle_car
CASE WHEN (hasvehicle_car is TRUE) THEN cyclist_injured_allocated ELSE 0 END AS cyclist_injured_bycar,
CASE WHEN (hasvehicle_car is TRUE) THEN cyclist_killed_allocated ELSE 0 END AS cyclist_killed_bycar,
CASE WHEN (hasvehicle_car is TRUE) THEN motorist_injured_allocated ELSE 0 END AS motorist_injured_bycar,
CASE WHEN (hasvehicle_car is TRUE) THEN motorist_killed_allocated ELSE 0 END AS motorist_killed_bycar,
CASE WHEN (hasvehicle_car is TRUE) THEN pedestrian_injured_allocated ELSE 0 END AS pedestrian_injured_bycar,
CASE WHEN (hasvehicle_car is TRUE) THEN pedestrian_killed_allocated ELSE 0 END AS pedestrian_killed_bycar,
CASE WHEN (hasvehicle_car is TRUE) THEN persons_injured_allocated ELSE 0 END AS persons_injured_bycar,
CASE WHEN (hasvehicle_car is TRUE) THEN persons_killed_allocated ELSE 0 END AS persons_killed_bycar,
--hasvehicle_suv
CASE WHEN (hasvehicle_suv is TRUE) THEN cyclist_injured_allocated ELSE 0 END AS cyclist_injured_bysuv,
CASE WHEN (hasvehicle_suv is TRUE) THEN cyclist_killed_allocated ELSE 0 END AS cyclist_killed_bysuv,
CASE WHEN (hasvehicle_suv is TRUE) THEN motorist_injured_allocated ELSE 0 END AS motorist_injured_bysuv,
CASE WHEN (hasvehicle_suv is TRUE) THEN motorist_killed_allocated ELSE 0 END AS motorist_killed_bysuv,
CASE WHEN (hasvehicle_suv is TRUE) THEN pedestrian_injured_allocated ELSE 0 END AS pedestrian_injured_bysuv,
CASE WHEN (hasvehicle_suv is TRUE) THEN pedestrian_killed_allocated ELSE 0 END AS pedestrian_killed_bysuv,
CASE WHEN (hasvehicle_suv is TRUE) THEN persons_injured_allocated ELSE 0 END AS persons_injured_bysuv,
CASE WHEN (hasvehicle_suv is TRUE) THEN persons_killed_allocated ELSE 0 END AS persons_killed_bysuv,
--hasvehicle_truck
CASE WHEN (hasvehicle_truck is TRUE) THEN cyclist_injured_allocated ELSE 0 END AS cyclist_injured_bytruck,
CASE WHEN (hasvehicle_truck is TRUE) THEN cyclist_killed_allocated ELSE 0 END AS cyclist_killed_bytruck,
CASE WHEN (hasvehicle_truck is TRUE) THEN motorist_injured_allocated ELSE 0 END AS motorist_injured_bytruck,
CASE WHEN (hasvehicle_truck is TRUE) THEN motorist_killed_allocated ELSE 0 END AS motorist_killed_bytruck,
CASE WHEN (hasvehicle_truck is TRUE) THEN pedestrian_injured_allocated ELSE 0 END AS pedestrian_injured_bytruck,
CASE WHEN (hasvehicle_truck is TRUE) THEN pedestrian_killed_allocated ELSE 0 END AS pedestrian_killed_bytruck,
CASE WHEN (hasvehicle_truck is TRUE) THEN persons_injured_allocated ELSE 0 END AS persons_injured_bytruck,
CASE WHEN (hasvehicle_truck is TRUE) THEN persons_killed_allocated ELSE 0 END AS persons_killed_bytruck,
--hasvehicle_other_unspecified
CASE WHEN (hasvehicle_other_unspecified is TRUE) THEN cyclist_injured_allocated ELSE 0 END AS cyclist_injured_byother,
CASE WHEN (hasvehicle_other_unspecified is TRUE) THEN cyclist_killed_allocated ELSE 0 END AS cyclist_killed_byother,
CASE WHEN (hasvehicle_other_unspecified is TRUE) THEN motorist_injured_allocated ELSE 0 END AS motorist_injured_byother,
CASE WHEN (hasvehicle_other_unspecified is TRUE) THEN motorist_killed_allocated ELSE 0 END AS motorist_killed_byother,
CASE WHEN (hasvehicle_other_unspecified is TRUE) THEN pedestrian_injured_allocated ELSE 0 END AS pedestrian_injured_byother,
CASE WHEN (hasvehicle_other_unspecified is TRUE) THEN pedestrian_killed_allocated ELSE 0 END AS pedestrian_killed_byother,
CASE WHEN (hasvehicle_other_unspecified is TRUE) THEN persons_injured_allocated ELSE 0 END AS persons_injured_byother,
CASE WHEN (hasvehicle_other_unspecified is TRUE) THEN persons_killed_allocated ELSE 0 END AS persons_killed_byother
FROM
--SECOND QUERY pulls in a bunch of pass through but also multiples the allocation amounts
( SELECT
--these are totals, unallocated
cyclist_injured,
cyclist_killed,
motorist_injured,
motorist_killed,
pedestrian_injured,
pedestrian_killed,
persons_injured,
persons_killed,
hasvehicle_bicycle,
hasvehicle_scooter,
hasvehicle_motorcycle,
hasvehicle_busvan,
hasvehicle_car,
hasvehicle_suv,
hasvehicle_truck,
hasvehicle_other_unspecified,
blame_factor,
bike_blame,
--fractional for allocation
(blame_factor * cyclist_injured) as cyclist_injured_allocated,
(blame_factor * cyclist_killed) as cyclist_killed_allocated,
(blame_factor * motorist_injured) as motorist_injured_allocated,
(blame_factor * motorist_killed) as motorist_killed_allocated,
(blame_factor * pedestrian_injured) as pedestrian_injured_allocated,
(blame_factor * pedestrian_killed) as pedestrian_killed_allocated,
(blame_factor * persons_injured) as persons_injured_allocated,
(blame_factor * persons_killed) as persons_killed_allocated,
year_month
FROM
--BASE QUERY gets the core data and also sets a "blame factor" based on involved vehicle types
(SELECT
cartodb_id,
number_of_cyclist_injured as cyclist_injured,
number_of_cyclist_killed as cyclist_killed,
number_of_motorist_injured as motorist_injured,
number_of_motorist_killed as motorist_killed,
number_of_pedestrian_injured as pedestrian_injured,
number_of_pedestrian_killed as pedestrian_killed,
(number_of_pedestrian_injured + number_of_cyclist_injured + number_of_motorist_injured) as persons_injured,
(number_of_pedestrian_killed + number_of_cyclist_killed + number_of_motorist_killed) as persons_killed,
hasvehicle_bicycle,
hasvehicle_scooter,
hasvehicle_motorcycle,
hasvehicle_busvan,
hasvehicle_car,
hasvehicle_suv,
hasvehicle_truck,
--set other to TRUE if nothing else is selected, which catches crashes with no vtype data
CASE
WHEN
(hasvehicle_bicycle::int + hasvehicle_motorcycle::int + hasvehicle_scooter::int + hasvehicle_busvan::int + hasvehicle_car::int + hasvehicle_suv::int + hasvehicle_truck::int + hasvehicle_other::int = 0)
THEN
TRUE
ELSE hasvehicle_other
END as hasvehicle_other_unspecified,
-- Determine if this record will "blame" bikes or scooters for injuries or deaths, only in cases with no other motor vehicles
CASE
WHEN
(hasvehicle_bicycle OR hasvehicle_scooter) AND (hasvehicle_motorcycle::int + hasvehicle_busvan::int + hasvehicle_car::int + hasvehicle_suv::int + hasvehicle_truck::int + hasvehicle_other::int = 0)
THEN
TRUE
ELSE
FALSE
END as bike_blame,
--determine the number of blameable vehicles involved and then turn to percentage blame. Run as suqquery to it can be used for math
CASE
WHEN
(hasvehicle_bicycle OR hasvehicle_scooter) AND (hasvehicle_motorcycle::int + hasvehicle_busvan::int + hasvehicle_car::int + hasvehicle_suv::int + hasvehicle_truck::int + hasvehicle_other::int = 0)
THEN
(1/CAST (NULLIF((hasvehicle_bicycle::int + hasvehicle_scooter::int),0) as FLOAT))
ELSE
(1/CAST (
NULLIF(
(hasvehicle_motorcycle::int + hasvehicle_busvan::int + hasvehicle_car::int + hasvehicle_suv::int + hasvehicle_truck::int + hasvehicle_other::int)
+
(CASE
WHEN (hasvehicle_bicycle::int + hasvehicle_motorcycle::int + hasvehicle_scooter::int + hasvehicle_busvan::int + hasvehicle_car::int + hasvehicle_suv::int + hasvehicle_truck::int + hasvehicle_other::int = 0)
THEN
1
ELSE
0 END
),0) as FLOAT))
END
as blame_factor,
year || '-' || LPAD(month::text, 2, '0') as year_month
from crashes_all_prod as c
WHERE year = 2019
) as a
) as b) as d
GROUP BY year_month
gregallensworth
Over in #22 we assigned booleans for the 8 canonical Vehicle Types.
Another part of that task, which is properly a separate issue, is this:
Portioning out the injured and the killed
22 is be sufficient to allow for basic filtering of crashes by involved vehicles -- "Show me crashes involving an SUV or CAR or TRUCK" like the push button Crash Type filters we have on the current application.
There's a further wrinkle in that we'll want to also know what proportion of injuries or fatalities were attributable to each vehicle type, but without double or triple-counting. If we simply add up all fatalities, say, where
CARandSUVare involved, a crash of an SUV and car with a single fatality could create1+1, when what we want to do is say that they were each equally responsible for a single fatality, so0.5+0.5. With the further challenge that aBICYCLEis the cause of a fatality only when no other vehicles are involved.So I think what we would need to do here is:
crashmapper_vehicletypesinvolved>1, check for bike, ifbike=true, then subtract 1 from total vehicle typesBut then it gets even more gnarly, in that we also need to retain whether the injured or killed were walking, biking, or driving.
Though this makes for a large schema, seems like a clear option would be to create a set of 48 (!) fields that directly allocate to the 8 canonical types:
e-bike-scootboolean?e-bike-scoot_pedInjurye-bike-scoot_bikeInjurye-bike-scoot_motoristInjurye-bike-scoot_pedFatalitye-bike-scoot_bikeFatalitye-bike-scoot_motoristFatality... and so on for all 8
crashmapper_vehicletypesThen we could always query the same fields for the same mode-related injury/death data, and rows without that data simply wouldn't contribute to the total.
Visualizing that data will be a challenge, but first we have to get the data (and I do have a plan for visualization within current application structure).