# Calculating distance between a point and the nearest line, PostGIS

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)
``````

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:

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

• What does <-> mean?
– BERA
Jun 22, 2021 at 18:02
• @BERA ha! Tell you what; if you promise by the god of cascading knowledge that you won't tell anyone, you may check out the super secret links in the answer. Jun 22, 2021 at 18:36

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;