|
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 [email protected] http://lists.osgeo.org/mailman/listinfo/qgis-user This mail was received via Mail-SeCure System.
|
_______________________________________________ Qgis-user mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/qgis-user
