PostGIS points along a line aren't actually falling on the line

Question

I have a line network and I am placing points at equal intervals along each line. These points will be used to cut the lines into segments. I have found a way to create the points along the lines:

CREATE TABLE split_pt as

WITH line AS 
    (SELECT
        id,
        geom 
    FROM line_table),
linemeasure AS
    (SELECT
        ST_AddMeasure(line.geom, 0, ST_Length(line.geom)) AS linem,
        generate_series(0, ST_Length(line.geom)::int, 160.9) AS i
    FROM line),
geometries AS (
    SELECT
        i,
        (ST_Dump(ST_GeometryN(ST_LocateAlong(linem, i), 1))).geom AS geom 
    FROM linemeasure)

SELECT
    row_number() over() as id,
    i,
    ST_SetSRID(ST_MakePoint(ST_X(geom), ST_Y(geom)), 3857) AS geom
FROM geometries;

However, I have two problems:

I'm getting around 400 duplicate points
About 60% of the points are not actually on the line so running the subsequent ST_Split() with these points against the lines doesn't work in most instances.

I am looking for help to eliminate duplicate points and to ensure that the points I create in the above query will return True on ST_Intersects(). I'd like to be able to incorporate these two aspects in the the above query and not in subsequent steps, if possible.

geozelot · Accepted Answer

Better look at using ST_LineSubstring; no issues with coordinate precision, but somewhat tricky to set up... Edit: I then packed it into a more sophisticated set of functions a while ago; there's a C function add-on as well, for those who like to build from source. I packed that functionality into a (naive) function a few years ago: CREATE OR REPLACE FUNCTION ST_LineSubstringByLength( geom GEOMETRY(LINESTRING), length FLOAT8, use_meter BOOLEAN DEFAULT TRUE ) RETURNS SETOF geometry_dump AS $$ DECLARE len_frac FLOAT8; s_frac FLOAT8; e_frac FLOAT8; BEGIN IF ($3) THEN len_frac := $2 / ST_Length(geom::GEOGRAPHY); ELSE len_frac := $2 / ST_Length(geom); END IF; FOR n IN 0..CEIL(1.0 / len_frac) LOOP s_frac := len_frac * n; IF (s_frac >= 1.0) THEN EXIT; END IF; e_frac := len_frac * (n + 1); IF (e_frac > 1.0) THEN e_frac := 1.0; END IF; RETURN NEXT (ARRAY[n + 1], ST_LineSubstring($1, s_frac, e_frac)); END LOOP; RETURN; END $$ LANGUAGE plpgsql; The function accepts: a LineString GEOMETRY a Length value by which the given LineString should be divided(until the last element which will be of the rest length) a Switch to set the given length to be interpreted as meter; defaults to TRUE(input geometry needs to be in EPSG:4326; it really just casts to GEOGAPHY for the fraction calculation) The function returns a SETOF geometry_dump (a RECORD just like e.g. ST_Dump) having: a path value (INTEGER[]) indicating the position of the current row in the extraction starting from the beginning of the input LineString a geom member (GEOMETRY) A record looks like: ({1}, 01020000000200000000000000000000000000000000000000A65F8237663FC13F0000000000000000) ({2}, 010200000002000000A65F8237663FC13F0000000000000000A65F8237663FD13F0000000000000000) Usage: SELECT * FROM ST_LineSubstringByLength('LINESTRING(0 0, 5 0)'::GEOMETRY, 15000); SELECT * FROM ST_LineSubstringByLength('LINESTRING(0 0, 5 0)'::GEOMETRY, 1, FALSE); SELECT id, (dmp).path[1], (dmp).geom FROM , LATERAL ST_LineSubstringByLength(geom, [, TRUE|FALSE]) AS dmp ;

PostGIS points along a line aren't actually falling on the line

One Answer

Add your own answers!

Ask a Question