Calculating distance between a point and the nearest line, PostGIS

Question

I have to add a column to my points table storing the distance (metres) between each point and the nearest line. Both are in the same projected coordinate system. How can I do it? I've tried this but doesn't work:

ALTER TABLE st09_2019_01 ADD coast_dist_M float8;
INSERT INTO st09_2019_01 (coast_dist_M)
VALUES
(SELECT ST_Distance(st09_2019_01.geom, coastline_ln.shape)
FROM st09_2019_01, coastline_ln
ORDER BY ST_Distance(st09_2019_01.geom, coastline_ln.shape)
LIMIT 1)

Answer 1

You can't INSERT into columns, only append rows to the table.

Run an UPDATE instead:

UPDATE st09_2019_01 AS pt
  SET  coast_dist_m = (
    SELECT ST_Distance(pt.geom, ln.shape)
    FROM   coastline_ln AS ln
    ORDER BY
           pt.geom <-> ln.shape
    LIMIT  1
  )
;

---

Related:

• How to UPDATE with LATERAL Nearest-Neighbour query?

Further information on (spatial) (K)NN and the <-> operator:

• Efficient way to find nearest feature between huge postgres tables
• Nearest Neighbor
• PostGIS: ST_ClosestPoint join error
• Optimizing my script; taking too long; distance between many objects PostGIS

Answer 2

Both tables need a id. The term "&& ST_Expand(p.geom, 10000))" sets the window size for the distance calculation (10km). You can change or remove it. This code will calculate de minimum distance between the each point in st09_2019_01 and the nearest point in a feature in coastline_ln layer.

update st09_2019_01 set coast_dist_M = tmp1.distance from (select tmp.pid, min(ST_distance(ST_closestpoint(tmp.r_geom, tmp.p_geom),tmp.p_geom)) as distance from (select p.id pid, r.id rid, p.geom p_geom, r.geom r_geom from st09_2019_01 as p, coastline_ln as r where ST_closestpoint(r.geom,p.geom) && ST_Expand(p.geom, 10000)) as tmp group by pid ) tmp1 where tmp1.pid=st09_2019_01.id;