Set you up with accounts here. This will give you a free upgrade with double the storage space and some neat features!
To get used to work with CartoDB we are first going to make a map that will help us explore crime data in Boston. On average, Thursdays in Boston are the days with the highest amount of high school and university public events. We want to look at crime rates on these days to infer an appropriate risk assessment.
We are going to grab a set of incident reports and style them in to a map using the CartoDB editor. You can grab the data from here. Don't download it, just copy that link address and then head over to the CartoDB Dashboard.
- Click on New Map.
- Then Connect Dataset.
- Paste in the URL and CartoDB will automatically grab the data for you.
This data looks good but we want to be able to style it a little more. We can do that by
- Changing the basemap
- Changing the styling for our points
- Making that styling dependent on data.
Now we have the crime data we want to add some neighborhoods. Same as before, right click and ‘copy’ this link for a Boston neighborhoods Shapefile Back in the dashboard connect the dataset as before.
- Just as before we can style the polygons to make map look much better.
- Want to add the crimes data to these polygons so we are going to have to do a spatial join between the datasets.
- Create a chloropleth from the resulting data
- We can view the individual data points on top of the shapes by adding a later to our visualization.
- This is as simple as clicking add layer and selecting the dataset you want to use. You can have up to 4 layers.
To see just how much information you can get by looking at data in this way have a look at this crimes map of Chicago colored by type of crime.
###CartoCSS and PostGIS
Whats happening beneath the scenes here is that the interface is generating SQL queries which are then combined with CARTOCSS a styling language for maps to produce the outputs. So for example in the last dataset we produces we might worry that the map didn’t reflect the size of each of the regions, just the number count of crimes. We can fix this by writing some SQL
SELECT *, crime_count/shape_area as crimes_per_area FROM bos_neighborhoods_new_merge
These SQL queries can be incredibly powerful. You can read more about them here
CaroCSS is also an incredibly useful way to really fine tune your map. Its like regular css but targets map features. For example the CartoCSS for the points in the example above is
/** category visualization */
#crime_incident_reports_thursday {
marker-fill-opacity: 0.9;
marker-line-color: #FFF;
marker-line-width: 0;
marker-line-opacity: 1;
marker-placement: point;
marker-type: ellipse;
marker-width: 2.5;
marker-allow-overlap: true;
}
#crime_incident_reports_thursday[incident_type_description="Argue"] {
marker-fill: #A6CEE3;
}
#crime_incident_reports_thursday[incident_type_description="DRUG CHARGES"] {
marker-fill: #1F78B4;
}
#crime_incident_reports_thursday[incident_type_description="InvPer"] {
marker-fill: #B2DF8A;
}
#crime_incident_reports_thursday[incident_type_description="LARCENY FROM MOTOR VEHICLE"] {
marker-fill: #33A02C;
}
#crime_incident_reports_thursday[incident_type_description="MVAcc"] {
marker-fill: #FB9A99;
}
#crime_incident_reports_thursday[incident_type_description="MedAssist"] {
marker-fill: #E31A1C;
}
#crime_incident_reports_thursday[incident_type_description="OTHER LARCENY"] {
marker-fill: #FDBF6F;
}
#crime_incident_reports_thursday[incident_type_description="SIMPLE ASSAULT"] {
marker-fill: #FF7F00;
}
#crime_incident_reports_thursday[incident_type_description="VAL"] {
marker-fill: #CAB2D6;
}
#crime_incident_reports_thursday[incident_type_description="VANDALISM"] {
marker-fill: #6A3D9A;
}
#crime_incident_reports_thursday {
marker-fill: #DDDDDD;
}Once we have created out beautiful map we want to be able to share it with the world. By clicking on "Publish" we can get a link url that we can send around via the usual methods, embed gives us an iframe which is excellent as well for blog posts etc. If we need more integration with a website however the carodb.js option is what we should use.
<link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v3/3.15/themes/css/cartodb.css" />
...
<div id="map"></div>
...
<script src="http://libs.cartocdn.com/cartodb.js/v3/3.15/cartodb.js"></script>
<script>
// get the viz.json url from the CartoDB Editor
// - click on visualize
// - create new visualization
// - make visualization public
// - click on publish
// - go to API tab
window.onload = function() {
cartodb.createVis('map', 'http://documentation.cartodb.com/api/v2/viz/2b13c956-e7c1-11e2-806b-5404a6a683d5/viz.json');
}
</script>Once the visualization is loaded you can hook in to the interface to control it. Its all built on leaflet.js so if you are familiar with that you will be able to move and interact with the map in code just fine.
var sql = new cartodb.SQL({ user: 'cartodb_user' });
sql.execute("SELECT * FROM table_name WHERE id > {{id}}", { id: 3 })
.done(function(data) {
console.log(data.rows);
})
.error(function(errors) {
// errors contains a list of errors
console.log("errors:" + errors);
})We can access live datasets in CartoDB in one of two ways. First we can use the data sync function on import. To do this we simply past in a url that is accessible on the internet and tell CartoDB how often we want to update that data. The other way is to push data directly to cartodb.
If we grab our api key from our dashboards, then we can use the SQL api to insert data in to our visualization.
curl --data "api_key=XXXXXXXXXXXXXXXXXXXXXXXXX&q='insert in to table (the_geom, value) (ST_SetSRID(ST_Point(-31.23543, 22.24244),4326), 20' " https://{username}.cartodb.com/api/v1/sqlWe can also use several different CartoDB client libraries to insert data in much the same way
var CartoDB = require('cartodb');
var secret = require('./secret.js');
/* you could change this providing an api_key instead of consumer key / secret if you want to use oath
client = new CartoDB({
user: secret.USER,
password: secret.password,
consumer_key: secret.CONSUMER_KEY,
consumer_secret: secret.CONSUMER_SECRET
});
*/
var client = new CartoDB({user: secret.USER,api_key: secret.API_KEY});
client.on('connect', function() {
console.log("connected");
// template can be used
client.query("select * from {table} limit 5", {table: 'tracker'}, function(err, data){
// JSON parsed data or error messages are returned
})
// chained calls are allowed
.query("select * from tracker limit 5 offset 5", function(err, data){});
});
// client is a Stream object instance so you can pipe responses as new line delimited JSON, for example, to a file
var output = require('fs').createWriteStream(__dirname + '/responses.log');
client.pipe(output);
client.connect();
Taxi's are great examples of objects in the world which collect data. Thanks to a FOIA request by Chris Wong we have a lot of this data available for use.
There is a lot of this data, too much to use today but lets try make a map with this sub sample of data here. Just like before don’t download the file but copy it to the clipboard and import it as a new dataset. This contains drop off and pick up locations and times.
medalion,time,longitude,latitude,type
51C567531541EE45C5EE86E956E46E4C,2013-01-07 04:45:14,-73.991936,40.749622,pickup
51C567531541EE45C5EE86E956E46E4C,2013-01-07 04:48:56,-73.978531,40.751308,dropoff
Lets animate this data to see how the traffic is spread out over new york. The small file only contains about 200 taxis.
The Taxi data is great but its a pretty static dataset. Lets use openaq data to create a live dataset. The format of the data in openaq isn’t quite what we want for CartoDB so I wrote a quick proxy server to get the data through. Its available here. Copy and paste it in to CartoDB as before but this time select the daily update option. This means that CartoDB will try to re-import the data every day keeping your project up to date with the data.
To quickly style the map use this CartoCSS
/** torque_cat visualization */
Map {
-torque-frame-count:256;
-torque-animation-duration:30;
-torque-time-attribute:"date";
-torque-aggregation-function:"sum(value)";
-torque-resolution:2;
-torque-data-aggregation:linear;
}
#openaq_2811_29{
marker-fill-opacity: 0.8;
marker-line-color: #FFF;
marker-line-width: 0;
marker-line-opacity: 1;
marker-width: 10;
marker-fill: #FFFFB2;
marker-allow-overlap: true;
}
#openaq_2811_29 [ value <= 224] {
marker-fill: #B10026;
}
#openaq_2811_29 [ value <= 138] {
marker-fill: #E31A1C;
}
#openaq_2811_29 [ value <= 114] {
marker-fill: #FC4E2A;
}
#openaq_2811_29 [ value <= 91] {
marker-fill: #FD8D3C;
}
#openaq_2811_29 [ value <= 67] {
marker-fill: #FEB24C;
}
#openaq_2811_29 [ value <= 44] {
marker-fill: #FED976;
}
#openaq_2811_29 [ value <= 20] {
marker-fill: #FFFFB2;
}