RE: [Qgis-user] Dissolve and sum of merged attributes?
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:mi...@arava.co.il] Sent: den 27 mars 2012 8:10 To: Florén, Fredrik C. Cc: qgis-user@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@lists.osgeo.orgmailto:Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user This mail was received via Mail-SeCure System.
RE: [Qgis-user] Dissolve and sum of merged attributes?
That's great. I much prefer doing all steps in one tool as it simplifies automation. I'll have get started with that tutorial you linked to then... BR, Fredrik From: Micha Silver [mailto:mi...@arava.co.il] Sent: den 28 mars 2012 10:17 To: Florén, Fredrik C. Cc: qgis-user@lists.osgeo.org Subject: Re: [Qgis-user] Dissolve and sum of merged attributes? On 28/03/2012 09:40, fredrik.flo...@teliasonera.commailto:fredrik.flo...@teliasonera.com wrote: 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 Cheers, 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. Regards, Micha 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:mi...@arava.co.il] Sent: den 27 mars 2012 8:10 To: Florén, Fredrik C. Cc: qgis-user@lists.osgeo.orgmailto:qgis-user@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
