Create links between points on CartoDB

Question

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.

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?

Will this work? http://gis.stackexchange.com/questions/5204/curved-point-to-point-route-maps — DPSSpatial_BoycottingGISSE, Mar 12 '15 at 15:03
Is this for CartoDB too? Because I don't see any tag to this. As I said, I am new on using it — Tasos, Mar 12 '15 at 15:10
Ah! I meant to say that if it's in PostGIS, it's in CartoDB as that's the underlying database technology... I'd be curious if it worked! — DPSSpatial_BoycottingGISSE, Mar 12 '15 at 15:11
Also, as I see, the links start from the same point in the example. I want to create links between different points. — Tasos, Mar 12 '15 at 15:13
in the PostGIS example the two tables are a and b and the geometry columns are a.geom and b.geom so it looks like two tables are being referenced...however, the entire query is not shown there, just the syntax to create the arc between the geometry of the two tables... — DPSSpatial_BoycottingGISSE, Mar 12 '15 at 15:15
I updated my question. If you could take a look, I would appreciate it. — Tasos, Mar 12 '15 at 15:34

Paul Ramsey · Accepted Answer · 2015-03-18T13:12:26.903

2

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.

edited Mar 18 '15 at 13:12

answered Mar 17 '15 at 15:00

Paul Ramsey

19,865
1
47
57

I have this errorMap interaction is disabled, select cartodb_id to enable it. I tried to google it, and a few says that the reason is the lack of cartodb_id – Tasos Mar 17 '15 at 15:24
Ah, right two required columns for any tabel you want to map: a the_geom_webmercator and a cartodb_id. I've created a synthetic one by summing the inputs... might not be unique though, hm. Maybe multiply a.cartodb_id by 1000 first. – Paul Ramsey Mar 17 '15 at 15:32
Now, I think we are closer. But still without a result on the map. I have a loading gif when I click on the map view and after a while, a message about problem with my connection. As I can understand, it cannot load the links. However, I don't have a problem with my connection. – Tasos Mar 17 '15 at 15:35
Not really sure. The result should only have as many records as your links table, which presumably isn't very big. I changed the id, since I realized that if you imported your links table into cartodb it would have a cartodb_id too, which would be unique. – Paul Ramsey Mar 17 '15 at 16:01
Well, the new code now return only one line on the SQL View. :( – Tasos Mar 17 '15 at 16:04
Is it possible you built your geometry with the lat and long reversed? That might confuse things a bit... – Paul Ramsey Mar 17 '15 at 19:27
If I use the point_table with the the_geom and the lat and long to create a map, every point is in the right place. So, I could reject a problem on this. – Tasos Mar 17 '15 at 22:14
Let us continue this discussion in chat. – Paul Ramsey Mar 18 '15 at 13:12
Great! Will wait for you there. – Tasos Mar 19 '15 at 15:31

Create links between points on CartoDB

1 Answers1

Linked