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

2012-03-28 Thread Fredrik.Floren 
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?

2012-03-28 Thread Micha Silver 

  
  
On 28/03/2012 09:40, 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: Florn, 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

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

2012-03-28 Thread Fredrik.Floren 
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

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

2012-03-27 Thread Micha Silver 

  
  
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.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

This mail was received via Mail-SeCure System.






  

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

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

2012-03-26 Thread Dr_Strangelove 
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.org
http://lists.osgeo.org/mailman/listinfo/qgis-user