I'm trying to draw a flight map in CartoDB. My starting point is a single lat long point. We'll pretend it is this:

ST_GeomFromText('POINT(-71.060316 48.432044)', 4326)

And I want draw lines between that point and the points in my CartoDB table. I had a hard time even getting started, but someone pointed me to this map:


Which is fancier than what I need (I don't need to redraw everytime you click someplace) but includes the basic query:

SELECT ST_Transform(ST_Segmentize(ST_MakeLine(
   ST_Transform(the_geom, 953027),
          (SELECT ST_PointOnSurface(the_geom) 
          FROM lower_48_zips 
          WHERE cartodb_id = "+data.cartodb_id+"), 953027)), 100000), 4326) 
   the_geom FROM us_ski_areas WHERE mindist < 1000

Someone also pointed me towards an example which uses a very similar query:

UPDATE experimental.airroutes
SET the_geom =
(SELECT ST_Transform(ST_Segmentize(ST_MakeLine(
       ST_Transform(a.the_geom, 953027),
       ST_Transform(b.the_geom, 953027)
     ), 100000 ), 4326 )
 FROM experimental.airports a, experimental.airports b
 WHERE a.id = airroutes.source_id 
   AND b.id = airroutes.dest_id

But I'm having trouble pulling either of these apart. I'm decently conversant in MySQL but totally new to PostGIS and I would love help taking apart what is happening here and replacing source_id with a specific point.

I thought I could do something like

UPDATE my_table
SET the_geom =
(SELECT ST_Transform(ST_Segmentize(ST_MakeLine(
       ST_Transform(my_table.the_geom, 953027),
       ST_Transform(ST_GeomFromText('POINT(-71.060316 48.432044)', 4326), 953027)
     ), 100000 ), 4326 )
 FROM my_table

But I know I need a "where" clause in there and I'm not even sure what it should be.


2 Answers 2


Short answer first, you are super close. Try this instead,

UPDATE my_table
SET the_geom =
         ST_Transform(my_table.the_geom, 953027),
         ST_Transform(CDB_LatLng(48.432044, -71.060316), 953027)

This would update your table from being points, to being a line from the original point to the point at -71, 48. I used the CDB_LatLng helper function. You didn't need to As_Text function, because that just turns geometries into human readable text.

Now the longer is that each of these functions has a documentation page, you can find those here,

ST_Transform: http://postgis.net/docs/ST_Transform.html

ST_Segmentize: http://postgis.net/docs/ST_Segmentize.html

ST_MakeLine: http://postgis.net/docs/ST_MakeLine.html

You are basically reprojecting the reference map to make it so that the straightest line between points follows more closely a curved globe (actually in this case I think it is conic, but minor detail). Next, you segmentize so that you have waypoints all along that line that follow the shortest path across the curved map. Then, your reproject back to WGS84. If you just grabbed the start and end points in the curved world, when you reproject back it would still just be a straight line. By segmentizing you get the shortest line plus waypoints along that line that use the curved map. When you reproject back to WGS84 then, it will still use the same way points which will now appear curved.

Hope that makes some sense.

  • Close and yet ... "more than one row returned by a subquery used as an expression"
    – Amanda
    Commented Jan 27, 2014 at 23:07
  • ah, shoot. you didn't need the subquery (SELECT...) since it is from the same row. I edited the above to show the right form (i hope) Commented Jan 27, 2014 at 23:10
  • Better! But now I'm getting what I think is CartoDB weirdness. the_geom says it is a line but maps as points. Taking a break for the night.
    – Amanda
    Commented Jan 27, 2014 at 23:30
  • A break should fix it! Sometimes if you change the geom type it takes a refresh for the styles to catch up. If that fails, try duplicating the table and using the new version. Commented Jan 27, 2014 at 23:44
  • 1
    Et voila. Looking forward to showing ya the map.
    – Amanda
    Commented Jan 28, 2014 at 16:26

The examples given, and the previous answer all depend on taking the line into a planar projection and then segmentizing. As of PostGIS 2.1 (which I think CartoDB is on) you can do a general-purpose job of it using ST_Segmentize(geography), so like this:

    ST_SetSRID(ST_MakePoint(-71.060316, 48.432044),4326), 
  , 10000
FROM mytable;

In words,

  • make a central point in SRS 4326
  • make a line between that point and each point in the table
  • convert that line into the geography type
  • segmentize in geography space (along the great circle route)
  • convert that detailed geography back into geometry
  • Am I correct in reading that I don't need to use ST_Transform here?
    – Amanda
    Commented Jan 28, 2014 at 21:51
  • Assuming your input table holds geometry in 4326, then no, no transform required, you just make a line, cast it to geography, segmentize, then cast back to geometry. Commented Jan 28, 2014 at 21:54
  • Any tips on how I can handle the date line better with this? I've got at least one line that crosses the date line and it is making my head explode.
    – Amanda
    Commented Feb 2, 2014 at 21:24
  • That's beyond the purview of PostGIS, and falls to your display system, I would imagine. PostGIS will perfectly happily segmentize a geography over the dateline. But if you plot it on a naive flat map, you'll get an ugly goalline-to-goalline run from the segment that traverses the dateline. Perhaps once you've cast it back to geometry, run ST_Intersection between the line and a pair of -180,-90,0,90 / 0,-90,180,90 boxes to chop it up. Or try ST_Split with the dataeline as an argument. Commented Feb 2, 2014 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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