This special workshop was given by Andrew Hill on January 14, 2015 at Deloitte Torque Intro. As taught, it was 2 hours long.

Deloitte Workshop


You can find this page at:

Watch the screencapture

Getting Started

Import a dataset

Today we’ll be making maps and interacting with a dataset of DC Tax lots. The original dataset is available here, DC tax lots. No need to download that though!

Today we will use a slightly cleaned up version of the dataset.

DC Tax Lots

Let’s save the WIFI a bit though, instead of downloading the dataset, copy this URL (ctrl+c):*%20FROM%20tax_lots_m&format=geojson

After the file is imported, let’s change the name to,


Making our first maps

Let’s take a second to tour the interface in case some of you haven’t picked up the tool in a while.


Transforming data types

Some SQL we hide for you, for example, no need to use SQL to change datatypes. But you can use SQL if you want!


Using SQL in CartoDB

CartoDB is built on PostgreSQL and gives you access to the full relational database model, plus geospatial functions based on PostGIS.

Example, calculating tax lot elevation based on DC contour lines. In this example, I’m using a second dataset from for elevation data.

Filtering on the map**

SELECT * FROM dc_tax_lots WHERE
  elevation > 50 AND elevation < 150

Updating data **

First I’ll add a new column, called elevation_b. Next, I’ll run a relational SQL statement, that measures the elevation by finding the closest contour line in my second table (no interpolation here!).

elevation data

 elevation_b = (
   the_geom <-> tax_lots_m.the_geom

Let’s create a choropleth from the elevation column.


Transforming data type

In CartoDB, you can move between points, lines and polygons fairly easily using the PostGIS geospatial functionality. For example, you can join a group of points into a linestring. In this case, let me show you how to extract the centroid of each building so that we can use Torque to animated it.

  ST_Centroid(the_geom) the_geom, elevation, creation_d
FROM dc_tax_lots

We can capture a snapshot of this by using the options->table from query functionality.


A quick look at Torque

Point torque


Categorical torque


Temporal bubbles


Cumulative bubbles


Method for creating the last two


Let’s all start with the same template, you can download an index.html file here,


The simplest CartoDB.js map

A map in one line of javascript


Adding to an existing Map object

First, make sure you delete the line we added above and create a leaflet map object,

var map = new L.Map('map', {
  zoomControl: false,
  center: [38.893, -77.03],
  zoom: 13

Next, add a basemap. You can use one of the freely available CartoDB Basemaps

var layer = L.tileLayer('http://{s}{z}/{x}/{y}.png',{
    attribution: '&copy; <a href="">OpenStreetMap</a> contributors, &copy; <a href="">CartoDB</a>'
The createLayer method

The createLayer method is a really powerful way to use CartoDB in your frontend applications. It allows you to add your own map object (leaflet, gmaps) and add your layers from CartoDB.

cartodb.createLayer(map, '{your-vis-json-url-here}')
    .done(function(layer) {
        // Do further things here
Add interactivity

You can add interactivity in the Editor very easily, but for maps you are creating on the fly in CartoDB.js, you can also add custom interactivity.

var lots = layer.getSubLayer(0);
lots.setInteractivity('cartodb_id, elevation');
Add custom interaction

You can use cursor events on your polygons (e.g. featureClick, featureOver, featureOut) to create your own actions on your webpage. Let’s log the Elevation value for any lot we hover over

lots.on('featureOver', function(e, pos, pixel, data) {
    console.log("Elevation: " + data.elevation);

Map lot similarity

Let’s build a simple app that let’s you click a lot and see all lots within a 30 foot elevation envelope. We can do this by combining the click event and the ability to define a new SQL statment on our map tiles.

First, let’s construct our new SQL string from the click input,

lots.on('featureClick', function(e, pos, pixel, data) {
    var newSql = "SELECT * FROM dc_tax_lots WHERE elevation >= "+(data.elevation - 30)+" AND elevation <= "+(data.elevation - 30);
    console.log("SQL: " + newSql);
Updating layer parameters

Using the layer object returned by createLayer, we can actually update our SQL, CartoCSS and interactivity on the fly. Let’s use setSQL to run our new statement:

Add a button

Let’s add a simple button to reset the map. I’ve already added the HTML to the webpage, we just need to remove the class hidden when we set the SQL filter and then wire up the button using jQuery.

Wire a button to a setSQL
    lots.setSQL("SELECT * FROM dc_tax_lots");