Familiarizing with Topology-Network: an elementary tutorial

Tutorial #2: network editing by using basic ISO SQL function

In this tutorial we will explore the ISO SQL/MM main core supporting Topology-Network (namely: Spatial Network).
We'll use very simple geometries (straight segments) so to avoid any possible complexity thus making basically simple following and understanding every step.
You can indifferently use spatialite CLI or spatialite_gui in order to test each SQL statement.

Later samples will become more complex, so it is important to understand the basic Topology terminology such as:

Node POINT : an Anchor for 'Links'
Link LINESTRING : connecting 2 'Nodes'
Seed POINT : a quick spatial reference intended to uniquely identify a Link.

Another important Topology-Term to remember, when you do not use the defined function to manipulate the Topology-Tables, is correctly spelled: ¡¡¡ D I S A S T E R !!!

CAVEAT

Any direct manipulation of the low-level DB tables implementing an ISO Network will almost certainly cause a major ¡¡¡ D I S A S T E R !!!.
You are never expected to execute any INSERT, UPDATE or DELETE statement on such tables.
You always have to call the appropriate ISO SQL functions for any operation targeting a Topology in order to preserve a proper consistency.

If you are strongly interested in better understanding many internal implementation mechanisms you could eventually inspect from time to time the content of the physical DB tables supporting the test Topology:

SELECT * FROM testnet_node;

SELECT * FROM testnet_link;

Please note: the actual layout adopted by these low-level DB tables could eventually change in future versions.
As a general principle any direct interaction with the low-level DB tables should be always carefully avoided even for SELECT queries (use instead the appropriate ISO SQL functions).
For didactic / debugging purposes, executing some SELECT statement in order to inspect the actual contents of these tables is an absolutely harmless operation.

We'll start by creating a brand new ISO Topology-Network (of the Spatial type) named testnet defining the SRID=4326 (alias WGS84 long/lat) with 2D XY dimension. Since topology functions, receiving geometries, expect the same defined SRID and dimension, an appropriate ST_Transform or CastToXY* must be used when needed. SELECT CreateNetwork('testnet', 1, 4326, 0, 0); Now we'll insert a bunch of Isolated Nodes: SELECT ST_AddIsoNetNode('testnet', MakePoint(-180, 90, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(-180, 60, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(-180, 0, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(-180, -30, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(-180, -60, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(-180, -90, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(-90, 90, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(-90, 30, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(-90, 0, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(-90, -30, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(-90, -60, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(-90, -90, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(30, 0, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(0, 0, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(-30, 0, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(30, 90, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(30, 30, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(180, 90, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(180, 30, 4326)); SELECT ST_AddIsoNetNode('testnet', MakePoint(180, 0, 4326));

Then we'll insert a bunch of Links: SELECT ST_AddLink('testnet', 1, 2, ST_GeomFromText('LINESTRING(-180 90, -180 60)', 4326)); SELECT ST_AddLink('testnet', 3, 4, ST_GeomFromText('LINESTRING(-180 0, -180 -30)', 4326)); SELECT ST_AddLink('testnet', 4, 5, ST_GeomFromText('LINESTRING(-180 -30, -180 -60)', 4326)); SELECT ST_AddLink('testnet', 5, 6, ST_GeomFromText('LINESTRING(-180 -60, -180 -90)', 4326)); SELECT ST_AddLink('testnet', 1, 7, ST_GeomFromText('LINESTRING(-180 90, -90 90)', 4326)); SELECT ST_AddLink('testnet', 2, 8, ST_GeomFromText('LINESTRING(-180 60, -90 30)', 4326)); SELECT ST_AddLink('testnet', 3, 9, ST_GeomFromText('LINESTRING(-180 0, -90 0)', 4326)); SELECT ST_AddLink('testnet', 6, 12, ST_GeomFromText('LINESTRING(-180 -90, -90 -90)', 4326)); SELECT ST_AddLink('testnet', 7, 8, ST_GeomFromText('LINESTRING(-90 90, -90 30)', 4326)); SELECT ST_AddLink('testnet', 8, 9, ST_GeomFromText('LINESTRING(-90 30, -90 0)', 4326)); SELECT ST_AddLink('testnet', 9, 10, ST_GeomFromText('LINESTRING(-90 0, -90 -30)', 4326)); SELECT ST_AddLink('testnet', 10, 11, ST_GeomFromText('LINESTRING(-90 -30, -90 -60)', 4326)); SELECT ST_AddLink('testnet', 11, 12, ST_GeomFromText('LINESTRING(-90 -60, -90 -90)', 4326)); SELECT ST_AddLink('testnet', 4, 11, ST_GeomFromText('LINESTRING(-180 -30, -90 -60)', 4326)); SELECT ST_AddLink('testnet', 5, 12, ST_GeomFromText('LINESTRING(-180 -60, -90 -90)', 4326)); SELECT ST_AddLink('testnet', 9, 15, ST_GeomFromText('LINESTRING(-90 0, -30 0)', 4326)); SELECT ST_AddLink('testnet', 15, 14, ST_GeomFromText('LINESTRING(-30 0, 0 0)', 4326)); SELECT ST_AddLink('testnet', 14, 13, ST_GeomFromText('LINESTRING(0 0, 30 0)', 4326)); SELECT ST_AddLink('testnet', 13, 20, ST_GeomFromText('LINESTRING(30 0, 180 0)', 4326)); SELECT ST_AddLink('testnet', 17, 19, ST_GeomFromText('LINESTRING(30 30, 180 30)', 4326)); SELECT ST_AddLink('testnet', 14, 17, ST_GeomFromText('LINESTRING(0 0, 30 30)', 4326)); SELECT ST_AddLink('testnet', 10, 15, ST_GeomFromText('LINESTRING(-90 -30, -30 0)', 4326)); SELECT ST_AddLink('testnet', 12, 14, ST_GeomFromText('LINESTRING(-90 -90, 0 0)', 4326)); SELECT ST_AddLink('testnet', 16, 17, ST_GeomFromText('LINESTRING(30 90, 30 30)', 4326)); SELECT ST_AddLink('testnet', 16, 18, ST_GeomFromText('LINESTRING(30 90, 180 90)', 4326)); SELECT ST_AddLink('testnet', 19, 18, ST_GeomFromText('LINESTRING(180 30, 180 90)', 4326)); SELECT ST_AddLink('testnet', 19, 20, ST_GeomFromText('LINESTRING(180 30, 180 0)', 4326));

We'll now assign a modified geometry to Link-6, Link14 and Link-15: SELECT ST_ChangeLinkGeom('testnet', 6, ST_GeomFromText('LINESTRING(-180 60, -135 60, -135 30, -90 30)', 4326)); SELECT ST_ChangeLinkGeom('testnet', 14, ST_GeomFromText('LINESTRING(-180 -30, -110 -30, -110 -60, -90 -60)', 4326)); SELECT ST_ChangeLinkGeom('testnet', 15, ST_GeomFromText('LINESTRING(-180 -60, -120 -60, -90 -90)', 4326)); Now we'll split Link-20 by inserting a new Node (Node-21). We've called a "mod" operator, and consequently Link-20 will be modified and Link-28 will be inserted: SELECT ST_ModGeoLinkSplit('testnet', 20, MakePoint(150 ,30, 4326)); We'll go to split Link-19 so Node-22 will be inserted. This time we've called a "new" operator and consequently Link-19 has been removed and replaced by Link-29 and Link-30: SELECT ST_NewGeoLinkSplit('testnet', 19, MakePoint(150 ,0, 4326)); Finally we'll add a new Link (Link-31) connecting Node-21 to Node-22: SELECT ST_AddLink('testnet', 21, 22, ST_GeomFromText('LINESTRING(150 30, 150 0)', 4326));

Now we'll merge (aka heal) Link-18 and Link-29 and consequently the Node shared by both them (Node-13) will be removed. We've called a "new" operator, so both Link-18 and Link-29 will be removed and replaced by Link-32: SELECT ST_NewLinkHeal('testnet', 18, 29); We'll now remove Link-22; the we'll heal Link-11 and Link-12 so to remove Node-10. This time too we've called a "new" operator, so both Link-11 and Link-12 will be removed and replaced by Link-33: SELECT ST_RemoveLink('testnet', 22); SELECT ST_NewLinkHeal('testnet', 11, 12); Finally we'll heal Link-16 and Link-17 so to remove Node-15. Anyway this time we've called a "mod" operator, so Link-16 will be modified and Link-17 will be removed: SELECT ST_ModLinkHeal('testnet', 16, 17);

Now we'll remove both Link-27 and Link-30. This action will free Node-20 from any Link reference; in other words now Node-20 is once again an Isolated Node, and we can safely remove it: SELECT ST_RemoveLink('testnet', 27); SELECT ST_RemoveLink('testnet', 30); SELECT ST_RemIsoNetNode('testnet', 20); As a last step concluding this tutorial we can now check our Network for consistency: SELECT ST_ValidSpatialNet('testnet'); You can examine the corresponding diagnostic report stored in the temporary DB table named TEMP.testnet_validate_spatialnet: SELECT * FROM TEMP.testnet_validate_spatialnet; As you can easily check, the table is completely empty, and this means that your Network is fully consistent.

back to index