Drawing curved lines in Carto?

Question

I have a dataset that has the following structure:

City_Start / Lat_City_Start / Long_City_Start / City_Dest / Lat_City_Dest / Long_City_Dest

What I want to do is create a curved line between those two points. I've used several code snippets from here, here and here – still, I am not able to make it work.

Maybe there is someone who can help?

I am totally new to SQL and PostGIS.

Answer 1

A very common mistake when trying to connect points with lines using CARTO is forgetting one or many of the mandatory fields that should appear in the query of your map: cartodb_id, the_geom and the_geom_webmercator. This works for me:

    WITH c as (
      SELECT 
         a.cartodb_id,
         a.city_start || '-' || b.city_dest as route,
         ST_Transform(
            ST_Segmentize(
                ST_Makeline(
                  cdb_latlng(a.lat_city_start, b.long_city_start), 
                  cdb_latlng(b.lat_city_dest, b.long_city_dest)
                )::geography, 
                100000
            )::geometry,
            3857
          ) as the_geom_webmercator
      FROM
        mytable a,
        mytable b
      WHERE
        a.cartodb_id = b.cartodb_id)
   SELECT 
     *, 
     ST_Transform(the_geom_webmercator, 4326) as the_geom
   FROM
    c

You can see a simplify working example in the following screenshot: