At the time of publication CartoDB used a freemium model. It has since been rebranded to CARTO and no longer offers free accounts. Steps, links and examples mentioned in this tutorial might no longer work.
As part of our terrestrial observatory, we are tracking large birds with lightweight, solar powered GPS trackers. The project1 is lead by INBO researchers Eric Stienen (for gulls) and Anny Anselin (for the Western Marsh Harrier) in collaboration with the VLIZ and UvA-BiTS.
30 birds have been tagged over the course of this spring and summer. The preliminary results were presented in the media and you can follow the birds live. Most birds have started their annual migration south however, where the antennas we use to download the data cannot pick them up. A good time to visualize some of the data we got.
Meet Eric and CartoDB
As an example, I will visualize two months of tracking data (June-July) from Eric in CartoDB. Eric is male Lesser Black-backed Gull, breeding in the colony of Zeebrugge. CartoDB is an open source tool to visualize and analyze geospatial data on the web, developed by Vizzuality.
Intensity map of occurrences
After uploading the tracking data to CartoDB, one of the easiest maps to make is an intensity map. Overlapping points generate a higher colour intensity, highlighting clusters on the map.
The dark red spot on the pier marks the nest of Eric. You can zoom and pan the map, or click on individual points to get the date and time of recording.
Map of trips per day
To get a better sense of the trips Eric made during those two months, we can string the points together in a path. All of this can be done in SQL, since CartoDB is built on PostgreSQL and PostGIS. In order to get a path per day, we first need to create a
ALTER TABLE tracking_eric ADD COLUMN day_of_year integer UPDATE tracking_eric SET day_of_year = extract(DOY FROM date_time)
Next, we order the points per
date_time, group them by
day_of_year and make a line2:
SELECT ST_MakeLine(the_geom_webmercator ORDER BY date_time ASC) AS the_geom_webmercator, day_of_year, 1 AS cartodb_id -- required FROM tracking_eric GROUP BY day_of_year
If we visualize this as a choropleth map, we get this:
You may discover as I did, that Eric flew multiple times to Mouscron in June, while he stayed closer to his nest in July.
Visualizing in time
To truly visualize Eric in time, I used the library Torque (also developed by Vizzuality):
The visualization compresses two months of data in 120 seconds. As with the previous maps, you can zoom and pan the map.
Note: since publishing this post, Torque has been integrated in CartoDB. The map above now uses the integrated version.
Analyzing time spent per UTM 1km square
So far, I only created visualizations of the data, but CartoDB also allows me to analyze the data. I would like to know how much time Eric spent per square kilometer. This was quite a challenge for my novice SQL skills, but good documentation goes a long way.
First, we need to calculate the duration for each occurrence point. We can do this by calculating the difference between the
date_time of the current point and the
date_time of the previous point, using the lag() function, and then translating this to seconds, using the extract() function:
ALTER TABLE tracking_eric ADD COLUMN duration_in_seconds integer
WITH calc_duration AS ( SELECT cartodb_id, extract(epoch FROM (date_time - lag(date_time,1) OVER(ORDER BY date_time))) AS duration_in_seconds FROM tracking_eric ORDER BY date_time ) UPDATE tracking_eric SET duration_in_seconds = calc_duration.duration_in_seconds FROM calc_duration WHERE calc_duration.cartodb_id = tracking_eric.cartodb_id
SELECT row_number() OVER (ORDER BY utm.the_geom_webmercator) AS cartodb_id, utm.the_geom_webmercator, sum(duration_in_seconds) as duration_in_seconds FROM utm_1km AS utm, tracking_eric AS eric WHERE ST_Intersects(utm.the_geom_webmercator, eric.the_geom_webmercator) GROUP BY utm.the_geom_webmercator
The resulting map, with a choropleth scale, looks like this4:
You can click on each square to get the time Eric spent there in seconds over two months time. We can now also easily figure out where Eric stayed more than an hour, by changing the above query to:
WITH utm_squares AS ( SELECT row_number() OVER (ORDER BY utm.the_geom_webmercator) AS cartodb_id, utm.the_geom_webmercator, sum(duration_in_seconds) as duration_in_seconds FROM utm_1km AS utm, tracking_eric AS eric WHERE ST_Intersects(utm.the_geom_webmercator, eric.the_geom_webmercator) GROUP BY utm.the_geom_webmercator ) SELECT * FROM utm_squares WHERE duration_in_seconds > 3600
In my opinion CartoDB is an intuitive, yet very powerful tool to visualize and analyse data, which is why I’ve been a fan from day one. I hope we can install and use it for all our tracking data, in collaboration with the VLIZ and UvA-BiTS. If you’re eager to explore yourself, CartoDB is offered as a freemium service. The data used in this post are dedicated to the public domain under a Creative Commons Zero waiver at:
- CartoDB: http://lifewatch.cartodb.com/tables/bird_tracking/public
- API: https://lifewatch.cartodb.com/api/v2/sql?q=SELECT * FROM bird_tracking WHERE device_info_serial = 851
A more in-depth tutorial is available on the CartoDB website. The main difference is that I stored the result as a view rather than a table, which is why I used
CartoDB requires an
cartodb_idto allow click interaction. I am cheating here by generating a new one based on
Obviously, for a real analysis, I would have to use a reference grid that extends beyond the borders of Flanders. ↩