[GIS] Summing up values of neighbouring polygons using QGIS

polygonqgisqgis-processing

I hope that you can help me with the following problem: I have a vector (polygon) layer. I would like to add an attribute to the layer that – for every polygon – sums the values of a specific field of all its neighboring polygons.

To give a more concrete example: I have a polygon layer of districts containing information on population. Now, for every district I would like to know how many people live in all its neighboring districts.

As I have more than 300 districts I cannot do this by hand for each and every district.

Is there any way do this more efficiently in QGIS?

Best Answer

This kind of thing is best done with Spatialite and SQL.

First you will need to load you data into a Spatialite database which can be doing using the DBManager plugin that ships with QGIS. Click the Import Layer/File button.

With your data into a database you can then run the following query using the SQL button. You will just have to change the names of the columns and tables to suit your data.

SELECT COALESCE(SUM(a2.pop),0) as pop_neighbours, 
        a1.pop, 
        a1.name, 
        a1.id, 
        a1.geomm FROM areas a1
LEFT OUTER JOIN areas a2 ON NOT a1.id = a2.id 
                            AND intersects(a2.geomm, a1.geomm)
GROUP BY a1.id

Tell the query tool your unique id column (id) and geometry column (geomm), then just click load.

You should having something like this, once you label it of course

enter image description here

The Query Breakdown

We are joining the layer onto itself using:

LEFT OUTER JOIN areas a2 ON NOT a1.id = a2.id 
                            AND intersects(a2.geomm, a1.geomm)

but only where the geometries intersect and the ids are not the same, otherwise we end up with the same record twice for each polygon. We are also using a LEFT OUTER JOIN so that we include the records that don't join i.e have no neighbours.

In the select part:

SELECT COALESCE(SUM(a2.pop),0) as pop_neighbours, 
            a1.pop, 
            a1.name, 
            a1.id, 
            a1.geomm

we are using COALESCE in order to convert the NULLS (no neighbours) into a 0 otherwise they just stay NULL.

Then we just GROUP BY a1.id so that we get a single record for each polygon.

Related Question