2

How do i create a linestring from points sort by date in PostgreSQL?

I tried:

SELECT observations.id,
   ST_MakeLine(observations.geom ORDER BY observations.date) AS newgeom
   FROM   observations 
   GROUP BY
   observations.id;

This is going wrong. It returns me a linestring with same coordinates. So, instead of a line from a to b, it gives me a to a.

enter image description here

What am I doing wrong?

  • 1
    Try losing the group-by clause – alphabetasoup Oct 17 at 7:13
  • Richard Law, how? – Tim van Heteren Oct 17 at 7:22
  • 2
    Group by something else, or nothing at all. You should add more information about your schema and goal here. Do you want to make a line that geometrically connects all of your observations? Or is there some criteria by which you mean to group them? Grouping on observation.id seems odd since presumably each observation has only one ID. – alphabetasoup Oct 17 at 7:34
  • I have gps points. These need to be connected with a line. I want 1 feature line for each 2 connected points. So a feature for a to b. One for b to c etc. They need to sort by date. So i get a route based on time. Got a table with: id, date, geom. – Tim van Heteren Oct 17 at 7:39
  • OK, I might get back with a fuller answer tomorrow, but the basic premise of the solution is to join the table to itself, such that each observation is joined once to the next observation in the sequence. At the moment you are joining the table to itself without that sequential offset. There might be similar Q&A for this already if you look for ST_Makeline questions dealing with things like bus routes formed from bus stops, which is the same logical rule. – alphabetasoup Oct 17 at 8:07
4

The point is, if you GROUP BY the row id column, you will get one result row per input row (this is equal to grouping by the actual date column)! And since a Linestring is only valid with a minimum of two points, ST_MakeLine adds the same point twice.

Either run

SELECT ST_MakeLine(geom ORDER BY date) AS geom
FROM   observations
;

to get one line for all points, or

SELECT ROW_NUMBER() OVER() AS id,
       *
FROM   (
  SELECT ST_MakeLine(geom, LEAD(geom) OVER(ORDER BY date)) AS geom
  FROM   observations
) q
WHERE  geom IS NOT NULL
;

to get a linestring between each pair of consecutive points.

  • Wow thanks, that worked great!! To get a linestring between each pair of consecutive points....can i give more values, like speed and sensor state? – Tim van Heteren Oct 17 at 8:17
  • 1
    I just updated that query to exclude NULL geometries. yes, you can add more values from your base table; but note that this query constructs a LineString from the current row to the next, and will add values from the current row! If that is undesirable, you can change that, too. – ThingumaBob Oct 17 at 8:27
  • Perfect, Thanks a lot!! – Tim van Heteren Oct 17 at 8:33
  • TIL about LEAD, that's neat. – alphabetasoup Oct 17 at 9:10

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.