Postgis: How do I select every second point from LINESTRING?

In DBeaver I have a table containing some GPS coordinates stored as Postgis LINESTRING format. My questions is: If I have, say, this info:

LINESTRING(20 20, 30 30, 40 40, 50 50, 60 60, 70 70)

which built-in ST function can I use to get every N-th element in that LINESTRING? For example, if I choose 2, I would get:

LINESTRING(20 20, 40 40, 60 60)

, if 3:

LINESTRING(20 20, 50 50)

and so on.

I've tried with ST_SIMPLIFY and ST_POINTN, but that's now exactly what I need because I still want it to stay a LINESTRING but just with less points (lower resolution).

Any ideas?

Thanks :-)

Solution

Welcome to SO. Have you tried using ST_DumpPoints and applying a module % over the vertices path? e.g. every second record:

WITH j AS (
SELECT 
  ST_DumpPoints('LINESTRING(20 20, 30 30, 40 40, 50 50, 60 60, 70 70)') AS point
) 
SELECT ST_AsText(ST_MakeLine((point).geom)) FROM j
WHERE (point).path[1] % 2 = 0;

           st_astext           
-------------------------------
 LINESTRING(30 30,50 50,70 70)
(1 Zeile)