0

I am using an online cartodb account to try and create a shapefile based on flight routes between one airport and another.

So far, I have uploaded my Lat/Long points for each connected airport, and used st_makeline to create a line string between them in geometry data type.

However, this outputs a direct straight line, whereas it should really follow a geographic projection and output as a curved line.

Does anyone have any advice on how to do this? Bearing in mind I am using cartodb online so am restricted to the custom SQL query it provides.

Current query is as follows:

SELECT 
    routeid, 
    boardpoint_offpoint,
    ST_Makeline(the_geom) as the_geom
FROM 
    routemapping
group by 
    routeid, boardpoint_offpoint
nmtoken
  • 13,355
  • 5
  • 38
  • 87
Daniel
  • 1
  • 1
  • Did you take a look at the answer over here,

    http://gis.stackexchange.com/questions/84443/what-is-this-postgis-query-doing-to-show-great-circle-connections

     – andrewxhill Feb 27 '14 at 15:58
  • Hi Andrew, thanks for the reply, yes i saw that one but it doesn't seem to be working for me. I used it as below: SELECT routeid, boardpoint_offpoint, ST_Transform(ST_Segmentize(ST_MakeLine(ST_Transform(the_geom), 953027), 100000), 4326) as the_geom FROM routemapping group by routeid, boardpoint_offpoint – Daniel Feb 27 '14 at 16:04
  • But I received an error regarding the spatial ref 953027. I know this can be sorted, but not (as far as I'm aware) on the online platform for cartodb. – Daniel Feb 27 '14 at 16:06
  • Have also tried another answer on the same page, SELECT routeid, boardpoint_offpoint, ST_Segmentize(ST_MakeLine( ST_SetSRID(ST_MakePoint(long::float, lat::float),4326),pt)::geography)::geometry FROM routemapping group by routeid, boardpoint_offpoint – Daniel Feb 27 '14 at 16:27
  • But no luck! Errors on ST_Segmentize(geography) does not exist. I'm fairly sure my syntax is just wrong...please help!! – Daniel Feb 27 '14 at 16:28
  • Right. ST_Segmentize(geography) does not exist. You need ST_Segmentize(geography, number). Try adding the 100000 back into your last query. – andrewxhill Feb 27 '14 at 21:07
  • 1
    You sir, are a genius. That worked exactly as I'd hoped. For anyone else looking, the resulting query was SELECT routeid, boardpoint_offpoint, ST_Segmentize(ST_Makeline(the_geom)::geography,100000)::geometry as the_geom FROM routemapping GROUP BY routeid, boardpoint_offpoint – Daniel Feb 27 '14 at 21:44
  • 1
    I don't suppose for a bonus point you know how to include distance as part of that query? ie - to get the shortest route. (I just noticed how north america to australia goes right across the map. Surely the shortest route is via the pacific ocean??) – Daniel Feb 27 '14 at 21:47
  • You can measure distance with ST_Distance(point1, point2) and measure it in meters with ST_Distance(point1::geography, point2::geography). What you really want to do is calculate if two points are greater than 180 deg without crossing the dateline, then if they are, do something else. I know combining it with curved lines is going to not work great. but start here http://gis.stackexchange.com/questions/85126/can-i-cross-the-dateline-in-cartodb – andrewxhill Feb 28 '14 at 15:12

1 Answers1

2

You need to hop through the geography type to get a great circle segmentation.

SELECT 
  routeid, boardpoint_offpoint, 
  ST_Segmentize(ST_Makeline(geom)::geography,100000)::geometry as geom 
FROM routemapping 
GROUP BY routeid, boardpoint_offpoint;
Paul Ramsey
  • 19,865
  • 1
  • 47
  • 57
  • Hi Paul, thanks very much for looking at my issue, I tried running the code you have suggested and receive the following error: "function st_segmentize(geography) does not exist" Any thoughts? – Daniel Feb 27 '14 at 20:34
  • I guess CartoDB isn't at a version that has that function yet. It is a little recent. – Paul Ramsey Feb 27 '14 at 23:24
  • 1
    No I think I was just missing an integer value, as AndrewXHill pointed out. ST_Segmentize(ST_Makeline(the_geom)::geography,100000)::geometry as the_geom worked for me. – Daniel Feb 28 '14 at 00:32