It had the following keys:
[u'dates',
u'processing_time',
u'generated',
u'races',
u'national_summary',
u'load_time',
u'data_load_time']
and within 'races' the following sub-keys:
key_list
[u'convention_districts',
u'townships',
u'state_house_districts',
u'candidates',
u'delegate_districts',
u'counties',
u'congressional_districts',
u'wards']
Python script was used to obtain the required results (important sub-keys are counties, state_house_districts and congressional_districts). Since the data did not have geometry, we filled in the geometry using the Data Observatory by the following commands:
For counties:
UPDATE table_29_3
SET the_geom = obs_getboundarybyid(geoid, 'us.census.tiger.county_clipped')
WHERE length(geoid) = 5
For Minnesota:
UPDATE table_29_3
SET geoid = substring (geoid from 3 for 4)
WHERE geoid LIKE 'MN%'
UPDATE table_29_3
SET the_geom = obs_getboundarybyid(geoid, 'us.census.tiger.congressional_district_clipped')
WHERE length(geoid) = 4
Did the same for Kansas, Alaska and North Dakota.
The sql queries for getting the totals, fractions, margin_of_victory and winners for each party:
with totals as (
select geoid,
NULLIF(trump_8639_president+
christie_60051_president+
cruz_61815_president+
kasich_36679_president+
bush_1239_president+
carson_64509_president+
santorum_1752_president+
gilmore_45650_president+
rubio_53044_president+
fiorina_60339_president+
huckabee_1187_president+
paul_60208_president,0) as r_total,
NULLIF(clinton_1746_president+
sanders_1445_president+
omalley_22603_president,0) as d_total,
NULLIF(trump_8639_president+
christie_60051_president+
cruz_61815_president+
kasich_36679_president+
bush_1239_president+
carson_64509_president+
santorum_1752_president+
gilmore_45650_president+
rubio_53044_president+
fiorina_60339_president+
huckabee_1187_president+
paul_60208_president+clinton_1746_president+
sanders_1445_president+
omalley_22603_president,0) as total
from table_29_3_copy
)
select table_29_3_copy.geoid, the_geom, r_total, d_total, total,
clinton_1746_president::float/d_total as frac_clinton,
sanders_1445_president::float/d_total as frac_sanders,
omalley_22603_president::float/d_total as frac_omalley,
trump_8639_president::float/ r_total as frac_trump,
christie_60051_president::float/ r_total as frac_christie,
cruz_61815_president::float/ r_total as frac_cruz,
kasich_36679_president::float/ r_total as frac_kasich,
bush_1239_president::float/ r_total as frac_bush,
carson_64509_president::float/ r_total as frac_carson,
santorum_1752_president::float/ r_total as frac_santorum,
gilmore_45650_president::float/ r_total as frac_gilmore,
rubio_53044_president::float/ r_total as frac_rubio,
fiorina_60339_president::float/ r_total as frac_fiorina,
huckabee_1187_president::float/ r_total as frac_huckabee,
paul_60208_president::float/r_total as frac_paul,
max_of(array[clinton_1746_president,sanders_1445_president, omalley_22603_president], array['Clinton', 'Sanders', 'OMalley']) as d_winner,
max_of(array[trump_8639_president,
christie_60051_president,
cruz_61815_president,
kasich_36679_president,
bush_1239_president,
carson_64509_president,
santorum_1752_president,
gilmore_45650_president,
rubio_53044_president,
fiorina_60339_president,
huckabee_1187_president,
paul_60208_president
], array['trump', 'christie', 'cruz', 'kasich','bush', 'carson', 'santorum', 'gilmore','rubio','fiorina','huckabee','paul']) as r_winner,
margin_of_victory(array[clinton_1746_president,sanders_1445_president, omalley_22603_president]) as d_margin,
margin_of_victory(array[clinton_1746_president,sanders_1445_president, omalley_22603_president])::float/d_total as d_margin_pc,
margin_of_victory(array[trump_8639_president,
christie_60051_president,
cruz_61815_president,
kasich_36679_president,
bush_1239_president,
carson_64509_president,
santorum_1752_president,
gilmore_45650_president,
rubio_53044_president,
fiorina_60339_president,
huckabee_1187_president,
paul_60208_president
]) as r_margin,
margin_of_victory(array[trump_8639_president,
christie_60051_president,
cruz_61815_president,
kasich_36679_president,
bush_1239_president,
carson_64509_president,
santorum_1752_president,
gilmore_45650_president,
rubio_53044_president,
fiorina_60339_president,
huckabee_1187_president,
paul_60208_president
])::float/d_total as r_margin_pc,
trump_8639_president,
christie_60051_president,
cruz_61815_president,
kasich_36679_president,
bush_1239_president,
carson_64509_president,
santorum_1752_president,
gilmore_45650_president,
rubio_53044_president,
fiorina_60339_president,
huckabee_1187_president,
paul_60208_president,
clinton_1746_president,
sanders_1445_president,
omalley_22603_president
from totals , table_29_3_copy
where table_29_3_copy.geoid = totals.geoid
For getting county names:
UPDATE final_election_subquery
SET name=(SELECT cb_2014_us_county_20m.name
FROM cb_2014_us_county_20m
WHERE final_election_subquery.geoid=cb_2014_us_county_20m.geoid)
For Alaska
UPDATE final_election_subquery
SET name = 'State House District ' || substring(geoid from 4 for 5)
WHERE geoid LIKE '02%'
Similarly for North Dakota, Minnesota and Kansas.
Getting state names
UPDATE final_election_subquery
SET state_name = (SELECT ne_50m_admin_1_states.name
FROM ne_50m_admin_1_states WHERE ST_Within(ST_CENTROID(final_election_subquery.the_geom), ne_50m_admin_1_states.the_geom))
Changing first letter of r_winner to upper case
UPDATE final_election_subquery
SET r_winner = UPPER(substring(r_winner from 1 for 1)) || substring(r_winner from 2 for length(r_winner))
Simplify Geometry
UPDATE final_primary_election
SET the_geom = ST_Simplify(the_geom, 0.0001)
Populating with the DO measures (still to do)
UPDATE final_election_subquery
SET total_population = OBS_GetUSCensusMeasure(ST_PointOnSurface(the_geom), 'Total Population', 'us.census.tiger.county_clipped')
Party - bent:
select final_primary_election.geoid, the_geom, the_geom_webmercator, r_total, d_total, total, frac_clinton, frac_sanders, frac_trump, frac_cruz, frac_kasich, frac_rubio, d_winner, r_winner, d_margin, r_margin, d_margin_pc, r_margin_pc, clinton_1746_president, sanders_1445_president, trump_8639_president, cruz_61815_president, rubio_53044_president, kasich_36679_president, name, state_name, total_population,
max_of(array[r_total,d_total], array['Republican', 'Democratic']) as party_bent
FROM final_primary_election
Frac total
select mid_simplify_use.geoid, the_geom,the_geom_webmercator, r_total, d_total, total, frac_clinton, frac_sanders, frac_trump, frac_cruz, frac_kasich, frac_rubio, d_winner, r_winner, d_margin, r_margin, d_margin_pc, r_margin_pc, clinton_1746_president, sanders_1445_president, trump_8639_president, cruz_61815_president, rubio_53044_president, kasich_36679_president, name, state_name, (r_total-d_total)::float/total as frac_tot
FROM mid_simplify_use