Loading the data

The table of points will be generated by randomly choosing points on the linestrings:

create sequence points_seq;
create table points as with rand as (
select random() as locus from generate_series (1, 10)
), rand2 as (
select nextval('points_seq') as gid, l.gid as lgid, r.locus, st_line_interpolate_point(l.geom, locus) as geom
from rand as r, lines l
) select * from rand2
order by random() limit 1500; -- only 1500 pts out of 3300 generated with random locus on the linestrings

Principle of Splitting

Linear Referencing function st_line_interpolate_point() and st_line_substring() will be used to first locate all points along their respective Linestrings, then cut linestrings based on these locations.

In the first CTE, an union is performed to generate the 0 and 1 line fractions, that are necessary to correctly split linestrings.

To identify each point location for a Linestring, we use the rank() windowing function to generate an ascending id for each successive point location.

Then, a self join of the table will be used to select the 2 locations to give to st_line_substring