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,
</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;
}
}
}
}
}
}
}
}
}
Here at CARTO we are constantly updating our platform so that our customers gain in system performance, reliability and security.
Core TechOne of my favorite parts about working at CARTO is getting to test out the new features that our product team is constantly shipping. A month or so ago, it was our Kepler c...
Core TechOur primary goal at CARTO is to be the world’s leading Location Intelligence platform, empowering our clients with the best data and the best spatial analysis. We frequentl...
Core TechPlease fill out the below form and we'll be in touch real soon.