Connecting two coordinates with Polyline? Cartodb?

Question

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).

Answer 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