Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

Topology-Geometry

A topology describes a planar graph of nodes, edges and faces. Each edge runs from a start node to an end node along a curve. Faces are the areas the edges bound, and the universal face, face 0, is everything outside them. The graph stays consistent: no two nodes coincide, no edge crosses another or passes through a node, and each edge knows its faces and the next edge round each of them. The routines of SQL/MM Spatial §11 build a topology and edit it, keeping it so.

The examples on this page are run in order, by the user ANN. ANN has a table R that takes the values of the functions that change a topology:

CREATE SCHEMA AUTHORIZATION ANN;
CREATE TABLE R (N INTEGER);
OK
OK

A topology's schema

CALL ST_INITTOPOGEO(name) makes a topology. It creates a schema of that name, spelled exactly as given, which the caller owns. The schema holds three views, ST_NODE, ST_EDGE and ST_FACE, and under them three base tables, ST_TOPO_NODE, ST_TOPO_EDGE and ST_TOPO_FACE, which hold the rows. ST_FACE starts with the universal face. Like every definition, it is undone if the transaction rolls back:

CALL ST_INITTOPOGEO('Field');
SELECT FACE_ID, MBR FROM "Field".ST_FACE;
OK
      FACE_ID | MBR
--------------+--------------------------------------------------------------
            0 | NULL
(1 row)
View Columns
ST_NODE NODE_ID INTEGER, GEOMETRY ST_POINT, CONTAINING_FACE INTEGER (the face of an isolated node, else NULL)
ST_EDGE EDGE_ID, START_NODE, END_NODE, NEXT_LEFT_EDGE, NEXT_RIGHT_EDGE, LEFT_FACE, RIGHT_FACE INTEGER, GEOMETRY ST_CURVE
ST_FACE FACE_ID INTEGER, MBR ST_POLYGON (the face's bounding rectangle, NULL for face 0)

NEXT_LEFT_EDGE is the next edge counterclockwise round the face on the edge's left, and NEXT_RIGHT_EDGE the same round the face on its right. Either is negative when that edge is walked from its end to its start.

A user who may SELECT from the three views may edit the topology with its routines. The routines read the views as that user and write the base tables as the schema's owner.

Routines

Routine Kind What it does
ST_ADDISONODE(t, face, point) function Adds an isolated node in the face it is within. A NULL face takes that face. Returns the node's id
ST_MOVEISONODE(t, node, point) procedure Moves an isolated node
ST_REMISONODE(t, node) procedure Removes an isolated node
ST_ADDISOEDGE(t, start, end, curve) function Adds an edge between two isolated nodes of one face. Returns its id
ST_CHANGEEDGEGEOM(t, edge, curve) procedure Gives an edge another curve between its nodes
ST_REMISOEDGE(t, edge) procedure Removes an edge no other edge touches, isolating its nodes
ST_NEWEDGESSPLIT(t, edge, point) function Replaces an edge with two new ones that meet at a new node. Returns the node
ST_MODEDGESPLIT(t, edge, point) function Splits an edge at a new node, keeping it as the first part. Returns the node
ST_NEWEDGEHEAL(t, edge1, edge2) function Replaces two edges meeting at a node no other edge touches with one new edge. Returns it
ST_MODEDGEHEAL(t, edge1, edge2) procedure Heals two edges into the first
ST_ADDEDGENEWFACES(t, start, end, curve) function Adds an edge. A face it splits is replaced by two new faces. Returns the edge
ST_ADDEDGEMODFACE(t, start, end, curve) function Adds an edge. A face it splits keeps its id on one side and a new face is made on the other. Returns the edge
ST_REMEDGENEWFACE(t, edge) function Removes an edge. The two faces it separated become a new face, whose id is returned, NULL if none is made
ST_REMEDGEMODFACE(t, edge) procedure Removes an edge. The face on its left absorbs the one on its right
ST_GETFACEEDGES(t, face) table function The signed edges round a face, counterclockwise, as rows (SEQUENCE, EDGE)
ST_GETFACEGEOMETRY(t, face) function The polygon of a face
ST_INITTOPOGEO(t) procedure Makes a topology
ST_CREATETOPOGEO(t, collection) procedure Fills an empty topology from a geometry collection
ST_VALIDATETOPOGEO(t) table function The inconsistencies of a topology, as rows (ERROR, PRIMITIVE1, PRIMITIVE2)

The procedures are called by CALL. The functions that change a topology may modify SQL-data, so they are called where such a function may be: in a row of an INSERT's VALUES, a SET clause, or a CALL's argument. The table functions are called as TABLE(f(args)) in a FROM clause. ST_GETFACEGEOMETRY is called in any value expression. The name of the topology given to these three is a literal or a parameter, as the views they read are found when the statement is prepared.

Building a topology

Four isolated nodes, then four edges round them, close a ring. The ring makes face 1. R holds the four nodes' identifiers, then the four edges':

INSERT INTO R VALUES (ST_ADDISONODE('Field', CAST(NULL AS INTEGER), ST_POINT(0, 0)));
INSERT INTO R VALUES (ST_ADDISONODE('Field', CAST(NULL AS INTEGER), ST_POINT(10, 0)));
INSERT INTO R VALUES (ST_ADDISONODE('Field', CAST(NULL AS INTEGER), ST_POINT(10, 10)));
INSERT INTO R VALUES (ST_ADDISONODE('Field', CAST(NULL AS INTEGER), ST_POINT(0, 10)));
INSERT INTO R VALUES (ST_ADDEDGEMODFACE('Field', 1, 2, TREAT(ST_GEOMFROMTEXT('LINESTRING (0 0, 10 0)') AS ST_CURVE)));
INSERT INTO R VALUES (ST_ADDEDGEMODFACE('Field', 2, 3, TREAT(ST_GEOMFROMTEXT('LINESTRING (10 0, 10 10)') AS ST_CURVE)));
INSERT INTO R VALUES (ST_ADDEDGEMODFACE('Field', 3, 4, TREAT(ST_GEOMFROMTEXT('LINESTRING (10 10, 0 10)') AS ST_CURVE)));
INSERT INTO R VALUES (ST_ADDEDGEMODFACE('Field', 4, 1, TREAT(ST_GEOMFROMTEXT('LINESTRING (0 10, 0 0)') AS ST_CURVE)));
SELECT N FROM R;
SELECT EDGE_ID, START_NODE, END_NODE, NEXT_LEFT_EDGE, NEXT_RIGHT_EDGE, LEFT_FACE, RIGHT_FACE
    FROM "Field".ST_EDGE ORDER BY EDGE_ID;
OK
OK
OK
OK
OK
OK
OK
OK
            N
--------------
            1
            2
            3
            4
            1
            2
            3
            4
(8 rows)
      EDGE_ID |   START_NODE |     END_NODE | NEXT_LEFT_EDGE | NEXT_RIGHT_EDGE |    LEFT_FACE |   RIGHT_FACE
--------------+--------------+--------------+----------------+-----------------+--------------+--------------
            1 |            1 |            2 |              2 |              -4 |            1 |            0
            2 |            2 |            3 |              3 |              -1 |            1 |            0
            3 |            3 |            4 |              4 |              -2 |            1 |            0
            4 |            4 |            1 |              1 |              -3 |            1 |            0
(4 rows)

An edge across the face splits it. ST_ADDEDGENEWFACES replaces face 1 with two new faces:

INSERT INTO R VALUES (ST_ADDEDGENEWFACES('Field', 1, 3, TREAT(ST_GEOMFROMTEXT('LINESTRING (0 0, 10 10)') AS ST_CURVE)));
SELECT FACE_ID, ST_ASTEXT(MBR) AS MBR FROM "Field".ST_FACE ORDER BY FACE_ID;
SELECT SEQUENCE, EDGE FROM TABLE(ST_GETFACEEDGES('Field', 2)) AS E;
VALUES ST_ASTEXT(ST_GETFACEGEOMETRY('Field', 3));
OK
      FACE_ID | MBR
--------------+--------------------------------------------------------------
            0 | NULL
            2 | POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))
            3 | POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))
(3 rows)
     SEQUENCE |         EDGE
--------------+--------------
            1 |            3
            2 |            4
            3 |            5
(3 rows)

--------------------------------------------------------------
 POLYGON ((0 0, 10 0, 10 10, 0 0))
(1 row)

Editing a topology

A split adds a node on an edge, and a heal takes it away again. Removing the diagonal edge heals the two faces into one:

INSERT INTO R VALUES (ST_MODEDGESPLIT('Field', 1, ST_POINT(4, 0)));
SELECT EDGE_ID, START_NODE, END_NODE, ST_ASTEXT(GEOMETRY) AS GEOMETRY FROM "Field".ST_EDGE WHERE EDGE_ID IN (1, 6);
CALL ST_MODEDGEHEAL('Field', 1, 6);
CALL ST_REMEDGEMODFACE('Field', 5);
SELECT FACE_ID FROM "Field".ST_FACE ORDER BY FACE_ID;
OK
      EDGE_ID |   START_NODE |     END_NODE | GEOMETRY
--------------+--------------+--------------+--------------------------------------------------------------
            1 |            1 |            5 | LINESTRING (0 0, 4 0)
            6 |            5 |            2 | LINESTRING (4 0, 10 0)
(2 rows)
OK
OK
      FACE_ID
--------------
            0
            2
(2 rows)

ST_VALIDATETOPOGEO lists what is inconsistent, and here finds nothing. COMMIT keeps the topology:

SELECT * FROM TABLE(ST_VALIDATETOPOGEO('Field')) AS V;
COMMIT;
 ERROR                          |   PRIMITIVE1 |   PRIMITIVE2
--------------------------------+--------------+--------------
(0 rows)
OK

Building from geometry

ST_CREATETOPOGEO fills a topology that has only its universal face. It nodes the collection's points, curves and polygon rings, splitting curves where they cross or touch, so two crossing lines meet at a new node. A polygon's ring bounds a face, and a point becomes an isolated node:

CALL ST_INITTOPOGEO('Roads');
CALL ST_CREATETOPOGEO('Roads', TREAT(ST_GEOMFROMTEXT(
    'GEOMETRYCOLLECTION (LINESTRING (0 0, 10 10), LINESTRING (0 10, 10 0), POLYGON ((20 0, 30 0, 30 10, 20 10, 20 0)), POINT (25 5))')
    AS ST_GEOMCOLLECTION));
SELECT NODE_ID, ST_ASTEXT(GEOMETRY) AS GEOMETRY, CONTAINING_FACE FROM "Roads".ST_NODE ORDER BY NODE_ID;
SELECT COUNT(*) AS EDGES FROM "Roads".ST_EDGE;
OK
OK
      NODE_ID | GEOMETRY                                                     | CONTAINING_FACE
--------------+--------------------------------------------------------------+-----------------
            1 | POINT (0 0)                                                  |            NULL
            2 | POINT (0 10)                                                 |            NULL
            3 | POINT (5 5)                                                  |            NULL
            4 | POINT (10 0)                                                 |            NULL
            5 | POINT (10 10)                                                |            NULL
            6 | POINT (20 0)                                                 |            NULL
            7 | POINT (25 5)                                                 |               1
(7 rows)
                EDGES
----------------------
                    5
(1 row)

Errors

Each routine checks its arguments in the order the standard gives and raises the SQLSTATE of its first failure. The topology is left as it was:

INSERT INTO R VALUES (ST_ADDISONODE('Field', CAST(NULL AS INTEGER), ST_POINT(0, 0)));
CALL ST_REMISOEDGE('Field', 99);
CALL ST_INITTOPOGEO('Field');
SELECT * FROM TABLE(ST_VALIDATETOPOGEO('Nowhere')) AS V;
turborel: SQLCODE -301 (SQLSTATE 2FF28): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §11.3.1 Description 2e i] node 1 is there
turborel: SQLCODE -301 (SQLSTATE 2FF43): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §11.3.7 Description 2e] no edge 99
turborel: SQLCODE -301 (SQLSTATE 2FF56): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §11.3.17 Description 2b] schema "Field" exists
turborel: SQLCODE -301 (SQLSTATE 2FF62): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §11.3 Description 2b] table Nowhere.ST_NODE is not defined (at character 14)
SQLSTATE When
2FF03 An argument is NULL
2FF62 The name is empty, longer than 128 characters, or names no topology
2FF63 The user may not SELECT from the topology's ST_NODE
2FF56 ST_INITTOPOGEO names a schema that exists
2FF47, 2FF48 ST_CREATETOPOGEO names no schema, or a schema without one of the views
2FF42, 2FF61 ST_CREATETOPOGEO's topology is not empty, or its one face is not face 0
2FF43, 2FF44, 2FF46 An edge, face or node does not exist
2FF27, 2FF28 An edge or node would coincide with another
2FF29 A curve is not simple
2FF30, 2FF34, 2FF35, 2FF36 A curve or point would cross an edge or node, or meet an edge
2FF33, 2FF57 A curve does not end or start at its node
2FF37, 2FF51 A curve or point is not within the face
2FF39 ST_ADDISOEDGE's nodes are in different faces
2FF40, 2FF52 Two edges to heal share no node, or another edge ends at it
2FF50 A node or edge to remove or move is not isolated
2FF54 The point to split at is not on the edge
2FF60 ST_GETFACEGEOMETRY is given face 0
2FF32 ST_VALIDATETOPOGEO's topology is empty
2FF10, 2FF25 Geometries of different SRIDs or coordinate dimensions are mixed

Standard

The views, the routines and their exceptions are those of ISO/IEC 13249-3:2016, SQL/MM Spatial, §11 and §22. The base tables under the views are Turborel's own, as §11.2 gives its tables as a model only. The readings taken of the text are listed in CONFORMANCE.md.

See also

Spatial Data, CALL