2

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?

4

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.

| improve this answer | |

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.