It is been awhile since my last post. A lot of cool things have happened since then! I continue to learn Javascript and PostGIS. In fact, with the combination of these two and a little help of CartoDB.js, you could make amazing visualizations. In this post, you will learn how to connect points using great circles and also how to stack your points as chips in order to solve the “overlapping points problem”.
I believe that everyone is familiar with great circles thanks to the Facebook friendship map. But in my case, I get completely in love with them when I learnt how to “paint” them in R. But maybe the most brilliant visualization is the one recently made by Carlos Matallín. Here we are going to learn the most basic SQL query used in the former viz that allows us to connect our points of interest and getting simple but outstanding cartographies:
And this beautiful visualization can be achieved with just a few lines of code. Here is the block where you can see how to embed both the following SQL query and the CartoDB.js:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT ST_Transform( ST_Segmentize( ST_Makeline( a.the_geom, b.the_geom )::geography, 100000 )::geometry, 3857 ) as the_geom_webmercator FROM ramirocartodb.cbd_offices_locations a, ramirocartodb.cbd_offices_locations b WHERE a.cartodb_id<>b.cartodb_id |
A little bit more complicated is the second example. This solution, explained in more detail here, will allow you to visualize multiple overlapping points. How? Stacking them as if they were casino chips. But be aware that as commented in the cited post, if you allow zoom interaction you will need to implement a function to keep the distance between each of them constant.
Here you can find a block with both the visualization and the following SQL to hack the map:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
WITH m AS ( SELECT array_agg(cartodb_id) id_list, the_geom_webmercator, ST_Y(the_geom_webmercator) y FROM ramirocartodb.overlapping_points GROUP BY the_geom_webmercator ORDER BY y DESC ), f AS ( SELECT generate_series(1, array_length(id_list,1)) p, unnest(id_list) cartodb_id, the_geom_webmercator FROM m ) SELECT ST_Translate(f.the_geom_webmercator,0,f.p*50) the_geom_webmercator, f.cartodb_id, q.city, q.country FROM f, ramirocartodb.overlapping_points q WHERE f.cartodb_id = q.cartodb_id |
More and better in the following posts. Happy coding, querying and mapping!
GIS Analyst. Working at @CARTO and @ongawa4d.
Pingback: 3D buildings with CartoCSS! | Ramiro Aznar
Pingback: How to use CARTO BUILDER analysis to create insightful map applications | Ramiro Aznar