2

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

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:

2
  • What does <-> mean? – BERA Jun 22 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. – geozelot Jun 22 at 18:36
1

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;

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.