Skip to content

Instantly share code, notes, and snippets.

@andy-esch
Last active September 16, 2016 03:59
Show Gist options
  • Save andy-esch/03a1c707ffafffbbd277 to your computer and use it in GitHub Desktop.
Save andy-esch/03a1c707ffafffbbd277 to your computer and use it in GitHub Desktop.
CartoCamp Elections

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:

  1. Opening the tray on the right
  2. Clicking on SQL
  3. Clicking on the hyper-linked table name
  4. 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:

  1. Clicking "+ Add Layer" on the top of the tray on the right,
  2. Click "Connect Dataset"
  3. 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:

basemap

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

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:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment