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.