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
2
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
1
  • 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 at 10:26

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.