Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.0x10^-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;
share|improve this question
you could try extract start and end points, snap them and replace old start and end points with data you get from st_snap – simplexio Mar 8 '13 at 8:38
There is ST_AddPoint(line, point, index), ST_RemovePoint(line, index) but you can use only ST_ReplacePoint (line,index,point) then you need ST_PointN(line, index) and ST_NumPoints(line). Add St_Snap twice and replace you old end points with new ones – simplexio Mar 8 '13 at 8:47
simplexia - I used St_RemovePoint and ST_AddPoint as a way to replace a vertex (as I couldn't find the ST_ReplacePoint you mentioned). It worked. If you upgrade your comment to an answer I will accept it as the answer I was looking for. – mrg Mar 8 '13 at 14:24
Thanks you. Could you add example SQL to asnwer ? – simplexio Mar 11 '13 at 7:33
A weird thing is that ST_RemovePoint and ST_AddPoint are 0 indexed but ST_PointN is 1 indexed. – mrg Mar 11 '13 at 7:55
up vote 4 down vote accepted

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.