How to split a linestring every 1000 points in postgis

Question

To assume compatibility with my survey software, I would like to lighten geometries of my linestrings by cutting them every 1000 points without creating or interpolating points.

I transformed a function found on the net (resample) but it cuts only the first 1000 points...

Does someone knows how to loop it correctly?

CREATE OR REPLACE FUNCTION simplify_npoints(inGeom geometry, maxPoints integer) RETURNS geometry AS $$

DECLARE nPoints integer; outGeom geometry; fraction float; points geometry[];

BEGIN

nPoints := ST_NPoints(inGeom); outGeom := inGeom;

IF nPoints > maxPoints THEN FOR i IN 1..maxPoints LOOP points := array_append(points, ST_PointN(inGeom,i)); END LOOP; outGeom := ST_MakeLine(points);

END IF;

RETURN outGeom;

END; $$ LANGUAGE plpgsql IMMUTABLE;

Answer 1

You haven't stated what output you want so an array of geometry should do:

CREATE OR REPLACE FUNCTION simplify_npoints(inGeom geometry, maxPoints integer) RETURNS geometry[] AS $$
DECLARE 
    outGeom geometry[]=ARRAY[]::geometry[]; 
    points geometry[]=ARRAY[]::geometry[]; 
    counter integer:=0;
BEGIN       
    IF maxPoints=1 THEN
        RAISE EXCEPTION  'maxPoints must be >1. Split not possible';
    END IF;
    FOR i IN 1..ST_NPoints(inGeom)
    LOOP
        counter:=counter+1;
        points := array_append(points, ST_PointN(inGeom,i));
        IF counter=maxPoints THEN
            outgeom:=array_append(outGeom,ST_MakeLine(points));
            points := ARRAY[ST_PointN(inGeom,i)];
            counter:=1;
        END IF;
    END LOOP;
    IF counter>1 THEN 
    outgeom:=array_append(outGeom,ST_MakeLine(points));
END IF;
    RETURN outGeom;
END; 
$$ LANGUAGE plpgsql IMMUTABLE;

Answer 2

I realized that the ending trunk of the linestrings was missing. We were only making new line when counter matched the maxpoints value, leaving the rest.
I add a new counter "totalpoints" and a new case system to reach the end of the linestring:

CREATE OR REPLACE FUNCTION simplify_npoints(ingeom geometry, maxpoints integer)
RETURNS geometry[] AS $BODY$
DECLARE 
outGeom geometry[]=ARRAY[]::geometry[]; 
points geometry[]=ARRAY[]::geometry[]; 
counter integer:=0;
countmax integer:=0;
totalpoints integer:=0;
BEGIN       
IF maxPoints=1 THEN
    RAISE EXCEPTION  'maxPoints must be >1. Split not possible';
END IF;

totalpoints:=ST_NPoints(inGeom);

FOR i IN 1..totalpoints
LOOP
    countmax:=countmax+1;
counter:=counter+1;
points := array_append(points, ST_PointN(inGeom,i));

CASE WHEN counter=maxPoints THEN

        outgeom:=array_append(outGeom,ST_MakeLine(points));
        points := ARRAY[ST_PointN(inGeom,i)];
        counter:=1;

    WHEN countmax=totalpoints THEN
    outgeom:=array_append(outGeom,ST_MakeLine(points));
    countmax:=0;
ELSE

END CASE;

END LOOP;
RETURN outGeom;
END; 
$BODY$
LANGUAGE plpgsql IMMUTABLE