[Qgis-user] Dissolve and sum of merged attributes?

[Fredrik.Floren at teliasonera.com]

Micha, thank you for your very comprehensive reply. As I understand it Spatialite is only concerned with the attribute table and not the associated polygons. Hence, the need to dissolve in Qgis with fTools first. This is certainly a solution that works, but it does appear as a bit roundabout, at least to me. The operation would fit so nicely in Dissolve. So the question is whether I should spend time learning python and maybe create a custom Dissolve, or use the time to learn some SQL (which I've never used before). Although I can certainly reuse your code, I am generally reluctant to use code for this kind of thing without fully understanding it. The latter is probably quicker while the former is probably a better long term investment.

Anyways, thank you!

Fredrik

From: Micha Silver [mailto:micha at arava.co.il]
Sent: den 27 mars 2012 8:10
To: Florén, Fredrik C.
Cc: qgis-user at lists.osgeo.org
Subject: Re: [Qgis-user] Dissolve and sum of merged attributes?

Whenever I encounter an aggregation problem like this, my inclination is to move to the realm of the database. In this case, you can take advantage of Spatialite with its full SQL support to get what you want.

In order to try out the process, I downloaded a shapefile of provinces in Italy [1] (recommended as part of the demo data in the Spatialite Cookbook tutorial [2] )
This shapefile covers all the 100 or so provinces in Italy, and each province has a "COD_REG" attribute indicating which region it is located in. The attribute table also has a column POP2001 with population data for each province.
The mission is to dissolve the province boundaries, using the COD_REG column into the 20 Italian regions, then aggregate the populations of the provinces into a total for each region. First, we can use the QGIS "Vector->Geometry Processing->Dissolve" function to make a new shapefile of the 20 regions. It will contain a column of POP2001, but the values will be bogus-each row will hold the first POP2001 value from the provinces attrib table, which of course is not the totals.

Now we pull both of these shapefiles into spatialite. THis can be done either from the spatialite CLI using the .loadshp built in command, or using the Load Shapefile button in the spatialite-gui, or with the QGIS plugin Qspatialite.
.loadshp prov_2001_s provinces utf8 32632
.loadshp regions regions utf8 32632

(The shapefiles are in UTM zone 32, EPSG code 32632)

We will now have two spatial tables, provinces and regions. Now aggregating the total populations of the regions from the provinces data is a simple SQL query:
SELECT
    COD_REG AS Region_code, sum(POP2001) AS Population
FROM provinces
GROUP BY COD_REG;
(The GROUP BY is essential in this query)

To upload these total populations to the regions table is relatively straightforward when we use an interim temporary table with the same select as above:
CREATE TEMP TABLE t AS
SELECT
    COD_REG AS Region_code, sum(POP2001) AS Population
FROM provinces
GROUP BY COD_REG;

Now we run an UPDATE on the regions table to put in the correct summaries of POP2001 for each region from this TEMP table.
UPDATE regions
    SET POP2001=(SELECT Population FROM t WHERE t.Region_code=regions.COD_REG)
WHERE EXISTS
    (SELECT * from t WHERE t.Region_code=regions.COD_REG);

In all SQL implementations, an UPDATE statement which gets values from a sub-expression must return a single row. In order to update multiple rows at once, the WHERE EXISTS statement allows the UPDATE to loop thru all the returned values.

Now running SELECT * FROM regions; should return correct popuation sums for each region.  We can continue to use this spatialite table as a spatial layer from within QGIS, or export it to shapefile if necessary.

HTH,
Micha

[1] http://www3.istat.it/ambiente/cartografia/province2001.zip
[2] http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/start.html

On 03/26/2012 08:44 AM, Dr_Strangelove wrote:

In a layer I have, e.g., a map of a number of areas of a country. Each area

has a row in the attribute table and a associated polygon. Each area also

has a number of fields with numerical values. Is there a way to merge the

areas based on a field value and sum the respective values of another field?

Like dissolve but instead just replacing fields of the merged polygons, I

would like to sum them up. I guess this operation must be applicable in a

number of cases, maybe when summing up inhabitants in different areas but

with the same zip code. However, I can't find a way to do it.



--

View this message in context: http://osgeo-org.1560.n6.nabble.com/Dissolve-and-sum-of-merged-attributes-tp4655757p4655757.html

Sent from the Quantum GIS - User mailing list archive at Nabble.com.

_______________________________________________

Qgis-user mailing list

Qgis-user at lists.osgeo.org<mailto:Qgis-user at lists.osgeo.org>

http://lists.osgeo.org/mailman/listinfo/qgis-user



This mail was received via Mail-SeCure System.





-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20120328/5e1f5af8/attachment.html>