Intersection with closest point using PostGIS?

Question

I'm trying to update the field of a point table with a value from a linestring one :

update object_elevation 
set contour_elevation = c.contour
from contours c
where st_intersects(st_ClosestPoint(c.geom,object_elevation.geom),c.geom)

So what I want to do going from this query is update the contour_elevation field from the table object_elevation with the value stored in the field contour of the layer contours.

So what the query is supposed to do is return the closespoint to the linestring and then get the value of the elevation from the line layer and update with it the point layer.

However the result returned is the same. All the features have the same value of the field after update and I know very well that shouldn't be like this.

What am I missing in this query?

Answer 1

So I assume you want the elevation of the closest line to each point assigned to that respective point?

Well, your query does create the closest point on every line for each point , finds that those intersect with the line they were created on (not exactly surprising...) and assigns some value.

Just to illustrate what happens in your example and maybe lighten up the somewhat tricky UPDATE command, try this query:

SELECT c.contour
FROM contours c,
     (SELECT *
      FROM object_elevation
      LIMIT 1) AS o
WHERE ST_Intersects(ST_ClosestPoint(c.geom, o.geom), c.geom)

This essentially equals what is assigned to contour_elevation for each row in your example, simulated with one random point. The result set consists of every elevation value in your contours table and is created once for every row in the object_elevation table via the SET block. I guess PostGIS picks each time the first (or last, not sure) item of that list and assigns it to your contour_elevation field.

What you want to do is to get only the one line that is closest to each point. This query filters by distance to get only the closest line and assigns it's value to the point:

UPDATE object_elevation e 
SET contour_elevation = (
    SELECT c.contour
    FROM contours c
    ORDER BY ST_Distance(e.geom, c.geom)
    LIMIT 1
)

EDIT:

Better yet, use the KNN operator <->:

UPDATE object_elevation e
SET contour_elevation = (
    SELECT c.contour
    FROM contours c
    ORDER BY e.geom <-> c.geom
    LIMIT 1
)

This should improve speed for large, indexed tables when used in ORDERED BY.

See if that get's you what you want.