Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

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.

share|improve this answer
    
Nathan, many thanks for your answer and helpful explanations. It worked even for a total spatialite and sql beginner! –  Alex Dec 28 '12 at 15:27
    
+1 The "query breakdown" section is nicely done and very helpful. –  whuber Dec 28 '12 at 16:25
    
@Alex good stuff. Don't forget to tick the accept button. –  Nathan W Dec 29 '12 at 1:44
add comment

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.

enter image description here

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.