I had made the assumption that CartoDB was all about data delivery. Much like the deprecated Google Maps Engine, I had assumed CartoDB’s value was really in fast data rendering and distribution.

But, there is more.

There is more, primarily because CartoDB uses PostGIS. This means you can use much of PostGIS’s analysis capability from the CartoDB user interface. Here is an example: In a trail map we recently built, I wanted to be able to indicate that some trails were one-way. We decided to do this using an arrow. Here is the CartoCSS to show the line’s directionality:

#otway_singletrack_clean_1::blaze {
  line-width: 12;
  line-color:#F4EFE0;
  line-opacity: 0.9;
  line-join: round;
  line-cap: round;
}
#otway_singletrack_clean_1{
  [difficulty="black"] {
    [one_way = true]{
      marker-line-width: 0;
      marker-opacity: 0.9;
      marker-type:arrow; 
      marker-placement:line;
      marker-line-color: #000000;
      marker-fill: #000000;
    }
    line-width: 2.5;
    line-opacity: 0.9;
    line-color: #000000;
  }
  [difficulty="blue"] {
    [one_way = true]{
      marker-line-width: 0;
      marker-opacity: 0.9;
      marker-type:arrow; 
      marker-placement:line;
      marker-line-color: #1f78b4;
      marker-fill: #1F78B4;
    }
    line-width: 2.5;
    line-opacity: 0.9;
    line-color: #1F78B4;
  }
  [difficulty="double black"] {
    [one_way = true]{
      marker-line-width: 0;
      marker-opacity: 0.9;
      marker-type:arrow; 
      marker-placement:line;
      marker-line-color: #000000;
      marker-fill: #000000;
    }
    line-width: 2.5;
    line-opacity: 0.9;
    line-color: #000000;
    line-dasharray: 10, 4;
  }
  [difficulty="green"] {
    [one_way = true]{
      marker-line-width: 0;
      marker-opacity: 0.9;
      marker-type:arrow; 
      marker-placement:line;
      marker-line-color: #33a02c;
      marker-fill: #33a02c;
    }
    line-width: 2.5;
    line-opacity: 0.9;
    line-color: #33A02C;
  }
  ::labels {
    text-name: [name];
    text-face-name: 'Open Sans Bold';
    text-size: 10;
    text-label-position-tolerance: 10;
    text-fill: #575757;
    text-halo-fill: #FFF;
    text-halo-radius: 1;
    text-dy: 0;
    text-allow-overlap: true;
    text-placement: line;
    text-placement-type: simple;
  }
}

This relies on the order of the verticies of each line, meaning if the trail had been digitised in the wrong way the arrows also point the wrong way, like this:

Oh dear, that’s no good! To resolve this issue I was able to run a simple command to change the direction of the line in question. It looks like this:

UPDATE <> SET the_geom = ST_REVERSE(the_geom) WHERE cartodb_id IN (2, 6, 23)

Let me explain this. The <

> is the table of the data layer in question. We are updating the geometry column of the data layer (the_geom) to reorder the verticies of the line in question, using a command called ST_reverse(). Finally, we are providing a list of id's where the lines point the "wrong way".

The end result is:

Yay! We did it! CartoDB can be used for pretty serious data manipulation as well as distribution.