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:

1. I'm getting around 400 duplicate points
2. 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.

Answer 1

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   <table_with_geom_and_id_columns>,
       LATERAL ST_LineSubstringByLength(geom, <length_in_meter?>[, TRUE|FALSE]) AS dmp
;