andy-esch/README.md

## README.md

      
    Raw
  

              README.md
            
          
    Election Mapping Bonanza

With Mamata Akella (@mamataakella) and Andy Eschbacher (@MrEPhysics)
Presentations here
Part 1: Importing our Datasets

We are going to be using a dataset of 2012 Presidential Election Results from Data.gov. To make it easier tonight, we made a simplified version.
To import it into your account, go to the following page and click "CREATE MAP":
https://elections-cartocamp.cartodb.com/tables/election_results_2012/public/map
This will import the dataset as cartodb_query or something like that. We need to change the name of the table to something more reasonable. Change this by:

Opening the tray on the right
Clicking on SQL
Clicking on the hyper-linked table name
and finally double-clicking the table name in the upper right

Go back to the original map by going to your dashboard, clicking the Datasets/Map dropdown on the top, and then choosing the map that was created when you imported your dataset.
The next dataset we will import by connecting with an external source. Start by:

Clicking "+ Add Layer" on the top of the tray on the right,
Click "Connect Dataset"
And paste in the following URL:

https://elections-cartocamp.cartodb.com:443/api/v2/sql?q=select%20*%20from%20public.state_county_boundaries&format=geojson&filename=state_county_boundaries
Exploring Our Dataset with SQL

Since we are making a choropleth map displaying the election results, we need to find the breaks for the vote percentages to change the colors that correspond to voting within ranges.
We can explore the bounds of our data using the min(value) and max(value) aggregate functions built into SQL. We will also need to filter by the winning candidate.
To find the minimum percentage Obama got to win a county, we would do the following:
SELECT 
  min(pct_obm) 
FROM 
  election_results_2012 
WHERE 
  winner = 'Obama'
This will produce:
  min
--------
48.72348

We are using a basic SELECT over an aggregate of one column and filtering by which candidate won. We will be doing similar queries later in this workshop.
Similarly, we can do the same to find the maximum for Obama, and the min and max for Romney. Once we have these values we can use them to assign values to classes to visualize our data.
Since the minimum value is around 48% for each, we can choose breaks such as these:

45 - 55 for a smaller win
55 - 65 for a larger win
65+ for a huge win

In CartoCSS, we will need to write rules similar to this to make it symbolize:
#layer [pct_rom > 45] {
  'light red';
}
#layer [pct_rom > 55] {
  'medium red';
}
#layer [pct_rom > 65] {
  'dark red';
}
We don't need any of that yet, but now that we have a good understanding of our dataset, let's design a basemap for it.
Part 2: Projections

Our final maps use the Albers Equal Area Conic projection centered on the contiguous United States (SRID 5070).
This is a common projection for thematic maps of the US. This is an equal area projection
meaning areas are preserved and distortion is minimized.
This projection is part of the default spatial_ref_sys table in your CartoDB account.
For a more detailed discussion on projections with CartoDB see this blog.
The following SQL queries do a couple of things:

project the data using ST_Transform
and also define any attributes that we'll need for styling and/or querying later in the process
cartodb_id needs to be selected to enable interactivity on any layer

state_county_boundaries (first copy)
SELECT 
  ST_Transform(the_geom, 5070) 
AS 
  the_geom_webmercator,
  feature
FROM
  state_county_boundaries
state_county_boundaries (second copy)
SELECT 
  ST_Transform(the_geom, 5070) 
AS 
  the_geom_webmercator
FROM
  state_county_boundaries
election_results_2012
SELECT 
  ST_Transform(the_geom, 5070) 
AS 
  the_geom_webmercator,
  cartodb_id,
  county,
  fips,
  obama,
  others,
  pct_obm,
  pct_othr,
  pct_rom,
  pct_wnr,
  romney,
  state,
  state_fips,
  ttl_vt,
  winner
FROM
  election_results_2012
Part 3: Basemap Design

Now that the data are added, projected, and the attributes we need are queried, we'll make a simple basemap that we can use for all of our election maps:

The purpose of the basemap is to provide geographic context to help interpret the election results without distracting from them visually.
Rename and Order Layers


First, let's rename each layer and reorder them as follows:

reference
elections
base


Turn off Elections Data and Positron


Since we are designing the basemap right now, we can turn off the elections layer
Let's also turn off the default basemap Positron and make the background white

click the option Change Basemap in the bottom left hand corner of the map editor view
next, choose the option for Custom
and then click the white color chip (#FFFFFF)


Base Layer Design


We'll start with the base layer


This is the solid background for the basemap. We won't symbolize lines in this layer, we'll do that in the reference layer.
Expand the CartoCSS Editor by clicking on CSS in the right hand layer panel
We'll modify the default CartoCSS to just fill the polygon with a neutral gray:

#state_county_boundaries {
   polygon-fill: #E1E1E1;
}

Click Apply Style to see the changes

Reference Layer Design


We'll start with the reference layer where we'll symbolize state and county lines


Let's look at DATA VIEW to see what attributes we have in the feature column
We'll symbolize state lines and county lines (depending on zoom level) so we'll need the feature attribute and its two values county and state to do that
Let's go back to MAP VIEW and expand the reference layer and modify the defualt CartoCSS
First, let's differentiate between which lines are state lines and which lines are county lines using the feature attribute and assigning each type a bold color:

#state_county_boundaries {
   line-color: #3B007F;
   line-width: 0.5;
   line-opacity: 1;
 
   [feature='state']{
     line-color: blue;
   }
 
   [feature='county']{
     line-color: green;
   }
 }

Next, we'll define which zoom level each layer will draw:

#state_county_boundaries {
  [feature='state'][zoom>=4],
  [feature='county'][zoom>=5]{
  
    line-color: #3B007F;
    line-width: 0.5;
    line-opacity: 1;
  
    [feature='state']{
      line-color: blue;
    }
  
    [feature='county']{
      line-color: green;
    }
  }
}

And then we'll assign some global variables to all lines and more specific styling to state lines and county lines specifically
Since we want all lines to be white, we can set that as a global property:

#state_county_boundaries {
   [feature='state'][zoom>=4],
   [feature='county'][zoom>=5]{
 
     line-color: #3B007F;
     line-width: 0.5;
     line-opacity: 1;
 ...

Next, we can assign feature specific styling for state lines (with a larger line-width) and county lines (with a smaller line-width) to push them to the background:

#state_county_boundaries {

  [feature='states'][zoom>=4],
  [feature='county'][zoom>=5]{

    line-color: #fff;

    [feature='states']{
      line-width: 1;
    }
  
    [feature='county']{
      line-width: 0.25;
    }
  }
}
Ok! Now we're done with the basemap. Once we get our thematic information sandwiched in, we can adjust the design and any zoom dependant styling we might need.
Part 4: Choropleth Map

Design Time!

Now that we know the values to use in the data, we'll write out the CartoCSS to symbolize each range of values for each candidate using appropriate colors.
Duplicate the Current Map


We'll keep this version of the map as our basemap template and make a copy to design the other maps
In the top right of the MAP VIEW click Edit and choose the option to Duplicate map
Rename the new map to Elections: Choropleth
Turn on the elections_2012 layer

Assign Colors to Each Candidate


We have two colors that we'll use for our maps a blue (#2F4886) for Obama/Democrat and a red (#AD373E) for Romney/Republican. We'll assign these two colors as CartoCSS variables that we can use throughout the different styles election maps
Open the CartoCSS Editor for the elections_2012 layer by clicking on CSS
Add these two variables above the CartoCSS:

@obama: #2F4886;
@romney:#AD373E;


As a first step, let's color each county based on the winner using the color variables for each candidate based on the winner field:

@obama: #2F4886;
@romney:#AD373E;
 
#election_results_2012 {
  
  //style for Obama
  [winner='Obama'] {
    polygon-fill: @obama;
  }
  
  //style for Romney
  [winner='Romney'] {
    polygon-fill: @romney;
  }
}

Click Apply Style to see the map update

Symbolize Each County by Winner and Percent Vote


Next, we'll write out the CartoCSS to symbolize each county based on the percentage votes for each candidate in the counties they won using the classifications we came up with. The fields that we'll use are winner,pct_rom,pct_obm
The three breaks that we determined are:

>=45
>=55
>=65


We'll use these numbers to write out our class breaks in CartoCSS and use a CartoCSS color variable (lighten) to make counties with less votes lighter

Let's start with Obama:


@obama: #2F4886;
@romney:#AD373E;
 
#election_results_2012 {
  
  //style for Obama
  [winner='Obama'] {
    polygon-fill: @obama;
    
    [pct_obm >= 45]{
      polygon-fill: lighten(@obama,40);
    }
    [pct_obm >= 55]{
      polygon-fill: lighten(@obama,20);
    }
    [pct_obm >= 65]{
      polygon-fill:@obama;
    }
  }
  
  //style for Romney
  [winner='Romney'] {
    polygon-fill: @romney;
  }
}

And then, the same for Romney:

@obama: #2F4886;
@romney:#AD373E;
 
#election_results_2012 {
  
  //style for Obama
  [winner='Obama'] {
    polygon-fill: @obama;
    
    [pct_obm >= 45]{
      polygon-fill: lighten(@obama,40);
    }
    [pct_obm >= 55]{
      polygon-fill: lighten(@obama,20);
    }
    [pct_obm >= 65]{
      polygon-fill:@obama;
    }
  }
  
  //style for Romney
  [winner='Romney'] {
    polygon-fill: @romney;
  
    [pct_rom >= 45]{
      polygon-fill: lighten(@romney,40);
    }
    [pct_rom >= 55]{
      polygon-fill: lighten(@romney,20);
    }
    [pct_rom >= 65]{
      polygon-fill: @romney;
    }
  }
}
Part 5: Proportional Symbol Map

Slide on proportional symbols

With ms As (SELECT max(abs(romney-obama)) As max_diff FROM election_results_2012)

SELECT 
  abs(romney - obama) As vote_diff,
  50 * sqrt(abs(romney - obama) / max_diff) As symbol_size,
  winner,
  romney,
  obama,
  ttl_vt,
  ST_Transform(ST_Centroid(the_geom),5070) As the_geom_webmercator,
  state_fips,
  fips,
  state,
  county,
  round(pct_obm::numeric,2) AS pct_obm,
  round(pct_rom::numeric,2) AS pct_rom,
  cartodb_id
FROM 
  election_results_2012, ms
ORDER BY
  symbol_size desc
Design Time!

Since all of the cartographic thinking has been written into the data for this map, the amount of styling that we have to do is minimal. We'll use the field symbol_size to assign symbol sizes, use the colors we have for Obama and Romney, and then make some final tweaks to the overall design.

First, let's get the ordering of the data right in the map
We want the proportional symbols to draw on top of the county and state lines with the solid base on the bottom
Next, open the CartoCSS Editor for the layer


First, we'll add our color variables for both candidates, symbolize the points using the symbol_size attribute for marker-width, and then setting marker-allow-overlap to true so all of the symbols draw:

@obama: #2F4886;
@romney:#AD373E;
 
#election_results_2012 {
  marker-width: [symbol_size];
  marker-allow-overlap: true;
}
 

Next, we'll use the winner attribute to set the color for each symbol:

@obama: #2F4886;
@romney:#AD373E;

#election_results_2012 {

  marker-width: [symbol_size];
  marker-allow-overlap: true;
 
  [winner='Obama']{
    marker-fill: @obama;
  }
 
  [winner='Romney']{
    marker-fill: @romney;
  }
 
}

And, finally, we'll add an outline to the points so the overlapping ones are visible against each other

#election_results_2012 {

  marker-width: [symbol_size];
  marker-allow-overlap: true;
  marker-line-width: 0.5;

  [winner='Obama']{
    marker-fill: @obama;
    marker-line-color: lighten(@obama,25);
  }

  [winner='Romney']{
    marker-fill: @romney;
    marker-line-color: lighten(@romney,25);
  }
}
Popup configuration

With a bit of basic HTML and the use of Mustache templates for displaying values from the database, we can create hovers for each of the symbols to give the underlying vote percentage.
To change the template, go to the Infowindow tray, click on "Hover" at the top of the tray. First select all of the fields by toggling the last switch on the bottom of the list.
Next click on the </> tag in the upper right to customize the HTML. Replace the HTML there with the following:
<div class="cartodb-tooltip-content-wrapper">
  <div class="cartodb-tooltip-content">
      <p><b>{{county}}, {{state}}</b></p>
    <p>Romney (R): {{pct_rom}}%</p>
    <p>Obama (D): {{pct_obm}}%</p>
  </div>
</div>
Part 6: Bonus Section

Put all of your maps into a nifty template:

Live version: http://bl.ocks.org/andrewxhill/raw/f200085101b228000094/
Source code: http://bl.ocks.org/andrewxhill/f200085101b228000094/