Update points geometry while snapping them to nearest line

Question

I have two tables in the PostGIS database - points and lines. And I want to update points geometry to snap them to line if they lay within a certain distance.

By now I managed to create a new table with snapped points which has only geometries. This works:

CREATE TABLE new_table AS
SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id;

But what I really need is to update the existing table. So I try:

UPDATE points
SET geom =  
(SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id);

And it did not work. How do I put these two pieces together?

Answer 1

An UPDATE requires you to explicitly define the one, single return value to be put in place of each specified column value. To get only those, one usually wants to use a reference to the current row...and that's no problem, since the current row is available as record values to all statements in the UPDATE query.

Your sub-query, however, fetches the full table join (all records of each possible pair in the JOIN) and picks one at random (or likely the first row in the set).

Using the points.id as in @ziggys answer is indeed one way to refer to the updated row; however, in cases where more than one line is in the given proximity, there will be as many candidates in the result with a matching id.

Instead, better make sure you will always snap the point to the closest line in the given proximity (if proximity is actually needed at all?); running

UPDATE points
  SET  geom = (
    SELECT ST_ClosestPoint(lines.geom, points.geom)
    FROM lines
    -- WHERE ST_DWithin(points.geom, lines.geom, 5)
    ORDER BY lines.geom <-> points.geom
    LIMIT 1
  )
;

will order the lines.geom in proximity by distance to the updated points.geom, and limits the set to the closest one. This is highly efficient only with a spatial index in place on lines.geom!

---

This is commonly referred to as (spatial) (K) Nearest Neighbor search; more on the concept can be found e.g. here

• How to UPDATE with LATERAL Nearest-Neighbour query?
• Efficient way to find nearest feature between huge postgres tables

Answer 2

just put the select qry in a cte and add the id column and join to the update clause

with a as(SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point,points.id id
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id)
UPDATE points
SET geom =  snapped_point from a where points.id=a.id