Social-Good Startups with CartoDB World Bank: Energy and Econ Data

This intermediate workshop was given by Aurelia Moser on February 21, 2015 at World Bank: Energy and Econ Data. As taught, it was 3 hours long.

Making Sense of Data with Maps: Time-Series Graphs and Thematic Maps, with CartoDB

Aurelia Moser, Map Scientist, CartoDB

Workshop - Beirut, Lebanon

February 22, 2015

Find this document here:

Find the demo-maps here:

Lebanon Flag from Wikipedia

Outline

  1. Visualizing Data
    • Why Maps?
  2. Introduction to Mapping with CartoDB
    • Examples
    • Tour of the interface
  3. Mapping Basics
    • Setting up accounts!
    • Data import
    • Datasets
  4. <BREAK - 10 minutes>
  5. Mapping Data
    • Getting Data
    • What is geospatial data? + Data representation in CartoDB (SQL schema)
    • Geocoding
    • SQL/PostGIS
    • Customizing UI
    • Adding Layers
    • Sharing your Visualization
  6. <BREAK - 10 minutes>
  7. Case Study: Blackout Map
  8. <BREAK - 10 minutes>
  9. Wrap-Up and Resources

Visualizing Data

Types 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

Some software:

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

Some resources:

Color Brewer

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.

Introduction to Mapping with CartoDB

Examples

Maps that tell Stories - Odyssey JS

  1. Tour of Scotland
  2. Al Jazeera: Israeli-Palestinian Conflict by Tweets
  3. The Sounds of 11M

Maps that tell Time - Torque

  1. Demonstrations in Brazil
  2. Tweets that mention sunrise map
  3. Animal migration patterns
  4. Beyonce Album Release

More in the CartoDB Gallery

Tour of the interface

Data Import

Basic Data Import

Data import dialog

Most major formats for storing data: Excel Spreadsheets, CSV files, Shapefiles, KML (Google Earth), etc.

See complete list here.

  1. Import by URL! Super handy when in a workshop and you don’t want to overwhelm the bandwidth
  2. Select file from your HD
  3. Common Data contains useful datasets for everyday use (admin regions, USGS earthquake data, ports and their locations, and many more)

Integration with Google Drive and Dropbox.

Twitter firehose access for Enterprise accounts.

Twitter Import

Data tables in CartoDB

Schema or column names

Column names

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.

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

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. 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.
  • Quantile bins so that each quantile has approximately the same number of values.

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 bar

Mapping Basics

Setting up accounts!

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

Data import

We’re going to be building visualizations about blackouts.

We’ll be mapping energy, population and temperature data as an index of power outages, as per a few relevant if not recent articles on the topic.

Problem

This is a common problem in many places globally.

Vital Signs

Some inspiration projects:

EPL Loss

You can find some more sample datasets related to Lebanon in the “Data” section at the bottom of this page.

###Datasets You can download the datasets we’ll be working with, and the files for the workshop here.

Description Source Download Dropbox
Population Data World Bank pop_2000s pop_2000s
Electrical Power Loss Data World Bank epl_2009 / epl_2000s epl_2009 epl_2000s

BREAK - 10 minutes

Mapping Data

Getting Data

  • Comes in multiple formats
  • Sources uncertain
  • Contains errors
  • etc.

Downloading the Electric Power Loss Data required some finessing:

  • removing headers/extra columns (in Excel or Open Refine)
  • importing the csv and auto-geocoding via carto
  • correcting column names to more intelligible terms

EPL Loss Data

This is what it might look like when you import it:

EPL-first upload

What is geospatial data?

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

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

###Consider the geom 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.

SQL/PostGIS

This is a SQL statement and you’ll note it in your visualization tray as a way of querying and exploring your data with immediate visual output.

SELECT
  epl.the_geom_webmercator,
  epl._2009,
  epl.country_name,
  epl.indicator_name,
  ceil(epl._2009) AS d
FROM
  epl_2000 AS epl
ORDER BY
  d ASC

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

###Testing SQL

You can also use “Filters” in the tray to traverse your data in lieu of SQL, and then browse the output in the SQL tray to learn more about how those queries are constructed.

If you navigate to the “Filters” pane after loading your epl_2000 data, you can adjust the interface to illustrate only _2009 data and see the choropleth that results.

Now navigate to your SQL pane

SQL for epl_2000 data

This query will give you the option of creating a more focused table for your visualization (filtering by 2009), run the query and elect to “create a table” which you should name epl_2009 pr epl_2009_sqltest.

SQL table

Customizing UI

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

###Testing Customization

  • in wizard try to adjust the quantile binning and stroke to match this

UI Adjustments

  • in info window customize so you can display a ‘dark’ tooltip on hover, disable the click window/interaction via custom html

Info Window Adjustments

  • in basemaps select a dark basemap; dark countries on the choropleth should blend into the background, and suggest a higher likelyhood of “blackout”

Adding Layers

You can add layers of data to your maps as well.

###Testing Layers Add the “pop_2000” or “Population Totals by Country” data from your downloaded files.

Just add the table, and select the “+” sign above your existing layer in the map/data view.

Create a visualization, which you can name “blackout map” or whatever you would like, and play with the UI options to style the second layer.

Multi-layer Vis

Note that you can toggle the layers on and off, and select custom colors, styles and tooltip info windows for each of the layers.

UI Pop

You might also consider the “Populated Places” data from our core library of “common data” which we’ve started to pre-populate with datasets that play well with others.

Common

You can also join tables in the GUI if you’d rather not write SQL:

Multi-layer Vis

Sharing Your Visualization

You can share your visualization right from your online editor, and embed it in other applications accordingly.

In the next section we will explore exporting and self-hosting your visualization.

ExportData

BREAK - 10 minutes

Case Study: Blackout Map

Background

Most dynamic maps can be created in the editor, visualized publically and embedded in other applications. If you’d like to go further and build your own applications, we have a host of APIs and libraries to support this.

CartoDB.js is our JavaScript API – a way to make maps using JavaScript.

Three pieces to JavaScript maps

The example above use HTML, CSS, and JavaScript to make a map appear on a webpage.

Check out our Map Academy course on CartoDB.js if you want to learn more.

Extensibility:

Use CartoDB.js with other JavaScript libraries to make powerful web map apps.

Check out Urban Reviewer.

If you take a look at the source code, there are a dozen libraries linked:

Urban Reviewer source code

Examples:

  • Illustreets shows standard of living information across England to amazing detail. There are millions of data points, each can be interacted with to give graphs, summaries, etc.
  • Metrologic weather based transport recs based on the forecast.io api

Blackout Map

We’ll start with an export of the map you just made in a HTML/JS format. Two simple scripts are all we need to make a page like the following.

Global Blackout Map

Blackout Map

Checkout the code in this block: http://bl.ocks.org/auremoser/bcefbded913be2daccb8

Color Block

Blackout Map + Graph

We’ll continue with an example of a visualization “mashup,” something that combines a map and a graph.

In this case, we’ll include the Electric loss and population map, focused on Lebanon, and partner it with a graph of average temperatures in Lebanon.

Blackout Map-Lebanon

Mashup Map

Data comes from this source, also a dataset culled from the World Bank.

Checkout the code in this block: http://bl.ocks.org/auremoser/bcefbded913be2daccb8

You can use the same JS and HTML template for your projects.

Don’t forget:

  • to scan the comments in the files for pointers
  • to change the “vizID” in Javascript to match your map

Social Map

You can also import Twitter data on certain accounts, to scan tweets and develop Torque visualizations based on the categories and trends you might like to analyze in social media.

Tweet

  • data for the past 30 days
  • multiple tags (#) possible
  • a map for blackout-related tweets: #Bassil or #energyloss #Waffir, you could pull in tweets and study their spacial/temporal properties on a map!

“beirut + “lebanon” 2/21/15

** BREAK - 10 minutes **

Wrap-Up and Resources

##CartoDB

  1. Map Academy
  2. CartoDB Tutorials
  3. CartoDB Editor Documentation
  4. CartoDB APIs
  5. Community help on StackExchange
  6. CartoDB Map Gallery

##Data

###GENERAL

###TRANSPORTATION

My contact: aurelia@cartodb.com

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

Rocket Society - Lebanon

Find out more about the Lebanese Rocket Society here.