Drawing curved lines in CartoDB?

Question

I am experimenting drawing curved line from Origin to Destination in Cartodb. I was able to do so once, however, failed the second and third time.

Could anyone offer me some insights into the following code and help me debug?

I am using CartoDB and start with a table of centroid points for all the countries.

Step 1:CHANGE THE REFERENCE SYSTEM I used the following code

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 953027, 'esri', 53027, '+proj=eqdc +lat_0=0 +lon_0=0 +lat_1=60 +lat_2=60 +x_0=0 +y_0=0 +a=6371000 +b=6371000 +units=m +no_defs ', 'PROJCS["Sphere_Equidistant_Conic",GEOGCS["GCS_Sphere",DATUM["Not_specified_based_on_Authalic_Sphere",SPHEROID["Sphere",6371000,0]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Equidistant_Conic"],PARAMETER["False_Easting",0],PARAMETER["False_Northing",0],PARAMETER["Central_Meridian",0],PARAMETER["Standard_Parallel_1",60],PARAMETER["Standard_Parallel_2",60],PARAMETER["Latitude_Of_Origin",0],UNIT["Meter",1],AUTHORITY["EPSG","53027"]]');

Step 2:CREATE THE LINES

update world_borders_coarse_2013
set the_geom=
(select st_transform(
  st_segmentize(
    st_makeline(
      st_transform(world_borders_coarse_2013.the_geom,953027),
      st_transform(b.the_geom,953027)),100),4326)
 from world_borders_coarse_2013 b
 where b.name='China'
 )

It should give me the curved lines between China to all the other places. However the reality is that I have the "the_geom" column as "line", but nothing shows in the "map view".

Would anyone give me some suggestion to change the code?

Answer 1

I was working on a similar problem (tried to make curved lines form MULTISTRINGs, see here. You use only minuscule letters in your functions in the SQL statement, have you already tried typing them with uppercase letters, as in e.g. st_segmentize() --> ST_Segmentize()? Not sure if this is needed (it should actually also work with minuscule letters, right?), but might be worth trying. Also, maybe change the 100 to 100000 (as in the video tutorial (which I suppose you watched). So, maybe this would work:

UPDATE world_borders_coarse_2013
SET the_geom=
(SELECT ST_Transform(
    ST_Segmentize(
    ST_MakeLine(
        ST_Transform(world_borders_coarse_2013.the_geom,953027),
        ST_Transform(b.the_geom,953027)),100000),4326)
FROM world_borders_coarse_2013 AS b
WHERE b.name='China'
)

And, does "the_geom" contain geometry information? I.e. does it have a "GEO" tag in it?

Answer 2

Here are two resources for you,

What is this PostGIS query doing to show great circle connections?

And a video tutorial: http://vimeo.com/96235251