With Jorge Sanz (@xurxosanz - jorge@cartodb.com), materials from Mamata Akella (@mamataakella) and Andy Eschbacher (@MrEPhysics)
Slides here
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/mapThis 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_boundariesSince 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.
To see everything together we can leverage the GROUP BY clause in order to get maximum and minimums for both candidates grouped by where they won:
SELECT
winner,
max(pct_obm) as max_obama,
min(pct_obm) as min_obama,
max(pct_rom) as max_romney,
min(pct_rom) as min_romney
FROM election_results_2012
GROUP BY winner
Since the minimum value is around 48% for each, we can choose breaks such as these:
45 - 55for a smaller win55 - 65for a larger win65+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.
If you want to get more into SQL our SQL and PostGIS in CartoDB course will give you a more in detail view of how to leverage the best geospatial database.
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 not part of the default spatial_ref_sys table in your CartoDB account. For a more detailed discussion on projections with CartoDB see this blog. So if you run the next queries on your account you'll get this error:
In order to fix this you need to run this query on the SQL tray so CartoDB will know about this projection:
INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext)
values ( 5070, 'EPSG', 5070, '+proj=aea +lat_1=29.5 +lat_2=45.5 +lat_0=23
+lon_0=-96 +x_0=0 +y_0=0 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m
+no_defs ', 'PROJCS["NAD83 / Conus Albers",GEOGCS["NAD83",
DATUM["North_American_Datum_1983",SPHEROID["GRS 1980",6378137,298.257222101,
AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6269"]],
PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",
0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4269"]],
PROJECTION["Albers_Conic_Equal_Area"],PARAMETER["standard_parallel_1",29.5],
PARAMETER["standard_parallel_2",45.5],PARAMETER["latitude_of_center",23],
PARAMETER["longitude_of_center",-96],PARAMETER["false_easting",0],
PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],
AXIS["X",EAST],AXIS["Y",NORTH],AUTHORITY["EPSG","5070"]]');Don't be afraid, just copy & paste this SQL on your tray and execute it. From now on you can use Albers projection on your maps!
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_idneeds 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_boundariesstate_county_boundaries (second copy)
SELECT
ST_Transform(the_geom, 5070) AS
the_geom_webmercator
FROM
state_county_boundarieselection_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_2012Now 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.
- First, let's rename each layer and reorder them as follows:
referenceelectionsbase
- 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)
- 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
CSSin 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
- 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
featurecolumn - We'll symbolize state lines and county lines (depending on zoom level) so we'll need the
featureattribute and its two valuescountyandstateto 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
featureattribute 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 smallerline-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.
Check the details of SQLs and symbology on this CDBFiddle
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.
- 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_2012layer
- 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_2012layer by clicking onCSS - 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
winnerfield:
@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
- 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;
}
}
}
Check the details of SQLs and symbology on this CDBFiddle
Again, duplicate your map to produce this new map.
To produce a proportional map we need to compute the size of the symbol for every county. To do that we will do two things:
- We will create a sub query that will obtain the maximum difference between the results of both candidates
- Then we will apply a normalization formula using that difference
The next query does the job but also instead of returning the county boundary as in the previous map it will return its centroid as a point geometry.
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 descSince 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_sizeattribute formarker-width, and then settingmarker-allow-overlaptotrueso 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
winnerattribute 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);
}
}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 dark">
<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>
Check the details of SQLs and symbology on this CDBFiddle
Put all of your maps into a nifty template:
- Live version: http://bl.ocks.org/jsanz/raw/c28f0d5bd2c518cddf5c/
- Source code: https://gist.github.com/jsanz/c28f0d5bd2c518cddf5c#file-app-js
In our maps Alaska and Hawaii get too far from the main map. That's pretty common in many country maps so we'll see here how to move those parts to get them closer to the main map to make them more readable.
What we will do is a bit of PostGIS fiddling here in order to detect the geometries on certain bounding boxes and then translate them to a more appropriate place.
Duplicate your base map and set this query as the base layer:
SELECT
CASE
-- Hawaii
WHEN
the_geom_webmercator && ST_MakeEnvelope(-18050757,1967794,-16907259,2621484,3857)
THEN
ST_Translate(
ST_Transform(the_geom, 5070),
4.5e6,-1.5e6)
-- Alaska
WHEN
the_geom_webmercator && ST_MakeEnvelope(-20878927,6540563,-13629027,11491237,3857)
THEN
ST_Translate(
ST_Transform(the_geom, 5070),
-.5e6,-4.5e6)
-- Rest of the geometries
ELSE
ST_Transform(the_geom, 5070)
END AS the_geom_webmercator,
feature
FROM
state_county_boundariesinstead of the original:
SELECT
ST_Transform(the_geom, 5070) AS
the_geom_webmercator,
feature
FROM
state_county_boundariesIn our query what we do is checking if the geometry bounding box is intersecting a rectangle made by the ST_MakeEnvelope. To get those coordinates boxes you can use a tool like bboxfinder.com. Once a geometry is trapped by the condition then use the ST_Translate to move it in the X and Y axes. Those axes increments can be guessed just by try and error right on the editor, feel free to change them to find a position for Alaska and Hawaii that suits better your map on your web application.
If you apply the change carefully to all the layers you can get something like this map.
Check the details of SQLs and symbology on this CDBFiddle