D 2015-11-19T11:58:28.543
L topo-advanced
P ae49231106e78c22db8773d7de8a95830b5cf78c
U sandro
W 33223
<table cellspacing="12" width="100%">
<tr><td colspan="2">
<table width="100%" bgcolor="#f0f0f8">
<tr><td align="center">
<h1>Topology-Geometry: an advanced tutorial</h1>
</td></tr></table>
<table width="100%"><tr>
<td width="33%" align="left"><a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=topo-intermediate">previous page</a></td>
<td align="center"><a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=misc-docs">back to index</a></td>
<td width="33%" align="right"><a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=topo-advanced">next page</a></td>
</tr></table>
<h3>TopoLayers and TopoFeatures</h3>
In the previous tutorial we've examined a first couple of SQL functions intended to export a GeoTable out from an existing Topology: <b>TopoGeo_ToGeoTable()</b> and <b>TopoGeo_ToGeoTableGeneralize()</b>.<br>
Anyway SpatiaLite supports an more sophisticated (and complex) approach based on <b>TopoLayers</b> and <b>TopoFeatures</b> specifically intended to support a very flexible mechanism for exporting a full set of GeoTable(s) directly deriving from a Topology.
<h4>a conceptual reference framework</h4>
<ul>
<li>an ISO Topology is just intended to support a consistent set of <b>Nodes</b>, <b>Edges</b> and <b>Faces</b> primitives fully covering the plane.
Topology primitives correspond to pure geometries, and can never be directly associated to a corresponding set of information attributes.
Both Node and Edge primitives directly correspond to a <u><i>real geometry</i></u>, but Face primitives simply correspond to a <u><i>virtual geometry</i></u> thus necessarily requiring to be dynamically reconstructed every time that they are referenced (and this could easily become a real bottleneck).</li>
<li>on the other hand a GeoTable (aka <u><i>layer</i></u>, as in the common GIS parlance) is always based on many distinct features, and for each feature a Geometry and an associated set of information attributes are expected to be declared.</li>
<li>there is further difference to be taken in proper account: Topology primitives necessarily are elementary. Feature Geometries can usually represent some complex type: MULTIPOINT, MULTILINESTRING, MULTIPOLYGON and, may be, GEOMETRYCOLLECTION. So it should be absolutely clear that a Feature Geometry does not necessarily corresponds to a single Topology primitive ; a Feature Geometry could easily be derived from aggregating many different Topology primitives.</li>
<li>that's not all: many different GeoTables could eventually be derived from a single Topology; and the same Topology primitive could play a very different role in everyone of these GeoTables.<br>
Just a basically simple example: imagine a Topology corresponding to some land registry map. We'll surely have many potential layers: buildings, agricultural land, populated places, administrative boundaries, roads, ponds, rivers, fences and so on.<br>
In such a context a fence could easily correspond to a Feature within the "<i><b>fences</b></i>" layer, could probably delimit two adjacent agricultural areas and could eventually be part of some administrative boundary.</li>
<li><b>TopoLayers</b> and <b>TopoFeatures</b> represent a complex and flexible structure intended to establish permanent explicit correspondences between Topology Primitives and GeoTable Features.
<ul>
<li>each <b>TopoLayer</b> is uniquely identified by its <b>name</b> and directly corresponds to a single GeoTable to be exported from the Topology<br>
An arbitrary number of TopoLayers can be created on the same Topology, and each one of them will act as a separate container.</li>
<li>each <b>TopoFeature</b> is uniquely identified by its <b>fid</b> (<i>feature-id</i>) and corresponds to a single Feature containing an individual set of information attributes.<br>
The Geometry for each TopoFeature is always indirectly defined by specifying a list of Topology primitives individually referenced by their IDs.<br>
Such a geometry obviously is a <u><i>virtual</i></u> one, and will be actually materialized (by aggregating all referenced Topology primitives) only when exporting the destination GeoTable.</li>
</ul></li>
<li>The TopoLayers / TopoFeatures structure is practically implemented as a set of several closely related DB-tables )we'll examine later their respective layouts in full detail):
<ul>
<li><b><<i>topo-prefix</i>>_topolayers</b>: this first table simply is a catalog of all TopoLayers supporting the same Topology.<br>
Each TopoLayer is identified by its <b>name</b> and by its <b>ID</b>; both identifiers are granted to be <i><u>unique</u></i>.</li>
<li><b><<i>topo-prefix</i>>_topofeatures</b>: this second table is intended to permanently store all cross-references existing between Topology primitives and TopoFeatures.</li>
<li><b><<i>topo-prefix</i>>_topofeatures_<<i>topolayer-id</i>></b>: for each single TopoLayer a separate table is required. The actual correspondence is established via the <b>TopoLayer-ID</b> suffix.<br>
All these tables are intended to permanently store the information attributes for each TopoFeature in the same TopoLayer.
Different TopoLayers will obviously support a different set of information attributes thus requiring an individual table layout.<br>
Anyway all these tables are always expected to declare a <b>Primary Key</b> of the <b>INTEGER</b> type named <b>fid</b> and intended to be an unique identifier for each TopoFeature.</li>
</ul></li>
<li>Several SQL functions are specifically intended to support processing operations based on TopoLayers and TopoFeatures; we'll examine later all them in full detail.</li>
</ul><br><br>
<hr>
<h2>a quick practical exercise</h2>
Prerequisites:
<ol>
<li>download the sample DB-file from <a href="https://www.gaia-gis.it/gaia-sins/toposamples/tuscany-census-2011.7z">here</a><br>
It contains Census data (2011) kindly released by ISTAT under <b>CC-BY</b> license terms.<br>
The original datasets have been slightly rearranged in a more convenient form.
All geometries are in the <b>SRID 32632</b> (<i>WGS 84 / UTM zone 32N</i>):
<ul>
<li><b>census_2011</b>: all Census Areas (2011) covering Tuscany.<br>
<u>Note:</u> several Census Areas are completely uninhabited (lakes, marshlands, high mountains and alike).</li>
<li><b>ppl_2011</b>: Populated Places (defined as an aggregation of Census Areas).<br>
<u>Note</u>: not all Census Areas belong to a Populated Place: there are many self-standing dispersed rural areas.</li>
<li><b>com_2011</b>: Tuscany Municipalities / Local Councils 2011 (defined as an aggregations of Census Areas).</li>
<li><b>com_2014_15</b>: few new Municipalities created during years 2014/15 by merging pre-existing smaller Municipalities.</li>
<li><b>prov_2011</b>: Tuscany Provinces / Counties (defined as an aggregation of Municipalities).</li>
<li><b>reg_2011</b>: Tuscany Region (defined as an aggregation of Provinces).</li>
<li>just <b>census_2011</b> alone supports geometries; any other administrative level is simply defined by relational codes.</li>
</ul></li>
<li>uncompress the downloaded db-file (<b><i>tuscany-census-2011.sqlite</i></b>).</li>
<li>now you can finally start a SpatiaLite session using your preferred SpatiaLite front end tool:
<ul>
<li><u>Attention</u>: you must not directly connect to <b><i>tuscany-census-2011.sqlite</i></b>; you are expected instead to connect a new (empty) DB-file named <b>tuscany-topo-2011.sqlite</b></li>
</ul></li>
</ol>
<verbatim>
SELECT CreateTopology('census2011', 32632, 0, 0);
1
ATTACH DATABASE "./tuscany-census-2011.sqlite" AS istat;
SELECT TopoGeo_FromGeoTable('census2011', 'istat', 'census_2011', NULL, 0, 512, -1);
1
SELECT ST_ValidateTopoGeo('census2011');
NULL
SELECT * FROM TEMP.census2011_validate_topogeo;
SELECT TopoGeo_CreateTopoLayer('census2011', 'istat', 'census_2011', NULL, 'census_areas');
1
SELECT TopoGeo_InitTopoLayer('census2011', 'istat', 'pop_ppl_2011', 'ppl');
1
SELECT TopoGeo_InitTopoLayer('census2011', 'istat', 'pop_com_2011', 'com');
1
SELECT TopoGeo_InitTopoLayer('census2011', 'istat', 'pop_prov_2011', 'prov');
1
SELECT TopoGeo_InitTopoLayer('census2011', 'istat', 'pop_reg_2011', 'reg');
1
DETACH DATABASE istat;
</verbatim>
We'll start this practical tutorial by duly replicating the same steps we've already examined in the intermediate tutorial:
<ul>
<li>we'll create a new 2D Topology named <b>census2011</b> and located into <b>SRID 32632</b>.</li>
<li>then we'll <i>attach</i> the <b>tuscany-topo-2011.sqlite</b> external DB-file.</li>
<li>now we'll populate the <b>census2011</b> Topology by importing the <b>istat.census_2011</b> GeoTable, and then we'll immediately check if this Topology is fully valid.</li>
</ul><br>
Now we are finally ready to start defining all <b>TopoLayers</b> based on the <b>census2011</b> Topology; each single TopoLayer will precisely represent some administrative level, and all TopoLayers altogether will completely represent the whole Tuscan administrative hierarchy.
<ol>
<li>so we'll duly invoke <b>TopoGeo_CreateTopoLayer()</b> in order to completely define a first TopoLayer representing <b>Census Areas</b>.<br>
<u>Recall</u>: the <b>istat.census_2011</b> is the unique real GeoTable we have in our initial dataset, because it contains both information attributes and geometries; and each Census Area is expected to directly correspond to a single Topology Face. (<i>we'll examine later in more depth how TopoLayer / TopoFeature relationships have been actually defined</i>).</li>
<li>then we'll continue by defining several further TopoLayers respectively corresponding to <b>Populated Places</b>, <b>Municipalities</b>, <b>Provinces</b> and <b>Region</b> administrative levels.<br>
<u>Recall</u>: all these administrative levels in the initial dataset are simply defined by relational codes intended to aggregate lower level entities; in this case there are no explicit geometries at all. So are are not directly allowed to create a fully defined TopoLayer, and calling <b>TopoGeo_CreateTopolayer()</b> is just impossible because we lack any appropriate Geometry column.<br>
Anyway we can invoke <b>TopoGeo_InitTopoLayer()</b>; this will initialize an only partially defined TopoLayer, but we can easily complete in a second time any missing information so to get a properly working TopoLayer (<i>as we'll see later in full detail).</li>
<li>finally we'll <i>detach</i> the now useless external db-file.</li>
</ol>
<hr>
<h3>TopoLayers / TopoFeatures: layout of DB Tables and corresponding relationships</h3>
<table cellpadding="6" cellspacing="8">
<tr><td>
<b><<i>topo-prefix</i>>_topolayers</b>
<verbatim>
SELECT * FROM census2011_topolayers;
</verbatim>
The <b>TopoLayers</b> table contains a distinct row for every TopoLayer defined on the current Topology.<br><br>
Each TopoLayer is identified by an <b>id</b> and by a <b>name</b>; both are required to be unique values.
</td><td>
"<i>census2011_topolayers</i>"
<table cellspan="4" cellpadding="6" bgcolor="#f8f8f0" border="1">
<tr><td bgcolor="#f0f0f8"><b>topolayer_id</b></td>
<td bgcolor="#f0f0f8"><b>topolayer_name</b></td></tr>
<tr><td align="right">1</td><td>census_areas</td></tr>
<tr><td align="right">2</td><td>ppl</td></tr>
<tr><td align="right">3</td><td>com</td></tr>
<tr><td align="right">4</td><td>prov</td></tr>
<tr><td align="right">5</td><td>reg</td></tr>
</table>
</td></tr>
<tr><td>
<b><<i>topo-prefix</i>>_topofeatures_<<i>topolayer-id</i>></b>
<verbatim>
SELECT * FROM census2011_topofeatures_2;
SELECT * FROM census2011_topofeatures_4;
</verbatim>
The same Topology can contain more than a single <b>TopoFeatures</b> table, and each TopoFeatures table corresponds to a single TopoLayer.<br>
Every table can have a specific layout on its own (strictly reflecting the information attributes for each layer), anyway all them will have a <b>unique TopoFeature identifier</b> (<b><i>fid</i></b>) acting in the Primary Key role.<br>
The relationship between table names and TopoLayers is established by appending a numeric suffix to the common name that must exactly match the <b>topolayer_id</b> value declared in the <b>TopoLayers</b> main table.<br><br>
As is shown in this example the first table <b>census2011_topofeatures_2</b> (<i>topolayer_id</i>=<i>2</i>) contains all TopoFeatures associated to the TopoLayer <b>ppl</b> (<i>Populated Places</i>).<br><br>
The second table <b>census2011_topofeatures_4</b> (<i>topolayer_id</i>=<i>4</i>) contains all TopoFeatures associated to the TopoLayer <b>prov</b> (<i>Provinces</i>).
</td><td>
"<i>census2011_topofeatures_2</i>"
<table cellspan="4" cellpadding="6" bgcolor="#f8f8f0" border="1">
<tr><td bgcolor="#f0f0f8"><b>fid</b></td>
<td bgcolor="#f0f0f8"><b>cod_ppl</b></td>
<td bgcolor="#f0f0f8"><b>cod_com</b></td>
<td bgcolor="#f0f0f8"><b>name</b></td>
<td bgcolor="#f0f0f8"><b>pop_2011</b></td>
<td bgcolor="#f0f0f8"><b>m_2011</b></td>
<td bgcolor="#f0f0f8"><b>f_2011</b></td></tr>
<tr><td align="right">1</td><td align="right">205142705</td><td align="right">45001</td><td>Albiano Magra</td><td align="right">1907</td><td align="right">919</td><td align="right">988</td></tr>
<tr><td align="right">2</td><td align="right">205142706</td><td align="right">45001</td><td>Aulla</td><td align="right">4321</td><td align="right">2046</td><td align="right">2275</td></tr>
<tr><td align="right">3</td><td align="right">205142710</td><td align="right">45001</td><td>Caprigliola</td><td align="right">482</td><td align="right">226</td><td align="right">256</td></tr>
<tr><td colspan="7" align="center"><hr></td></tr>
<tr><td align="right">1018</td><td align="right">1410775415</td><td align="right">100007</td><td>Sasseta</td><td align="right">285</td><td align="right">137</td><td align="right">148</td></tr>
<tr><td align="right">1019</td><td align="right">1410785415</td><td align="right">100007</td><td>Terrigoli</td><td align="right">537</td><td align="right">264</td><td align="right">273</td></tr>
</table>
<br>
"<i>census2011_topofeatures_4</i>"
<table cellspan="4" cellpadding="6" bgcolor="#f8f8f0" border="1">
<tr><td bgcolor="#f0f0f8"><b>fid</b></td>
<td bgcolor="#f0f0f8"><b>cod_prov</b></td>
<td bgcolor="#f0f0f8"><b>cod_reg</b></td>
<td bgcolor="#f0f0f8"><b>name</b></td>
<td bgcolor="#f0f0f8"><b>abbrev</b></td>
<td bgcolor="#f0f0f8"><b>pop_2011</b></td>
<td bgcolor="#f0f0f8"><b>m_2011</b></td>
<td bgcolor="#f0f0f8"><b>f_2011</b></td></tr>
<tr><td align="right">1</td><td align="right">45</td><td align="right">9</td><td>Massa Carrara</td><td>MS</td><td align="right">199650</td><td align="right">95754</td><td align="right">103896</td></tr>
<tr><td align="right">2</td><td align="right">46</td><td align="right">9</td><td>Lucca</td><td>LU</td><td align="right">388327</td><td align="right">186183</td><td align="right">202144</td></tr>
<tr><td align="right">3</td><td align="right">47</td><td align="right">9</td><td>Pistoia</td><td>PT</td><td align="right">287866</td><td align="right">138054</td><td align="right">149812</td></tr>
<tr><td colspan="8" align="center"><hr></td></tr>
<tr><td align="right">9</td><td align="right">53</td><td align="right">9</td><td>Grosseto</td><td>GR</td><td align="right">220564</td><td align="right">105585</td><td align="right">114979</td></tr>
<tr><td align="right">10</td><td align="right">100</td><td align="right">9</td><td>Prato</td><td>PO</td><td align="right">245916</td><td align="right">119088</td><td align="right">126828</td></tr>
</table>
</td></tr>
<tr><td>
<b><<i>topo-prefix</i>>_topofeatures</b>
<verbatim>
SELECT * FROM census2011_topofeatures;
</verbatim>
The <b>TopoFeatures-geometries</b> table is intended to permanently store all relations between TopoFeatures and Topology primitives required in order to correctly build the expected output Geometry for each TopoFeature.
<ul>
<li><b>uid</b> is the Primary Key, and is simply intended to be an unique identifier for each row but doesn't intend to have any special meaning.</li>
<li><b>node_id</b>, <b>edge_id</b> and <b>face_id</b> are Foreign Keys directly referencing a Topology primitive; two of these values are always expected to be NULL, and only one is expected to effectively reference a Topology primitive depending on its type.</li>
<li><b>topolayer_id</b> and <b>fid</b> together are intended to establish a relational reference to some specific TopoFeature.</li>
</ul>
<br>
<u>Example #1</u>: all Topology primitives directly referenced by rows declaring <b>topolayer_id=2</b> and <b>fid=3</b> must be aggregated in order to build the output Geometry corresponding to the Populated Place of <b>Caprigliola</b>.<br>
<u>Example #2</u>: all primitives referenced by rows declaring <b>topolayer_id=4</b> and <b>fid=9</b> must be aggregated in order to build the output Geometry corresponding to the Province of <b>Grosseto</b>.
</td><td>
"<i>census2011_topofeatures</i>"
<table cellspan="4" cellpadding="6" bgcolor="#f8f8f0" border="1">
<tr><td bgcolor="#f0f0f8"><b>uid</b></td>
<td bgcolor="#f0f0f8"><b>node_id</b></td>
<td bgcolor="#f0f0f8"><b>edge_id</b></td>
<td bgcolor="#f0f0f8"><b>face_id</b></td>
<td bgcolor="#f0f0f8"><b>topolayer_id</b></td>
<td bgcolor="#f0f0f8"><b>fid</b></td></tr>
<tr><td align="right">1</td><td>NULL</td><td>NULL</td><td align="right">1</td><td align="right">1</td><td align="right">1</td></tr>
<tr><td align="right">2</td><td>NULL</td><td>NULL</td><td align="right">2</td><td align="right">1</td><td align="right">2</td></tr>
<tr><td align="right">3</td><td>NULL</td><td>NULL</td><td align="right">3</td><td align="right">1</td><td align="right">3</td></tr>
<tr><td colspan="6" align="center"><hr></td></tr>
<tr><td align="right">28870</td><td>NULL</td><td>NULL</td><td align="right">28864</td><td align="right">1</td><td align="right">28867</td></tr>
<tr><td align="right">28871</td><td>NULL</td><td>NULL</td><td align="right">28868</td><td align="right">1</td><td align="right">28868</td></tr>
</table>
</td></tr>
</table>
<table cellpadding="8" bgcolor="#d0ffb0">
<tr><td>
<h3>Final remarks</h3>
<ol>
<li>both <b>TopoGeo_CreateTopoLayer()</b> and <b>TopoGeo_InitTopoLayer()</b> will register the TopoLayer into the <b>TopoLayers</b> table, and will create and populate the corresponding <b>TopoFeatures</b> table by importing all information attributes for each Feature defined by the reference table (or reference view).</li>
<li>only <b>TopoGeo_CreateTopoLayer()</b> will automatically populate the <b>TopoFeatures-geometry</b> table, and while doing so, identify all relationships existing between the Geometries found into the reference table or view and the Topology primitives based on the values of the <b>TopoSeeds</b>.</li>
<li>as apposed to <b>TopoGeo_InitTopoLayer()</b>, which will never attempt to identify the relationships intercurring between output Geometries and Topology primitives. This task will always be deferred for the user to deal with later.<br>
You could, for example, manually select all Topology primitives corresponding to a single TopoFeature; or more probably you could perform this task in a second round by executing some appropriate SQL statement (<i>as we'll seen soon in the next example</i>).
</ol>
</td></tr>
</table><br>
<hr>
<h3>TopoGeo_ExportTopoLayer: exporting a full TopoLayer into a GeoTable</h3>
<table width="100%" cellspan="4" cellpadding="6" bgcolor="#f8f8f0" border="1">
<tr><td colspan="2" align="center"><b>exporting the Census Areas TopoLayer</b></td></tr>
<tr><td>
We'll start by exporting first the only completely defined TopoLayer we have at this point in the test DB-file, i.e. <b>census_areas</b>
<verbatim>
SELECT TopoGeo_ExportTopoLayer('census2011', 'census_areas', 'out_census_2011', 1);
</verbatim>
As you can easily check by yourself a new GeoTable named <b>out_census_2011</b> has been created by <b>TopoGeo_ExportTopoLayer()</b>:
<ul>
<li>this GeoTable contains all TopoFeatures defined into the TopoLayer.</li>
<li>each TopoFeature faithfully preserves its initial information attributes.</li>
<li>all Geometries are precisely built by aggregating the corresponding Topology primitives as specified by TopoLayer / TopoFeatures relationships.</li>
</ul><br><br>
The image examplifies a choropleth centered around the Island of Elba and based on the <b>out_census_2011</b> GeoTable: all Census Areas are displayed by adopting a colour directly corresponding to their population density.
</td><td>
<img src="https://www.gaia-gis.it/gaia-sins/topofigs/elba-choropleth.png" alt="elba-choropleth">
</td></tr>
</table><br>
<hr>
<h3>Building a full hierarchy of Administrative Levels by SQL statements</h3>
As we've already seen before we are now expected to complete someway the TopoFeatures definitions supporting the <b>Populated Places</b>, <b>Municipalities</b>, <b>Provinces</b> and <b>Region</b> TopoLayers we've previously created by calling <b>TopoGeo_InitTopoLayer()</b>.<br><br>
Happily enough all these administrative levels are simply based on direct aggregations of <b>Census Areas</b>, so we just have to execute few appropriate SQL statements.
<verbatim>
INSERT INTO census2011_topofeatures
SELECT NULL, c.node_id, c.edge_id, c.face_id, 2, a.fid
FROM census2011_topofeatures_2 AS a
JOIN census2011_topofeatures_1 AS b ON (b.cod_ppl = a.cod_ppl)
JOIN census2011_topofeatures AS c ON (c.topolayer_id = 1 AND c.fid = b.fid);
</verbatim>
<ul>
<li><b>census2011_topofeatures_1</b> is the TopoFeatures table corresponding to <b>Census Areas</b>.</li>
<li><b>census2011_topofeatures_2</b> is the corresponds to <b>Populated Places</b>; the clause <b>b.cod_ppl = a.cod_ppl</b> will relationally join each Populated Place to its underlaying Census Areas.</li>
<li>so we can duly insert into the <b>TopoFeatures-geometry</b> table (i.e. <i>census2011_topofeatures</i>) a new level of references to Topology primitives by simply copying all definitions already stored into the previous hierarchical level.<br>
And to do such a thing we simply have to read from <b>topolayer=1</b> then inserting into <b>topolayer=2</b> after setting the appropriate <b>fid</b> values.<br>
Really not a difficult task.</li>
</ul>
<verbatim>
INSERT INTO census2011_topofeatures
SELECT NULL, c.node_id, c.edge_id, c.face_id, 3, a.fid
FROM census2011_topofeatures_3 AS a
JOIN census2011_topofeatures_1 AS b ON (b.cod_com = a.cod_com)
JOIN census2011_topofeatures AS c ON (c.topolayer_id = 1 AND c.fid = b.fid);
</verbatim>
We can adopt exactly the same identical approach in order to complete <b>topolayer=3</b> corresponding to <b>Municipalities</b>.
<verbatim>
INSERT INTO census2011_topofeatures
SELECT NULL, c.node_id, c.edge_id, c.face_id, 4, a.fid
FROM census2011_topofeatures_4 AS a
JOIN census2011_topofeatures_3 AS b ON (b.cod_prov = a.cod_prov)
JOIN census2011_topofeatures AS c ON (c.topolayer_id = 3 AND c.fid = b.fid);
</verbatim>
More or less the same is for deriving <b>topolayer=4</b> corresponding to <b>Provinces</b>; this time we'll simply aggregate the underlying Municipalities from <b>topolayer=3</b>.
</verbatim>
We can adopt exactly the same identical approach in order to complete <b>topolayer=3</b> corresponding to <b>Municipalities</b>.
<verbatim>
INSERT INTO census2011_topofeatures
SELECT NULL, c.node_id, c.edge_id, c.face_id, 5, a.fid
FROM census2011_topofeatures_5 AS a
JOIN census2011_topofeatures_4 AS b ON (b.cod_reg = a.cod_reg)
JOIN census2011_topofeatures AS c ON (c.topolayer_id = 4 AND c.fid = b.fid);
</verbatim>
And finally we can derive <b>topolayer=5</b> corresponding to <b>Regions</b> by directly aggregating the underlying Provinces from <b>topolayer=4</b>.
<br><br>
<table width="100%" cellspan="4" cellpadding="6" bgcolor="#f8f8f0" border="1">
<tr><td>
<b>step #1: exporting the Region TopoLayer</b>
<verbatim>
SELECT TopoGeo_ExportTopoLayer('census2011', 'reg', 'out_reg_2011', 1);
</verbatim>
We are now definitely ready to export any other Administrative Level.<br>
For the sake of clarity we'll follow a top-bottom order, so we'll start by exporting first the whole Tuscany.
</td><td>
<img src="https://www.gaia-gis.it/gaia-sins/topofigs/topolayer-region.png" alt="topolayer-region">
</td></tr>
<tr><td>
<b>step #2: exporting the Provinces TopoLayer</b>
<verbatim>
SELECT TopoGeo_ExportTopoLayer('census2011', 'prov', 'out_prov_2011', 1);
</verbatim>
We'll continue by exporting all the Tuscany Provinces.
</td><td>
<img src="https://www.gaia-gis.it/gaia-sins/topofigs/topolayer-provinces.png" alt="topolayer-provinces">
</td></tr>
<tr><td>
<b>step #3: exporting the Municipalities TopoLayer</b>
<verbatim>
SELECT TopoGeo_ExportTopoLayer('census2011', 'com', 'out_com_2011', 1);
</verbatim>
Then we'll export all the Tuscany Municipalities.<br>
For better clarity the figure shows a magnified detail centered around the Island of Elba.
</td><td>
<img src="https://www.gaia-gis.it/gaia-sins/topofigs/topolayer-municipalities.png" alt="topolayer-municipalities">
</td></tr>
<tr><td>
<b>step #4: exporting the Populated Places TopoLayer</b>
<verbatim>
SELECT TopoGeo_ExportTopoLayer('census2011', 'ppl', 'out_ppl_2011', 1);
</verbatim>
And we'll finally export all Populated Places.<br><br><br><br>
<u>Conclusion</u>: we started this advanced tutorial by importing into a Topology just a single layer (Census Areas) and now we've finished by producing a complete set of administrative boundaries at different hierarchical levels:
<ul>
<li>Populated Places</li>
<li>Municipalities</li>
<li>Provinces</li>
<li>Region</li>
</ul>
</td><td>
<img src="https://www.gaia-gis.it/gaia-sins/topofigs/topolayer-populated-places.png" alt="topolayer-populated-places">
</td></tr>
</table><br>
<hr>
<h3>Recent changes: old Municipalities merging into new Municipalities</h3>
If you remember the initial <b>tuscany-census-2011.sqlite</b> DB-file contains a <b>com_2014_15</b> Table; during years 2014 and 2015 few Municipalities merged two by two thus giving birth to new Municipalities. This is the full list:<br><br>
<table cellspan="4" cellpadding="6" bgcolor="#f8f8f0" border="1">
<tr><td colspan="3" align="center" bgcolor="#f0f0f8"><b>New Municipality (2014/15)</b></td><td colspan="2" align="center" bgcolor="#f0f8f8"><b>suppressed #1</b></td><td colspan="2" align="center" bgcolor="#f8f0f8"><b>suppressed #2</b></td></tr>
<tr><td bgcolor="#f0f0f8"><b>cod_prov</b></td><td bgcolor="#f0f0f8"><b>cod_com</b></td><td bgcolor="#f0f0f8"><b>name</b></td><td bgcolor="#f0f8f8"><b>cod_com</b></td><td bgcolor="#f0f8f8"><b>name</b></td><td bgcolor="#f8f0f8"><b>cod_com</b></td><td bgcolor="#f8f0f8"><b>name</b></td></tr>
<tr><td>AR</td><td align="right">51040</td><td>Castelfranco Piandiscò</td><td bgcolor="#f0f8f8" align="right">51009</td bgcolor="#f0f8f8"><td bgcolor="#f0f8f8">Castelfranco di Sopra</td><td bgcolor="#f8f0f8" align="right">51029</td><td bgcolor="#f8f0f8">Pian di Sco</td></tr>
<tr><td>AR</td><td align="right">51041</td><td>Pratovecchio Stia</td><td bgcolor="#f0f8f8" align="right">51032</td><td bgcolor="#f0f8f8">Pratovecchio</td><td bgcolor="#f8f0f8" align="right">51036</td><td bgcolor="#f8f0f8">Stia</td></tr>
<tr><td>FI</td><td align="right">48052</td><td>Figline e Incisa Valdarno</td><td bgcolor="#f0f8f8" align="right">48016</td><td bgcolor="#f0f8f8">Figline Valdarno</td><td bgcolor="#f8f0f8" align="right">48023</td><td bgcolor="#f8f0f8">Incisa in Val d'Arno</td></tr>
<tr><td>FI</td><td align="right">48053</td><td>Scarperia e San Piero</td><td bgcolor="#f0f8f8" align="right">48042</td><td bgcolor="#f0f8f8">Scarperia</td><td bgcolor="#f8f0f8" align="right">48040</td><td bgcolor="#f8f0f8">San Piero a Sieve</td></tr>
<tr><td>LU</td><td align="right">46036</td><td>Fabbriche di Vergemoli</td><td bgcolor="#f0f8f8" align="right">46012</td><td bgcolor="#f0f8f8">Fabbriche di Vallico</td><td bgcolor="#f8f0f8" align="right">46032</td><td bgcolor="#f8f0f8">Vergemoli</td></tr>
<tr><td>LU</td><td align="right">46037</td><td>Sillano Giuncugnano</td><td bgcolor="#f0f8f8"align="right">46029</td><td bgcolor="#f0f8f8">Sillano</td><td bgcolor="#f8f0f8" align="right">46016</td><td bgcolor="#f8f0f8">Giuncugnano</td></tr>
<tr><td>PI</td><td align="right">50050</td><td>Casciana Terme Lari</td><td bgcolor="#f0f8f8" align="right">50007</td><td bgcolor="#f0f8f8">Casciana Terme</td><td bgcolor="#f8f0f8" align="right">50017</td><td bgcolor="#f8f0f8">Lari</td></tr>
<tr><td>PI</td><td align="right">50041</td><td>Crespina Lorenzana</td><td bgcolor="#f0f8f8" align="right">50013</td><td bgcolor="#f0f8f8">Crespina</td><td bgcolor="#f8f0f8" align="right">50018</td><td bgcolor="#f8f0f8">Lorenzana</td></tr>
</table>
<br>
Such changes simply affects Municipalities; Provinces are unaffected by this process; so we can now duly generate a further layer corresponding to 2015 Municipalities, and we simply have to slightly rearrange the TopoLayers/TopoFeatures definitions in order to achieve this further goal.
<verbatim>
ATTACH DATABASE "./tuscany-census-2011.sqlite" AS istat;
CREATE TABLE com2015 AS
SELECT * FROM istat.pop_com_2011;
INSERT INTO com2015
SELECT a.cod_com, a.cod_prov, a.cod_istat, a.cod_cadrastre, a.name, a.townhall_addr,
Sum(b.pop_2011), Sum(b.m_2011), Sum(b.f_2011)
FROM istat.com_2014_15 AS a
JOIN istat.pop_com_2011 AS b ON (b.cod_com IN (a.old_cod_com_1, a.old_cod_com_2))
GROUP BY a.cod_com;
DETACH DATABASE istat;
</verbatim>
<ol>
<li>we'll <i>attach</i> yet another time the initial <b>tuscany-census-2011.sqlite</b> DB-file.</li>
<li>we'll create a new <b>com2015</b> table by just just copying the <b>istat.pop_com_2011</b> View.</li>
<li>and finally we'll append to the same table the new Municipalities from the <b>istat.com_2014_15</b> table; during this step we'll compute the Population 2011 census values.<br>
<u>Note</u>: after completing the above steps now the <b>com2015</b> table contains both suppressed and new Municipalities</li>
</ol>
<verbatim>
SELECT TopoGeo_InitTopoLayer('census2011', NULL, 'com2015', 'com2015');
INSERT INTO census2011_topofeatures
SELECT NULL, a.node_id, a.edge_id, a.face_id, 6, a.fid
FROM census2011_topofeatures AS a
JOIN census2011_topofeatures_3 AS b ON (a.topolayer_id = 3 AND a.fid = b.fid);
</verbatim>
Nothing really interesting in this, because we already performed this step during the previous exercise. We've simply initialized yet another TopoLayers, and we've copied into it the same TopoFeatures definitions already supporting 2011 Municipalities.
<verbatim>
UPDATE census2011_topofeatures
SET fid = (SELECT fid FROM census2011_topofeatures_6 WHERE cod_com = 51040)
WHERE topolayer_id = 6 AND fid IN (SELECT fid FROM census2011_topofeatures_6 WHERE cod_com in (51009, 51029));
UPDATE census2011_topofeatures
SET fid = (SELECT fid FROM census2011_topofeatures_6 WHERE cod_com = 51041)
WHERE topolayer_id = 6 AND fid IN (SELECT fid FROM census2011_topofeatures_6 WHERE cod_com in (51032, 51036));
</verbatim>
Now we've simply updated all relationships between Topology Faces and TopoFeatures by transferring them from <b>51009 <i>Castelfranco di Sopra</i></b> and <b>51029 <i>Pian di Sco</i></b> to <b>51045 <i>Castelfranco Piandiscò</i></b>. Then we've replied the same action.from <b>51032 <i>Pratovecchio</i></b> and <b>51041 <i>Stia</i></b> to <b>51041 <i>Pratovecchio Stia</i></b> so to correctly update all new Municipalities interesting the Province of Arezzo.<br>
You could eventually continue by yourself so to update all Provinces.
<verbatim>
SELECT TopoGeo_ExportTopoLayer('census2011', 'com2015', 'out_com_ar_2015', 1, 1);
SELECT TopoGeo_InsertFeatureFromTopoLayer ('census2011', 'com2015', 'out_com_ar_2015', fid)
FROM census2011_topofeatures_8
WHERE cod_prov = 51 AND cod_com NOT IN (51009, 51029, 51032, 51036);
</verbatim>
Finally we'll export a new layer representing the updated 2015 Province of Arezzo.<br>
This time we'll use the <b>TopoGeo_InsertFeatureFromTopoLayer()</b> SQL function in order to selectively export only a selected subset from the overall TopoFeatures.<br><br>
<table cellspan="4" cellpadding="6" bgcolor="#f8f8f0" border="1">
<tr><td>
<img src="https://www.gaia-gis.it/gaia-sins/topofigs/topo-ar-2011.png" alt="topo-ar-2011">
</td><td>
<img src="https://www.gaia-gis.it/gaia-sins/topofigs/topo-ar-2015.png" alt="topo-ar-2015">
</td></tr>
<tr><td align="center">Municipalities in the Province of Arezzo: <b>2011</b></td><td align="center">Municipalities in the Province of Arezzo: <b>2015</b></td></tr>
</table><br>
<hr>
<table width="100%"><tr>
<td width="33%" align="left"><a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=topo-intermediate">previous page</a></td>
<td align="center"><a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=misc-docs">back to index</a></td>
<td width="33%" align="right"><a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=topo-advanced">next page</a></td>
</tr></table>
Z 0543187b1a3a968b42c7202f9e13c9ca