<html style="direction: ltr;">
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<style>body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;"
bidimailui-detected-decoding-type="latin-charset" bgcolor="#FFFFFF"
text="#000000">
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. <br>
<br>
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] )<br>
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. <br>
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. <br>
<br>
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. <br>
.loadshp prov_2001_s provinces utf8 32632<br>
.loadshp regions regions utf8 32632<br>
<br>
(The shapefiles are in UTM zone 32, EPSG code 32632)<br>
<br>
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:<br>
SELECT <br>
COD_REG AS Region_code, sum(POP2001) AS Population <br>
FROM provinces <br>
GROUP BY COD_REG;<br>
(The GROUP BY is essential in this query)<br>
<br>
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:<br>
CREATE TEMP TABLE t AS <br>
SELECT<br>
COD_REG AS Region_code, sum(POP2001) AS Population<br>
FROM provinces<br>
GROUP BY COD_REG;<br>
<br>
Now we run an UPDATE on the regions table to put in the correct
summaries of POP2001 for each region from this TEMP table. <br>
UPDATE regions<br>
SET POP2001=(SELECT Population FROM t WHERE
t.Region_code=regions.COD_REG)<br>
WHERE EXISTS<br>
(SELECT * from t WHERE t.Region_code=regions.COD_REG);<br>
<br>
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.<br>
<br>
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.<br>
<br>
HTH,<br>
Micha<br>
<br>
[1] <a class="moz-txt-link-freetext" href="http://www3.istat.it/ambiente/cartografia/province2001.zip">http://www3.istat.it/ambiente/cartografia/province2001.zip</a><br>
[2]
<a class="moz-txt-link-freetext" href="http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/start.html">http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/start.html</a><br>
<br>
On 03/26/2012 08:44 AM, Dr_Strangelove wrote:
<blockquote cite="mid:1332744243560-4655757.post@n6.nabble.com"
type="cite">
<pre wrap="">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: <a class="moz-txt-link-freetext" href="http://osgeo-org.1560.n6.nabble.com/Dissolve-and-sum-of-merged-attributes-tp4655757p4655757.html">http://osgeo-org.1560.n6.nabble.com/Dissolve-and-sum-of-merged-attributes-tp4655757p4655757.html</a>
Sent from the Quantum GIS - User mailing list archive at Nabble.com.
_______________________________________________
Qgis-user mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a>
This mail was received via Mail-SeCure System.
</pre>
</blockquote>
<p><br>
</p>
</body>
</html>