Group geometry points and create a linestring for each group

Question

I have a table of points at a Postgres database that have two main columns (geom geometry, time timestamp).

I need a query to retrieve as linestrings or as array of the geometries the points that are close to each other by 1 m aprox and follow the sequence of time.

The points table:

Example:

Within this set of points, how can i get in a query those two sequences of points as two different linestrings? Also excluding points that are isolated.

Assuming that this is a particular case within millions like those two cases, which would be the more efficient way to find all the linestrings?

Answer 1

Among the most versatile approaches would be to use ST_ClusterDBSCAN to create cluster of points based on distance; apart from the general uncertainty of results due to the lack of a common identifier, the only drawback is that it currently only supports the GEOMETRY type and is thus limited to the CRS units (degrees for EPSG:4326).

Running

SELECT ROW_NUMBER() OVER() AS id,
       ST_MakeLine(geom ORDER BY "time") AS geom
FROM (
  SELECT "time",
         geom,
         ST_ClusterDBSCAN(geom, 0.00001, 2) OVER() AS cid
  FROM   <table>
) q
WHERE  cid IS NOT NULL
GROUP BY
       cid
;

should get you close to what you intent; you may need to tweak the eps parameter. Setting the minpts parameter to 2 marks isolated points with cid = NULL, which can then be used to exclude them from creating (invalid) lines.