Getting creative with CartoDB

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.

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

Here is the SQL used

##_INIT_REPLACE_ME_PRE_##

WITH setup AS (
  SELECT ST_Transform(ST_Buffer(the_geom::geography n100)::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((2cars/mostcars)::numeric 1)/2 cars 'topo' as layer FROM setup maxcars GROUP BY round((2cars/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 ##_END_REPLACE_ME_PRE_##

Here is the style

##_INIT_REPLACE_ME_PRE_##

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; } } } } } } } } } ##_END_REPLACE_ME_PRE_##