I'm looking to be able to update a value by its nearest polygon.

The below image shows polygons in green where the column roadclass = 'A' and shows polygons where column roadclass IS NULL in brown.

I'm manually selecting and updating these currently so would like to automate it if possible.

enter image description here

enter image description here

I'm keen to learn but I've not used python before in QGIS so please consider this in any response. Also if useful the layer is stored within a PostGIS database and I'm using QGIS 3.6.

  • 1
    what do you want to update? Can you share the screen shot of the tables? – Ayaz49 Jul 16 at 9:13
  • I want to be able to update the null values based on the value from the nearest polygon. So as an example above I'd be wanting all the null values to be replaced with 'A' – Chris Jenner Jul 16 at 9:14
  • yes, but can you share the table structure? – Ayaz49 Jul 16 at 9:16
  • @ayaz49 added above – Chris Jenner Jul 16 at 9:20

Hope this answers your question,

update your_table
set roadclass  =sub.nearest_value
      p.idd as updating_id,b.id2 as nearest_id, b.roadclass  as 
  FROM  (
   --selecting id and geom of null values 
   SELECT gid AS idd, geom
    FROM your_table where roadclass  is null
  ) p
  --finding nearest polygon and its of the selected ids.
    ,LATERAL (
  --the nearest columns you want to update
  SELECT b.gid as id2,b.roadclass  
  FROM   your_table b
  where b.gid<>p.idd and b.roadclass is not null
  ORDER BY  b.geom <->(p.geom )
  LIMIT  1
  ) b
  ORDER  BY idd,id2
   where your_table.gid=sub.updating_id;
  • Just to be clear sorry - you suggest running this in PostgreSQL? – Chris Jenner Jul 16 at 9:54
  • 1
    Yes run this query in PostgreSQL. – Ayaz49 Jul 16 at 9:57
  • Got the following but with no change to the null values. "UPDATE 2177 Query returned successfully in 2 secs 159 msec." – Chris Jenner Jul 16 at 9:58
  • 1
    Yes because all null values are updated from its nearest polygon's value. – Ayaz49 Jul 16 at 9:59
  • 1
    Welcome ,you also run this query on a new table, may be the previous query might have updated the records not as expected? – Ayaz49 Jul 16 at 10:23

You can also use ST_ClusterDBSCAN to cluster adjacent polygons together and then assign class by most frequent class in that cluster group:

--Add a temp column holding cluster id
alter table sometable add column cid int;
update sometable
set cid = sub.clusterid
from (SELECT *, ST_ClusterDBSCAN(geom, eps := 0, minpoints := 1) OVER () clusterid
    FROM sometable) sub
where sometable.id = sub.id;

--Update column "value" with the most common value in each cluster
update sometable
set value = sub.mode
from (select mode() within group (order by value), cid
from sometable
group by cid) sub
where sometable.cid = sub.cid;

alter table sometable drop column cid;

enter image description here

  • Hi @BERA thanks for this - could you evolve this further to only do this on clusters of polygons with the same unique identifier? (.e.g road names) I've got a large dataset and each road is could have a seperate value. – Chris Jenner Jul 17 at 8:26

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.