I have a few linestrings that together form a long line. The start- and endpoints of the linestrings are equal with their direct neighbour, but when tested the ST_Distance between any 2 lines is like 1.0×10^-11.
This seems to be the known issue with encoding floats. They are the same but not the same.
However when I update 2 neighbouring lines with their existing Astext coordinates they are encoding by the same algorithm and their distance is 0. But then they have a small distance with their outside neighbours (that are not updated this way).
What I want to do is snap a line left and right with its neighbours so the ST_Distance with both neighbours is 0. I think ST_Snap only moves a geometry to another geometry. That is not what I want. I only want the start- and endpoint of a line to snap to the neighbour. The rest of the line should stay where it is.
How do I do that?
With the given answer I fixed both ends of the conflicting line like this:
update network set the_geom = ST_RemovePoint(the_geom, (select st_npoints(the_geom)-1 from network where gid=6909)) where gid=6909;
update network set the_geom = ST_AddPoint(the_geom, (select st_endpoint(the_geom) from network where gid=6123), (select st_npoints(the_geom) from network where gid=6909)) where gid=6909;
update network set the_geom = ST_RemovePoint(the_geom, 0) where gid=6055;
update network set the_geom = ST_AddPoint(the_geom, (select st_startpoint(the_geom) from network where gid=6909), 0) where gid=6055;
Best Answer
You can use ST_RemovePoint(line, index) and ST_AddPoint(line, point, index). First, get first and last point with ST_pointN(line, index) and ST_NumPoints(line), then use ST_Snap to those points. Then use returned points to replace the old ones.
These functions should be in Postgis < 2.0 ST_ReplacePoint hould be in Postgis 2.0