Topology-Network: ISO SQL Functions

ISO-defined SQL functions supporting Topology-Network

The SQL/MM specification defines a standard set of SQL functions specifically intended for manipulating a Topology-Network and mainly oriented toward editing and validation tasks.
As a general design principle all these SQL function will immediately raise an exception whenever an error would arise.

*NODE* A Node must have an unique integer ID. In a Network of the Spatial type all Nodes must have a Point geometry. In a Network of the Logical type all Nodes must have a NULL geometry. An isolated Node is a Node not referenced by any Link.
*ISO SQL functions on isolated NODE* only**
ST_AddIsoNetNode	ST_AddIsoNetNode( net-name , face-id , point-geom) Inserts a row into the <network>_NODE corresponding to an isolated Node, returning the generated unique ID.
ST_RemIsoNetNode	ST_RemIsoNetNode( net-name , node-id ) Removes the row for the Node identified by its ID.	the Node to be removed must be isolated.
ST_MoveIsoNetNode	ST_MoveIsoNetNode( net-name , node-id , point-geom ) Updates the geometry for the Node identified by its ID.	the Node to be moved must by isolated.

*LINK* A Link must have an unique integer ID. A Link must have a start Node and an end Node identified by their node-ids. In a Network of the Spatial type all Links must have a Curve geometry. In a Network of the Logical type all Links must have a NULL geometry.
*ISO SQL functions on LINK* only**
ST_AddLink	ST_AddLink( net-name , start-node-id , end-node-id, curve-geom) Inserts a row into the <network>_LINK table, returning the generated unique ID.	start-node-id and end-node-id must reference existing Nodes. Only in the case of a Spatial Network: the first vertex of curve-geom must equal the geometry of start Node and the last vertex of curve-geom must equal the geometry of end Node.
ST_RemoveLink	ST_RemoveLink( net-name , link-id ) Removes the row for the Link identified by its ID. The start and end Nodes are not removed from the Network.
ST_ChangeLinkGeom	ST_ChangeLinkGeom( net-name , link-id , curve-geom ) Updates the geometry for the Link identified by its ID.	Only in the case of a Spatial Network: the first vertex of curve-geom must equal the geometry of start Node and the last vertex of curve-geom must equal the geometry of end Node.
ISO SQL functions on NODE and LINK
ST_NewGeoLinkSplit	ST_NewGeoLinkSplit( net-name , link-id , point-geom ) Splits a Link by creating a new Node along an existing Link, deleting the original Link and replacing it with two new Links. Returns the generated unique Node ID. Both new Links will have the same directions as the Link being split.	This function only applies to a Network of the Spatial type. link-id must identify an existing Link. point-geom must intersect the Link geometry.
ST_ModGeoLinkSplit	ST_ModGeoLinkSplit( net-name , link-id , point-geom ) Splits a Link by creating a new Node along an existing Link, modifying the original Link and adding a new Link. Returns the generated unique Node ID. Both the modified and the added Links will have the same directions as the Link being split.	This function only applies to a Network of the Spatial type. edge-id must identify an existing Link. point-geom must intersect the Link geometry.
ST_NewLogLinkSplit	ST_NewLogLinkSplit( net-name , link-id ) Splits a Link by creating a new Node along an existing Link, deleting the original Link and replacing it with two new Links. Returns the generated unique Node ID. Both new Links will have the same directions as the Link being split.	This function only applies to a Network of the Logical type. link-id must identify an existing Link.
ST_ModLogLinkSplit	ST_ModLogLinkSplit( net-name , link-id ) Splits a Link by creating a new Node along an existing Link, modifying the original Link and adding a new Link. Returns the generated unique Node ID. Both the modified and the added Links will have the same directions as the Link being split.	This function only applies to a Network of the Logical type. link-id must identify an existing Link.
ST_NewLinkHeal	ST_NewLinkHeal( net-name , link-id1 , link-id2 ) Heals (merges) two Links by deleting the Node connecting them, deleting both Links and replacing them with a new Link whose direction is the same as the first Link provided. Returns the generated unique Link ID.	Both link-id1 and link-id2 must identify existing Links. The two Links must share a common Node. No additional Links must share the common Node, that is being deleted.
ST_ModLinkHeal	ST_ModLinkHeal( net-name , link-id1 , link-id2 ) Heals two Links by deleting the Node connecting them, modifying the first Link provided, and deleting the second Link.	Both link-id1 and link-id2 must identify existing Links. The two Links must share a common Node. No additional Links must share the common Node.

miscellaneous ISO SQL functions
ST_InitTopoNet	ST_InitTopoNet( net-name ) Creates a new Network and all related tables.	This SQL function is required by ISO SQL/MM specifications, the usage of which is strongly discouraged in SpatiaLite. CreateNetwork() represents a more flexible, complete, replacement.
ST_SpatNetFromGeom	ST_SpatNetFromGeom( net-name , geom-collection ) Populates a full Network from the geometry values in a collection.	This SQL function is required by ISO SQL/MM specifications, anyway its usage is strongly discouraged in SpatiaLite. TopoNet_FromGeoTable() represents a more flexible, complete, replacement.
ST_SpatNetFromTGeo	ST_SpatNetFromTGeo( net-name , topo-name ) Populates a full Spatial Network from a Topology-Geometry. A Topo-Net Node will be created for each Topo-Geo Node. A Topo-Net Link will be created for each Topo-Geo Edge. A Spatial Network will result, with Nodes and Links having geometry values obtained from their corresponding Topo-Geo primitives.	This function only applies to an existing Network of the Spatial type. The Spatial Network must not contain any row. The Topology-Geometry must exist.
ST_LogiNetFromTGeo	ST_LogiNetFromTGeo( net-name , topo-name ) Populates a full Logical Network from a Topology-Geometry. A Topo-Net Node will be created for each Topo-Geo Node. A Topo-Net Link will be created for each Topo-Geo Edge. A Logical Network will result, with Nodes and Links having geometry values set to the NULL value.	This function only applies to an existing Network of the Logical type. The Logical Network must not contain any row. The Topology-Geometry must exist.
ST_ValidSpatialNetwork	ST_ValidSpatialNetwork( net-name ) Returns a table containing possible spatial network inconsistencies. The temporary table created and populated by this function will be named TEMP.<network>_valid_spatialnet	This function only applies to an existing Network of the Spatial type.
ST_ValidLogicalNetwork	ST_ValidLogicalNetwork( net-name ) Returns a table containing possible logical network inconsistencies. The temporary table created and populated by this function will be named TEMP.<network>_valid_logicalnet	This function only applies to an existing Network of the Logical type.

non-ISO general utility SQL functions supporting Topology-Network

We'll now introduce few general utility SQL functions not defined by the ISO SQL/MM standard specification that are very useful when handling SpatiaLite's own Networks.

non-ISO general utility SQL functions on Topology-Network
GetLastNetworkException	GetLastNetwprlException( net-name ) Will return a text message corresponding to most recent exception raised by the given Network. May return NULL if no exception is currently pending or if the Network does not exist.
CreateNetwork	CreateNetwork( net-name ) CreateNetwork( net-name , is_spatial ) CreateNetwork( net-name , is_spatial , srid ) CreateNetwork( net-name , is_spatial , srid , has_z ) CreateNetwork( net-name , is_spatial , srid , has_z , allow_coincident_node_link ) Will create a new Network and will create and initialize all related DB objects. Will return 1 (TRUE) on success, 0 (FALSE) on failure and -1 on invalid arguments.	net-name a symbolic name uniquely identifying the Network. is_spatial boolean flag; if set to 0 (FALSE) a Logical Network will be created.. In any other case a Spatial Network will be assumed. (default value: 0). srid the SRID expected by all geometries in the Network. (default value -1). has_z boolean flag; if set to 0 (FALSE) all geometries in the Network will be expected to have of 2D XY dimensions. In any other case 3D XYZ dimensions will be assumed. (default value: 0). allow_coincident_node_link boolean flag; if set to 0 (FALSE) stricter topological rules will be enforced and all Node-on-Node, Node-on-Link and Link-on-Node intersections will be always forbidden. In any other case the standard rules dictated by ISO SQL MM will be applied. (default value: 1).
DropNetwork	DropNetwork( net-name ) Will completely destroy a Network and will drop all related DB objects. Will return 1 (TRUE) on success, 0 (FALSE) on failure and -1 on invalid arguments.	topo-name symbolic name of the Topology to be dropped.
TopoNet_Clone	TopoNet_Clone( net-name , new-net-name ) Will create a new Network of exactly the same type of the original one, and will copy all Network primitives from the original Network into the cloned one. Will return 1 (TRUE) on success, 0 (FALSE) on failure and -1 on invalid arguments.	net-name symbolic name of the original Network to be cloned. new-net-name symbolic name of the cloned Network to be created.

back to index