[GIS] Using ST_split to split a line feature using nearby points

linepostgispostgresqlqgissplitting

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？

Best Answer

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