2016_data_update.sql

--- Updating 2016 crashes data in CARTO
--- The NYPD deleted and updated rows in Socrata for 2016 sometime in early 2017,
--- requiring a db update in CARTO
--- This may happen for future years as well, so documenting the process here

--- When imported into CARTO, a value for the "contributing_factor" field may look like:
--- {"Driver Inattention/Distraction",Unspecified}
--- Getting CARTO to recognize "contributing_factor" as an array type requires
--- doing a find and replace, then converting the column type to text array

--- Note that SQL queries in CARTO do not require a trailing semicolon except
--- in the case of running multiple queries in a row.

--- Remove curly braces
UPDATE crashes_2016_to_2017
SET contributing_factor = replace(contributing_factor, '{', '');

UPDATE crashes_2016_to_2017
SET contributing_factor = replace(contributing_factor, '}', '');

UPDATE crashes_2016_to_2017
SET vehicle_type = replace(vehicle_type, '{', '');

UPDATE crashes_2016_to_2017
SET vehicle_type = replace(vehicle_type, '}', '');

-- Remove double quotes
UPDATE crashes_2016_to_2017
SET contributing_factor = replace(contributing_factor, '"', '');

UPDATE crashes_2016_to_2017
SET vehicle_type = replace(vehicle_type, '"', '');

--- Convert data type from text to text array
ALTER TABLE crashes_2016_to_2017
ALTER contributing_factor type text[] using array[string_to_array(contributing_factor, ',', '')];

ALTER TABLE crashes_2016_to_2017
ALTER vehicle_type type text[] using array[string_to_array(vehicle_type, ',', '')];

--- Drop rows in master crashes table for 2016 & early 2017
DELETE FROM crashes_all_prod
WHERE date_val >= date '2016-01-01' AND date_val <= '2017-02-26';

--- Insert updated data into the master crashes table
INSERT INTO crashes_all_prod
(the_geom, borough, contributing_factor, crash_count, cross_street_name, date_val, latitude, longitude,
 month, number_of_cyclist_injured, number_of_cyclist_killed, number_of_motorist_injured,
 number_of_motorist_killed, number_of_pedestrian_injured, number_of_pedestrian_killed,
 number_of_persons_injured, number_of_persons_killed, off_street_name, on_street_name,
 socrata_id, unique_key, vehicle_type, year, zip_code)
SELECT
the_geom, borough, contributing_factor, crash_count, cross_street_name, date_val, latitude, longitude,
 month, number_of_cyclist_injured, number_of_cyclist_killed, number_of_motorist_injured,
 number_of_motorist_killed, number_of_pedestrian_injured, number_of_pedestrian_killed,
 number_of_persons_injured, number_of_persons_killed, off_street_name, on_street_name,
 socrata_id, unique_key, vehicle_type, year, zip_code
FROM crashes_2016_to_2017;

--- Double check it worked!
SELECT count(*) FROM crashes_all_prod WHERE date_val >= date '2016-01-01' AND date_val <= '2017-02-26';
--- 258082

--- After doing this create a copy of the production table as a backup and lock it in the CARTO dashboard