Create links between points on CartoDB
I have two tables on CartoDB.
The first one (with name "points_table") has 4 columns.
- Country Name (name: country)
- Lat (name: lat)
- Long (name: long)
- the_geom (lat + long)
The second one (with name "links_table") has 2 columns.
- Country Name A (name: country1)
- Country Name B (name: country2)
The second demonstrate links between two countries. I am new on CartoDB. I found how to visualize the first table, but I still cannot find how I can create curve links between countries from table 2.
SELECT a.cartodb_id AS cartodb_id, l.country1 || ' - ' || l.country2 AS link, ST_Transform( Geometry( ST_Segmentize( Geography( ST_SetSRID( ST_MakeLine(a.the_geom, b.the_geom), 4326)), 100000)), 3857) AS the_geom_webmercator FROM points_table a JOIN links_table l ON a.country = l.country1 JOIN points_table b ON b.country = l.country2
The SQL Views result contain the cartodb_id column, a column with the two countries and a "the_geom_webmercator" with content as "line". If I hover on it, I have "linestring" title.
However, when I try the MAP view, the website stack on "Loading tiles… " and after a few second, I have a internet connection problem. Any ideas how I can solve it?
First see if the join works…
SELECT l.country || ' - ' || l.country2 AS link, FROM points_table a JOIN links_table l ON a.country = l.country JOIN points_table b ON b.country = l.country2;
Hopefully you'll get a finite number of results (actually, exactly the same number of results as your links table has). If not, perhaps your country keys don't actually match.
To generate the connecting lines for all links you'll want something like this:
SELECT 10000 * a.cartodb_id + b.cartodb_id AS cartodb_id, l.country || ' - ' || l.country2 AS link, ST_Transform( Geometry( ST_Segmentize( Geography( ST_SetSRID( ST_MakeLine(ST_MakePoint(a.long, a.lat), ST_MakePoint(b.long, b.lat)), 4326)), 100000)), 3857) AS the_geom_webmercator FROM points_table a JOIN links_table l ON a.country = l.country JOIN points_table b ON b.country = l.country2;
The magic part is the bottom, where you join the points table to the links twice, using different columns, to get the end points of the links.
The other magic part is the big nest of functions, which make a simple point-to-point line, then flip into geography, segmentize to get a great circle route, flip back to geometry and reproject to mercator for mapping in CartoDB.