01/14/15
You can find this page at:
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.
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
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!
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.
SELECT * FROM dc_tax_lots WHERE
elevation > 50 AND elevation < 150
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!).
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.
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.
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,
A map in one line of javascript
cartodb.createVis('map','http://team.cartodb.com/api/v2/viz/59428614-9c1f-11e4-be16-0e0c41326911/viz.json')
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: '© <a href="http://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors, © <a href="http://cartodb.com/attributions">CartoDB</a>'
}).addTo(map);
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);
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)
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);
});
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);
});
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);
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');
$('button').click(function(){
$('button').addClass('hidden');
lots.setSQL("SELECT * FROM dc_tax_lots");
});