CartoDB – How to Create Links Between Points

Question

I have two tables on CartoDB.

The first one (with name "points_table") has 4 columns.

1. Country Name (name: country)
2. Lat (name: lat)
3. Long (name: long)
4. the_geom (lat + long)

The second one (with name "links_table") has 2 columns.

1. Country Name A (name: country1)
2. 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.

Edit

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?

Answer 1

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.