PostGIS 2.0 ST_Split rounding errors when splitting line with point

Question

My apologies for the bad title; I couldn't think of anything more descriptive.

I have been testing ST_Split with a dataset, and found that even with points that are supposed to be exactly on a line (found by ST_Line_Interpolate_Point) often the line doesn't get split.

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

Result:

GEOMETRYCOLLECTION(LINESTRING(0 0,80 80),LINESTRING(80 80,100 100,200 200,400 400))

Which is as expected. Now I introduce some decimals:

select ST_AsText(ST_Split(ST_GeomFromText('LINESTRING(0 0, 100.123456789 100.987654321, 200 200, 400 400)'), ST_Line_Interpolate_Point(ST_GeomFromText('LINESTRING(0 0, 100.123456789 100.987654321, 200 200, 400 400)'), 0.2)))

Result:

GEOMETRYCOLLECTION(LINESTRING(0 0,100.123456789 100.987654321,200 200,400 400))

Stripping some decimals:

select ST_AsText(ST_Split(ST_GeomFromText('LINESTRING(0 0, 100.123456 100.987654, 200 200, 400 400)'), ST_Line_Interpolate_Point(ST_GeomFromText('LINESTRING(0 0, 100.123456 100.987654, 200 200, 400 400)'), 0.2)))

Result:

GEOMETRYCOLLECTION(LINESTRING(0 0,79.6558670241487 80.3434026298966),LINESTRING(79.6558670241487 80.3434026298966,100.123456 100.987654,200 200,400 400))

Nearly all records in my dataset have a lot of decimals (which I think the problem is), meaning NONE of my lines get split, even though the point is supposed to be exactly on the line. Is there any way to work around this (quickly; I have to split 300k lines like this)? Preferably without modifying my dataset by using ST_SnapToGrid?

I'm Using POSTGIS="2.0.3 r11132" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012"

Answer 1

After some more searching, it seems that this simply isn't supported yet in PostGIS 2.0. See: http://trac.osgeo.org/postgis/ticket/2192

Heho. ST_Split does not work well when splitting a line by a point. Consider the following example where the line is not split even though the blade is supposed to be a point on the line:

SELECT ST_AsText((ST_Dump(ST_Split(lin.geom, ST_Line_Interpolate_Point(lin.geom, 0.5)))).geom) AS geom FROM (SELECT ST_GeomFromText('LINESTRING(604630.408 5792499.778,604623.849 5792500.886)') AS geom) AS lin

For the case of a line split by a point some kind of tolerance parameter would help. Until now I it is necessary first to ST_Snap your line to that interpolated point before splitting it by the point, which is not straightforward (thanks to strk for this hint). At least you could mention this hint in the documentation.