Getting creative with CartoDB
- Core Tech
- 5 mins read
- Share
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, maxcarsHere 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;
}
}
}
}
}
}
}
}
}Related Posts
-
Inside CARTO Engineering: Configuration Management Makeover
Our 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 Tech -
Upgrading our Core Platform to PostgreSQL 11
Traditional standards are being replaced by new processes that feed our modern, data-driven world, where large amounts of data are created and shared every second by indivi...
Core Tech -
Airship 2.0 is here
Today we are very happy to announce a new version of Airship, our front-end library for building Location Intelligence applications. In this version, we focused on bringing...
Core Tech
