Splitting lines with PostGIS and view result in QGIS?

Question

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

ST_Split is going to give you back a geometry collection. Unfortunately, geometry collections aren't well supported outside of the database world.
A bit of background: the split of geometries depends on what you split, and what you split them with. Potentially you could get back a multipolygon instead of a multilinestring. I'd try creating the table first, then inserting the output of ST_CollectionExtract. — BradHards, Dec 10 '14 at 06:22

score 4 · Accepted Answer · edited Jul 16 '17 at 21:10

4

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

edited Jul 16 '17 at 21:10

PolyGeo

65,136
29
109
338

answered Dec 10 '14 at 08:39

John Powell

13,649
5
46
62

Thanks for your detailed answer. The first query works fine, but it returns seven times the line, without splitting it into pieces. Is ST_Split maybe no the right operation for what I am trying to do? I wish to have four lines in the end. – ustroetz Dec 10 '14 at 19:11
1

No, it is the right function. Just me being idiotic and not testing properly, sorry. I will attempt to fix in the morning, a bit tied up right now, if someone else doesn't answer. – John Powell Dec 10 '14 at 19:40
I have put a slightly crazy query together. I understand if you don't use it, but it does work :D – John Powell Dec 11 '14 at 12:29
Works perfect! I'll mark it as answered if you add a create table statement so it can be viewed in QGIS (just to answer the original question properly). – ustroetz Dec 11 '14 at 19:17
Done. You just put create table as before the with :D – John Powell Dec 11 '14 at 19:37
Thanks for the edit. The ST_AsText in the original answer was only to visualize the results. Sorry for not being more clear. – John Powell Dec 11 '14 at 23:43
No worries. Thanks for your help John. It all works now :) – ustroetz Dec 11 '14 at 23:49

score 0 · Answer 2 · answered Jul 08 '15 at 20:52

When i try the solution

i do get the the next error :

"ERROR: more than one row returned by a subquery used as an expression" with the following sql query:

CREATE TABLE osm.roads_separado AS

 WITH 

 lines as (SELECT geom from osm.lines),
 points as (SELECT geom from osm.points),
 numgeoms (pt_count) as (SELECT st_numgeometries(geom) + 1 as pt_count FROM osm.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';

why is that? there's lots of lines and lots of dots.

What do i need to change? txs!

(sorry i couldnt add comment on solution!)

i'm trying now with st_union in both tables (lines, points). SELECT geom from osm.lines_union (where osm.lines are prepared as create table osm.lines_union as ( select st_union(geom) from osm.lines); & points the same way. — vlasvlasvlas, Jul 10 '15 at 14:03
Ok. this didnt worked either. It got me sort of strange cuts but no one following the points table.. — vlasvlasvlas, Jul 10 '15 at 14:31

Splitting lines with PostGIS and view result in QGIS?

2 Answers2