Snapping end points of line to point using PostGIS

Question

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?

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...