<html style="direction: ltr;">
<head>
<meta content="text/html; charset=windows-1255"
http-equiv="Content-Type">
<style>body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;"
bidimailui-detected-decoding-type="preferred-charset"
bgcolor="#FFFFFF" text="#660000">
On 28/03/2012 09:40, <a class="moz-txt-link-abbreviated" href="mailto:Fredrik.Floren@teliasonera.com">Fredrik.Floren@teliasonera.com</a> wrote:
<blockquote
cite="mid:233C7A113EC57C4596925025DE7261091C7BEE239D@EXCCR04TSBRZ2.tcad.telia.se"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1255">
<meta name="Generator" content="Microsoft Word 14 (filtered
medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";
color:black;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";
color:black;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";
color:black;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:"Consolas","serif";
color:black;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size: 11pt; font-family:
"Calibri","sans-serif"; color: rgb(31,
73, 125);">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 </span></p>
</div>
</blockquote>
<p><br>
</p>
<p>Cheers,</p>
<p>Well it's certainly possible to handle the dissolving of the
polygons in spatialite also, using the function GUnion(). You
would create a new table, make it a spatial table, and insert rows
(polygon features) for the regions as the union of the provinces,
with the GROUP BY COD_REG as before. </p>
<p>Regards, Micha </p>
<p><br>
</p>
<blockquote
cite="mid:233C7A113EC57C4596925025DE7261091C7BEE239D@EXCCR04TSBRZ2.tcad.telia.se"
type="cite">
<div class="WordSection1">
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Anyways,
thank you!<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Fredrik<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div style="border:none;border-top:solid #B5C4DF
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext">From:</span></b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext">
Micha Silver [<a class="moz-txt-link-freetext" href="mailto:micha@arava.co.il">mailto:micha@arava.co.il</a>] <br>
<b>Sent:</b> den 27 mars 2012 8:10<br>
<b>To:</b> Florén, Fredrik C.<br>
<b>Cc:</b> <a class="moz-txt-link-abbreviated" href="mailto:qgis-user@lists.osgeo.org">qgis-user@lists.osgeo.org</a><br>
<b>Subject:</b> Re: [Qgis-user] Dissolve and sum of
merged attributes?<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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 moz-do-not-send="true"
href="http://www3.istat.it/ambiente/cartografia/province2001.zip">http://www3.istat.it/ambiente/cartografia/province2001.zip</a><br>
[2] <a moz-do-not-send="true"
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: <o:p></o:p></p>
<pre>In a layer I have, e.g., a map of a number of areas of a country. Each area<o:p></o:p></pre>
<pre>has a row in the attribute table and a associated polygon. Each area also<o:p></o:p></pre>
<pre>has a number of fields with numerical values. Is there a way to merge the<o:p></o:p></pre>
<pre>areas based on a field value and sum the respective values of another field?<o:p></o:p></pre>
<pre>Like dissolve but instead just replacing fields of the merged polygons, I<o:p></o:p></pre>
<pre>would like to sum them up. I guess this operation must be applicable in a<o:p></o:p></pre>
<pre>number of cases, maybe when summing up inhabitants in different areas but<o:p></o:p></pre>
<pre>with the same zip code. However, I can't find a way to do it.<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>--<o:p></o:p></pre>
<pre>View this message in context: <a moz-do-not-send="true" 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><o:p></o:p></pre>
<pre>Sent from the Quantum GIS - User mailing list archive at Nabble.com.<o:p></o:p></pre>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>Qgis-user mailing list<o:p></o:p></pre>
<pre><a moz-do-not-send="true" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a><o:p></o:p></pre>
<pre><a moz-do-not-send="true" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>This mail was received via Mail-SeCure System.<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre><o:p> </o:p></pre>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<br>
<br>
This mail was received via Mail-SeCure System.<br>
</blockquote>
<p><br>
</p>
<br>
<pre class="moz-signature" cols="72">--
Micha Silver
052-3665910
</pre>
</body>
</html>