0

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.

  • Look at this post to get some PostGIS solutions – JGH Sep 10 '17 at 15:54
  • 1
    I have answered your question, but next time I recommend adding also all you queries (not just the linked answers you have shared) and any error you were getting after applying those queries. – ramiroaznar Sep 10 '17 at 17:33
3

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:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.