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

<table width="100%"><tr>
<td width="33%" align="left"><a
<td align="center"><a to
<td width="33%" align="right"><a

<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 but Face primitives simply correspond to a <u><i>virtual 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 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 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 one, and will be actually materialized (by aggregating all referenced Topology primitives) only when exporting the destination GeoTable.</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>
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
this second table is intended to permanently store all cross-references existing between Topology primitives and TopoFeatures.</li>
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>

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

<li>uncompress the downloaded db-file
<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 you are expected instead to connect a new (empty) DB-file named

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

<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
<td
<tr><td
<tr><td
<tr><td
<tr><td
<tr><td
</table>

<tr><td>

<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> 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> contains all TopoFeatures associated to the TopoLayer <b>ppl</b> (<i>Populated Places</i>).<br><br>
The second table <b>census2011_topofeatures_4</b> 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
<td
<td
<td
<td
<td
<td
<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
<tr><td align="right">2</td><td align="right">205142706</td><td align="right">4321</td><td align="right">2046</td><td
<tr><td align="right">3</td><td align="right">205142710</td><td align="right">482</td><td align="right">226</td><td
<tr><td colspan="7"
<tr><td align="right">1018</td><td align="right">1410775415</td><td align="right">285</td><td align="right">137</td><td
<tr><td align="right">1019</td><td align="right">1410785415</td><td align="right">537</td><td align="right">264</td><td
</table>
<br>
"<i>census2011_topofeatures_4</i>"
<table cellspan="4" cellpadding="6" bgcolor="#f8f8f0" border="1">
<tr><td
<td
<td
<td
<td
<td
<td
<td
<tr><td align="right">1</td><td align="right">45</td><td align="right">199650</td><td align="right">95754</td><td
<tr><td align="right">2</td><td align="right">46</td><td align="right">388327</td><td align="right">186183</td><td
<tr><td align="right">3</td><td align="right">47</td><td align="right">287866</td><td align="right">138054</td><td
<tr><td colspan="8"
<tr><td align="right">9</td><td align="right">53</td><td align="right">220564</td><td align="right">105585</td><td
<tr><td align="right">10</td><td align="right">100</td><td align="right">245916</td><td align="right">119088</td><td
</table>

<tr><td>

<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
<td
<td
<td
<td
<td
<tr><td align="right">1</td><td align="right">1</td><td
<tr><td align="right">2</td><td align="right">1</td><td
<tr><td align="right">3</td><td align="right">1</td><td
<tr><td colspan="6"
<tr><td align="right">28864</td><td align="right">1</td><td
<tr><td align="right">28868</td><td align="right">1</td><td
</table>

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

</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
<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 examples 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 alt="elba-choropleth">

</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
<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 alt="topolayer-region">

<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 alt="topolayer-provinces">

<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 alt="topolayer-municipalities">

<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 alt="topolayer-populated-places">

</table><br>
<table width="100%"><tr>
<td width="33%" align="left"><a
<td align="center"><a to
<td width="33%" align="right"><a