2

A classic data structure for describing the topology of a polygon layer is the polygon-arc table and left-right polygon table as described in the ArcGIS documentation and the following illustration from the same link:

polygon arc table

Basically, for each polygon, its list of arcs is stored (polygon-arc table); and for each arc, the two incident polygons on its left and on its right are stored (left-right polygon table).

My questions are:

Given a input polygon table, does PostGIS (as of 3.x) have functions to compute the polygon-arc table and left-right polygon table as two relational tables (plus maybe a third table for arc geometry)?

If not, how to write SQL statements/functions to compute the two tables?

Here I am assuming that the polygon layer is digitized in a topologically consistent manner (e.g. with snapping options in modern GISes).

Note: I have read about the PostGIS topology extension. But it seems to be different. It seems to be more complex than the polygon-arc table in that 1) it involves multiple tables and requires joining these tables, 2) that the examples I've seen are based on building topology for roads, not spatial tessellation, and 3) I am not sure whether PostGIS topology avoids the duplicate storage of common boundaries between polygons as the polygon-arc table does.

I also looked at other open source solutions such as ogr/gdal, and it does not seem to support writing coverage files.

4
  • ArcGIS has a command to do this, but even ArcGIS uses whole-ring polygon topology now (first shapefile, then SDE, then other geodatabase flavors)
    – Vince
    Dec 16, 2022 at 4:01
  • Since that's a compound set of geometric predicate operations at its core, PostGIS sure has the capability - given that you tell it what to do. However, I would suggest to either use ArcGIS in conjunction with PostGIS to utilize interoperability with ESRI's topology concepts, or use tools tailored to the PostGIS environment, namely its topology extension.
    – geozelot
    Dec 16, 2022 at 8:56
  • Not clear what you are looking for. An alternative to PostGIS topology? How to use PostGIS topology? Creating a PostGIS topology and adding geometry to it creates and initializes a set of tables (edge_data, faces, etc.) that contain the information for the arc_table you are looking for.
    – hgb
    Dec 18, 2022 at 21:54
  • @hgb. As described in the question, what I am looking for is a function to convert a polygon layer to the polygon coverage format . If PostGIS topology has a function for that, then it's an answer. If one needs to decompose polygons to boundary arcs manually, then no.
    – tinlyx
    Dec 18, 2022 at 22:05

2 Answers 2

3

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.

View of topology created by inserting polygons

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().

2
  • Thanks for your input. I am specifically looking for an automatic way to convert a polygon layer to the traditional coverage format, in which polygons are decomposed into a sequence of arcs. The coordinates of each arc should be stored only once. If PostGIS topology extension can be used to convert, please elaborate. Otherwise, this is related, but not what I am looking for.
    – tinlyx
    Dec 19, 2022 at 21:26
  • I read about PostGIS topology documentation a few times over the years, but I am under the impression that it couldn't convert a polygon layer to polygon-arc table out of the box. But I could be wrong.
    – tinlyx
    Dec 19, 2022 at 21:28
1

It seems that there is some tickets around adding polygon coverage support in GEOS, currently targeted in the GEOS 3.12.0 milestone. Also mentioned in the ticket is this GEOS commit:

Adds the package geos::coverage containing classes for operating on Polygonal Coverages.

Hopefully, this will show up eventually in PostGIS in the near future.

5
  • 1
    Correct, we are working on adding support for Simple Polygonal Coverages to PostGIS. But note that these will work on sets of polygons, not an arc-node topology format.
    – dr_jts
    Dec 18, 2022 at 21:41
  • @dr_jts Thanks for the great work. Will there be support for identifying common polgyon boundaries and then building the polygon arc table? I think it would be very useful to expose/export those tables for the end users.
    – tinlyx
    Dec 18, 2022 at 21:54
  • 1
    that's not in the current plan, I'm afraid. That kind of processing is what the PostGIS Topology API is designed for.
    – dr_jts
    Dec 19, 2022 at 18:38
  • "hope" is an answer?
    – hgb
    Dec 19, 2022 at 19:08
  • @hgb I am trying to find if an existing solution exists. The polygonal coverage support in JTS and the port to GEOS is the closest I could find. The answer could be a "no, there is no such support at present". But the committed code in GEOS is probably better than a negative answer. If you can show how to convert a polygon table to polygon-arc table with PostGIS topology extension, I'd be happy to accept that as an answer.
    – tinlyx
    Dec 19, 2022 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.