Using ST_split to split a line feature using nearby points

Question

I have a line shapefile and a point shapefile, and

• the line has just one segment

and its attribute table,

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

and the close look,

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).

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,

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,

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

Answer 1

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)")
);

Answer 2

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;

Answer 3

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.