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:
) as the_geom_webmercator
FROM ramirocartodb.cbd_offices_locations a, ramirocartodb.cbd_offices_locations b
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:
m AS (
SELECT array_agg(cartodb_id) id_list, the_geom_webmercator, ST_Y(the_geom_webmercator) y
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
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!