5

I am trying to calculate shortest distance from each point to polygon where idfield match and add the distance to a field in the point table:

enter image description here

For the two "A" points the distance is the same, which is incorrect. I understand why but not how to fix it. If I had only one A point, it would work.

update public.tbl2
SET dist = sub.dist
from (

select  tbl2.idfield,
        st_shortestline(tbl1.geom, tbl2.geom) geom, 
        round(st_length(st_shortestline(tbl1.geom, tbl2.geom))::numeric, 0) dist
from public.tbl1 , public.tbl2
where tbl1.idfield = tbl2.idfield
) sub
where sub.idfield = tbl2.idfield
PolyGeo
  • 65,136
  • 29
  • 109
  • 338
BERA
  • 72,339
  • 13
  • 72
  • 161
  • 2
    Remember that an UPDATE is effectively a per-row operation; you can refer to the updated row in the whole statement, and filter accordingly. Use this https://gis.stackexchange.com/questions/278357/how-to-update-with-lateral-nearest-neighbour-query/278362#278362 for performance, with an additional WHERE filter for idfield in the sub-query, and ST_Distance in the SELECT. – geozelot Mar 23 '21 at 08:39
  • 2
    ...and one more concerning UPDATE statements with direct relevance to your use case https://gis.stackexchange.com/questions/365070/update-points-geometry-while-snapping-them-to-nearest-line/365091#365091 – geozelot Mar 23 '21 at 09:09

1 Answers1

4

Try including the geometry in the outer where condition like:

update public.tbl2
SET dist = sub.dist
from (

select  tbl2.idfield, tbl2.geom t2geom,
        st_shortestline(tbl1.geom, tbl2.geom) geom, 
        round(st_length(st_shortestline(tbl1.geom, tbl2.geom))::numeric, 0) dist
from public.tbl1 , public.tbl2
where tbl1.idfield = tbl2.idfield
) sub
where sub.idfield = tbl2.idfield AND sub.t2geom = tbl2.geom
BERA
  • 72,339
  • 13
  • 72
  • 161
drlmbrd
  • 81
  • 3
  • 2
    Sorry for being pedantic, but while this does work, it is utterly inefficient. I urge everyone interested in this to get familiar with the concepts outlined in the links in my comments above (why yes, they are my own answers; but there are plenty others out there featuring the same principle), as well as the core structure of UDPATE statements. – geozelot Mar 23 '21 at 10:26