Topology-Geometry: an intermediate tutorial

more non-ISO SQL functions supporting Topology-Geometry

SpatiaLite supports an extended set of SQL functions not defined by the ISO SQL MM specifications implementing many useful operations on Topology-Geometry.
You can easily distinguish ISO SQL MM functions because all them have a name starting with ST_; non-ISO names usually start with TopoGeo_ or have no prefix at all.
Both ISO and non-ISO SQL function will immediately raise an exception whenever an error would occurs.

non-ISO SQL functions identifying Topology primitives by location
GetNodeByPoint	GetNodeByPoint( topo-name , point-geom , tolerance ) Will attempt to find the ID of a Node located at point or presenting a distance to point within the given tolerance.	If no Node satisfies this condition, or more Nodes are found, an exception will be raised.
GetEdgeByPoint	GetEdgeByPoint( topo-name , point-geom , tolerance ) Will attempt to find the ID of an Edge intersecting the given point or presenting a distance to point within the given tolerance.	If no Edge satisfies this condition, or more Edges are found, an exception will be raised.
GetFaceByPoint	GetFaceByPoint( topo-name , point-geom , tolerance ) Will attempt to find the ID of a Face intersecting the given point or presenting a distance to point within the given tolerance.	If no Face satisfies this condition, or more Faces are found, an exception will be raised.

non-ISO SQL functions for inserting Simple Features into a Topology
TopoGeo_AddPoint	TopoGeo_AddPoint( topo-name , point-geom , tolerance ) Will attempt to add a POINT (or even a MULTIPOINT) to an already existing Topology, possibly splitting Edges. Will return a comma separated list of all IDs of corresponding Nodes.	an exception will be raised on failure.
TopoGeo_AddLinestring	TopoGeo_AddLinestring( topo-name , curve-geom , tolerance ) Will attempt to add a LINESTRING (or even a MULTILINESTRING) to an already existing Topology, possibly splitting Edges and Faces. Will return a comma separated list of all IDs of corresponding Edges.	an exception will be raised on failure.
TopoGeo_SubdivideLines	TopoGeo_SubdivideLines( geometry , line-max-points , line-max-length ) Will split a LINESTRING (or even a MULTILINESTRING, POLYGON or MULTIPOLYGON) into a collection of shorter LINESTRINGs fully respecting topological consistency. Will return a MULTILINESTRING on success or NULL on failure. Remark: inserting very complex Linestrings into a Topology is a slow process; splitting into simpler/shorter lines ensures a noticeable performance boost.	if the argument line-max-points has a positive value then all input linestrings will be split into many shorter lines having no more than the required number of points. if the argument line-max-length has a positive value then a separate output line will be split every time that it becomes longer than the allowed limit. if both line-max-points and line-max-length are active at the same time then the first limit encountered will apply. in any other case all input Linestrings will be simply copied into the output collection exactly as they are.
	There are no similar functions supporting Polygons. This is simply because you can easily use the Linestring-oriented functions; just convert all input Polygon/MultiPolygons into a collection of Rings/Linestrings, as e.g.; **TopoGeo_AddLinestring ( topo_name , LinesFromRings ( geometry ) , tolerance );** TopoGeo_AddLinestring ( topo_name , TopoGeo_SubdivideLines ( geometry , line-max-points , line-max-length ) , tolerance );
non-ISO SQL functions for inserting a whole GeoTable into a Topology
TopoGeo_FromGeoTable	TopoGeo_FromGeoTable( topo-name , db-prefix , input-table-name , geometry-column , tolerance ) TopoGeo_FromGeoTable( topo-name , db-prefix , input-table-name , geometry-column , tolerance , line-max-points , line-max-length ) This high-level SQL function automatically dispatches an appropriate TopoGeo_AddPoint(), TopoGeo_AddLinestring() and TopoGeo_SubdivideLines() so to load a whole GeoTable in a single shot. The input GeoTable can contain any kind of Geometry (POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION and GEOMETRY). The Topology must always be located in the MAIN DB, but the input GeoTable could also be located in a attached DB. Will return 1 on success; an exception will be raised on failure.	*db-prefix* can be NULL, in this case the input GeoTable is expected to be located within the MAIN DB. geometry-column can be NULL, in this case the name of the column containing Geometries will be automatically retrieved. if db.table does not contains a Geometry column, or if it contains two or more Geometries an exception will be raised. the input GeoTable must be properly registered and both its SRID and dimensions fields must match the declaration in the target Topology. the tolerance, line-max-points and line-max-length arguments are directly passed to the lower level functions and preserve identical meanings. (both line-max-points and line-max-length default to -1).

*Topology-Seeds* a Topology-Seed is single Point uniquely identifying a Topology primitive and intended to establish an approximative but robust enough spatial relationships between Simple Feature Geometries and Topology primitives. Topology-Seeds can be divided in three distinct classes, one for each Topology primitive class: Node Seeds: a Node already has a Point geometry, so Node-Seeds are not required at all. A Node primitive already is the Seed of itself. Edge Seeds: a Point geometry being coincident to some Vertex of the Edge geometry (chosen at random and excluding the Start and the End Nodes) can be adequately assumed to be a Seed uniquely identifying the Edge as a whole. A possible ambiguity arises for all Edges presenting just two Vertices; in this very special case the Seed will always be located on the Start Node. Face Seeds: a Point geometry guaranteed to lay on the surface of the Face can be adequately assumed to be a Seed uniquely identifying the Face as a whole. Remark: Seeds aren't static, they are dynamic objects. Every time that a Topology primitive changes the corresponding Seed will change as well. For efficiency reasons SpatiaLite will never attempt to immediately update all Seeds being modified by some editing action; the affected Seeds will simply become invalid, and no special action will be taken to prevent this. Updating the Seeds to correctly reflect the current state of the Topology primitives is always an asynchronous batch process that will be done automatically later, only when it's really required.
non-ISO SQL functions on Topology-Seeds
TopoGeo_GetEdgeSeed	TopoGeo_GetEdgeSeed( topo-name , edge-id ) Returns a Point Geometry uniquely identifying an Edge (i.e. spatially intersecting the Edge identified by *edge-id*).	if the Edge has two points only (i.e. it's just a straight segment directly connecting the Start and the End Nodes) then the Seed will be always located on the Start Node.
TopoGeo_GetFaceSeed	TopoGeo_GetFaceSeed( topo-name , face-id ) Returns a Point Geometry uniquely identifying a Face (i.e. spatially intersecting the Face identified by *face-id*).
TopoGeo_UpdateSeed	TopoGeo_UpdateSeed( topo-name ) TopoGeo_UpdateSeed( topo-name , incremental-mode ) Will rebuild all Edge- and Face-Seeds supporting a Topology. When executed in the incremental mode only the Seeds effectively missing or being invalidated since the previous batch run will be updated (thus implying a noticeably shorter execution time).	incremental-mode is a boolean flag: by passing a 0 value (FALSE) all Seeds will be unconditionally rebuilt from scratch. any other value different from zero will be assumed to be TRUE thus selecting the incremental mode. by default incremental-mode is set as FALSE.
non-ISO SQL functions bases on Seeds and exporting a whole GeoTable out from a Topology
TopoGeo_ToGeoTable	TopoGeo_ToGeoTable( topo-name , db-prefix , ref-table-name , geometry-column , out-table ) TopoGeo_ToGeoTable( topo-name , db-prefix , ref-table-name , geometry-column , out-table, with-spatial-index ) This high-level SQL function automatically creates and populates an output GeoTable starting from a Topology. A reference GeoTable is expected to contain information attributes and rough geometries for every Simple Feature to be exported out from the Topology. Both the Topology and the output GeoTable must always be located on the MAIN DB, but the reference GeoTable could be eventually located on any attached DB. Will return 1 on success; an exception will be raised on failure.	*db-prefix* can be NULL, in this case the reference GeoTable is expected to be located within the MAIN DB. geometry-column can be NULL, in this case the name of the column containing Geometries will be automatically retrieved. if db.table does not contains a Geometry column, or if it contains two or more Geometries an exception will be raised. the reference GeoTable must be properly registered and must match both SRID and dimensions declared by the target Topology. the reference GeoTable can contain any kind of Geometries (POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION and GEOMETRY). the output GeoTable must not exist and will be automatically created with the same identical attributes of the reference GeoTable. all spatial features found into the reference GeoTable will be copied into the output GeoTable. All information attribute values will be preserved exactly as they are and the feature geometry will be built by aggregating all Topology primitives matching the reference geometry by Seed correspondences. If no matching Topology primitives are found the result will be a NULL Geometry Note: this function will automatically invoke TopoGeo_UpdateSeeds() in incremental mode
TopoGeo_ToGeoTableGeneralize	TopoGeo_ToGeoTable( topo-name , db-prefix , ref-table-name , geometry-column , out-table , tolerance ) TopoGeo_ToGeoTableGeneralize( topo-name , db-prefix , ref-table-name , geometry-column , out-table, tolerance, with-spatial-index ) This SQL function is very similar to the previous one; the only difference is in that it supports the additional capability to export simplified / generalized geometries fully preserving topological consistency. Will return 1 on success; an exception will be raised on failure.	Arguments have the same interpretations as above. *tolerance* is the approximation radius expected by the Douglas-Peucker simplification algorithm.

a quick practical exercise

Prerequisites:

download the sample DB-file from here
It simply contains administrative boundaries kindly released by Tuscany Region and ISTAT under CC-BY license terms.
The original datasets have been slightly rearranged in a more convenient form.
All geometries are in the SRID 3003 (Monte Mario / Italy zone 1):
- comuni: Tuscany Municipalities / Local Councils
- province: Tuscany Provinces / Counties
- regioni: Tuscany Region itself
- unioni_com: Unions / Confederations between Municipalities
- prov_istat2011: an alternative Provinces dataset.
  This dataset is not exactly identical to province because it has a different origin (ISTAT - National Statistic Authority): both information attributes and geometries are slightly different.
uncompress the downloaded db-file (tuscany-admin.sqlite).
now you can finally start a SpatiaLite session using your preferred SpatiaLite front end tool:
- Attention: you must not directly connect to tuscany-admin.sqlite; you are expected instead to connect a new (empty) DB-file named tuscany-topo.sqlite

SELECT CreateTopology('topo', 3003, 0, 0);
1

ATTACH DATABASE "./tuscany-admin.sqlite" AS admin;

SELECT TopoGeo_FromGeoTable('topo', 'admin', 'comuni', NULL, NULL, 512);
1

DETACH DATABASE admin;

we'll start by creating a new 2D Topology named topo located into SRID 3003
then we'll attach the tuscany-admin.sqlite db-file.
and we'll invoke TopoGeo_FromGeoTable in order to fully populate the above Topology by importing in single pass all Geometries from the admin.comuni GeoTable.
Few interesting details worth to pay some attention:
- we'll pass NULL as the name of the geometry column: admin.comuni just contains a single geometry column, so the SQL function will be automatically able to guess the appropriate column name without ambiguity.
- we'll ask the SQL function to split all Linestrings and Rings being imported into the Topology into a collection of many shorted lines each one of them presenting no more than 512 vertices.
  this ensures a noticeable performance boost; you can easily check by yourself how long it could be importing the original unsplit Linestrings/Rings.
finally we'll detach the now useless external db-file.

SELECT Count(*) FROM topo_node;
3431

SELECT Count(*) FROM topo_edge;
3714

SELECT Count(*) FROM topo_face;
1328

SELECT ST_ValidateTopoGeo('topo');
NULL

SELECT * FROM TEMP.topo_validate_topogeo;

SELECT TopoGeo_UpdateSeeds('topo');
1

Now we'll quickly check our Topology:

a short statistic summary:
- imported Nodes: 3431
- imported Edges: 3714
- imported Faces: 1328
we'll now invoke ST_ValidateTopoGeo() so to thoroughly test the Topology for consistency and validity:
- this SQL function will create a report table named TEMP.topo_validate_topogeo
- this table is completely empty; this means that the inspected Topology is fully consistent and that no critical issues have been identified.
- Note: TEMP.topo_validate_topogeo is a temporary table, so it will automatically disappear when the connection terminates.
anything seems right, so as a final touch we'll now update all Topology Seeds and we'll be finally ready to visually inspect our Topology.

This first snapshot shows both Nodes and Edges.

Remember: Topology Nodes and Edges have a real geometry, respectively of the POINT and LINESTRING types, so you can directly show on a map both topo_node and topo_edge tables.

This second snapshot shows Topology Seeds (both Edge-Seeds and Face-Seeds); real geometries corresponding to each Face are shown as well.

Note: Faces never have a direct geometry representation, anyway a real geometry for each Face can be easily reconstructed by invoking the ST_GetFaceGeometry() SQL function.
The Spatial View topo_face_geoms is exactly intended to support this task.

You could eventually show both Edge- and Face-Seeds from the topo_seeds table, anyway the sister spatial views topo_edge_seeds and topo_face_seeds allows for a more precise distinction between the ones and the others.

ATTACH DATABASE "./tuscany-admin.sqlite" AS admin;

SELECT TopoGeo_ToGeoTable('topo', 'admin', 'prov_istat2011', NULL, 'out_prov');
1

SELECT TopoGeo_ToGeoTableGeneralize('topo', 'admin', 'comuni', NULL, 'out_comuni_10m', 10);
1

SELECT TopoGeo_ToGeoTableGeneralize('topo', 'admin', 'comuni', NULL, 'out_comuni_100m', 100);
1

SELECT TopoGeo_ToGeoTableGeneralize('topo', 'admin', 'comuni', NULL, 'out_comuni_500m', 500);
1

DETACH DATABASE admin;

In this last step we'll attach yet another time tuscany-admin.sqlite and we'll then create few new datasets based on our Topology.

both TopoGeo_ToGeoTable() and TopoGeo_ToGeoTableGeneralize() are intended to create a new GeoTable starting from a valid Topology and another GeoTable acting as a reference.
all geometries being exported into the output GeoTable will be always provided by the Topology.
for every row found in the reference table a corresponding feature will be exported into the output GeoTable, and the information attributes will always be the ones provided by the reference table.
The feature own geometry could be eventually NULL if no matching topology primitives are found.
all topology primitives matching a spatial correspondence via TopoSeeds will be aggregated so to build the exported feature own geometry.
TopoGeo_ToGeoTable() will simply export geometries exactly as they are.
TopoGeo_ToGeoTableGeneralize() supports the additional capability to simplify / generalize all exported geometries by applying the Douglas-Peucker algorithm in such a way that topological consistency will be fully preserved.

TopoGeo_ToGeoTable: exporting a GeoTable out from Topology
SELECT TopoGeo_ToGeoTable('topo', 'admin', 'prov_istat2011', NULL, 'out_prov'); As you can easily check by yourself the new GeoTable out_prov now contains geometries directly corresponding to our Topology (from Tuscany Region origin and fully validated). Anyway all information attributes directly correspond to the reference GeoTable admin.prov_istat2011 (form ISTAT origin).
This magnified detail directly compares the ISTAT own geometries (displayed in pink color) and the Topology geometries (blue color). It's rather obvious that they substantially differ, and the Topology (derived from Tuscany Region data) is more accurate and represents many more fine-grained details. Conclusion: by invoking TopoGeo_ToGeoTable() you can easily derive a new dataset fully preserving the original information attributes but supporting a better geometry representation. Topo-Seeds are exactly intended to support this task: they allow to establish a direct correspondence between topology primitives and Feature Geometries even when they does not exactly match; just a coarse match interesting only the Seed itself will suffice. Side note: one of the many interesting outcomes of Topology is that you can easily extract from the Topology not only the initial layer (in our case: Municipalities administrative boundaries). You can directly extract also any other higher hierarchical level (Provinces, Regions, States and so on) directly based on aggregation of lower level entities. As you can easily imagine, this technically implies to aggregate many different Faces (topology primitives) so to get a bigger Polygon covering all Faces: anyway directly working at the Edge level is a noticeably faster process than merging many individual Polygons by repeatedly calling ST_Union(): all interested Faces will be identified by a matching Face-Seed criteria. then all Edges required by those Faces will be identified: many of them must be ignored simply because they have a RightFace and a LeftFace already defined in the current Faces list (i.e. they are completely internal to the Polygon we are attempting to build). all Edges surviving the above decimation process will surely define the outer boundary of the aggregated Polygon; and building such a Polygon directly from an Edges list is a faster process than repeatedly merging many individual sub-polygons.

TopoGeo_ToGeoTableGeneralize: exporting a simplified/generalized GeoTable out from Topology

This first screenshot shows a full resolution map of the Island of Elba based on the original comuni layer.

Since this layer is very detailed it could easily become heavy to be processed at the higher representation scales, thus causing long rendering timings. In any case many of the fine details will be completely invisible at such scales, so progressively simplifying geometries accordingly to the current reproduction ratio would become a desirable option.

Any approach directly based on ST_Simplify() or ST_SimplifyPreserveTopology() can never produce an acceptable result simply because each Polygon will be individually simplified/generalized, thus destroying the overall topological consistency.

This image shows the result after executing ST_SimplifyPreserveTopology(geom, 500.0); as you can see there are many empty gaps and several overlapping areas between adjacent polygons.

SELECT TopoGeo_ToGeoTableGeneralize('topo', 'admin', 'comuni', NULL, 'out_comuni_10m', 10);

With TopoGeo_ToGeoTableGeneralize() can effectively simplify/generalize a polygonal layer, while still preserving a full topological consistency.

In this first sample a mild simplification radius (10 m) has been applied, so many of the finely grained details are still preserved.

SELECT TopoGeo_ToGeoTableGeneralize('topo', 'admin', 'comuni', NULL, 'out_comuni_100m', 100);

In this second sample a more aggressive radius (100 m) has been applied; many details have been suppressed and a rather strong simplification is now well evident.

SELECT TopoGeo_ToGeoTableGeneralize('topo', 'admin', 'comuni', NULL, 'out_comuni_500m', 500);

In this third sample a very high radius (500 m) has been applied, so only a rough, sketchy, shape has remained, all of the details have been lost.

The following table shows a brief information about the Capoliveri Municipality; notice the strong impact that the simplification process has had. Here you can experiment with the the Douglas-Peucker radius, until you find the most appropriate level of simplification.

Layer type	Points in the exterior ring of main polygon	Total polygons in multipolygon	Size (bytes) of binary geom
Original layer / not simplified	14768	232	291080
Simplified - 10 m	829	23	15467
Simplified - 100 m	107	2	1866
Simplified - 500 m	38	1	669

Note: topology-based simplification/generalization does not only simply affect the number of Points for each Ring, but also suppresses smaller polygons (and internal holes), when their size falls below certain thresholds.

A final visual comparison; these two maps apparently seem to be identical, but they are rendered from two greatly different layers:

the map on the left (pale yellow) corresponds to the (not simplified) layer comuni.
the map on the right (pale grey) corresponds to the (simplified) layer out_comuni_500n (higher approximation radius: 500m).

There is no practical difference between the one and the other, since their visual quality is more or less the same at this reproduction scale. There is, however, a noticeable rendering difference between the two:

rendering the full resolution map is a slow process, because a huge I/O data transfer is needed, which will require more graphic operations since there are more points to be processed.
rendering the simplified map is a quicker process, because of the lower amount of I/O data needed.

Conclusion: topological simplification, when wisely used, can introduce a noticeable performance boot in many multiscale rendering processes, while retaining an uncompromised visual quality.
The most obvious candidates to take profit from this opportunity are WMS layers based on vector datasources.

back to index