Chapter 9. Topology
Chapter 9. Topology
- 9.1. Building Faces from Linework
- 9.2. Topology Primitive Tables
- 9.3. Topology Types
- 9.4. Topology Domains
- 9.5. Topology and TopoGeometry Management
- 9.6. Topology Statistics Management
- 9.7. Topology Constructors
- 9.8. Topology Editors
- 9.9. Topology Accessors
- 9.10. Topology Processing
- 9.11. TopoGeometry Constructors
- 9.12. TopoGeometry Editors
- 9.13. TopoGeometry Accessors
- 9.14. TopoGeometry Outputs
- 9.15. Topology Spatial Relationships
- 9.16. Importing and exporting Topologies
The PostGIS Topology types and functions are used to manage topological objects such as faces, edges and nodes.
Sandro Santilli's presentation at PostGIS Day Paris 2011 conference gives a good synopsis of PostGIS Topology and where it is headed Topology with PostGIS 2.0 slide deck .
Vincent Picavet provides a good synopsis and overview of what is Topology, how is it used, and various FOSS4G tools that support it in PostGIS Topology PGConf EU 2012 .
An example of a topologically based GIS database is the US Census Topologically Integrated Geographic Encoding and Referencing System (TIGER) database. If you want to experiment with PostGIS topology and need some data.
The PostGIS topology module has existed for a long time but was not always part of the official documentation. Extensive cleanup removed deprecated functions, fixed known usability issues, documented the features and functions, added new functionality, and improved SQL-MM compliance.
This chapter is the maintained reference for the topology module, including its primitive tables, TopoGeometry type, topology management functions, editing functions, validation functions, and conversion helpers.
All functions and tables associated with this module are installed in a schema called
topology
.
Functions that are defined in SQL/MM standard are prefixed with ST_ and functions specific to PostGIS are not prefixed.
Topology support is built by default and can be disabled by specifying the --without-topology configure option at build time as described in Chapter 2, PostGIS Installation
9.1. Building Faces from Linework
This example builds topology faces from noded linework and associates point attributes with the resulting faces. It uses ST_CreateTopoGeo to populate an empty topology, ValidateTopology to check the result, GetFaceByPoint to locate each attribute point, and ST_GetFaceGeometry to materialize ordinary polygon geometries.
Create an empty topology and populate it with linework that encloses two faces:
CREATE EXTENSION IF NOT EXISTS postgis_topology;
SELECT topology.CreateTopology('topo_example', 3857);
WITH lines(geom) AS (VALUES
('SRID=3857;LINESTRING(0 0, 2 0)'::geometry),
('SRID=3857;LINESTRING(0 1, 2 1)'::geometry),
('SRID=3857;LINESTRING(0 0, 0 1)'::geometry),
('SRID=3857;LINESTRING(1 0, 1 1)'::geometry),
('SRID=3857;LINESTRING(2 0, 2 1)'::geometry)
)
SELECT topology.ST_CreateTopoGeo(
'topo_example',
ST_Collect(geom)
)
FROM lines;
SELECT * FROM topology.ValidateTopology('topo_example');
An empty result from
ValidateTopology
means that no topology errors were found. Attribute points can now
be matched to faces. A face identifier of
0
denotes the universal face, so the point is outside every bounded
face.
WITH labels(name, geom) AS (VALUES
('west', 'SRID=3857;POINT(0.5 0.5)'::geometry),
('east', 'SRID=3857;POINT(1.5 0.5)'::geometry),
('outside', 'SRID=3857;POINT(3 0.5)'::geometry)
),
located AS (
SELECT name,
topology.GetFaceByPoint('topo_example', geom, 0) AS face_id
FROM labels
)
SELECT name,
face_id,
CASE WHEN face_id = 0 THEN NULL
ELSE topology.ST_GetFaceGeometry('topo_example', face_id)
END AS geom
FROM located
ORDER BY name;
Before copying attributes into a face table, check both for points outside bounded faces and for multiple source points assigned to the same face:
WITH labels(name, geom) AS (VALUES
('west', 'SRID=3857;POINT(0.5 0.5)'::geometry),
('east', 'SRID=3857;POINT(1.5 0.5)'::geometry),
('outside', 'SRID=3857;POINT(3 0.5)'::geometry)
),
located AS (
SELECT name,
topology.GetFaceByPoint('topo_example', geom, 0) AS face_id
FROM labels
)
SELECT 'outside face' AS problem,
array_agg(name ORDER BY name) AS labels
FROM located
WHERE face_id = 0
HAVING count(*) > 0
UNION ALL
SELECT 'duplicate face ' || face_id,
array_agg(name ORDER BY name)
FROM located
WHERE face_id != 0
GROUP BY face_id
HAVING count(*) > 1;
Use DropTopology to remove the example topology when it is no longer needed:
SELECT topology.DropTopology('topo_example');
9.2. Topology Primitive Tables
The core primitives of any topology are stored in the
edge_data
,
node
, and
face
tables that live in the schema created by
CreateTopology
. Each row of
edge_data
represents an oriented edge: it records a directed curve from
start_node
to
end_node
together with the identifier of the face encountered on the left of that direction (
left_face
) and the face encountered on the right (
right_face
). The same geometric segment may therefore appear twice—once for each orientation—when it belongs to two faces.
The
next_left_edge
and
next_right_edge
columns complete this orientation information by encoding how to keep walking around a face. They store signed integers whose absolute value is the identifier of the next oriented edge and whose sign determines whether the stored orientation has to be followed as-is or reversed when traversing. Formally, the following rules hold for every edge
e
:
-
abs(next_left_edge)is the identifier of the edge reached by continuing around the face that lies to the left ofe. If the value is positive the walk continues from the end node ofealong the stored orientation of the referenced edge; if it is negative the referenced edge must be followed backwards so that the shared face remains on the walker’s left. -
abs(next_right_edge)analogously follows the boundary of the face located on the right ofe. A positive value means that the next edge is taken with its recorded orientation starting from the end node ofe, whereas a negative value instructs to traverse the referenced edge in reverse, starting from its end node, so that the right-hand face is preserved. -
A zero value indicates that the edge is dangling on the corresponding side (for example an isolated edge whose incident face is the universal face
0). Theabs_next_left_edgeandabs_next_right_edgecolumns exposed by theedgeview are convenience projections of these absolute values.
This representation is a variant of a doubly connected edge list and is exploited by many topology routines. Functions such as
GetRingEdges
and
ValidateTopology
rely on it to reconstruct face boundaries and to diagnose inconsistencies—hence the “invalid next_left_edge” and “invalid next_right_edge” diagnostics reported during validation. Constructors like
AddEdge
initialise the
next_*
attributes with trivial self references, while editing routines including
ST_AddEdgeModFace
and
ST_RemEdgeModFace
update the links as edges are inserted or removed. Other bulk operations (for example
Polygonize
) may intentionally leave the fields unset, which is why the documentation flags their behaviour explicitly.
9.3. Topology Types
This section lists the PostgreSQL data types installed by PostGIS Topology. Note we describe the casting behavior of these which is very important especially when designing your own functions.
- getfaceedges_returntype — A composite type that consists of a sequence number and an edge number.
- TopoGeometry — A composite type representing a topologically defined geometry.
-
validatetopology_returntype
— A composite type that consists of an error message and id1 and id2 to denote location of error. This is the return type for
ValidateTopology.
9.4. Topology Domains
This section lists the PostgreSQL domains installed by PostGIS Topology. Domains can be used like object types as return objects of functions or table columns. The distinction between a domain and a type is that a domain is an existing type with a check constraint bound to it.
- TopoElement — An array of 2 integers generally used to identify a TopoGeometry component.
- TopoElementArray — An array of TopoElement objects.
9.5. Topology and TopoGeometry Management
This section lists the Topology functions for building new Topology schemas, validating topologies, and managing TopoGeometry Columns
- AddTopoGeometryColumn — Adds a topogeometry column to an existing table, registers this new column as a layer in topology.layer and returns the new layer_id.
- RenameTopoGeometryColumn — Renames a topogeometry column
- DropTopology — Use with caution: Drops a topology schema and deletes its reference from topology.topology table and references to tables in that schema from the geometry_columns table.
- RenameTopology — Renames a topology
-
DropTopoGeometryColumn
— Drops the topogeometry column from the table named
table_namein schemaschema_nameand unregisters the columns from topology.layer table. - FixCorruptTopoGeometryColumn — Fixes topogeometry corruption caused by upgrade to postgis_topology 3.6.0 and higher
- Populate_Topology_Layer — Adds missing entries to topology.layer table by reading metadata from topo tables.
- TopologySummary — Takes a topology name and provides summary totals of types of objects in topology.
- ValidateTopology — Returns a set of validatetopology_returntype objects detailing issues with topology.
- ValidateTopologyRelation — Returns info about invalid topology relation records
- ValidateTopologyPrecision — Returns non-precise vertices in the topology.
- MakeTopologyPrecise — Snap topology vertices to precision grid.
- FindTopology — Returns a topology record by different means.
- FindLayer — Returns a topology.layer record by different means.
- TotalTopologySize — Total disk space used by the specified topology, including all indexes and TOAST data.
- UpgradeTopology — Upgrades the specified topology to support large ids (int8) for topology and primitive ids.
9.6. Topology Statistics Management
This section discusses management of database statistics during topology building.
Adding elements to a topology triggers many database queries for finding existing edges that will be split, adding nodes and updating edges that will node with the new linework. For this reason it is useful that statistics about the data in the topology tables are up-to-date.
PostGIS Topology population and editing functions do not automatically update the statistics because a updating stats after each and every change in a topology would be overkill, so it is the caller's duty to take care of that.
|
|
|
That the statistics updated by autovacuum will NOT be visible to transactions which started before autovacuum process completed, so long-running transactions will need to run ANALYZE themselves, to use updated statistics. |
9.7. Topology Constructors
This section covers the topology functions for creating new topologies.
- CreateTopology — Creates a new topology schema and registers it in the topology.topology table.
- CopyTopology — Makes a copy of a topology (nodes, edges, faces, layers and TopoGeometries) into a new schema
- ST_InitTopoGeo — Creates a new topology schema and registers it in the topology.topology table.
- ST_CreateTopoGeo — Adds a collection of geometries to a given empty topology and returns a message detailing success.
- TopoGeo_AddPoint — Adds a point to an existing topology using a tolerance and possibly splitting an existing edge.
- TopoGeo_AddLineString — Adds a linestring to an existing topology using a tolerance and possibly splitting existing edges/faces.
- TopoGeo_AddPolygon — Adds a polygon to an existing topology using a tolerance and possibly splitting existing edges/faces. Returns face identifiers.
- TopoGeo_LoadGeometry — Load a geometry into an existing topology, snapping and splitting as needed.
9.8. Topology Editors
This section covers topology functions for adding, moving, deleting, and splitting edges, faces, and nodes. All of these functions are defined by ISO SQL/MM.
- ST_AddIsoNode — Adds an isolated node to a face in a topology and returns the nodeid of the new node. If face is null, the node is still created.
-
ST_AddIsoEdge
— Adds an isolated edge defined by geometry
alinestringto a topology connecting two existing isolated nodesanodeandanothernodeand returns the edge id of the new edge. - ST_AddEdgeNewFaces — Add a new edge and, if in doing so it splits a face, delete the original face and replace it with two new faces.
- ST_AddEdgeModFace — Add a new edge and, if in doing so it splits a face, modify the original face and add a new face.
- ST_RemEdgeNewFace — Removes an edge and, if the removed edge separated two faces, delete the original faces and replace them with a new face.
- ST_RemEdgeModFace — Removes an edge, and if the edge separates two faces deletes one face and modifies the other face to cover the space of both.
- ST_ChangeEdgeGeom — Changes the shape of an edge without affecting the topology structure.
- ST_ModEdgeSplit — Split an edge by creating a new node along an existing edge, modifying the original edge and adding a new edge.
- ST_ModEdgeHeal — Heals two edges by deleting the node connecting them, modifying the first edge and deleting the second edge. Returns the id of the deleted node.
- ST_NewEdgeHeal — Heals two edges by deleting the node connecting them, deleting both edges, and replacing them with an edge whose direction is the same as the first edge provided.
-
ST_MoveIsoNode
— Moves an isolated node in a topology from one point to another. If new
apointgeometry exists as a node an error is thrown. Returns description of move. - ST_NewEdgesSplit — Split an edge by creating a new node along an existing edge, deleting the original edge and replacing it with two new edges. Returns the id of the new node created that joins the new edges.
- ST_RemoveIsoNode — Removes an isolated node and returns description of action. If the node is not isolated (is start or end of an edge), then an exception is thrown.
- ST_RemoveIsoEdge — Removes an isolated edge and returns description of action. If the edge is not isolated, then an exception is thrown.
9.9. Topology Accessors
- GetEdgeByPoint — Finds the edge-id of an edge that intersects a given point.
- GetFaceByPoint — Finds face intersecting a given point.
- GetFaceContainingPoint — Finds the face containing a point.
- GetNodeByPoint — Finds the node-id of a node at a point location.
- GetTopologyID — Returns the id of a topology in the topology.topology table given the name of the topology.
- GetTopologySRID — Returns the SRID of a topology in the topology.topology table given the name of the topology.
- GetTopologyName — Returns the name of a topology (schema) given the id of the topology.
-
ST_GetFaceEdges
— Returns a set of ordered edges that bound
aface. - ST_GetFaceGeometry — Returns the polygon in the given topology with the specified face id.
- GetRingEdges — Returns the ordered set of signed edge identifiers met by walking on an a given edge side.
- GetNodeEdges — Returns an ordered set of edges incident to the given node.
- FindVertexSegmentPairsBelowDistance — Find pairs of topology vertex/segment that are closer than tolerated distance
9.10. Topology Processing
This section covers the functions for processing topologies in non-standard ways.
- Polygonize — Finds and registers all faces defined by topology edges.
- AddNode — Adds a point node to the node table in the specified topology schema and returns the nodeid of new node. If point already exists as node, the existing nodeid is returned.
- AddEdge — Adds a linestring edge to the edge table and associated start and end points to the point nodes table of the specified topology schema using the specified linestring geometry and returns the edgeid of the new (or existing) edge.
- AddFace — Registers a face primitive to a topology and gets its identifier.
- ST_Simplify — Returns a "simplified" geometry version of the given TopoGeometry using the Douglas-Peucker algorithm.
- RemoveUnusedPrimitives — Removes topology primitives which not needed to define existing TopoGeometry objects.
9.11. TopoGeometry Constructors
This section covers the topology functions for creating new topogeometries.
- CreateTopoGeom — Creates a new topo geometry object from topo element array - tg_type: 1:[multi]point, 2:[multi]line, 3:[multi]poly, 4:collection
- toTopoGeom — Converts a simple Geometry into a topo geometry.
-
TopoElementArray_Agg
— Returns a
topoelementarrayfor a set of element_id, type arrays (topoelements). - TopoElement — Converts a topogeometry to a topoelement.
9.12. TopoGeometry Editors
This section covers the topology functions for editing existing topogeometries.
- clearTopoGeom — Clears the content of a topo geometry.
- TopoGeom_addElement — Adds an element to the definition of a TopoGeometry.
- TopoGeom_remElement — Removes an element from the definition of a TopoGeometry.
- TopoGeom_addTopoGeom — Adds element of a TopoGeometry to the definition of another TopoGeometry.
- toTopoGeom — Adds a geometry shape to an existing topo geometry.
9.13. TopoGeometry Accessors
-
GetTopoGeomElementArray
— Returns a
topoelementarray(an array of topoelements) containing the topological elements and type of the given TopoGeometry (primitive elements). -
GetTopoGeomElements
— Returns a set of
topoelementobjects containing the topological element_id,element_type of the given TopoGeometry (primitive elements). - ST_SRID — Returns the spatial reference identifier for a topogeometry.
9.14. TopoGeometry Outputs
- AsGML — Returns the GML representation of a topogeometry.
- AsTopoJSON — Returns the TopoJSON representation of a topogeometry.
9.15. Topology Spatial Relationships
This section lists the Topology functions used to check relationships between topogeometries and topology primitives
- Equals — Returns true if two topogeometries are composed of the same topology primitives.
- Intersects — Returns true if any pair of primitives from the two topogeometries intersect.
9.16. Importing and exporting Topologies
Once you have created topologies, and maybe associated topological layers, you might want to export them into a file-based format for backup or transfer into another database.
Using the standard dump/restore tools of PostgreSQL is problematic because topologies are composed by a set of tables (4 for primitives, an arbitrary number for layers) and records in metadata tables (topology.topology and topology.layer). Additionally, topology identifiers are not univoque across databases so that parameter of your topology will need to be changes upon restoring it.
In order to simplify export/restore of topologies a pair of
executables are provided:
pgtopo_export
and
pgtopo_import
. Example usage:
pgtopo_export dev_db topo1 | pgtopo_import topo1 | psql staging_db
9.16.1. Using the Topology exporter
The
pgtopo_export
script takes the name of a
database and a topology and outputs a dump file which can be used
to import the topology (and associated layers) into a new database.
By default
pgtopo_export
writes the
dump file to the standard output so that it can be piped to
pgtopo_import
or redirected to a file
(refusing to write to terminal). You can optionally specify
an output filename with the
-f
commandline switch.
By default
pgtopo_export
includes a dump
of all layers defined against the given topology. This may be more
data than you need, or may be non-working (in case your layer tables
have complex dependencies) in which case you can request skipping the
layers with the
--skip-layers
switch and deal with those
separately.
Invoking
pgtopo_export
with the
--help
(or
-h
for short) switch
will always print short usage string.
The dump file format is a compressed tar archive of a
pgtopo_export
directory containing
at least a
pgtopo_dump_version
file with
format version info. As of version
1
the directory
contains tab-delimited CSV files with data of the topology
primitive tables (node, edge_data, face, relation), the
topology and layer records associated with it and
(unless
--skip-layers
is given) a custom-format
PostgreSQL dump of tables reported as being layers of the given
topology.
9.16.2. Using the Topology importer
The
pgtopo_import
script takes a
pgtopo_export
format topology dump and a
name to give to the topology to be created and outputs
an SQL script reconstructing the topology and associated
layers.
The generated SQL file will contain statements that create a topology with the given name, load primitive data in it, restores and registers all topology layers by properly linking all TopoGeometry values to their correct topology.
By default
pgtopo_import
reads the dump
from the standard input so that it can be used in conjunction
with
pgtopo_export
in a pipeline.
You can optionally specify an input filename with the
-f
commandline switch.
By default
pgtopo_import
includes in the output
SQL file the code to restore all layers found in the dump.
This may be unwanted or non-working in case your target database already
have tables with the same name as the ones in the dump. In that case
you can request skipping the layers with the
--skip-layers
switch and deal with those separately (or later).
SQL to only load and link layers to a named topology can be generated
using the
--only-layers
switch. This can be useful to load
layers AFTER resolving the naming conflicts or to link layers to a
different topology (say a spatially-simplified version of the starting
topology).
If the target topology already exists and you want it dropped upfront
you can pass the
--drop-topology
switch (since PostGIS-3.6.0).