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:

enter image description here

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?

  • Well, you can always UPDATE <table> SET <column> = <some_value>, where <some_value> can be that of any other row in your table based on arbitrary complex rules...but I don´t really get yours. could you explain some more how to get from the left to the right picture? do owner and priority change and if, how? – geozelot Apr 19 '18 at 11:06

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
   SELECT t.keycol,j.colval
   FROM   mytable t
   JOIN   othertab j ON j.geomcol && t.geomcol AND 
) 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).

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.