postgis - PostgreSQL create line from points

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.

What am I doing wrong?

Answer

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.