4

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:

http://bl.ocks.org/andrewxhill/raw/8406976/

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),
   ST_Transform(
          (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.

4

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

UPDATE my_table
SET the_geom =
   ST_Transform(
     ST_Segmentize(
       ST_MakeLine(
         ST_Transform(my_table.the_geom, 953027),
         ST_Transform(CDB_LatLng(48.432044, -71.060316), 953027)
       ), 
       100000
     ), 
    4326 
   )

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 Jan 27 '14 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) – andrewxhill Jan 27 '14 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 Jan 27 '14 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. – andrewxhill Jan 27 '14 at 23:44
  • 1
    Et voila. Looking forward to showing ya the map. – Amanda Jan 28 '14 at 16:26
2

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:

SELECT 
  ST_Segmentize(
  ST_MakeLine(
    ST_SetSRID(ST_MakePoint(-71.060316, 48.432044),4326), 
    pt
  )::geography
  , 10000
  )::geometry
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 Jan 28 '14 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. – Paul Ramsey Jan 28 '14 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 Feb 2 '14 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. – Paul Ramsey Feb 2 '14 at 22:09

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.