[GIS] Splitting lines with PostGIS and view result in QGIS

postgisqgissplitting

I have two tables, one contains lines (test_line) and the other contains the points at the vertices of the lines (test_line_vertex).
enter image description here

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.

enter image description here

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.

 CREATE TABLE sometable AS
 WITH 
 lines as (SELECT ST_GeomFromText('LINESTRING(0 0, 2 2, 10 10, 15 15, 20 20)') as geom),
 points as (SELECT ST_GeomFromText('MultiPoint((0 0), (2 2), (10 10), (15 15), (20 20))') as geom),
 numgeoms (pt_count) as (SELECT st_numgeometries(geom) + 1 as pt_count FROM points),
 knife_points as (
   SELECT x as segment, CASE WHEN x = 0 then 0 
     WHEN x = (select pt_count from numgeoms) THEN 1 ELSE  
     ST_Line_Locate_Point(l.geom, ST_GeometryN(p.geom,x)) END as line_fraction_end, 
     CASE WHEN x = 1 THEN 0  else 
     ST_Line_Locate_Point(l.geom, ST_GeometryN(p.geom,x-1)) END as line_fraction_start 
   FROM points p, lines l, (SELECT generate_series(0, (SELECT pt_count from numgeoms)) as x ) g),
   segments as 
    (SELECT ST_Line_Substring(geom, line_fraction_start, line_fraction_end) as geom, segment 
     FROM knife_points, lines WHERE segment >0 )
   SELECT geom, segment from segments 
   WHERE ST_GeometryType(geom) = 'ST_LineString';

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,

 LINESTRING(0 0,2 2)     |       2
 LINESTRING(2 2,10 10)   |       3
 LINESTRING(10 10,15 15) |       4
 LINESTRING(15 15,20 20) |       5
Related Question