BAD voter turnout 2016

voter turnout 2016.sql

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;

-- votes
------------

-- wards

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) ;

-- voters
-----------

insert into voters_turnout_municipal_2016 (geo_level, geo_code, registered_voters, total_votes)
with voters as (select distinct ward, votingstationname, registeredvoters from municipal_election where ward = 'Ward 21001001') select ward, sum(registeredvoters::int) from voters group by ward;



wazimap_za=> select ballottype, sum(totalvalidvotes::int) from municipal_election group by ballottype;
 ballottype |   sum
------------+---------
 Ward       | 1763225
 DC 40%     | 1174729
 PR         | 1754042



wazimap_za=> select ballottype, sum(totalvalidvotes::int) + 112103 from municipal_election group by ballottype; ballottype | ?column?
------------+----------
 Ward       |  1875328
 DC 40%     |  1286832
 PR         |  1866145

wazimap_za=> select sum(greatest(p.totalvalidvotes::int, w.totalvalidvotes::int)) from municipal_election p, municipal_election w where p.partyname = w.partyname and p.votingdistrict= w.votingdistrict and p.ward = w.ward and p.ballottype = 'PR' and w.ballottype = 'Ward';
   sum
---------
 1764625


wazimap_za=> select sum(greatest(p.totalvalidvotes::int, w.totalvalidvotes::int)) + 112103 from municipal_election p, municipal_election w where p.partyname = w.partyname and p.votingdistrict= w.votingdistrict and p.ward = w.ward and p.ballottype = 'PR' and w.ballottype = 'Ward';
 ?column?
----------
  1876728

with districttotals as (select p.ward, p.votingdistrict, min(p.spoiltvotes::int) as pspoilt, sum(p.totalvalidvotes::int) as ptotal, min(w.spoiltvotes::int) as wspoilt, sum(w.totalvalidvotes::int) as wtotal from municipal_election p, municipal_election w where p.partyname = w.partyname and p.votingdistrict= w.votingdistrict and p.ward = w.ward and p.ballottype = 'PR' and w.ballottype = 'Ward' group by p.ward, p.votingdistrict) select votingdistrict, sum(greatest(ptotal+pspoilt, wtotal+wspoilt)) from districttotals where votingdistrict = '10520075' group by votingdistrict;

COMMIT;