Search code examples
sqlcartodb

Cartodb SQL: ST_MakeLine Conversion

I am using Cartodb to map some GPS points that I would like to connect the unique IDs with lines. I was using this site as a reference when writing my SQL. The SQL executes without any errors, but my map is not generating.

Here is my CSV dataset I'm running the SQL on:

X           Y         track_fid       track_seg_point_id    time
-87.5999    41.7083     0              0                   2/17/2018 16:10
-87.74214   41.91581    0              0                   2/17/2018 16:11
-87.6005    41.7081     0              0                   2/17/2018 16:14
-87.6584    41.8265     0              1                   2/17/2018 16:41
-87.63029   41.85842    0              1                   2/17/2018 16:59
-87.7308    41.8893     0              1                   2/17/2018 17:07
-87.59857   41.708393   0              2                   2/17/2018 17:08
-87.5995    41.7081     0              2                   2/17/2018 17:15
-87.68106   41.799088   0              2                   2/17/2018 17:47

Here is my SQL:

SELECT 
    ST_MakeLine(the_geom_webmercator ORDER BY time ASC) AS the_geom_webmercator,
    extract(hour from time) as hour,
    track_seg_point_id AS cartodb_id


    FROM snow_plow_data

    GROUP BY
    track_seg_point_id,
    hour

Here is the resulting table from my SQL:

Hour       cartodb_id
16         0
16         1
17         1
17         2

Any ideas or suggested would be great on why my map points are not being displayed as lines.

Solution

  • If you are using BUILDER UI, you can add Create Lines from Points analysis, ordering by time and grouping your lines by the track_fid field (or track_seg_point_id, don't know which field you want to use):

    connect-with-lines

    On the other hand, if you want to do it using the SQL console. CARTO BUILDER now needs not only the cartodb_id and the_geom_webmercator, but also the_geom column. So you would need to add this last field to your query. Something like this should work:

    WITH lines as (
  SELECT 
      ST_MakeLine(the_geom_webmercator ORDER BY time ASC) AS the_geom_webmercator,
      ROW_NUMBER() OVER() as cartodb_id
  FROM 
      tracks
  GROUP BY
      track_seg_point_id)

SELECT
    ST_Transform(the_geom_webmercator, 4326) as the_geom,
    *
FROM
    lines

    sql-lines