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.

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？