[GIS] Converting GPS points with timestamp to polylines in PostgreSQL

Question

I have some GPS coordinates points based on timestamp in a table. My table consists of and id, time_stamp and GPS points as the geometrical data.

I want to create a line by joining all the points from this table. I assume I would have to convert the point table into a polyline table. Should I find out all the points in a particular area using St_Collect() function?

I also think that as table has time stamp related to each coordinate, I should follow the timestamp to get the next point in a line?

Putting it in other words, I have created a table with columns as id(int) , time(int) and geom(GPS coordinates as POINT). I have a set of rows added to this table. Now in order to create a polyline I assume that I should take the time in increasing order and join all the points associated with. Is my view correct?

If yes, I would like to convert these GPS points into a polyline. How should I go about it? Is there any geometric function in PostgreSQL that could support this idea?

Answer 1

As commented by John Powell, ST_MakeLine in its spatial aggregate form will take rows of points (multipoint or line geometries) and will return a line string (it can also handle arrays of point and multipoint).

Here is an example from ST_MakeLine documentation (for PostgreSQL 9+):

SELECT gps.gps_track, ST_MakeLine(gps.the_geom ORDER BY gps_time) As newgeom
    FROM gps_points As gps
    GROUP BY gps.gps_track;