• Oct 2, 2012

Getting creative with CartoDB

Core Tech
5 mins read
Share
Facebook Twitter Linkedin

The combination of styling with SQL on CartoDB gives a surprising range of freedom to create data visualizations. I find myself often thinking about creative solutions to mapping data in interesting ways.

Recently I met some of the people from Change Administration and Tomorrow Lab behind a DIY traffic counter. They had already set up a couple traffic counters and were collecting data in a couple areas. They had an interesting challenge, they wanted to create a map of the points that could represent busier areas by interpolation. The data was also going to grow, as they collected new days and added new collection devices over time.

DIY Traffic Counter

So the challenge was, could we interpolate the traffic data over space and create a visual representation of that traffic intenstity? We thought something similar to a contour map, but one that would instantly update as new data were added to the map. I also wanted to do it entirely in SQL and with styles. We came up with this interesting map, where red is the most busy area and each line of the countour represents 10 meters and 10% decay in traffic. Of course it is a simplisitic approximation, but it helps to show what is possible on CartoDB.

If you want to see how this procedure could work in other areas, take a look at a map of earthquake magnitude below,

</a>

Here is the SQL used,

WITH setup AS (
  SELECT ST_Transform(ST_Buffer(the_geom::geography,n*100)::geometry,3857) the_geom_webmercator,
         (cars*(10.0 - n)/10.0) as cars
  FROM troparevo_nikulino_1, generate_series(1,9) n
), maxcars AS (
  SELECT max(cars)::float as mostcars
  FROM setup
)

(SELECT ST_Buffer(ST_Buffer(ST_Union(the_geom_webmercator),400),-400) AS the_geom_webmercator,
  round((2*cars/mostcars)::numeric,1)/2 cars, 'topo' as layer
FROM setup, maxcars
GROUP BY round((2*cars/mostcars)::numeric,1)/2
ORDER BY round((2*cars/mostcars)::numeric,1)/2 DESC)
UNION ALL
SELECT the_geom_webmercator, round((cars/mostcars)::numeric,1) as cars, 'points' as layer
FROM troparevo_nikulino_1, maxcars

Here is the style,

#troparevo_nikulino_1 [layer='points']{
  marker-fill:#FF3366;
  marker-width:8;
  marker-line-color:#000000;
  marker-line-width:1;
  marker-opacity:1;
  marker-line-opacity:1;
  marker-placement:point;
  marker-type:ellipse;
  marker-allow-overlap:true;
}
#troparevo_nikulino_1 [layer='topo']{
  polygon-fill:transparent;
  line-color:#D53E4F;
  line-width:2;
  line-opacity:1;
  [cars<1] {
    line-color:#F46D43;
    line-opacity: 0.95;
  }
  [cars<0.9]{
    line-color:#FDAE61;
    line-opacity: 0.9;
  }
  [cars<0.8]{
    line-color:#FEE08B;
    line-opacity: 0.85;
  }
  [cars<0.7]{
    line-color:#FFFFBF;
    line-opacity: 0.8;
  }
  [cars<0.6]{
    line-color:#E6F598;
    line-opacity: 0.75;
  }
  [cars<0.5]{
    line-color:#ABDDA4;
    line-opacity: 0.7;
  }
  [cars<0.4]{
    line-color:#66C2A5;
    line-opacity: 0.65;
  }
  [cars<0.3]{
    line-color:#3288BD;
    line-opacity: 0.6;
  }
}
#troparevo_nikulino_1::glow [layer='topo']{
  polygon-fill:transparent;
  line-color:#D53E4F;
  line-width:5;
  line-opacity:0.5;
  [cars<1]{
    line-color:#F46D43;
    [cars<0.9]{
      line-color:#FDAE61;
      line-opacity:0.4;
      line-width:8;
      [cars<0.8]{
        line-color:#FEE08B;
        [cars<0.7]{
          line-color:#FFFFBF;
          [cars<0.6]{
            line-color:#E6F598;
            line-opacity:0.3;
            line-width:10;
            [cars<0.5]{
              line-color:#ABDDA4;
              [cars<0.4]{
                line-color:#66C2A5;
                line-opacity:0.2;
                line-width:25;
                [cars<0.3]{
                  line-color:#3288BD;
                }
              }
            }
          }
        }
      }
    }
  }
}