[GIS] How to split a linestring every 1000 points in postgis

algorithmlinestringpostgis

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;

Best Answer

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;
Related Question