Torque + Tax Data Deloitte Torque Intro

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

01/14/15

You can find this page at:

http://bit.ly/cdb-deloitte

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

http://andrew.cartodb.com/api/v2/sql?q=SELECT%20*%20FROM%20tax_lots_m&format=geojson

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

dc_tax_lots

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.

Imgur

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!

Imgur

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 opendata.dc.gov 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

UPDATE
 dc_tax_lots
SET
 elevation_b = (
  SELECT
   elevation
  FROM
   dc_20_foot_contours
  ORDER BY
   the_geom <-> tax_lots_m.the_geom
  LIMIT 1
 )

Let’s create a choropleth from the elevation column.

Imgur

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.

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

Imgur

A quick look at Torque

Point torque

Imgur

Categorical torque

Imgur

Temporal bubbles

Imgur

Cumulative bubbles

Imgur

Method for creating the last two

CartoDB.js

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

index.html

The simplest CartoDB.js map

A map in one line of javascript

    cartodb.createVis('map','http://team.cartodb.com/api/v2/viz/59428614-9c1f-11e4-be16-0e0c41326911/viz.json')

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}.basemaps.cartocdn.com/dark_nolabels/{z}/{x}/{y}.png',{
    attribution: '&copy; <a href="http://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors, &copy; <a href="http://cartodb.com/attributions">CartoDB</a>'
  }).addTo(map);
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
    })
    .addTo(map);
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');
lots.setInteraction(true);
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:

lots.setSQL(newSql);
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.

$('button').removeClass('hidden');
Wire a button to a setSQL
$('button').click(function(){
    $('button').addClass('hidden');
    lots.setSQL("SELECT * FROM dc_tax_lots");
});