OpenDataPolicingNC / Traffic-Stops

Open Data Policing NC aggregates, visualizes, and publishes public records related to all known traffic stops to have occurred in North Carolina since January 1, 2002.
https://opendatapolicingnc.com/
MIT License
25 stars 9 forks source link

[ODPM-210] Update NC agency census data #237

Closed copelco closed 6 years ago

copelco commented 6 years ago

This updates the saved NC agency census IDs using Mike's spreadsheet (linked in the JIRA ticket).

To load the new census IDs without reruning a full NC import, you can use the following commands. First, connect to the NC database using psql:

docker-compose run --rm web psql traffic_stops_nc

Then from the psql promt, run:

CREATE TEMPORARY TABLE temp_nc_agency AS TABLE nc_agency;
DELETE FROM temp_nc_agency;
\copy temp_nc_agency (id, name, census_profile_id) FROM '/code/nc/data/NC_agencies.csv' WITH DELIMITER ',' CSV HEADER FORCE NOT NULL census_profile_id;
UPDATE nc_agency a SET census_profile_id = tmp.census_profile_id FROM temp_nc_agency tmp WHERE a.id = tmp.id;