Summing up values of neighbouring polygons using QGIS?

Question

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?

Answer 1

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

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.

Answer 2

Another way to do this is in GRASS (using the GRASS toolbox or directly in GRASS). In the example below, the layer EA is a vector layer with countries and in the attribute table a column with the population per country. See this post for a more detailed explanation.

Step 1) Create new layer with attribute table linked to boundaries, with two columns with IDs of polygons bordering the boundary line on the left and right respectively

v.category EA out=EAc layer=2 type=boundary option=add
v.db.addtable EAc layer=2 col="left integer,right integer"
v.to.db EAc option=sides col=left,right layer=2 type=boundary

Step 2) Run a SQL to create a table which links the country IDs with the sum of the population of all neighboring countries:

db.execute sql="CREATE TABLE tmp AS
SELECT ID, sum(pop) as population FROM (
SELECT DISTINCT EAc_2.left as ID, EAc.pop as pop
FROM EAc_2
LEFT JOIN EAc ON EAc_2.right = EAc.cat
WHERE EAc_2.left > -1 AND EAc_2.right > -1
UNION
SELECT DISTINCT EAc_2.right as ID, EAc.pop as pop
FROM EAc_2
LEFT JOIN EAc ON EAc_2.left = EAc.cat
WHERE EAc_2.left > -1 AND EAc_2.right > -1
) GROUP BY ID"

Step 3) Join the new table tmp with the original attribute table.

v.db.join map=EA@ConsStat layer=1 column=cat otable=tmp ocolumn=ID

The attribute table of your vector layer should now have an extra column with summed population of all neighboring countries.

Answer 3

Great answer by @Nathan. I tried doing this using pyqgis and shapely. Check out this post to download the scirpt and run it in QGIS. An advantage of this method would be that you get the results as part of the attribute table.