This intermediate workshop was given by Aurelia Moser on May 13, 2015 at MediaLab Prado. As taught, it was 3 hours long.

City Cartography with CartoDB

BCN Footprint Map

Aurelia Moser, Map Scientist, CartoDB Workshop - MediaLab Prado

May 11-13, 2015, 2H30

Find this document here:

Find the code checkpoints here:

Catalan/Spain Flag

Outline

  1. Visualizing Data
    • Why Maps?
  2. Introduction to CartoDB
    • Examples
    • Tour of the interface
    • APIs / JS Libs
  3. Mapping Basics
    • Setting up accounts! + Data import
    • Datasets
  4. Mapping Data
    • Getting Geospatial Data + Data representation in CartoDB (SQL schema)
    • Geocoding + SQL/PostGIS
    • Merging Tables
    • Customizing UI
  5. Building a Map
  6. Building a Narrative
  7. Wrap-Up and Resources
    • CartoDB Links
    • Data Sources
    • Visualization Resources

Visualizing Data

Types of Visualizations

Source: The Data Visualization Catalogue.

PeriodicTable

Source: Periodic Table of Visualizations

####Some terms:

  • Time-Series: visualizations that include a temporal component, show change
  • Thematic Maps: maps related to a body of topics or a subject of discussion
  • Building Footprints: the area on a project site that is used by the building structure and is defined by the perimeter of the building plan. Parking lots, landscapes, and other nonbuilding facilities are not included in the building footprint

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

travel time

urban-interface

urban-reviewer

Tour of the interface

int-data int-upload int-vis int-share

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 that dynamically filters/affects/queries your mapped data stored in CartoDB
  • Import API - CRUD files in your CartoDB Account

Mapping Basics

Setting Up Accounts

You can setup a free student account today since we’re all learning: https://cartodb.com/signup?plan=academy

Data Import

We’re going to be building a visualization of land use data in Barcelona, particularly how building footprints and train lines fit into that projection.

data

We’ll be mapping building footprints according to data available in the CartoDB Data Library (c. 2014), exploring some choropleth methods and some SQL for manipulating these data.

Why?

parcelas_tweet

  • these data have names and physical features of the footprints
  • they also have a column for habitants or counts of inhabitants per footprint (presumably)
  • we can calculate population density by footprint

Datasets

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

Description Source Download
2014 Building Footprints CartoBCN parcelas_islas_barcelona.geojson
2012 Spain Railway Stations DataHub estaciones.geojson

** You can also import the data from our “Data Library.” **

DataLibrary

Select parcelas_islas_barcelona and pull it into you dashboard for manipulation.

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:

Downloading the Census Data/FIPS directly requires some finessing.

Data Check:

  • check the source and update date of your data
  • remove headers/extra columns (in Excel or Open Refine)
  • import the csv/xls/geojson and auto-geocoding via carto
  • correct column names to more intelligible terms
  • correct datatypes
  • do any preliminary sql or filtering that suits

correctDatatypes

Here is what it might look like when you upload your data:

Geocoding

Data representation in CartoDB

(SQL schema)

The most basic SQL statement is:

SELECT * FROM table_name

The * means everything. This means that all rows and columns from the table are given back once the query is run.

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
  )
  1. SELECT is what you’re requesting (required)
  2. FROM is where the data is located (required)
  3. WHERE is the filter on the data you’re requesting (optional)
  4. GROUP BY and ORDER BY are optional additions, you can read more about aggregate/other functions below.

Geocoding + SQL/PostGIS

There are two special columns in CartoDB:

  1. the_geom
  2. 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.

This is a SQL statement and you can load it in your visualization tray as a way of querying and exploring your data with immediate visual output. In this case, we are calculating the resetting the projection for the Barcelona polygons such that it matches better with the basemaps; this uses a PostGIS function ST_Transform to update the table with the correct projection. You can find another example with bikelanes here.

UPDATE table_name SET the_geom = ST_Transform(ST_setsrid (the_geom, 4230), 4326)

offset

This is a query that adds some more information to the table, calculating the relative space in each footprint per habitant, called person_area.

SELECT *,
	CASE WHEN
   		habitants > 0
        THEN
          ceil(ST_Area(the_geom::geography)/habitants)
        ELSE
          null
        END
          person_area
FROM parcelas_islas_barcelona

sql

You can enter queries, apply them, click on “create table from query” in the green field below the column names.

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 Wizard

wizard

The Wizard allows you to select your visualization “type” and customize color palettes, design details, and otherwise set the tone for your map. In the example above, I also included estaciones information, which you can find linked above to 2012 Railway Station Data should you like to also make a station map.

Here is an example of how you might write a query to connect the docs plotted by those data:

SELECT 0 as cartodb_id,
        ST_MakeLine(the_geom_webmercator ORDER BY cartodb_id ASC) AS the_geom_webmercator FROM estaciones

estaciones

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; emphasis on far over near-view

Check out visualization documentation for more.

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

Styling options

Styles available in the wizard:

  • 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. Notice the CartoCSS screenshot above.

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

Go back to tables

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 load the parselas data from the “Data Library” it has place placenames
  • this data source has placenames and population data
  • you can load them both into cartodb, and select the “merge tables” button
  • select column or spatial join

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

merge

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

  • create table and voilà

Building a Map

Once you load your data, you can play with the editor options to see what type of visualization you might light to make.

I started with the SQL query to add a column for person_area as mentioned above

person_area

You can then select that column in the Wizard for a “choropleth” map, and choose your colors.


BREAK

Let’s copy the repository for the next half of the workshop, and learn more about Github!

Github is a social coding site, and we can use it to store our documentation. We will have a longer workshop tomorrow with Chema about how Github works.

For now:

  1. Create an account on Github: https://github.com/
  2. View this repository: https://github.com/auremoser/prado-2015
  3. Let’s clone that repository: git@github.com:auremoser/prado-2015.git

clone

  • Open Terminal

term

  • type: cd Desktop

cd-desk

  • Go to Github.com
  • Select the URL of this repository: git@github.com:auremoser/prado-2015.git
  • type git clone git@github.com:auremoser/prado-2015.git

clone-2

  • type ls to list the files in your desktop
  • type cd prado-2015
  • then you are in your repository and you can manipulate the files, save them, and push them to the repository on github if you want.

In this repository (prado-2015), there is a file called notes. “notes” has a template.md file for documentation.

ls-notes

It looks like this:

template

You can fill it out with your project details to start documenting your project for the workshop.

  • Notes in this template are written in Markdown, which is a special syntax for formating text, and images.
  • You can learn about it here
  • Here is another example of project documentation.

Tomorrow we will revisit this, come to me with any questions :).

Think about how you would like to document your project, and fill out the template here. You can edit the file directly in your cloned repository.

There is also a template sample, in case you have trouble documenting your project.

NEXT: Advanced Mapping

  • TOMORROW: I will hold “mapping office hours”, come visit my table in the morning for help with your maps!

Quick map with CreateVis

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

You will need:

  • dataset from above
  • visjson from your account, you can reference mine to find yours too.
  • Basic Text Editor
  • Browser

Running Locally

  • You can open HTML files on your hard drive from a browser. Use CMD+O or CTRL+O like you’d do to open a file in any program.
  • You can also run a little server by navigating to the folder where you will store your files and running http-server &; you have node installed with http-server setup on most macs! if not, no stress, just follow along.

###VisJson

Share-vizualization

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

Creating Basic Visualization in JavaScript

Copy & paste template from here.

Overview of template:

  1. Included JavaScript libraries and CSS file
  2. map element
  3. <script> tags

Create basic visualization using createVis by copying and pasting the following either between script tags in your html or by making a file called [?].js (I used main.js in the template) and referencing it between your script tags:

window.onload = function() {
    var vizjson_url = ''; // <-- Paste viz.json URL between quotes

    cartodb.createVis(map_id, vizjson_url) // <-- Change map_id to 'map'
        .done(function(vis, layers) {
            // do stuff
            console.log("Map successfully created");
        })
        .error(function(err) {
            // report error
            console.log("An error occurred: " + err);
        });
}

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.

Edit the fields to match your map reload your browser window, your map should work.

Custom map with CreateLayer

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

createLayer is the other main method for bring maps to your browser.

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/f2735936-f268-11e4-8858-0e853d047bba/viz.json'; // <-- Paste viz.json URL between quotes

       var options = {
           sql: "SELECT *, CASE WHEN habitants > 0 THEN ceil(ST_Area(the_geom::geography)/habitants) ELSE null END person_area FROM parcelas_islas_barcelona",
           // cartocss: ""
       }

       var sublayers = [];

       // instantiate map object from Leaflet
       var mapObj = new L.Map(map, {
           center: [41.3833, 2.1833], // Barcelona, Spain
           zoom: 13 // zoom projection to adjust starting point zoom
       });


       // add basemap tiles
       L.tileLayer('http://{s}.basemaps.cartocdn.com/dark_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[2] = layer.getSubLayer(2);
               sublayers[0].set(options); // altering the SQL and CartoCSS; see above
               sublayers[1].hide(); // hiding the station line data
               sublayers[2].hide(); // hiding the station point data
           })
           .error(function(err) {
               console.log("Map not created: " + err);
           });
    }

One big difference here is that we explicitly expose the SQL and CartoCSS, allowing for easy customization.

createLayer

Edit the fields to match your map reload your browser window, your map should work.

Add SQL/CSS Templates

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

New goal: We’ll create an interactive map that allows us to toggle between the basic outline of parcelas and the view of person_area density (a choropleth) in those districts.

To accomplish this, we’ll use the SQL we worked on before.

You can do this a number of ways, we’ll be using SQL, you can read documentation on available function magic in the PostGIS docs and otherwise just follow along.

Going back to the createLayer example we just made:

  • Copy the following SQL into your index.html file below the <style> tags.
<script type='sql/text' id='sql'>
      SELECT
        *,
        CASE WHEN
          habitants > 0
        THEN
          ceil(ST_Area(the_geom::geography)/habitants)
        ELSE
          null
        END
          person_area
      FROM parcelas_islas_barcelona
</script>
  • 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.
<style type='cartocss/text' id='choropleth'>
    /** choropleth visualization */

    #parcelas_islas_barcelona{
      polygon-fill: #FFFFB2;
      polygon-opacity: 0.8;
      line-color: #FFF;
      line-width: 0;
      line-opacity: 1;
    }
    #parcelas_islas_barcelona [ person_area <= 145920] {
       polygon-fill: #BD0026;
    }
    #parcelas_islas_barcelona [ person_area <= 3192] {
       polygon-fill: #F03B20;
    }
    #parcelas_islas_barcelona [ person_area <= 1026] {
       polygon-fill: #FD8D3C;
    }
    #parcelas_islas_barcelona [ person_area <= 461] {
       polygon-fill: #FECC5C;
    }
    #parcelas_islas_barcelona [ person_area <= 191] {
       polygon-fill: #FFFFB2;
    }
    #parcelas_islas_barcelona [ person_area = null] {
       polygon-fill: #5CA2D1;
    }
</style>
  • Next replace the string for `sql in the options object with
$("#sql").text(),

(don’t forget the comma!), and the string after cartocss with

$("#choropleth").text()

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:

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

Check the checkpoint code here if you’re stuck. You can also run the SQL in the tray (not in the your local files) and the the map will populate. The advantage to adding it as a template, is that you can swap it for other SQL or run different queries with different template references locally, and you are not limited to one query option.

Reload your browser window, your map should work!

Add Interactivity - Buttons

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

createButtons

To add more interactivity, we’ll create two buttons to toggle between the Simple 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="simple"> tag set with the following CartoCSS style. Make sure the id is set to simple.

      /** simple visualization */
      #parcelas_islas_barcelona{
        polygon-fill: #5CA2D1;
        polygon-opacity: 0.7;
        line-color: #FFF;
        line-width: 0.25;
        line-opacity: 1;
      }
  • Next, let’s create some buttons. Put the following snippet below the div with an id='map'.
<div id="cartocss" class="layer_selector">
        <p>Layers</p>
        <ul>
            <li data="choropleth">Habitant Choropleth</li>
            <li data="simple">Building Map (no habitants)</li>
        </ul>
    </div>
  • Wire up the buttons with click events:
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);
      });
   }

sqlcss

Examples:

quake-sample

Infowindows + More

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

Adding infowindows in Editor

You can enable hover infowindows in your editor, that will port to your map and give you some choropleth context.

  • customization in html/css
  • all data in your table is available to you to populate the tooltips

Adding infowindows in JS

  • HTML templates
    • Handlebar notation
    • Customizing display of information
    • Pulling in images
<script type="infowindow/html" id="infowindow_template">
<div class="cartodb-tooltip-content-wrapper">
  <div class="cartodb-tooltip-content">
    <h4>Person Area:</h4>
    <p></p>
    <h4>% Area:</h4>
    <p></p>
  </div>
</div>
</script>

Then add this to the options:

interactivity: 'cartodb_id, name, person_area'

After sublayers[0].set(...), add this:

sublayers[0].infowindow.set('template', $('#infowindow_template').html());
  • Click events
    • On hover
    • On click

You can build on this, or checkout the demo block here to view the result of your work with some limited interactivity!

Final project here: BCN Person Map

info

Building Narrative

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

TimeSeries Data + Storytelling

Maps that tell Time - Torque

  1. Demonstrations in Brazil
  2. Tweets that mention sunrise map
  3. Animal migration patterns
  4. Beyonce Album Release
  5. Diwali Celebrated
  6. Ramadan Tweets w/OdysseyJS
  7. Alcatraz Escapees
  8. Lynching and the Press

Twitter Import

Maps that tell Stories - Odyssey JS

  1. Tour of Scotland
  2. Al Jazeera: Israeli-Palestinian Conflict by Tweets
  3. The Sounds of 11M
  4. Berlin Wall Historic Tour
  5. Maya Angelou Quotes

CartoDB Data in Charts

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

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 census track water density, check the code here.

footprint-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
Chart.js Bar Graph Building Footprint Data Aurelia’s Block  

More

  • sql.execute(SQL command) to extract data from your account, place into charts, infowindows, etc.
  • sql.getBounds(SQL command) to find the bounding box of data returned by SQL command

Resources

CartoDB

  1. Map Academy
  2. CartoDB Tutorials
  3. CartoDB Editor Documentation
  4. CartoDB APIs
  5. Community help on StackExchange
  6. CartoDB Map Gallery
  7. CartoDB Bootstrap Template by Chris Wong

Data

  1. World GeoSpatial Datasets: countries, cities, codes, flags, languages, latitude/longitude, etc.
  2. Center for National Geographic Information
  3. IDEE Metadata Catalog: spatial data from multiple agencies
  4. Datos Gob Es
  5. Open Data Euskadi: basque country
  6. Open Data BCN Barcelona city hall open data
  7. Open Data gencat: Catalunya
  8. SIGPAC data dictionary
  9. Spain National Railway Stations (2012)
  10. Geogig: distributed geospatial data
  11. International GIS Data - Penn State
  12. Projections Shapefiles for Spain

Visualization

  1. Charting Tools Repository
  2. Workshops @ CartoDB
  3. Recommended tools for Visualizations
  4. Perception Concerns
  5. Gestalt Theory
  6. Color Brewer or Geocolor
  7. Open Source Design a collection of repositories for free and open source projects!

My contact: aurelia@cartodb.com

If you make a map you’re proud of or just want to say hello, connect with me @auremoser

MD-Seal