5

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

  • 2
    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 6:22
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
  • 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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.