SpatiaLite logo

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



Union 1

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:
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:
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

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

GNU logo 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.