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

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

Better look at using ST_LineSubstring; no issues with coordinate precision, but somewhat tricky to set up...

---

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
;