Using CartoDB to visualize two months in the life of gull Eric

We no longer have a CartoDB/CARTO account and have replaced embedded maps with screenshots. 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 project¹ 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 day_of_year column:

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 line²:

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

Then:

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

Next, we upload a reference grid to CartoDB. I uploaded a shapefile of all UTM 1km squares of Belgium. Then we join the two tables by geospatial intersection³:

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 this⁴:

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

Conclusion

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