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.

  • 1
    Table structure and some sample data records would help...
    – mlinth
    Dec 4 '15 at 13:34

Something like:

SELECT gps.line_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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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