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.

Image: NASA
Sharing options
mountain during golden hour

Observations • Will Cadell

Sparkgeo & N51

Sparkgeo is very excited to be co-hosting N51 in Banff this year. Come and let the scenery take your breath away, while N51 stimulates your geospatial ideas.

selective focus photography of licensed plate with open text hanged

Observations • Dan Ormsby

FOSS4G UK 2022 – Sparkgeo represent at key open-source conference

The UK “free and open-source software for geo” (FOSS4G) community came together on November 17th to celebrate PostGIS Day.

closeup photography of clear glass window closed

Observations • Darren Wiens

How to use SVG Filters on Web Maps

Here at Sparkgeo, while we often prefer to provide answers over visualizations, we still make a lot of web maps. And when we do, we take pride in…

Need a geospatial partner?

Our team complements organizations like yours—by providing on-tap access to geospatial, analytics, and mapping expertise.

Let’s talk

Join our team?

We’re always looking for skilled technologists to help us build the future of geospatial. Got a minute to find out more about us?

Working Here

Image: NASA
Sharing options