Import party votes per geographic area for 2016 municipal elections to wazimap ZA

municipal-elections.sql

-- Remove BOM for psql \copy
-- sed -i '/^\s*$/d' *
-- Remove blank lines for psql \copy
-- sed -i '1s/^\xEF\xBB\xBF//' *

BEGIN;

CREATE TEMPORARY TABLE municipal_election
(
        province TEXT,
        muni TEXT,
        ward TEXT,
        votingdistrict TEXT,
        votingstationname TEXT,
        registeredvoters TEXT,
        ballottype TEXT,
        spoiltvotes TEXT,
        partyname TEXT,
        totalvalidvotes TEXT,
        dategenerated TEXT
) ON COMMIT DROP;

\copy municipal_election (province, muni, ward, votingdistrict, votingstationname, registeredvoters, ballottype, spoiltvotes, partyname, totalvalidvotes, dategenerated) FROM '/home/jdb/proj/code4sa/wazimap-za/elections/EC.csv' DELIMITER ',' CSV HEADER;
\copy municipal_election (province, muni, ward, votingdistrict, votingstationname, registeredvoters, ballottype, spoiltvotes, partyname, totalvalidvotes, dategenerated) FROM '/home/jdb/proj/code4sa/wazimap-za/elections/FS.csv' DELIMITER ',' CSV HEADER;
\copy municipal_election (province, muni, ward, votingdistrict, votingstationname, registeredvoters, ballottype, spoiltvotes, partyname, totalvalidvotes, dategenerated) FROM '/home/jdb/proj/code4sa/wazimap-za/elections/GP.csv' DELIMITER ',' CSV HEADER;
\copy municipal_election (province, muni, ward, votingdistrict, votingstationname, registeredvoters, ballottype, spoiltvotes, partyname, totalvalidvotes, dategenerated) FROM '/home/jdb/proj/code4sa/wazimap-za/elections/KN.csv' DELIMITER ',' CSV HEADER;
\copy municipal_election (province, muni, ward, votingdistrict, votingstationname, registeredvoters, ballottype, spoiltvotes, partyname, totalvalidvotes, dategenerated) FROM '/home/jdb/proj/code4sa/wazimap-za/elections/MP.csv' DELIMITER ',' CSV HEADER;
\copy municipal_election (province, muni, ward, votingdistrict, votingstationname, registeredvoters, ballottype, spoiltvotes, partyname, totalvalidvotes, dategenerated) FROM '/home/jdb/proj/code4sa/wazimap-za/elections/NW.csv' DELIMITER ',' CSV HEADER;
\copy municipal_election (province, muni, ward, votingdistrict, votingstationname, registeredvoters, ballottype, spoiltvotes, partyname, totalvalidvotes, dategenerated) FROM '/home/jdb/proj/code4sa/wazimap-za/elections/WP.csv' DELIMITER ',' CSV HEADER;
\copy municipal_election (province, muni, ward, votingdistrict, votingstationname, registeredvoters, ballottype, spoiltvotes, partyname, totalvalidvotes, dategenerated) FROM '/home/jdb/proj/code4sa/wazimap-za/elections/NC.csv' DELIMITER ',' CSV HEADER;
\copy municipal_election (province, muni, ward, votingdistrict, votingstationname, registeredvoters, ballottype, spoiltvotes, partyname, totalvalidvotes, dategenerated) FROM '/home/jdb/proj/code4sa/wazimap-za/elections/NP.csv' DELIMITER ',' CSV HEADER;

insert into party_votes_municipal_2016 (geo_level, geo_code, party, total, geo_version) (
select 'ward', substring(ward from 6), partyname, sum(totalvalidvotes::int), '2016' from municipal_election where ballottype in ('PR', 'Ward') group by ward, partyname);

-- wards into local and metro munis

insert into party_votes_municipal_2016 (geo_level, geo_code, party, total, geo_version) (
select g.parent_level, g.parent_code, party, sum(total::int), '2016' from wazimap_geography g, party_votes_municipal_2016 v where  g.version = '2016' and g.geo_code = v.geo_code and g.geo_level = v.geo_level and v.geo_level = 'ward' group by g.parent_code, g.parent_level, party) ;

-- local munis into district munis

insert into party_votes_municipal_2016 (geo_level, geo_code, party, total, geo_version) (
select g.parent_level, g.parent_code, party, sum(total::int), '2016' from wazimap_geography g, party_votes_municipal_2016 v where  g.version = '2016' and g.geo_code = v.geo_code and g.geo_level = v.geo_level and g.parent_level = 'district' group by g.parent_code, g.parent_level, party) ;

-- district and metro munis into provinces

insert into party_votes_municipal_2016 (geo_level, geo_code, party, total, geo_version) (
select g.parent_level, g.parent_code, party, sum(total::int), '2016' from wazimap_geography g, party_votes_municipal_2016 v where  g.version = '2016' and g.geo_code = v.geo_code and g.geo_level = v.geo_level and g.parent_level = 'province' group by g.parent_code, g.parent_level, party) ;

COMMIT;