0

I have some trip data and I want to draw a line between the start and end coordinates.

I have two layers in CartoDB - one contains the start coordinates (with trip_id & cartodb_id). The other contains the corresponding end coordinates (with trip_id & cartodb_id).

As I'm new to CartoDB/SQL in general, how can I join these the two coordinates for the same trip?

I found this code somewhere but it hasn't been working for me thus far:

SELECT 
  ST_MakeLine(
    the_geom_webmercator, 
    (SELECT 
      the_geom_webmercator
    FROM 
      hubway_july2012_end   
    WHERE trip_id = cartodb_id LIMIT 1
    )
  ) AS the_geom_webmercator 
  FROM 
  hubway_july2012_start
    AS c

(I've edited it a little bit, but I'm not sure if what I have done is right).

4
  • Welcome to GIS SE! As a new user please take the Tour as an introduction to our focussed Q&A format. You have mentioned CartoDB in your title so I suggest that you explore that as your first candidate rather than asking for Software Recommendations here on a site that starts from the premise that you are already using or well into researching a particular software. Previous questions have been asked here that are tagged CartoDB and CSV - they could be worth reviewing for ideas of how they can interact.
    – PolyGeo
    Dec 14, 2015 at 6:50
  • Does it appear to be working. If not, what is wrong? Dec 14, 2015 at 10:27
  • No it's not for some reason! If I put this code into the SQL box for the start coordinates layer, all the points on that layer just disappear.
    – shaneg1991
    Dec 14, 2015 at 20:49
  • And vice versa if I do the same for the end coordinates layer
    – shaneg1991
    Dec 14, 2015 at 20:50

1 Answer 1

1

For what you say I'm assuming you can link start and end points by their trip_id. If that's the case (and each trip only has one point stored in table A and one in table B) then your sql only needs to do the relation right (now you are using cartodb_id vs trip_id, which I don't think is correct).

I'd maybe do something like this:

WITH coords AS
 (SELECT 
 a.the_geom_webmercator as coord_a, 
 b.the_geom_webmercator AS coord_b 
 FROM hubway_july2012_start a JOIN hubway_july2012_end b on a.trip_id=b.trip_id )

SELECT ST_MakeLine(coord_a, coord_b) AS the_geom_webmercator FROM coords
2
  • Thanks, I'll give that a try. This might be a stupid question but would I put that in the SQL box for the start or end layer? (right now I have two layers - one is for the start coordinates and the other is for the end coordinates)
    – shaneg1991
    Dec 14, 2015 at 20:41
  • Actually it doesn't matter. Whatever you put in that box gets executed as SQL regardless of the currently selected table Dec 15, 2015 at 14:33

Your Answer

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

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