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:
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