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?

  • postgis.net/docs/ST_MakeLine.html does what you want. You certainly do not need to use ST_Collect as the ST_MakeLine essentially takes an array of points, ordered by time, and optionally grouped by track. I think the 2nd example should suit your needs perfectly. If this hasn't answered your question, post your table structure, and I will attempt to write a query for you. – John Powell Nov 14 '14 at 10:36
  • I see. I get the idea now. – john mathew Nov 14 '14 at 17:38

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;

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.