Postgis ST_Split to split linestring into multiple smaller linestrings based on node locations

Question

I have 2 tables, one with linestrings and an array of id values, and the other points with the before listed id values. I want to perform a slice at the node locations associated with the linestrings and add those newly cut linestring pieces to a new table.

I don't have much experience with sql or postgresql either.

table 'routes':

table 'pois':

All under the 'Public' schema.

I'm looking at repurposing this to deal with the connections between tables.

with dat as (select
    st_collect(array((select geom from pois where pois.id = any( array( (select routes.stops from routes where routes.id = 919290) ) ) ) ) ) as slice,
    (select geom from routes where id = 919290) as r
)
select st_dump( st_split(r, slice) ) as lines from dat;

results in the necessary aliased table that I need to insert into a new table with any id value, a column that represents the id = 919290 value and then the geom linestring.

Now, I just need to handle the same for every row in the routes table.

The above works for splitting the line into segments for route.id=919290 but I need/want to insert the data into Ideally, output would be another table with each split linestring as a row geometry and a column value to list associated id from the original routes table id.

Answer 1

Here's a generalised version of your attempt...

WITH route_stop AS
(
    SELECT
        id AS route_id,
        UNNEST(stops) AS stop_id
    FROM routes
),
route_stop_point AS
(
    SELECT
        rs.route_id,
        p.geom
    FROM route_stop AS rs
    JOIN pois AS p ON p.id = rs.stop_id
),
route_slice AS
(
    SELECT
        route_id,
        ST_COLLECT(ARRAY_AGG(geom)) AS slice
    FROM route_stop_point
    GROUP BY
        route_id
)
SELECT
    rt.id,
    ST_DUMP(ST_SPLIT(rt.geom, rs.slice)) AS lines
FROM routes AS rt
LEFT JOIN route_slice AS rs ON rs.route_id = rt.id;