PostgreSQL group by but sort inside aggregation function (ST_Makeline)

I have a trajectory table in PostgreSQL and want to make polyline for each trajectory.

Each line of data contains datetime (datetime), session_id (string) and geom (Point geometry)

The goal is to construct each polyline with by all points following chronological order of datetime

I tried:

select session_id, st_makeline(geom) as geom
from trajectory
group by session_id

The code above gives me polylines of each trajectory, but for each polyline, the order was wrong.

How can I order nodes in the polyline so that they follow the order of datetime?

Solution

You can add an order by clause to st_makeline(), as explained in the postgis docs:

select session_id, st_makeline(geom order by datetime) as geom
from trajectory
group by session_id