Postgresql – SQL Select rows between starting flag and make linestring over geometries

As said: there are more ways to achieve the same result. This one can be a bit of a puzzle, but it works:

WITH reorder AS (
  SELECT id, car, timestamp
  , row_number() OVER (PARTITION BY car ORDER BY timestamp) AS tcid
  , geom, startflag 
  FROM mytable
), tracks AS (
  SELECT car, tcid, ROW_NUMBER() OVER (PARTITION BY car ORDER BY tcid) AS track, geom
  FROM reorder
  WHERE startflag = 4
UNION ALL
  SELECT car, tcid, tcid - ROW_NUMBER() OVER (PARTITION BY car ORDER BY tcid) AS track, geom
  FROM reorder
  WHERE startflag <> 4
)
SELECT car, track, ST_MakeLine(geom ORDER BY tcid) AS geom
FROM tracks
GROUP BY car, track
HAVING count(*) > 1
ORDER BY car, track;

Some explanation:

• CTE "reorder" adds a continuous number sequence to all the collected points for each car, stored as "tcid" (calculating a "tcid_last" is not necessary);
• the first part of CTE "tracks" collects only the starting points (startflag = 4) from the previous CTE, and assigns a new sequence number (to be used as a track number) starting with 1 for every car (handled by PARTITION BY);
• after the UNION ALL, the second part of CTE "tracks" collects all the non-starting points (startflag <> 4) and assigns a new continuous sequence number to the obtained records, starting with 1 for each car;
• the resulting sequence number is subtracted from the old "tcid" that was obtained in CTE "reorder", resulting in a track number that corresponds to the re-assigned track numbers in the first part (prior to UNION ALL) of CTE "tracks";
• in the main query at the end, the tracks can again be grouped by car and track, aggregating the points into a linestring, with a HAVING clause that eliminates all tracks with only 1 point.

Why does it work?

Let’s say we started with the following records (abbreviated column list):

 car  | tcid | flag
-------------------
test1 | 1    | 4
test1 | 2    | 0
test1 | 3    | 0
test1 | 4    | 4
test1 | 5    | 0

Obviously there are 2 tracks for car ‘test1’, starting at tcid 1 and 4, so the first part of CTE "tracks" would result in the following table (where column "track" is filled with a new number sequence, starting with 1):

 car  | tcid | track
-------------------
test1 | 1    | 1
test1 | 4    | 2

The second part would (temporarily) contain the following values:

 car  | tcid | row_number | tcid - row_number AS track
------------------------------------------------------
----[tcid 1 is eliminated by the WHERE condition)----
test1 | 2    | 1          | 1
test1 | 3    | 2          | 1
----[tcid 4 is eliminated by the WHERE condition)----
test1 | 5    | 3          | 2

In this result list, the newly created row_numbers again form a series of consecutive numbers, but the original tcid’s now have ‘holes’ in their sequence (some records were filtered away, when they had a startflag = 4).

• Had there been no ‘holes’ in any number sequence, both starting with ‘1’, the subtraction would always have been ‘0’ (a constant)
• in parts of the table where tcid is constantly increasing with 1 for every new record, the newly created row_number is also increasing with 1, so the subtraction again yields a constant value (until a new ‘hole’ appears). In other words: tcid’s 2 and 3 are consecutive, and also the new row_numbers 1 and 2, so the subtraction has equal values;
• there are as many ‘holes’ in the tcid-sequence as there are track starts, and with every ‘hole’ the subtraction results in a new track number (tcid – row_number) equal to the previous track number plus 1;
• therefore the column calculated as "tcid – row_number" will result in a track number corresponding to the n-th track from the first part of the CTE (the part before UNION ALL).

One caveat: if the first inserted record in the original table was (for whatever reason) not flagged as a starting point, this query will create a linestring (given enough points for that track) but will assign a track number 0. That could quite easily be eliminated in the main query, using WHERE track > 0.