St_Segmentize on CARTO

Question

I need help with the phrasing of this query. I am applying this in CARTO to a file with lines. The following query does not generate a curve as expected. I am new to SQL/PostGIS.

select
  mycartoid.home2015_1, the_geom
  st_transform(
    st_segmentize(
      st_makeline(origin.h_block, dests.w_block )::geography,
      10000
     )::geometry,
    3857) the_geom_webmercator
  from origin,dests

Also attached is a snippet of my data for reference:

I am trying to generate a map with curved lines similar to this link:

https://team.carto.com/u/jsanz/viz/89f6adfe-48f8-11e5-b416-0e0c41326911/public_map

SQL Query Link line 26-35 from this gist:

lines as(
  select
  dests.cartodb_id,  dests.name, dests.adm0name, dests.distance,
  st_transform(
    st_segmentize(
      st_makeline(origin.the_geom, dests.the_geom )::geography,
      10000
     )::geometry,
    3857) the_geom_webmercator
  from origin,dests

Answer 1

looking at this again, I think you've got it working :-)

some of the long-haul routes are showing up as curved, although it's difficult to see - there are a lot of short-haul routes in there.

Reducing line opacity can sometimes help with this sort of visualisation (or making shorter routes more transparent)

When zoomed far in, great circles tend to look linear - you'll typically only see curvature over hundreds or thousands of Kilometers. If you zoom out, the curves should appear as you expected.

EDIT: I see you want to maximise the curviness of the lines at the scale you're showing.

For this you might need to digitise curves manually. See answers to this question, especially this one

Also, I see PostGIS can convert linestrings to curves e.g. ST_LineToCurve. I haven't tried this but it might be worth looking at.. if you change the control points you might be able to alter the curvature. (Not sure if CARTO supports curves, but you can always change the curves back to linestrings)