D 2015-10-28T16:31:09.125
L toponet-start
P 1316ac45f4f882b1c9391cca8c3d8b62c5511e61
U sandro
W 11084
<table cellspacing="12" width="100%">
<tr><td colspan="2">
<table width="100%" bgcolor="#f0f0f8">
<tr><td align="center">
<h1>Familiarizing with Topology-Network: an elementary 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-start">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=toponet-start">next page</a></td>
</tr></table>
<h3>Tutorial #2: network editing by using basic ISO SQL function</h3>
In this tutorial will quickly explore the ISO SQL/MM main core supporting Topology-Network (namely: Spatial Network).<br>
We'll use very simple geometries (straight segments) so to avoid any possible complexity thus making basically simple following and understanding any step.<br>
You can indifferently use <b>spatialite CLI</b> or <b>spatialite_gui</b> in order to test each SQL statement.<br><br>
<table bgcolor="#ffffe0" cellspacing="8" cellpadding="8">
<tr><td align="center"><b>CAVEAT</b></td></tr>
<tr><td>
Any direct manipulation of the low-level DB tables implementing an ISO Network will almost certainly cause a major disaster.<br>
You are never expected to execute any <b>INSERT</b>, <b>UPDATE</b> or <b>DELETE</b> statement on behalf of such tables.<br>
You always have to call the appropriate ISO SQL functions for any operation targeting a Topology in order to preserve a proper consistency.
</td></tr>
<tr><td>
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:
<verbatim>
SELECT * FROM testnet_node;
SELECT * FROM testnet_link;
</verbatim></td></tr>
<tr><td><u>Please note</u>: the actual layout adopted by these low-level DB tables could eventually change in future versions.<br>
As a general principle any direct interaction with the low-level DB tables should be always carefully avoided even for <b>SELECT</b> queries (use instead the appropriate ISO SQL functions).<br>
Anyway, at least for didactic / debugging purposes, executing some SELECT statement in order to inspect the actual contents of these tables is an absolutely harmless operation.
</td></tr>
</table><br><br><hr>
<table cellspacing="4" cellpadding="6">
<tr><td>
<ul>
<li>
We'll start by creating a brand new ISO Topology-Network (of the Spatial type) named <b>testnet</b>; all network geometries are expected to be located on <b>SRID=4326</b> (aka <i>WGS84 long/lat</i>) and will have <b>2D XY</b> dimensions:
<verbatim>
SELECT CreateNetwork('testnet', 1, 4326, 0, 0);
</verbatim>
</li>
<li>
Now we'll insert a bunch of Isolated Nodes:
<verbatim>
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));
</verbatim>
</li></ul></td>
<td><img src="http://www.gaia-gis.it/gaia-sins/topofigs/net-1.png" alt="net-example-1"></td></tr>
<tr><td colspan="2"><hr></td></tr>
<tr><td>
<ul>
<li>
Then we'll insert a bunch of Links:
<verbatim>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));
</verbatim>
</li>
</ul></td>
<td><img src="http://www.gaia-gis.it/gaia-sins/topofigs/net-2.png" alt="net-example-2"></td></tr>
<tr><td colspan="2"><hr></td></tr>
<tr><td>
<ul>
<li>
We'll now assign a modified geometry to Link-6, Link14 and Link-15:
<verbatim>
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));
</verbatim>
</li>
<li>
Now we'll split Link-20 by inserting a new Node (Node-21).<br>
We've called a <b>"mod"</b> operator, and consequently Link-20 will be modified and Link-28 will be inserted:
<verbatim>
SELECT ST_ModGeoLinkSplit('testnet', 20, MakePoint(150 ,30, 4326));
</verbatim>
</li>
<li>
We'll go to split Link-19 so Node-22 will be inserted.<br>
This time we've called a <b>"new"</b> operator and consequently Link-19 has been removed and replaced by Link-29 and Link-30:
<verbatim>
SELECT ST_NewGeoLinkSplit('testnet', 19, MakePoint(150 ,0, 4326));
</verbatim>
</li>
<li>
Finally we'll add a new Link (Link-31) connecting Node-21 to Node-22:
<verbatim>
SELECT ST_AddLink('testnet', 21, 22, ST_GeomFromText('LINESTRING(150 30, 150 0)', 4326));
</verbatim>
</li>
</ul></td>
<td><img src="http://www.gaia-gis.it/gaia-sins/topofigs/net-3.png" alt="net-example-3"></td></tr>
<tr><td colspan="2"><hr></td></tr>
<tr><td>
<ul>
<li>
Now we'll merge (aka heal) Link-18 and Link-29 and consequently the Node shared by both them (Node-13) will be removed.<br>
We've called a <b>"new"</b> operator, so both Link-18 and Link-29 will be removed and replaced by Link-32:
<verbatim>
SELECT ST_NewLinkHeal('testnet', 18, 29);
</verbatim>
</li>
<li>
We'll now remove Link-22; the we'll heal Link-11 and Link-12 so to remove Node-10.<br>
This time too we've called a <b>"new"</b> operator, so both Link-11 and Link-12 will be removed and replaced by Link-33:
<verbatim>
SELECT ST_RemoveLink('testnet', 22);
SELECT ST_NewLinkHeal('testnet', 11, 12);
</verbatim>
</li>
<li>
Finally we'll heal Link-16 and Link-17 so to remove Node-15.<br>
Anyway this time we've called a <b>"mod"</b> operator, so Link-16 will be modified and Link-17 will be removed:
<verbatim>
SELECT ST_ModLinkHeal('testnet', 16, 17);
</verbatim>
</li>
</ul>
</td>
<td><img src="http://www.gaia-gis.it/gaia-sins/topofigs/net-4.png" alt="net-example-4"></td></tr>
<tr><td colspan="2"><hr></td></tr>
<tr><td>
<ul>
<li>
Now we'll remove both Link-27 and Link-30.<br>
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:
<verbatim>
SELECT ST_RemoveLink('testnet', 27);
SELECT ST_RemoveLink('testnet', 30);
SELECT ST_RemIsoNetNode('testnet', 20);
</verbatim>
</li>
<li>
As a last step concluding this tutorial we can now check our Network for consistency:
<verbatim>
SELECT ST_ValidSpatialNet('testnet');
</verbatim>
You can examine the corresponding diagnostic report stored in the temporary DB table named <b>TEMP.testnet_validate_spatialnet</b>:
<verbatim>
SELECT * FROM TEMP.testnet_validate_spatialnet;
</verbatim>
As you can easily check, the table is completely empty, and this means that your Network is fully consistent.
</li>
</ul>
</td>
<td><img src="http://www.gaia-gis.it/gaia-sins/topofigs/net-5.png" alt="net-example-5"></td></tr>
</table><br><br>
<table width="100%"><tr>
<td width="33%" align="left"><a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=topo-start">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=toponet-start">next page</a></td>
</tr></table>
Z 4ba32b3c0e73638f0521574a295001a7