Map Election Data.md

Primary Election Results 2016 (County-level data)

The data was downloaded as a .csv file from kaagle.com (https://www.kaggle.com/benhamner/2016-us-election) The geometries were added to this map, using the Data Observatory and populating the counties using the Fips code in the dataset. Please note: Some states and their codes are not in the standard format and hence, despite the existing data, we could not populate the geometries.

##Exloring the dataset As you see, the data consists of votes and vote-fractions for each candidate of each party for the counties of United States.

UPDATE election_results
SET fips = substring(fips from 2 for 7)
WHERE state_abbreviation = 'MA'

UPDATE election_results
SET the_geom = obs_getboundarybyid(fips, 'us.census.tiger.place_clipped')
WHERE state_abbreviation LIKE 'MA'

###For Kansas

INSERT INTO primary_results_transposed_copy_copy(the_geom, state)

with cte as (
    SELECT '200' || x::text As geoid
    FROM generate_series(1, 4) As m(x)
)
SELECT OBS_GetBoundaryById(geoid, 'us.census.tiger.congressional_district_clipped') As the_geom, 'Kansas' As state
FROM cte

###For Massachusetts

Got data for MA counties in a separate table

INSERT INTO election_results(the_geom, state, state_abbreviation, county, fips, bernie_votes, bernie_fraction, hillary_votes, hillary_fraction, carson_votes, carson_fraction, trump_votes, trump_fraction, kasich_votes, kasich_fraction, rubio_votes, rubio_fraction, cruz_votes, cruz_fraction)
SELECT the_geom, state, state_abbreviation, county, fips, bernie_votes, bernie_fraction, hillary_votes, hillary_fraction, carson_votes, carson_fraction, trump_votes, trump_fraction, kasich_votes, kasich_fraction, rubio_votes, rubio_fraction, cruz_votes, cruz_fraction FROM table_29_1

###For Maine

UPDATE election_results
SET fips = substring(fips from 2 for 8)
WHERE state_abbreviation = 'ME'

###json for Election Data

import urllib, json

url = 'https://int.nyt.com/applications/elections/2016/api/3/races/president/primary.json'

response = urllib.urlopen(url)
data = json.loads(response.read())
ouJSON = 'primary.json'
with open(ouJSON, 'w') as f:
	json.dump(data, f)

Kansas State Senate District shapefiles (joining with the district boundary shapefiles)

SELECT c.geoid, nd.fips FROM nd
JOIN cb_2015_38_sldu_500k As c
ON nd.fips = c.geoid

UPDATE nd
SET the_geom = c.the_geom
FROM cb_2015_38_sldu_500k As c
WHERE nd.fips = c.geoid

SQL manipulation of fips

UPDATE alaska
SET fips = substring(fips from 1 for 3) || substring(fips from 6 for 7)
WHERE state_abbreviation = 'AK'

Simplifying Geometry

SELECT ST_Mem_Size(the_geom) / 1024 FROM primary_results_transposed_simplified

SELECT sum(ST_Mem_Size(the_geom) / 1024) FROM primary_results_transposed_simplified

SELECT sum(ST_Mem_Size(the_geom) / 1024) / 1024 FROM primary_results_transposed_simplified

Final-final sql manipulations

UPDATE table_29_3
SET geoid = '0' || geoid
WHERE length(geoid) = 4
AND cartodb_id < 2955

Data Observatory for counties

UPDATE table_29_3
SET the_geom = obs_getboundarybyid(geoid, 'us.census.tiger.county_clipped')
WHERE length(geoid) = 5

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

SELECT the_geom, geoid, 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
FROM table_29_3_copy_copy

For the 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%'

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

Populating with the DO measures

UPDATE final_election_subquery
SET total_population = OBS_GetUSCensusMeasure(ST_PointOnSurface(the_geom), 'Total Population', 'us.census.tiger.county_clipped')

Simplify Geometry

UPDATE final_primary_election
SET the_geom = ST_Simplify(the_geom, 0.0001)