CartoDB Fiesta

gameplan.md

CartoDB Build Day

Introduction

Hello!

this document

http://bit.ly/cdb-fiesta

schedule

time	what	who
10:00	introduction	andrew
10:15	setup	andrew
10:45	torque it out	andrew
11:15	cartodb.js deep dive	andy
1:15	lunch
1:45	api and more	andrew/andy
2:15	what is a named map	alejandro/andrew
3:15	too much data?	javier de la torre
4:15	question slam	alejandro/andrew
5:00	stop!

questions

We have a ton to get through today, so we ask that you follow this general structure for questions.

1. small questions about the material, just go ahead and ask them!
2. stuck questions about something we are teaching that you get stuck on, get the attention of one of the roaming hands to help you get unstuck
3. big questions please log them in our PiratePad so that we can address them at the end of the day, Big Questions Pirate Pad

Setup

Two datasets

Denver Traffic Accidents - a couple of years worth

Denver Census x Neighborhood - allows for normalization of choropleth made on the fly

Explaining data import options to clients

1. Training the drag & drop
2. Sync tables
3. Writable API / Upload API e.g. Python util

Okay, import the two datasets

Looking at data

SELECT count(*) FROM traffic_accidents

Create a filter on first_occu

Delete data from before 2013

DELETE FROM traffic_accidents WHERE first_occu < '01/01/2013'::timestamp

Verify that it worked,

SELECT count(*) FROM traffic_accidents

Make a visualization

Add a second layer

And make a nice map

Torque it out

See Torque section

CartoDB.js deep dive

See CartoDB.js section

APIs and more

From Editor to API

A taxing query...

SELECT 
  *, 
  (
   SELECT 
    cartodb_id 
   FROM 
    census_neighborhood_demographics_2010 
   WHERE 
    ST_Intersects(the_geom, traffic_accidents.the_geom) 
   LIMIT 1
  ) AS district_id 
FROM 
  traffic_accidents

Making data from nothing in the Editor

Notice, the CDB_LatLng helper

SELECT 
  *, 
  ST_Distance(the_geom, CDB_LatLng(39.739167, -104.984722)) d 
FROM traffic_accidents

Moving to the API

Base URL

http://{username}.cartodb.com/api/v2/sql

Adding a query

http://partners.cartodb.com/api/v2/sql?q=SELECT *, ST_Distance(the_geom, CDB_LatLng(39.739167, -104.984722)) d FROM traffic_accidents LIMIT  10

The AMAZING order by optimization

Useful if you want to get the 10 closest records to a location

... ORDER BY the_geom <-> other_geom

Full example

SELECT * FROM traffic_accidents ORDER BY the_geom <-> CDB_LatLng(39.739167, -104.984722) LIMIT 10

Over the API

http://partners.cartodb.com/api/v2/sql?q=SELECT * FROM traffic_accidents ORDER BY the_geom <-> CDB_LatLng(39.739167, -104.984722) LIMIT 10

Quick overview of Import API

View example Python library here

Maps API

Obfuscated in the CartoDB.js getTiles method:

 cartodb.Tiles.getTiles(layerOptions, callback);

Example

      // request cartodb layer
      cartodb.Tiles.getTiles({
        type: 'cartodb',
        user_name: 'examples',
        sublayers: [{
         sql: 'select * from ne_10m_populated_p_2',
         cartocss: '#ne_10m_populated_p_2{ marker-fill: #F11810; marker-opacity: 0.9; marker-allow-overlap: true; marker-placement: point; marker-type: ellipse; marker-width: 7.5; marker-line-width: 2; marker-line-color: #000; marker-line-opacity: 0.2; }'
        }]
      }, function(tileTemplate) {
        // modestmaps use uppercase template variables, replace them
        var template = tileTemplate.tiles[0]
          .replace('{s}','{S}')
          .replace('{z}','{Z}')
          .replace('{x}','{X}')
          .replace('{y}','{Y}')
        // use 0,1,2,3 subdomains
        var cartodbLayer = new MM.TemplatedLayer(template, '0123');
        map.insertLayerAt(1, cartodbLayer);
      });

OpenLayers

ModestMaps

Now Andy for some more JS!

See getSQL file

What is a named map?

See Named Maps

Too much data...

Javier