CartoDBMakeline - UCL-ShippingGroup/shipviz GitHub Wiki

If we want to link points with lines in CartoDB, there is a way to do it, although not very straight forward. Since it's not possible to have different kinds of geometries visualized in the same CartoDB layer, we'll have to:

  1. Start with a layer of points.
  2. Organise the points in a certain way.
  3. Create a line out of the points.
  4. Make that line into a new dataset.
  5. Plot that line as a different layer bellow the points in the first step.

So we start with a layer that contains the points describing the trajectory followed by 3 ships that crossed the Hawaiian ECA area.

Now, in the SQL box, insert something like this:

SELECT mmsi, ST_Makeline(the_geom order by time asc) AS the_geom
FROM hawaiian
GROUP BY mmsi

This query won't display any points. We won’t be able to see them on the map because we are using the_geom here, not the_geom_webmercator.

If we look in the Data View, there should be a line per mmsi of the original table, and a column containing LINESTRING elements instead of POINTs.

In order to plot it, we'll need to click on "create dataset from query" and give it a name. CartoDB automatically opens a new visualisation with the LINESTRING elements we have created. Check them out in the Map View.

Now the lines are loaded in a new dataset and we can add a new layer to the original map and import the new dataset.

Note the ships crossing -180/180 and 90/-90 will display odd lines and the SQL above needs fixing. Also noise needs to be reduced in the DB from boats sending messages from the middle of land areas incorrectly.