The topology extension of PostGIS has facilities to create topologies and to add linestrings and polygons to the topology. When created, topology is created as a set of tables in a new schema: node, edge_data, face, and relation. Then you can start to add linestrings and polygons to the topology (using, for example,TopoGeo_AddLineString() and TopoGeo_AddPolygon()). These functions do the work of creating nodes, edges and faces in the topology as feature geometries are added and the intersections are detected.
PostGIS topology supports topogeometries. These are geometries composed out of topology primitives (edges, nodes, faces) or other topogeometries. You can think, in the simplest case, of a topogeometry as the list of identifiers of shareable topology elements that constitute that feature. For example, a road centerline topogeometry would be a list of edges that might also be used as parts of faces forming neighbourhoods in a different table defining area topogeometries.
PostGIS in Action, 3rd Edition, Chapter 13, by Leo S. Hsu and Regina O. Obe, provides a good introductory overview of PostGIS topology, first using a simplified topology of Colorado divided by interstate highways and then using the neighbourhoods of Victoria, B.C. to demonstrate the ideas. (I'm not affiliated with the authors or the publisher - I have used it in a course)
QGIS provides a handy viewer for PostGIS topology in its DB Manager interface (Schema -> TopoViewer).
Update - example
All of the work below was done using psql, except for the use of QGIS to create the topology image (TopoViewer).
Create a pair of overlapping geometries.
-- might need to disconnect / reconnect for postgis and topology paths to be picked up properly
-- after creating these extensions and updating the search_path
CREATE SCHEMA postgis;
CREATE EXTENSION postgis SCHEMA postgis;
ALTER DATABASE set search_path=contrib,public,postgis,topology;
CREATE EXTENSION postgis_topology;
-- create table to hold overlapping polygons and then insert them
CREATE TABLE poly (
id serial PRIMARY KEY,
geom Geometry(POLYGON, 32618)
);
INSERT INTO poly (geom) VALUES
(ST_Polygon('LINESTRING(75 29, 77 29, 77 31, 75 31, 75 29)'::geometry, 32618)),
(ST_Polygon('LINESTRING(76 30, 78 30, 78 32, 76 32, 76 30)'::geometry, 32618));
Now create a new topology and insert the geometries.
Results in a new schema containing the required tables edge_data, face, node and relation.
-- create a topology and push geomtries from poly into it
SELECT CreateTopology('polytopo', 32618);
SELECT
id,
TopoGeo_AddPolygon('polytopo', geom) As face_id
FROM (
SELECT id, geom
FROM poly
) As f;
Inserting these polygons into the topology will result in updates to the topology tables. Using the QGIS TopoViewer and displaying the directed edges shows the following structure. There are three marked faces (1, 2 and 3) although there is actually a fourth - the universal face (0) not shown. Edges 1 through 6 are labelled and the grey circles are the topology nodes.
List edges for faces.
PostGIS topology includes constructor functions and topology accessors. To list the edge IDs for a specific face, use ST_GetFaceEdges(). This dumps all edges for a given face (quoting from the documentation):
Enumeration of each ring edges start from the edge with smallest
identifier. Order of edges follows a left-hand-rule (bound face is on
the left of each directed edge).
The following query lists all edges for all faces. Here each edge is its own row. I'll aggregate the edges for each list into an ordered array below.
-- show ordered edges for faces
WITH faces AS (
SELECT face_id FROM polytopo.face
)
SELECT face_id,
(ST_GetFaceEdges('polytopo', face_id)).*
FROM faces
ORDER BY face_id, sequence;
The result of the above is shown below. Negative edge IDs indicate that the ordered traversal would go against the direction of the edge to keep the bounded area to the left of the edge. For example, the traversal of the universal face (0) runs against the edge direction for all arcs because the face is outside the drawn faces and therefore left of the outer arcs only when traversing them in reverse.
face_id | sequence | edge
---------+----------+------
0 | 1 | -1
0 | 2 | -2
0 | 3 | -6
1 | 1 | 1
1 | 2 | -5
1 | 3 | -3
1 | 4 | 2
2 | 1 | 3
2 | 2 | 5
2 | 3 | 4
3 | 1 | -4
3 | 2 | 6
(12 rows)
List edges for each face as an ordered array (single row)
I am not sure if this is more useful than the query above. Depends what you are trying to do. But this is more similar to the polygon-arc table in the original question so I show it.
-- show ordered edges for faces, grouped into ordered arrays
WITH faces AS (
SELECT face_id FROM polytopo.face
), oedges AS (
SELECT face_id,
(ST_GetFaceEdges('polytopo', face_id)).*
FROM faces
)
SELECT face_id, array_agg(edge) AS edge_ids
FROM oedges
GROUP BY face_id
ORDER BY face_id;
The result form the above query is shown below. The logic of the ordered lists is the same as for the previous query. I've removed the sequence number from the display while creating an ordered array of edge IDs.
face_id | edge_ids
---------+-------------
0 | {-1,-2,-6}
1 | {1,-5,-3,2}
2 | {3,5,4}
3 | {-4,6}
(4 rows)
Summary
Within the topology, only the edge_data table contains the boundary geometries. The face table stores only face IDs and minimum bounding boxes. Those face IDs are used in the edge table to relate each edge to the faces bounded by that edge and to store information about sequencing of edges around faces (see the table definition for details). The node table stores point geometries that are, technically, a duplication of the arc endpoint vertices but a reasonable trade-off for efficiency. The relation table relates topology elements, especially edges, to application feature geometries (topogeometries, mentioned earlier) that can be composed from topology elements at the lowest layer or from other topogeometries to create layered relationships within the topology (e.g., "blocks" could be composed from "parcels" that are, in turn, topology faces bounded by topology edges). In the example above, no topogeometries have been created so the relation table is still empty.
As opposed to the creation of face edge lists, shown in the examples above, face boundary geometry can be (re)created using ST_GetFaceGeometry().
