gregallensworth
commented
1 year ago Interesting. Off the cuff, I wonder whether something is sorting a data point back to the start of the list.
Yes... Check this out. When we do the aggregation of counts by year_month (e.g. 2020-07) there is a record with null for the year_month and 1 crash for it. I suppose that would sort back to the start, and could cause this effect.
https://chekpeds.carto.com/api/v2/sql?q=SELECT+COUNT(c.cartodb_id)+as+total_crashes,+SUM(c.number_of_cyclist_injured)+as+cyclist_injured,+SUM(c.number_of_cyclist_killed)+as+cyclist_killed,+SUM(c.number_of_motorist_injured)+as+motorist_injured,+SUM(c.number_of_motorist_killed)+as+motorist_killed,+SUM(c.number_of_pedestrian_injured)+as+pedestrian_injured,+SUM(c.number_of_pedestrian_killed)+as+pedestrian_killed,+SUM(c.number_of_pedestrian_injured+%2B+c.number_of_cyclist_injured+%2B+c.number_of_motorist_injured)+as+persons_injured,+SUM(c.number_of_pedestrian_killed+%2B+c.number_of_cyclist_killed+%2B+c.number_of_motorist_killed)+as+persons_killed,+year+%7C%7C+%27-%27+%7C%7C+LPAD(month::text,+2,+%270%27)+as+year_month,++SUM(cyclist_injured_bybike)+as+cyclist_injured_bybike,+SUM(cyclist_killed_bybike)+as+cyclist_killed_bybike,+SUM(motorist_injured_bybike)+as+motorist_injured_bybike,+SUM(motorist_killed_bybike)+as+motorist_killed_bybike,+SUM(pedestrian_injured_bybike)+as+pedestrian_injured_bybike,+SUM(pedestrian_killed_bybike)+as+pedestrian_killed_bybike,+SUM(persons_injured_bybike)+as+persons_injured_bybike,+SUM(persons_killed_bybike)+as+persons_killed_bybike,+SUM(cyclist_injured_byscooter)+as+cyclist_injured_byscooter,+SUM(cyclist_killed_byscooter)+as+cyclist_killed_byscooter,+SUM(motorist_injured_byscooter)+as+motorist_injured_byscooter,+SUM(motorist_killed_byscooter)+as+motorist_killed_byscooter,+SUM(pedestrian_injured_byscooter)+as+pedestrian_injured_byscooter,+SUM(pedestrian_killed_byscooter)+as+pedestrian_killed_byscooter,+SUM(persons_injured_byscooter)+as+persons_injured_byscooter,+SUM(persons_killed_byscooter)+as+persons_killed_byscooter,+SUM(cyclist_injured_bymotorcycle)+as+cyclist_injured_bymotorcycle,+SUM(cyclist_killed_bymotorcycle)+as+cyclist_killed_bymotorcycle,+SUM(motorist_injured_bymotorcycle)+as+motorist_injured_bymotorcycle,+SUM(motorist_killed_bymotorcycle)+as+motorist_killed_bymotorcycle,+SUM(pedestrian_injured_bymotorcycle)+as+pedestrian_injured_bymotorcycle,+SUM(pedestrian_killed_bymotorcycle)+as+pedestrian_killed_bymotorcycle,+SUM(persons_injured_bymotorcycle)+as+persons_injured_bymotorcycle,+SUM(persons_killed_bymotorcycle)+as+persons_killed_bymotorcycle,+SUM(cyclist_injured_bybusvan)+as+cyclist_injured_bybusvan,+SUM(cyclist_killed_bybusvan)+as+cyclist_killed_bybusvan,+SUM(motorist_injured_bybusvan)+as+motorist_injured_bybusvan,+SUM(motorist_killed_bybusvan)+as+motorist_killed_bybusvan,+SUM(pedestrian_injured_bybusvan)+as+pedestrian_injured_bybusvan,+SUM(pedestrian_killed_bybusvan)+as+pedestrian_killed_bybusvan,+SUM(persons_injured_bybusvan)+as+persons_injured_bybusvan,+SUM(persons_killed_bybusvan)+as+persons_killed_bybusvan,+SUM(cyclist_injured_bycar)+as+cyclist_injured_bycar,+SUM(cyclist_killed_bycar)+as+cyclist_killed_bycar,+SUM(motorist_injured_bycar)+as+motorist_injured_bycar,+SUM(motorist_killed_bycar)+as+motorist_killed_bycar,+SUM(pedestrian_injured_bycar)+as+pedestrian_injured_bycar,+SUM(pedestrian_killed_bycar)+as+pedestrian_killed_bycar,+SUM(persons_injured_bycar)+as+persons_injured_bycar,+SUM(persons_killed_bycar)+as+persons_killed_bycar,+SUM(cyclist_injured_bysuv)+as+cyclist_injured_bysuv,+SUM(cyclist_killed_bysuv)+as+cyclist_killed_bysuv,+SUM(motorist_injured_bysuv)+as+motorist_injured_bysuv,+SUM(motorist_killed_bysuv)+as+motorist_killed_bysuv,+SUM(pedestrian_injured_bysuv)+as+pedestrian_injured_bysuv,+SUM(pedestrian_killed_bysuv)+as+pedestrian_killed_bysuv,+SUM(persons_injured_bysuv)+as+persons_injured_bysuv,+SUM(persons_killed_bysuv)+as+persons_killed_bysuv,+SUM(cyclist_injured_bytruck)+as+cyclist_injured_bytruck,+SUM(cyclist_killed_bytruck)+as+cyclist_killed_bytruck,+SUM(motorist_injured_bytruck)+as+motorist_injured_bytruck,+SUM(motorist_killed_bytruck)+as+motorist_killed_bytruck,+SUM(pedestrian_injured_bytruck)+as+pedestrian_injured_bytruck,+SUM(pedestrian_killed_bytruck)+as+pedestrian_killed_bytruck,+SUM(persons_injured_bytruck)+as+persons_injured_bytruck,+SUM(persons_killed_bytruck)+as+persons_killed_bytruck,+SUM(cyclist_injured_byother)+as+cyclist_injured_byother,+SUM(cyclist_killed_byother)+as+cyclist_killed_byother,+SUM(motorist_injured_byother)+as+motorist_injured_byother,+SUM(motorist_killed_byother)+as+motorist_killed_byother,+SUM(pedestrian_injured_byother)+as+pedestrian_injured_byother,+SUM(pedestrian_killed_byother)+as+pedestrian_killed_byother,+SUM(persons_injured_byother)+as+persons_injured_byother,+SUM(persons_killed_byother)+as+persons_killed_byother++FROM+crashes_all_prod+c+WHERE+(hasvehicle_car+OR+hasvehicle_truck+OR+hasvehicle_motorcycle+OR+hasvehicle_bicycle+OR+hasvehicle_suv+OR+hasvehicle_busvan+OR+hasvehicle_scooter+OR+hasvehicle_other+OR+(+NOT+hasvehicle_car+AND+NOT+hasvehicle_truck+AND+NOT+hasvehicle_motorcycle+AND+NOT+hasvehicle_bicycle+AND+NOT+hasvehicle_suv+AND+NOT+hasvehicle_busvan+AND+NOT+hasvehicle_scooter+AND+NOT+hasvehicle_other+))+GROUP+BY+year,+month+ORDER+BY+year+asc,+month+asc
So:
- If this is removed from the picture, does the chart appear as expected?
- How is there a null year_month in here? Has a crash crept in that has a null
date_val? If so, can we fix it?
Naturally now that we made the data sound and didn't touch frontend code, something is off on the charting:
https://vis.crashmapper.org/?cfat=false&cinj=true&geo=city_council&mfat=false&minj=true&p1end=2023-02&p1start=2022-02&p2end=2022-02&p2start=2021-02&pfat=false&pinj=true&reference=citywide&trendAggMonths=1&vbicycle=true&vbusvan=true&vcar=true&view=trend&vmotorcycle=true&vother=true&vscooter=true&vsuv=true&vtruck=true
The trendline is behaving like a closed "polygon" instead of a simple line. That was never true in the past.
The single geographies do not do this: