How to re-assign attributes based on spatial relationship

Question

I want to re-assign the attributes of a table of regular polygons based on their spatial relantionship so that the polygons are clustered like this:

This essentially means to swap the attributes from one polygon to another based on some condition(s). This is the table behind the picture:

CREATE TABLE public.grid_attributes
(
  uid numeric NOT NULL,
  owner character varying(50),
  priority boolean,
  geom geometry(MultiPolygon,32630)
  )

The order/direction by which this clustering happens should (preferably) start from the polygons which are designated as priority of which there is one for each category I wish to use to cluster. That would be the ownerattribute.

My question (assuming I formulated this in an understandable way) is if SQL/PostGIS is right tool for this problem, and if it is how should I approach it/any hints?

Answer 1

I've done something similar to this hundreds of times. You didn't provide enough information to provide more than a stick-figure solution, but one way to do it would look like this:

UPDATE mytable t
   SET colval = vt.colval
FROM (
   SELECT t.keycol,j.colval
   FROM   mytable t
   JOIN   othertab j ON j.geomcol && t.geomcol AND 
                        ST_Within(ST_PointOnSurface(t.geomcol),j.geomcol)
) vt
WHERE t.keycol = vt.keycol

Make sure your "colval" column is integer, not numeric (or you may have equivalence difficulties), and indexed (to avoid a really ugly N-squared performance hit).