Closed danrademacher closed 1 year ago
So similar queries like this for City Council work:
https://chekpeds.carto.com/api/v2/sql?q=SELECT+COUNT(c.cartodb_id)+as+total_crashes,+CONCAT(%27City+Council%27,+%27+%27,+city_council)+AS+city_council,+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+the_geom+IS+NOT+NULL+AND+city_council+IS+NOT+NULL+AND+city_council::text+!%3D+%27%27+AND+TRUE+AND+(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,+CONCAT(%27City+Council%27,+%27+%27,+city_council)+ORDER+BY+year_month+asc,+CONCAT(%27City+Council%27,+%27+%27,+city_council)+asc
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
https://chekpeds.carto.com/api/v2/sql?q=SELECT+COUNT(c.cartodb_id)+as+total_crashes,+borough+AS+borough,+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+the_geom+IS+NOT+NULL+AND+borough+IS+NOT+NULL+AND+borough::text+!%3D+%27%27+AND+TRUE+AND+(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,+borough+ORDER+BY+year_month+asc,+borough+asc
But these for NYPD Precincts fail:
https://chekpeds.carto.com/api/v2/sql?q=SELECT+COUNT(c.cartodb_id)+as+total_crashes,+CASE+WHEN+borough+!%3D+%27%27+THEN+CONCAT(borough,+%27,+%27,+%27NYPD+Precinct%27,+%27+%27,+nypd_precinct)+ELSE+CONCAT(%27+No+Borough,+%27,+%27NYPD+Precinct%27,+%27+%27,+nypd_precinct)+END+AS+nypd_precinct,+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+the_geom+IS+NOT+NULL+AND+nypd_precinct+IS+NOT+NULL+AND+nypd_precinct::text+!%3D+%27%27+AND+borough+IS+NOT+NULL+AND+borough+!%3D+%27%27+AND+(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,+CASE+WHEN+borough+!%3D+%27%27+THEN+CONCAT(borough,+%27,+%27,+%27NYPD+Precinct%27,+%27+%27,+nypd_precinct)+ELSE+CONCAT(%27+No+Borough,+%27,+%27NYPD+Precinct%27,+%27+%27,+nypd_precinct)+END+ORDER+BY+year_month+asc,+CASE+WHEN+borough+!%3D+%27%27+THEN+CONCAT(borough,+%27,+%27,+%27NYPD+Precinct%27,+%27+%27,+nypd_precinct)+ELSE+CONCAT(%27+No+Borough,+%27,+%27NYPD+Precinct%27,+%27+%27,+nypd_precinct)+END+asc
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
https://chekpeds.carto.com/api/v2/sql?q=SELECT+COUNT(c.cartodb_id)+as+total_crashes,+borough+AS+borough,+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+the_geom+IS+NOT+NULL+AND+borough+IS+NOT+NULL+AND+borough::text+!%3D+%27%27+AND+TRUE+AND+(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,+borough+ORDER+BY+year_month+asc,+borough+asc
Hmm, here we see a different error:
Still not sure what is happening here.
Emailed support@carto.com
Hello!
We developed a site at Crashmapper.org that relies on an Enterprise Engine-level account, which is generally pretty awesome!
In the last few days, some previously working API calls are failing in a way we have not seen before and that we're having trouble diagnosing since there's very little error messaging.
As described in this open Github issue, https://github.com/GreenInfo-Network/nyc-crash-mapper-chart-view/issues/124, we make a range of API requests, and a few them stopped working just in the last couple of days, with no change to our code. the data the app relies on changes daily, so it's always possible that new data uncovers a new bug in our work, but that would usually produce an API error in JSON format that we could then track down, like a badly formatted SQL query or a timeout or something like that.
Instead, I'm seeing what look like server errors or even DNS errors, like "ERR_CONNECTION_CLOSED" and "ERR_HTTP2_PROTOCOL_ERROR" — and when I try the query we're running in the CARTO GUI, it works fine.
I tried searching online and on your help systems for any similar issues, but couldn't find them.
If you don't want to look at the Github issue, here are example passing and failing API calls:
1. This fails [first precincts link above]
2. This passes [first City Council link above]
Can you help us understand what's happening here?
Best,Dan
This is a weird one!
when we load data, we make three calls to CARTO (I forget why, but I know there's a good reason!). All three were working fine until earlier this week. Now they fail and prevent the loading of Community Boards and NYPD Precincts consistently, and NTAs at least some of the time.
So a URL like this:
We might expect to fail with some sort of SQL error if the query is bad, but instead we get:
We get no errors in the console, like the URL just failed:![image](https://user-images.githubusercontent.com/1423200/92291031-7cee8680-eecb-11ea-83f0-4e7389e15ca5.png)
Just to be sure this is not a query error, I decoded the URL string for the SQL and ran it in carto's GUI:![image](https://user-images.githubusercontent.com/1423200/92291048-92fc4700-eecb-11ea-8f78-4a43b63629d2.png)
Works as expected.
And this same query was working until a couple of days ago. It is 5689 characters long. That's long, but it was just as long a week ago... Maybe a new limit somewhere? Other queries that still work are 4,938 characters and 5,203 characters.
The other thing that changed is noted in https://github.com/GreenInfo-Network/nyc-crash-mapper-etl-script/issues/28, where we were missing 17K data rows and had to change the ETL to get them in place. I wonder about bad data, but in that case we should get an API error, not a closed connection