CODEWITHSUNDEEP
postgresqlpostgis
user1298426
user1298426

Reputation: 3717

Update table column by comparing geometry columns from other table

I have 2 tables. 1. island 2. region

enter image description here

enter image description here

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.

Upvotes: 1

Views: 127

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246523

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.

Upvotes: 1

Related Questions