How to create lines from coordinates?

Question

I have a data table about national bus lines, with timestamps, vehicle IDs, line IDs (which line the bus is moving on), and coordinates(geom). I can add my points to QGIS on show them on OSM, but I'd like to create lines from these points (so I can show fuel lvl on them, for example or speed).

Tried using plugins inside QGIS for this, but did not properly worked.

I am quite new to SQL (just started using it, for like a few weeks), so how can I make lines from my coordinates, grouping by line IDs and vehichle IDs?

Found something about ST_Makeline, but with my very basic knowledge, I am struggling getting a grip on it.

• mlinth: Table structure and some sample data records would help...

Answer 1

Something like:

SELECT gps.line_id,
gps.vehicle_id,
ST_MakeLine(gps.geom ORDER by gps.gps_time) AS geom
FROM gps
GROUP BY gps.line_id, gps.vehicle_id

If you add more information about your schema that would be handy, but you should be able to adapt this. Ask any additional questions in the comments.

ST_MakeLine documentation