6

I have a line shapefile and a point shapefile, and

  • the line has just one segment

enter image description here

and its attribute table,

enter image description here

  • the point shapefile has points near the line above, not on it

enter image description here

and the close look,

enter image description here

I want to use ST_split to split this line at locations which are nearest to the points I want to get the original line with more segments(because being split). For example, find the location on the line, which is the nearest to the point, and split the line at this location(star).

enter image description here

I loaded this line shapefile into PostGIS and here's my SQL code in PostgreSQL,

select ST_AsText(ST_Split(ST_GeomFromText('LINESTRING(0 0, 247907.60 2746404.62, 200    200, 400 400)'),
       ST_Line_Interpolate_Point(ST_GeomFromText('LINESTRING(0 0, 247907.60 2746404.62, 200 200, 400 400)'), 0.2)))

And I checked the table of this line, it wasn't being splited.

Where did I make mistakes? I am new to use ST_split, please give me any suggestion, thanks.

I am using QGIS 2.2, PostgreSQL 9.2 and PostGIS 2.0.4 under Windows 8.1 x64.


Update#1

@simplexio, I used your code,

select ST_Line_SubString(ST_GeomFromText('LINESTRING(0 0, 247907.60 2746404.62, 200 200, 400 400)'), 0 , 0.200 ) as geom into split_line ;

and got a new table named split_line,

enter image description here

But as I used the following code you posted,

SELECT ST_Line_SubString(line, 0, ST_Line_Locate_Point(l.line, p.point)) as from_start_geom,
       ST_Line_SubString(l.line,ST_Line_Locate_Point(l.line, p.point), 1) to_end_geom, l.line_id, p.point_id FROM lines l , points p 
WHERE l.line_id = 1 , p.point_id = 1

I got a syntax error,

enter image description here

How to fix this and move on? And where should I assign the line I want to split as input of st_split?

3
  • syntax error fix, change , from where clause to AND Jun 13 '14 at 6:32
  • I change the comma in this line:"WHERE l.line_id = 1 , p.point_id = 1" to AND, but got another error:LINE 3: l.line_id, p.point_id FROM lines l , points p , relation "lines" doesn't exist.
    – Heinz
    Jun 13 '14 at 8:13
  • FROM clause line means line table, points means point table. WHERE l.line_id points to line table and lines_id point to id column. query should be modified to suit your need , it only shows basic idea how you can extract substring from specific line using specific point. Jun 13 '14 at 10:41
5

As has been suggested earlier and in the ST_Split documentation, you must first snap your line to the points and then call ST_Split.

If you are like me, an example is worth more than words:

DROP TABLE IF EXISTS split;
CREATE TABLE split AS(
SELECT
    (ST_Dump(ST_Split(ST_Snap(a.the_geom, b.the_geom, 0.00001),b.the_geom))).geom
FROM 
    line_table a
JOIN 
    point_table b 
ON 
    ST_DWithin(b.the_geom, a.wkb_geometry, "Your Tolerance to Search for Lines (e.g., a number)")
);
1
  • Why you are snapping line to point instead point to line? Wouldn't this move line to location of point?
    – zwornik
    Jun 20 at 18:04
0

i didn't get you code to work and far as i can see it should work, maybe there is some problem with ST_Split and tolerance. You can use following code :

select ST_Line_SubString(ST_GeomFromText('LINESTRING(0 0, 247907.60 2746404.62, 200 200, 400 400)'), 0 , 0.200 ) as geom into split_line ;

Now you have table (split_line) which has one colum(geom) which has lines from 0 to 0.2 (max is 1). locating fraction is done using ST_Line_Locate_Point(line,geom) return float, st_Line_SubString(line, from,to) return always linestrings, so it's easier to handle those rather than geometrycollections which you need to "open" and extract only lines

 SELECT ST_Line_SubString(line, 0, ST_Line_Locate_Point(l.line, p.point)) as from_start_geom,
    ST_Line_SubString(l.line,ST_Line_Locate_Point(l.line, p.point), 1) to_end_geom,
 l.line_id, p.point_id FROM lines l , points p 
WHERE l.line_id = 1 AND p.point_id = 1 

see: http://trac.osgeo.org/postgis/ticket/2192 it seems that when using st_split you need to use st_snap to snap point on to line. if possible i would prefer linear referencing methods because they are more mature code.

0
0

Another method to split lines:

create table in_edges_split as
select a.id, (st_dump(st_split(st_segmentize(a.geom,1),st_union(b.geom)))).geom as geom 
from in_edges a, in_points b where st_intersects(a.geom,b.geom) group by a.id;

Or if you need to snap your points to the lines first:

create table in_edges_split as
select a.id, (st_dump(st_split(st_segmentize(a.geom,1),st_union(b.geom)))).geom as geom 
from in_edges a, 
(select st_snap(d.geom,c.geom,0.00000001) as geom from in_edges c, in_points d) b 
group by a.id;

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.