I have a problem with over 10k lines from linetable that do not end/start in a point from pointtable.

I need to get these lines snapped so I tried to intersect them by using st_snap

update linetable c set geometry = st_snap(c.geometry, (select geometry from pointtable d where st_dwithin(c.geometry,d.geometry,0.1)  limit 1) , 0.1)
where  not (st_intersects(ST_StartPoint(c.geometry), (select st_union(geometry) from pointtable ) )
or st_intersects(ST_endPoint(c.geometry), (select st_union(geometry) from pointtable ) )   )

The problem is that it just moves the line to intersect the pointtable, instead of just moving the start/endpoint.

How do I make only the start/end point intersect, rather than adding a vertex or moving a random node?

1 Answer 1


Without a picture, it's a little hard for me to grasp the intricacies of your question, but if I understand it correctly, run the script:

UPDATE linetable c SET geometry=ST_Snap(c.geometry, (SELECT ST_Union(geometry) geometry FROM pointtable d WHERE ST_DWithin(c.geometry,d.geometry,0.1)), 0.1)

P.S. Remember that UPDATE queries are expensive, at least for me :-) and I try to use them rarely...and don't forget to delete duplicates...


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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