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
9
  • Did you take a look at the answer over here, gis.stackexchange.com/questions/84443/… Feb 27, 2014 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, 2014 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, 2014 at 16:06
  • 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, 2014 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, 2014 at 21:47

1 Answer 1

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;
3
  • 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, 2014 at 20:34
  • I guess CartoDB isn't at a version that has that function yet. It is a little recent. Feb 27, 2014 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, 2014 at 0:32

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.