Topology-Geometry: ISO SQL Functions

ISO-defined SQL functions supporting Topology-Geometry

The SQL/MM specification defines a standard set of SQL functions specifically intended for manipulating a Topology-Geometry 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. A Node must have a Point geometry. If a Node is an isolated Node it must have a containing Face identified by its face-id.
*ISO SQL functions on isolated NODE* only**
ST_AddIsoNode	ST_AddIsoNode( topo-name , face-id , point-geom ) Inserts a row into the <topology>_NODE corresponding to an isolated Node, returning the generated unique ID.	face-id must reference the Face intercepted by the Point. If face-id is NULL the appropriate Face will be automatically determined. point-geom must not overlap an existing Node and must not cross an existing Edge.
ST_RemIsoNode	ST_RemIsoNode( topo-name , node-id ) Removes the row for the Node identified by its ID.	the Node to be removed must be isolated.
ST_MoveIsoNode	ST_MoveIsoNode( topo-name , node-id , point-geom ) Updates the geometry for the Node identified by its ID.	the Node to be moved must by isolated. the new location defined by point-geom must not overlap an existing Node and must not cross an existing Edge.

*EDGE* An Edge must have an unique integer ID. An Edge must have a start Node and an end Node identified by their node-ids. An Edge must have a left Face and a right Face identified by their face-ids. An isolated Edge must have its containing Face as both left and right Faces. An Edge must have a next left Face and a next right Face Edges identified by their edge-ids. An isolated Edge must have itself as the next right Face Edge, and must have the the negative value of the Edge ID as the next left Face Edge. An Edge must have a Curve geometry.
*ISO SQL functions on isolated EDGE* only**
ST_AddIsoEdge	ST_AddIsoEdge( topo-name , start-node-id , end-node-id, curve-geom ) Inserts a row into the <topology>_EDGE corresponding to an isolated Edge, returning the generated unique ID. The next right Face Edge ID is set equal to the generated ID; the next left Face Edge ID is set equal to the negative of this value. The left and right Face IDs are set equal to the containing Face ID of the start and end Node.	start-node-id and end-node-id must reference existing Nodes Both are required to be isolated Nodes. The start Node and the end Node must have the same containing Face. 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. curve-geom must be simple (i.e. it must not have self-intersections). curve-geom must not cross an existing Node and must not cross an existing Edge. curve-geom must be fully within the geometry of the containing Face of start Node and end Node.
ST_RemIsoEdge	ST_RemIsoEdge( topo-name , edge-id ) Removes the row for the Edge identified by its ID. The start and end Nodes are not removed from the Topology.	the Edge to be removed must be isolated.
*ISO SQL functions on EDGE* only**
ST_ChangeEdgeGeom	ST_ChangeEdgeGeom( topo-name , edge-id , curve-geom ) Updates the geometry for the Edge identified by its ID.	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. curve-geom must be simple (i.e. it must not have self-intersections). curve-geom must not cross an existing Node and must not cross an existing Edge.
ISO SQL functions on NODE and EDGE
ST_NewEdgesSplit	ST_NewEdgesSplit( topo-name , edge-id , point-geom ) Splits an Edge by creating a new Node along an existing Edge, deleting the original Edge and replacing it with two new Edges. Returns the generated unique Node ID. Both new Edges will have the same directions as the Edge being split.	edge-id must identify an existing Edge. point-geom must intersect the Edge geometry. point-geom must not overlap an existing Node.
ST_ModEdgesSplit	ST_ModEdgesSplit( topo-name , edge-id , point-geom ) Splits an Edge by creating a new Node along an existing Edge, modifying the original Edge and adding a new Edge. Returns the generated unique Node ID. Both the modified and the added Edges will have the same directions as the Edge being split.	edge-id must identify an existing Edge. point-geom must intersect the Edge geometry. point-geom must not overlap an existing Node.
ST_NewEdgeHeal	ST_NewEdgeHeal( topo-name , edge-id1 , edge-id2 ) Heals (merges) two Edges by deleting the Node connecting them, deleting both Edges and replacing them with a new Edge whose direction is the same as the first Edge provided. Returns the generated unique Edge ID.	Both edge-id1 and edge-id2 must identify existing Edges. The two Edges must share a common Node. No additional Edges must share the common Node.
ST_ModEdgeHeal	ST_ModEdgeHeal( topo-name , edge-id1 , edge-id2 ) Heals two Edges by deleting the Node connecting them, modifying the first Edge provided, and deleting the second Edge.	Both edge-id1 and edge-id2 must identify existing Edges. The two Edges must share a common Node. No additional Edges must share the common Node, that is being deleted.

*FACE* A Face must have an unique integer ID. A Face (except the Universal Face) must have a Rectangle geometry corresponding to the MBR (Minimum Bounding Rectangle aka BBOX) of the Face geometry. A topology must always define a Universal Face identifies by face-id = 0; the Universal Face contains everything else in the Topology exterior to all other Faces. There is no geometry associated with the Universal Face.
ISO SQL functions on EDGE and FACE
ST_AddEdgeNewFaces	ST_AddEdgeNewFaces( topo-name , start-node-id , end-node-id , curve-geom ) Adds a new Edge and, if in doing so it splits a Face, deletes the original Face and replaces it with two new Faces. Returns the generated unique Edge ID.	start-node-id and end-node-id must reference existing Nodes. 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. curve-geom must be simple (i.e. it must not have self-intersections). curve-geom must not cross an existing Node and must not cross an existing Edge. start-node-id, end-node-id and curve-geom must not equal an already existing Edge.
ST_AddEdgeModFaces	ST_AddEdgeModFaces( topo-name , start-node-id , end-node-id , curve-geom ) Adds a new Edge and, if in doing so it splits a Face, modifies the original Face and add a new Face. Returns the generated unique Edge ID.	start-node-id and end-node-id must reference existing Nodes. 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. curve-geom must be simple (i.e. it must not have self-intersections). curve-geom must not cross an existing Node and must not cross an existing Edge. start-node-id, end-node-id and curve-geom must not equal an already existing Edge.
ST_RemEdgeNewFace	ST_RemEdgeNewFace( topo-name , edge-id ) Removes an Edge and, if the removed Edge separated two Faces, deletes the original Faces and replaces them with one new Face. Returns the generated unique Face ID. The start and end Nodes of the removed Edge are not removed from the Topology.	The Edge to be removed must exist.
ST_RemEdgeModFace	ST_RemEdgeModFace( topo-name , edge-id ) Removes an Edge and, if the removed Edge separated two Faces, heals the two Faces by modifying one of the Faces and removing the other. The start and end Nodes of the removed Edge are not removed from the Topology.	The Edge to be removed must exist.
ISO SQL functions on FACE
ST_GetFaceGeometry	ST_GetFaceGeometry( topo-name , face-id ) Returns the exact geometry of the Face.	*face-id* must reference an existing Face. *face-id* must not reference the Universal Face (face-id=0).
ST_GetFaceEdges	ST_GetFaceEdges( topo-name , face-id ) Returns a table containing the integer Edge IDs for the Edges which bound the Face, in counterclockwise order. Edge IDs will be negated in the query result if the Face is right of the Edge when looking in the direction of the Edge from start to end Node. The temporary table created and populated by this function will be named TEMP.<topology>_face_edges_temp	*face-id* must reference an existing Face. *face-id* must not reference the Universal Face (face-id=0).

miscellaneous ISO SQL functions
ST_InitTopoGeo	ST_InitTopoGeo( topo-name ) Creates a new Topology and all related tables. Inserts a row in <topology>_FACE representing the Universal Face.	This SQL function is required by ISO SQL/MM specifications, anyway its usage is strongly discouraged in SpatiaLite. CreateTopology() represents a more flexible full replacement.
ST_CreateTopoGeo	ST_CreateTopoGeo( topo-name , geom-collection ) Populates a full Topology from the geometry values in a collection.	This SQL function is required by ISO SQL/MM specifications, the usage of which is strongly discouraged in SpatiaLite. TopoGeo_FromGeoTable() represents a more powerful and flexible, complete, replacement.
ST_ValidateTopoGeo	ST_ValidateTopoGeo( topo-name ) Returns a table containing possible topological inconsistencies. The temporary table created and populated by this function will be named TEMP.<topology>_validate_topogeo	The Topology to be validated must exist.

non-ISO general utility SQL functions supporting Topology-Geometry

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

non-ISO general utility SQL functions on Topology-Geometry
GetLastTopologyException	GetLastTopologyException( topo-name ) Will return a text message corresponding to most recent exception raised by the given Topology. May return NULL if no exception is currently pending or if the Topology does not exist.
CreateTopology	CreateTopology( topo-name ) CreateTopology( topo-name , srid ) CreateTopology( topo-name , srid , tolerance ) CreateTopology( topo-name , srid , tolerance , has_z ) Will create a new Topology and will create and initialize all related DB objects. Will return 1 (TRUE) on success, 0 (FALSE) on failure and -1 on invalid arguments.	topo-name a symbolic name uniquely identifying the Topology. srid the SRID expected by all geometries in the Topology. (default value -1). tolerance standard tolerance/approximation factor for this Topology. (default value: 0.0). has_z boolean flag; if set to 0 (FALSE) all geometries in the Topology will be expected to have 2D XY dimensions. In any other case 3D XYZ dimensions will be assumed. (default value: 0).
DropTopology	DropTopology( topo-name ) Will completely destroy a Topology 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.
TopoGeo_Clone	TopoGeo_Clone( topo-name , new-topo-name ) Will create a new Topology of exactly the same type of the original one, and will copy of Topology primitives from the original Topology into the cloned one. Will return 1 (TRUE) on success, 0 (FALSE) on failure and -1 on invalid arguments.	topo-name symbolic name of the original Topology to be cloned. new-topo-name symbolic name of the cloned Topology to be created.

back to index