4

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?

5

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
5

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

2
  • 1
    Ok but this work if a Closest Point already exists .Else you Update the geom to NULL! So this is not a good solution to replace a point if it find a candidate. Oct 12 '20 at 16:45
  • @GeoStoneMarten This fails only if ST_DWithin returns no matches. Without proximity limitation, the subquery will always return a nearest neighbor. Here, OP asks for proximity limitation. Handle this in a WHERE filter in the outer UPDATE.
    – geozelot
    Oct 12 '20 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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