This intermediate workshop was given by Aurelia Moser on July 7, 2015 at Hasadna: Meetup at Google Campus. As taught, it was 2 hours long.

Hasadna: Pollution Mapping + CartoDB

Aurelia Moser, CartoDB Workshop - Hasadna, IL

July 6, 2015

Find this document here:

Stackedit: http://tinyurl.com/stack-IL
Gist: http://tinyurl.com/gist-IL

Find the code checkpoints here:

Github: https://github.com/auremoser/hasadna

Outline

Visualizing Data
Introduction to CartoDB
- Examples
- Tour of the interface
- APIs / JS Libs
Mapping Basics
- Setting up accounts! + Data import
- Datasets (available here)
Mapping Data
- Geocoding + SQL/PostGIS
- Merging Tables
- Customizing UI
Building a Map
- Basics of VisJson (ckpt-1)
- Quick map with CreateVis (ckpt-2)
- Custom map with CreateLayer (ckpt-3)
- Add SQL/CSS Templates (ckpt-4)
- Add Buttons (ckpt-5)
- Infowindows (ckpt-6) + BONUS: Charts (ckpt-7)
Building a Narrative
- Case Study: Air Pollution Map
- Tell Time/Stories: Odyssey + Torque
- Graphs + Charts (Air Pollution Chart)
Wrap-Up and Resources

Visualizing Data

Types of Visualizations

Source: The Data Visualization Catalogue.

Some samples:

Time Travel Map

Source: Time Travel Between Counties, Carto.JS

County Chart

Source: Geogia County Car Crash Counts, C3.JS

####Some software:

CartoDB: light open source library and graphical user interface application for hosting and visualizing geospatial data
ChartJS: light library for creating charts and graphs

####Some resources:

Product

Why Maps?

Michael's Syrian Refugee visualization Map population by relative density

Maps give you more context than most visualizations.
They allow you to apply data to a recognizable topography.

Intro to CartoDB

Examples

urban-interface

urban-reviewer

Tour of the interface

int-data int-upload int-vis

APIs / JS Libs

You can read more about the CartoDB APIs and JS Library here

CartoJS - JS library for interacting with CartoDB
Maps API - generate public/private maps with data hosted on your CDB account
SQL API - run sql in your code to dynamically filter/request/query your mapped data stored in CartoDB via http calls
Import API - push data to your CartoDB Account

Datasets

You can fork the dataset we’ll be working with, and the files for the workshop here. Clone the repo to download.

Municipality Data (for polygons)
Pollution Data (for counts and numerical values)

We also have a “Data Library” full of datasets that you can mashup with your own data.

DataLibrary

Mapping Data

Getting Geospatial Data

Geospatial data is info that ids a geolocation and its characteristic features/frontiers, typically represented by points, lines, polygons, and/or complex geographic features.

Issues:

Comes in multiple formats (supported formats for CartoDB)
Sources uncertain
Contains errors
etc.

I based most of this demo on my assumptions, because I wanted to make a fast map, because the data didn’t have headers, I guessed on which fields corresponded to the pollution counts for transport/electrical/industrial factors contributing to air quality.

Geocoding + SQL/PostGIS

The most basic SQL statement is:

SELECT * FROM table_name

A more detailed query is like this:

SELECT
  name,
  height,
  age
FROM
  class_list
WHERE
  name = 'Aure'
  AND (
    height > 1.2
    OR
    height < 1.9
  )

SELECT is what you’re requesting (required)
FROM is where the data is located (required)
WHERE is the filter on the data you’re requesting (optional)
GROUP BY and ORDER BY are optional additions, you can read more about aggregate/other functions below.

There are two special columns in CartoDB:

the_geom
the_geom_webmercator

The first of these is in the units of standard latitude/longitude, while the second is a projection based on the original Mercator projection but optimized for the web.

If you want to run SQL commands and see your map update, make sure to SELECT the the_geom_webmercator because this is the column that’s used for mapping–the other is more of a convenience column since most datasets use lat/long.

You can also do cool things like re-set the projection in SQL. Say you load in your data and want to make sure it’s in ITM (Israeli Transverse Mercator) or another ESPG, you can do that on the fly.

SELECT cartodb_id, ST_Transform(the_geom_webmercator,2039) AS the_geom_webmercator FROM aureliamoser.municipalities

ITM

“municipalities” is my table name, “aureliamoser” is my username I reset the data to the correct projection and can generate a table with it.

Customizing UI

You have myriad customization options in the in-browser editor.

sql - run sql and postgis functions across your data
wizard - adjust the type, colors and fills in your map
infowindow - create hovers, tooltips with information from your datatables
css - customize the css and style of your map outside the wizard
legends - create keys for your map
filters - filter the data without sql

Filters & SQL

filters

Filters are a great way to explore your data. Besides filtering your data, they allow you to see histograms of the distributions, the number of unique entries, or a search box for columns that have a large number of text entries.

CartoDB Editor

wizard

The Editor allows you to select your visualization “type” and customize color palettes, design details, and otherwise set the tone for your map.

Types of visualizations

Simple – most basic visualization
Cluster – counts number of points within a certain binned region
Choropleth – makes a histogram of your data and gives bins different colors depending on the color ramp chosen
Category – color data based on unique category (works best for a handful of unique types)
Bubble – size markers based on column values
Intensity – colors by density
Density – data aggregated by number of points within a hexagon
Torque – temporal visualization of data
Heat – more fluid map of concentration; ephasis on far over near-view

heat

Check out visualization documentation for more.

###Simple Map The visualization style simple is the default visualization for all maps.

Simple visualization

Styles available in the wizard

Styling options

Marker Fill: change the size, color, and opacity of all markers
Marker Stroke: change the width, color, and opacity of every marker’s border
Composite Operation: change the color of markers when they overlap
Label Text: Text appearing by a marker (can be from columns)

Infowindows/hovers

Infowindow options

Select which column data appear in infowindow by toggling column on
Customize further by selecting HTML-view

Change basemap

Select basemaps from different providers, use custom color, NASA data, MapBox tiles, etc.

Basemap options

Choropleth

Choropleth maps show map elements colored according to where a value associated with the map element falls in a range. It’s like a histogram where each bin is colored differently according to a color scale you pick.

Quantification is an option to pay attention to since it controls how the data is binned into different colors.

Equal interval gives bins of equal size across the range, which means that outliers stand out.

Equal-Interval

Quantile bins so that each quantile has approximately the same number of values. This is the default and works for most “normal” data.

Quant

Jenks aims to increase the standard deviation between each group of data while decreasing the standard deviation within each group. In other words, it increases the similarity within a given group in conjunction with the differences from each of the other groups. The Jenks method does this by shuffling data across each group until it detects an optimization.

Jenks

Heads/Tails breaks can be powerful for data with a long-tail distribution.

heads

Play around with them and see what works best for your dataset.

CartoCSS basics

CartoCSS is the styling language for our maps.

CartoCSS screenshot

Legends

…can be easily customized!

Legend

You have the option of giving it a title, and changing the text for the colors. You can also change the colors manually, or, even better, change the color ramp back in the wizard.

Click on the 90-degree arrow to get back to view your tables/visualizations

Navigation for tables

Navigation for maps

Merging Tables

Joining and merging tables to make one dataset is a common need. Say you have two datasets related to the same place/map and need to combine them so that they can share the same geometry.

You can do this in SQL read more here, but CartoDB also has an in-editor button for that.

mergeButton

Here is a usecase relative to these datasets:

when you open the pollution data it has municipality names but geometry
when you open the municipality data it has geometries and names but not pollution values
you can load them both into cartodb, and select the “merge tables” button to join them on their mutual name column into one unified table
select column or spatial join

joins

select the columns that you want to join on, in this case, both datasets share a name

merge

toggle the columns you want to exist in your new “joined” dataset

mergGen

Building a Map

Using the data above, you can load in two tables, merge them in the same visualization with the “merge button” or with SQL, and visualize them on the map with the GUI, or pull them into your own applications with the libraries and APIs.

Here’s a few steps you can follow, the code it in github (clone and follow along or do go through the steps in your own account).

You will need:

datasets from above
visjson from your account, you can reference mine to find yours too.
Basic Text Editor
Browser
- Basics of VisJson (ckpt-1)
- Quick map with CreateVis (ckpt-2)
- Custom map with CreateLayer (ckpt-3)
- Add SQL/CSS Templates (ckpt-4)
- Add Buttons (ckpt-5)
- Infowindows (ckpt-6)
  - BONUS: Charts (ckpt-7)

VisJson

The viz.json file is the main source of data for CartoDB JavaScript functions (createVis and createLayer) for creating visualizations in the browser.

visjson

Structure of file: JSON
Defines how to access data: listing servers, subdomains, etc.
Most important for developers is the layers array because it explicitly shows the structure of how visualizations are put together
- Defines base maps, if applicable, as layers[0]
- CartoDB data layer is layers[1], may consist of multiple sublayers
  - Defines infowindows, which we’ll cover in this workshop
  - Defines data accessed by using a SQL statement
  - Defines styling for tile layers, if applicable
  - Defines interactivity (what data shows up on layer events)
  - layer_name is the also the name of table where data comes from in the account with key user_name

You can view it by opening a text editor and loading the file, or downloading a JSON viewer extension for inbrowser views (Chrome or Firefox).

CreateVis

createVis is excellent for creating maps quickly with very little code. There is a lot of customization with it as well. The documentation is here.

Here’s a reference point for this section ckpt-2

CreateLayer

createLayer is the other main method for bring maps to your browser. One big difference here is that we explicitly expose the SQL and CartoCSS, allowing for easy customization.

The following is the basic createLayer structure (depends on Leaflet.js):


window.onload = function() {
    var vizjson_url = 'https://team.cartodb.com/u/aureliamoser/api/v2/viz/f1af78c8-23c7-11e5-bff0-0e9d821ea90d/viz.json'; // <-- Paste viz.json URL between quotes

    var options = {
           sql: "SELECT * FROM pollutant_emissions_merge",
           // cartocss: ""
       }

       var sublayers = [];

       // instantiate map object from Leaflet
       var mapObj = new L.Map(map, {  // <-- Replace map_id with your #id for rendering
           center: [31.5000, 34.9000], // Telaviv, IL
           zoom: 8 // zoom projection to adjust starting point zoom
       });


       // add basemap tiles
       L.tileLayer('http://{s}.basemaps.cartocdn.com/light_nolabels/{z}/{x}/{y}.png', {
           attribution: '&copy; <a href="http://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors'
       }).addTo(mapObj);

       // add data tile layers here (if you have multiple layers, you can manipulate them in js here)
       cartodb.createLayer(mapObj,vizjson_url)
           .addTo(mapObj)
           .done(function(layer) {
               console.log("Map successfully created.");
               sublayers[0] = layer.getSubLayer(0);
               sublayers[1] = layer.getSubLayer(1);
               sublayers[1].set(options); // altering the SQL and CartoCSS; see above
               sublayers[1].hide(); // hiding the municipality basic polygons
           })
           .error(function(err) {
               console.log("Map not created: " + err);
           });
    }

CreateLayer

Here’s a reference point for this section ckpt-3

Add SQL/CSS Templates

You can create interactive maps that respond to javascript methods a number of ways, we’ll be using CSS templates, you can also make SQL templates, you can read documentation on available function magic in the PostGIS docs and otherwise just follow along.

Paste the following CartoCSS structure in the <head> section of your webpage.
This is a pre-configured Choropleth style. You could also create one on the fly by calculating the range in data and creating bins within that range.

<!-- CHOROPLETH CSS -->
    <style type='cartocss/text' id='choropleth'>
      /** choropleth visualization */

      #pollutant_emissions_merge{
        polygon-fill: #FFFFCC;
        polygon-opacity: 0.8;
        line-color: #FFFFFF;
        line-width: 0;
        line-opacity: 1;
      }
      #pollutant_emissions_merge [ industry <= 6590.1] {
         polygon-fill: #253494;
      }
      #pollutant_emissions_merge [ industry <= 1041.9] {
         polygon-fill: #2C7FB8;
      }
      #pollutant_emissions_merge [ industry <= 130.1] {
         polygon-fill: #41B6C4;
      }
      #pollutant_emissions_merge [ industry <= 38.2] {
         polygon-fill: #A1DAB4;
      }
      #pollutant_emissions_merge [ industry <= 15.7] {
         polygon-fill: #FFFFCC;
      }
    </style>

These two pieces of code are just a jQuery operation that finds the HTML element that has an id of sql or cartocss and extracts the text contained within it.

add a sublayer reference to your data tile layer function at the end of your js, sublayer 1 is my second layer, zero indexed so I pass it in as “1”:

sublayers[1].set(options); // altering the SQL and CartoCSS; see above

SQL

Here’s a reference point for this section: ckpt-4

Add Interactivity - Buttons

To add more interactivity, we’ll create two buttons to toggle between the Choropleth map view and the view that gives a choropleth map. We can easily do this in CartoDB by using the sublayer.setSQL() and sublayer.setCartoCSS() methods to change the data.

First, create another <style type="cartocss/text" id="electric"> tag set with the following CartoCSS style. Make sure the id is set to simple.

Next, let’s create some buttons. Put the following snippet below the div with an id='map'.

<!-- ADD BUTTONS -->
    <div id="cartocss" class="layer_selector">
        <p><strong>Layers</strong></p>
        <ul>
            <li data="industry">Industrial Pollution</li>
            <li data="electric">Electrical Pollution</li>
        </ul>
    </div>

function createSelector(layer) {
      var cartocss = "";
      var $options = $(".layer_selector").find("li");
      $options.click(function(e) {
          var $li = $(e.target);
          var selected = $li.attr('data');

          $options.removeClass('selected');
          $li.addClass('selected');

          cartocss = $('#'+selected).text();

          layer[0].setCartoCSS(cartocss);
      });
   }

Examples:

quake-sample

buttons

Here’s a reference point for this section ckpt-5

Here’s a reference point for adding infowindows ckpt-6

Building Narrative

Final

Outside of the CartoJS library, we have others to help you build dynamic narrative with your data.

Tell Time + Stories

Maps that tell Time - Torque

Twitter Import

Maps that tell Stories - Odyssey JS

Talk Data in Charts

You can use CartoDB’s SQL API to query your data and pull it into any charting library of your choosing.

You can easily wire up a chart of pollution data, check the code here.

chart

Learn more about it here!

Here are some examples:

Type	Title	Link/Demo	BlogPost
Chart.js Bar Graph	Traffic Data	Aurelia’s Block
Highcharts	Sensor Data	Github / Demo	MOW Post
Highcharts	Weather Data	Aurelia’s Block	Tutorial
Chart.js Line Graph	Tornado Data	Andrew’s Block
Plot.ly	Earthquake Data	Plotly Tutorial	CartoDB Blog

sql.execute(SQL command) to extract data from your account, place into charts, infowindows, etc.
- Using Chart.js
sql.getBounds(SQL command) to find the bounding box of data returned by SQL command
- Porpoise Map

Resources

##CartoDB

Map Academy
- CartoDB.js – build a web app to visualize your data, allowing for user interaction
- SQL and PostGIS
CartoDB Tutorials
CartoDB Editor Documentation
CartoDB APIs
Community help on StackExchange
CartoDB Map Gallery
CartoDB Bootstrap Template by Chris Wong

##Visualization

My contact: aurelia@cartodb.com

If you make a map you’re proud of or just want to say hello, connect with me @auremoser. And if you have a minute, please give me feedback on the workshop here.

CartoDB Workshops Materials for Teaching Maps

Merging Data + Municipal Maps Hasadna: Meetup at Google Campus

Hasadna: Pollution Mapping + CartoDB

Outline

Visualizing Data

Some samples:

Why Maps?

Intro to CartoDB

Examples

Tour of the interface

APIs / JS Libs

Datasets

Mapping Data

Getting Geospatial Data

Issues:

Geocoding + SQL/PostGIS

Customizing UI

Filters & SQL

CartoDB Editor

Types of visualizations

Infowindows/hovers

Change basemap

Choropleth

CartoCSS basics

Legends

Navigation

Merging Tables

Building a Map

VisJson

CreateVis

Here’s a reference point for this section ckpt-2

CreateLayer

Here’s a reference point for this section ckpt-3

Add SQL/CSS Templates

Here’s a reference point for this section: ckpt-4

Add Interactivity - Buttons

Here’s a reference point for this section ckpt-5

Here’s a reference point for adding infowindows ckpt-6

Building Narrative

Tell Time + Stories

Talk Data in Charts

More

Resources