Note: these pages are no longer maintained

Never the less, much of the information is still relevant.
Beware, however, that some of the command syntax is from older versions, and thus may no longer work as expected.
Also: external links, from external sources, inside these pages may no longer function.

	recipe #19 Merging Local Councils into Counties and so on ...
2011 January 28

Previous Slide

Table of Contents

Next Slide

The problem

Local Councils, Counties and Regions follow a well defined order of hierarchy.
For administrative purposes Italy is subdivided into Regions; Regions are subdivided into Counties; and Counties are subdivided into Local Councils.
Following the appropriate Spatial SQL procedures you can start from Local Councils geometries and then generate corresponding Counties geometries. And so on ...

Important notice: the ISTAT 2001 census dataset isn't well suited for this task, because it is plagued by several topology inconsistencies.
We'll use instead the latest ISTAT 2010 dataset, presenting a much better quality and consistency.
http://www.istat.it/ambiente/cartografia/comuni2010.zip
http://www.istat.it/ambiente/cartografia/province2010.zip
http://www.istat.it/ambiente/cartografia/regioni2010.zip

You'll start creating a new DB; then using spatialite_gui you'll import the com2010_s shapefile.

Union 2

The next step is the one to load the prov2010_s dataset: yes, this one too actually is a shapefile.
But for your specific purposes you can ignore at all Counties Geometries.
(generating all them by yourself is the specific task assigned to you this time, isn't ?).
You can simply import the corresponding .DBF file, so to import any data but discarding and ignoring at all related Geometries.

Then you can import the Regions .DBF file from reg2010_s. exactly in the same way.

CREATE VIEW local_councils AS
SELECT c.cod_reg AS cod_reg,
  c.cod_pro AS cod_pro,
  c.cod_com AS cod_com,
  c.nome_com AS nome_com,
  p.nome_pro AS nome_pro,
  p.sigla AS sigla,
  r.nome_reg AS nome_reg,
  c.geometry AS geometry
FROM com2010_s AS c
JOIN prov2010_s AS p USING (cod_pro)
JOIN reg2010_s AS r USING(cod_reg);

SELECT * FROM local_councils;

cod_reg	cod_pro	cod_com	nome_com	nome_pro	sigla	nome_reg	geometry
1	1	1	Agliè	Torino	TO	PIEMONTE	BLOB sz=1117 GEOMETRY
1	1	2	Airasca	Torino	TO	PIEMONTE	BLOB sz=1149 GEOMETRY
1	1	3	Ala di Stura	Torino	TO	PIEMONTE	BLOB sz=1933 GEOMETRY
...	...	...	...	...	...	...	...

This will create the local_councils VIEW; this VIEW represents a nicely de-normalized flat table, so to make any subsequent activity absolutely painless.

CREATE TABLE counties AS
SELECT cod_pro, nome_pro, sigla, cod_reg, nome_reg,
ST_Union(geometry) AS geometry
FROM local_councils
GROUP BY cod_pro;

SELECT RecoverGeometryColumn('counties', 'geometry',
23032, 'MULTIPOLYGON', 'XY');

Now you'll create and populate the countries table:

the ST_Union() SQL Spatial function is used to aggregate / merge Geometries.
by defining a GROUP BY cod_pro clause then ST_Union() will work as an aggregate function, thus effectively building the Geometry representation corresponding to each single County.
please note well: you must absolutely call RecoverGeometryColumn() so to properly register the counties.geometry column into the geometry_columns metadata table.

SELECT * FROM counties;

cod_pro	nome_pro	sigla	cod_reg	nome_reg	geometry
1	Torino	TO	1	PIEMONTE	BLOB sz=36337 GEOMETRY
2	Vercelli	VC	1	PIEMONTE	BLOB sz=27357 GEOMETRY
3	Novara	NO	1	PIEMONTE	BLOB sz=15341 GEOMETRY
...	...	...	...	...	...

Just a quick check ...

Union 3

And then you are ready to display the counties map layer using QGIS.

CREATE TABLE regions (
cod_reg INTEGER NOT NULL PRIMARY KEY,
nome_reg TEXT NOT NULL);

SELECT AddGeometryColumn('regions', 'geometry',
23032, 'MULTIPOLYGON', 'XY');

INSERT INTO regions (cod_reg, nome_reg, geometry)
SELECT cod_reg, nome_reg, ST_Union(geometry)
FROM counties
GROUP BY cod_reg;

Now you'll create and populate the regions table:

as in the previous step you'll use ST_Union() and GROUP BY to aggregate regions Geometries.
please note well: in this example you have explicitly created the regions table, then using AddGeometryColumn() so to create the regions.geometry column.
And finally you have used INSERT INTO ... (...) SELECT ... in order to populate the table.
The procedure is different, but the final result is exactly the same one as in the previous example.

SELECT * FROM regions;

cod_reg	nome_reg	geometry
1	PIEMONTE	BLOB sz=75349 GEOMETRY
2	VALLE D'AOSTA/VALLÉE D'AOSTE	BLOB sz=18909 GEOMETRY
3	LOMBARDIA	BLOB sz=83084 GEOMETRY
...	...	...

Just a quick check ...

Union 4

And then you can display the regions map layer using QGIS.

CREATE TABLE italy AS
SELECT 'Italy' AS country,
ST_Union(geometry) AS geometry
FROM regions;

SELECT RecoverGeometryColumn('italy', 'geometry',
23032, 'MULTIPOLYGON', 'XY');

As a final step you can now create the italy table representing the whole Italian Republic international boundaries.

Union 5

Then you can display the italy map layer using QGIS … and that's all.

Previous Slide

Table of Contents

Next Slide

	Author: Alessandro Furieri a.furieri@lqt.it
	This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

	Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.