I have two tables, one contains lines (test_line) and the other contains the points at the vertices of the lines (test_line_vertex).
I want to split the line into individual segments based on the vertices.
I tried the following query:
CREATE TABLE test_intersect AS
SELECT ST_Split(a.wkb_geometry,b.wkb_geometry)
FROM test_line as a,
test_line_vertex as b;
It returns successfully the rows with the line segments, but when I try to display it in QGIS it shows me that something is wrong. The spatial type is not defined and I can't view the lines in QGIS. So I assume something is wrong with my query.
Best Answer
There is a plpgsql function is this post that splits a Line using multiple points.
However, it can also be done in a query, using generate_series, ST_Line_Locate_Point and ST_GeometryN to get knife points and ST_Line_Substring to split the line. Whichever you prefer, I leave up to you, as this query is not exactly terse.
where you would replace the initial lines and points tables with your own. I suspect this query could be simplified quite a bit by using lead or lag, to get calculate each successive pair of knife points, but it does work.
The output of the above is,