Update table column by comparing geometry columns from other table

Question

I have 2 tables. 1. island 2. region

I want to update island table region column based on the closet region to it using st_distance function. For e.g. for g1, st_distance(g1, geom1), st_distance(g1, geom2)... st_distance(g1, geom4) and update the region column for g1 geometry with the closet distance.

Answer 1

That could work like this:

UPDATE island
SET region = (SELECT regions.geom <-> island.geom
              FROM regions
              ORDER BY regions.geom <-> island.geom
              LIMIT 1);

This can use a GiST index on regions(geom), but it will still take a while if island is large.