PostGIS 2.0 Topology Cheatsheet

New in this release 1
Requires GEOS 3.3 or higher3.3
Topology Types
getfaceedges_returntype A composite type that consists of a sequence number and edge number. This is the return type for ST_GetFaceEdges
topogeometry A composite type that refers to a topology geometry in a specific topology, layer, having specific type (1:[multi]point, 2:[multi]line, 3:[multi]poly, 4:collection) with specific identifier id in the topology. The id uniquely defines the topogeometry in the topology.
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 ST_ValidateTopology
Topology Domains
TopoElement An array of 2 integers generally used to identify a TopoGeometry component.
topoelementarray An array of element_id,element_type values. a bidimensional array of integers: '{{id,type}, {id,type}, ...}'
Topology and TopoGeometry Management
AddTopoGeometryColumn Adds a topogeometry column to an existing feature table, registers this new column as a layer in topology.layer and returns the new layer_id.
CreateTopology Creates a new topology schema and registers this new schema in the topology.topology table.
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.
CopyTopology1 Makes a copy of a topology structure (nodes, edges, faces, layers and TopoGeometries).
DropTopoGeometryColumn Drops the topogeometry column from the table named table_name in schema schema_name and unregisters the columns from topology.layer table.
ST_InitTopoGeo Creates a new topology schema and registers this new schema in the topology.topology table and details summary of process.
TopologySummary1 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
Topology Constructors
ST_CreateTopoGeo1 Adds a collection of geometries to a given empty topology and returns a message detailing success.
Topology Editors
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 alinestring to a topology connecting two existing isolated nodes anode and anothernode and returns the edge id of the new edge.
ST_AddEdgeNewFaces1 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_AddEdgeModFace1 Add a new edge and, if in doing so it splits a face, modify the original face and add a new face.
ST_RemEdgeNewFace1 Removes an edge and, if the removed edge separated two faces, delete the original faces and replace them with a new face.
ST_RemEdgeModFace1 Removes an edge and, if the removed edge separated two faces, delete one of the them and modify the other to take the space of both.
ST_ChangeEdgeGeom Changes the linestring that define the specified edge. Will raise an error if the start and end points are not the same as the original or the new linestring crosses an existing edge (not at end points) or new linestring is not simple.
ST_ModEdgeSplit Split an edge by creating a new node along an existing edge, modifying the original edge and adding a new edge.
ST_ModEdgeHeal1 Heal 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_NewEdgeHeal1 Heal 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 apoint geometry 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.
Topology Accessors
GetEdgeByPoint1 Find the edge-id of an edge that intersects a given point
GetFaceByPoint1 Find the face-id of a face that intersects a given point
GetNodeByPoint1 Find the 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.
GetTopologyName Returns the name of a topology (schema) given the id of the topology.
ST_GetFaceEdges1 Returns a set of ordered edges that bound aface includes the sequence order.
ST_GetFaceGeometry Returns the polygon in the given topology with the specified face id.
Topology Processing
AddEdge1 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.
AddFace1 Registers a face primitive to a topology and get it's identifier.
Polygonize1 Find and register all faces defined by topology edges
AddNode1 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.
TopoGeometry Constructors
CreateTopoGeom Creates a new topo geometry object from topo element array - tg_type: 1:[multi]point, 2:[multi]line, 3:[multi]poly, 4:collection
TopoElementArray_Agg1 Returns a topoelementarray for a set of element_id, type arrays (topoelements)
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 topoelement objects containing the topological element_id,element_type of the given TopoGeometry (primitive elements)
TopoGeometry Outputs
AsGML1 Returns the GML representation of a topogeometry.
Topology Domains Examples
TopoElement
SELECT ARRAY[1,2]::topology.topoelement;
te
-------
{1,2}
topoelementarray
SELECT '{{1,2},{4,3}}'::topology.topoelementarray As tea;
tea
-------
{{1,2},{4,3}}

-- more verbose equivalent --
SELECT ARRAY[ARRAY[1,2], ARRAY[4,3]]::topology.topoelementarray As tea;

tea
-------
{{1,2},{4,3}}

--using the array agg function packaged with topology --
SELECT topology.TopoElementArray_Agg(ARRAY[e,t]) As tea
FROM generate_series(1,4) As e CROSS JOIN generate_series(1,3) As t;
tea
--------------------------------------------------------------------------
{{1,1},{1,2},{1,3},{2,1},{2,2},{2,3},{3,1},{3,2},{3,3},{4,1},{4,2},{4,3}}
Topology and TopoGeometry Management Examples
AddTopoGeometryColumn
-- Note for this example we created our new table in the ma_topo schema
-- though we could have created it in a different schema -- in which case topology_name and schema_name would be different
CREATE SCHEMA ma;
CREATE TABLE ma.parcels(gid serial, parcel_id varchar(20) PRIMARY KEY, address text);
SELECT topology.AddTopoGeometryColumn('ma_topo', 'ma', 'parcels', 'topo', 'POLYGON');
CreateTopology
SELECT topology.CreateTopology('ma_topo',26986, 0.5);
DropTopology
SELECT topology.DropTopology('ma_topo');
CopyTopology
SELECT topology.CopyTopology('ma_topo', 'ma_topo_bakup');
DropTopoGeometryColumn
SELECT topology.DropTopoGeometryColumn('ma_topo', 'parcel_topo', 'topo');
ST_InitTopoGeo
SELECT topology.ST_InitTopoGeo('topo_schema_to_create') AS topocreation;
astopocreation
------------------------------------------------------------
Topology-Geometry 'topo_schema_to_create' (id:7) created.
TopologySummary
SELECT topology.topologysummary('city_data');
topologysummary
--------------------------------------------------------
Topology city_data (329), SRID 4326, precision: 0
22 nodes, 24 edges, 10 faces, 29 topogeoms in 5 layers
Layer 1, type Polygonal (3), 9 topogeoms
Deploy: features.land_parcels.feature
Layer 2, type Puntal (1), 8 topogeoms
Deploy: features.traffic_signs.feature
Layer 3, type Lineal (2), 8 topogeoms
Deploy: features.city_streets.feature
Layer 4, type Polygonal (3), 3 topogeoms
Hierarchy level 1, child layer 1
Deploy: features.big_parcels.feature
Layer 5, type Puntal (1), 1 topogeoms
Hierarchy level 1, child layer 2
Deploy: features.big_signs.feature
ValidateTopology
SELECT * FROM topology.ValidateTopology('ma_topo');
error | id1 | id2
-------------------+-----+-----
face without edges | 0 |
Topology Constructors Examples
ST_CreateTopoGeo

-- Populate topology --
SELECT topology.ST_CreateTopoGeo('ri_topo',
ST_GeomFromText('MULTILINESTRING((384744 236928,384750 236923,384769 236911,384799 236895,384811 236890,384833 236884,
384844 236882,384866 236881,384879 236883,384954 236898,385087 236932,385117 236938,
385167 236938,385203 236941,385224 236946,385233 236950,385241 236956,385254 236971,
385260 236979,385268 236999,385273 237018,385273 237037,385271 237047,385267 237057,
385225 237125,385210 237144,385192 237161,385167 237192,385162 237202,385159 237214,
385159 237227,385162 237241,385166 237256,385196 237324,385209 237345,385234 237375,
385237 237383,385238 237399,385236 237407,385227 237419,385213 237430,385193 237439,
385174 237451,385170 237455,385169 237460,385171 237475,385181 237503,385190 237521,
385200 237533,385206 237538,385213 237541,385221 237542,385235 237540,385242 237541,
385249 237544,385260 237555,385270 237570,385289 237584,385292 237589,385291 237596,385284 237630))',3438)
);

st_createtopogeo
----------------------------
Topology ri_topo populated


-- create tables and topo geometries --
CREATE TABLE ri.roads(gid serial PRIMARY KEY, road_name text);

SELECT topology.AddTopoGeometryColumn('ri_topo', 'ri', 'roads', 'topo', 'LINE');
Topology Editors Examples
ST_ChangeEdgeGeom
SELECT topology.ST_ChangeEdgeGeom('ma_topo', 1,
ST_GeomFromText('LINESTRING(227591.9 893900.4,227622.6 893844.3,227641.6 893816.6, 227704.5 893778.5)', 26986) );
----
Edge 1 changed
ST_ModEdgeSplit

-- Add an edge --
SELECT topology.AddEdge('ma_topo', ST_GeomFromText('LINESTRING(227592 893910, 227600 893910)', 26986) ) As edgeid;

-- edgeid-
3


-- Split the edge --
SELECT topology.ST_ModEdgeSplit('ma_topo', 3, ST_SetSRID(ST_Point(227594,893910),26986) ) As result;
result
-------------------------
7
ST_MoveIsoNode

-- Add an isolated node with no face --
SELECT topology.ST_AddIsoNode('ma_topo', NULL, ST_GeomFromText('POINT(227579 893916)', 26986) ) As nodeid;
nodeid
--------
7
-- Move the new node --
SELECT topology.ST_MoveIsoNode('ma_topo', 7, ST_GeomFromText('POINT(227579.5 893916.5)', 26986) ) As descrip;
descrip
----------------------------------------------------
Isolated Node 7 moved to location 227579.5,893916.5
ST_NewEdgesSplit

-- Add an edge --
SELECT topology.AddEdge('ma_topo', ST_GeomFromText('LINESTRING(227575 893917,227592 893900)', 26986) ) As edgeid;
-- result-
edgeid
------
2
-- Split the new edge --
SELECT topology.ST_NewEdgesSplit('ma_topo', 2, ST_GeomFromText('POINT(227578.5 893913.5)', 26986) ) As newnodeid;
newnodeid
---------
6
ST_RemoveIsoNode

-- Add an isolated node with no face --
SELECT topology.ST_RemoveIsoNode('ma_topo', 7 ) As result;
result
-------------------------
Isolated node 7 removed
Topology Accessors Examples
GetEdgeByPoint
SELECT topology.GetEdgeByPoint('ma_topo',geom, 1) As with1mtol, topology.GetEdgeByPoint('ma_topo',geom,0) As withnotol
FROM ST_GeomFromEWKT('SRID=26986;POINT(227622.6 893843)') As geom;
with1mtol | withnotol
-----------+-----------
2 | 0
GetFaceByPoint
SELECT topology.GetFaceByPoint('ma_topo',geom, 10) As with1mtol, topology.GetFaceByPoint('ma_topo',geom,0) As withnotol
FROM ST_GeomFromEWKT('POINT(234604.6 899382.0)') As geom;

with1mtol | withnotol
-----------+-----------
1 | 0
GetNodeByPoint
SELECT topology.GetNodeByPoint('ma_topo',geom, 1) As nearnode
FROM ST_GeomFromEWKT('SRID=26986;POINT(227591.9 893900.4)') As geom;
nearnode
----------
2
GetTopologyID
SELECT topology.GetTopologyID('ma_topo') As topo_id;
topo_id
---------
1
GetTopologyName
SELECT topology.GetTopologyName(1) As topo_name;
topo_name
-----------
ma_topo
ST_GetFaceEdges

-- Returns the edges bounding face 1
SELECT (topology.ST_GetFaceEdges('tt', 1)).*;
-- result --
sequence | edge
----------+------
1 | -4
2 | 5
3 | 7
4 | -6
5 | 1
6 | 2
7 | 3
(7 rows)
ST_GetFaceGeometry

-- Returns the wkt of the polygon added with AddFace
SELECT ST_AsText(topology.ST_GetFaceGeometry('ma_topo', 1)) As facegeomwkt;
-- result --
facegeomwkt

--------------------------------------------------------------------------------
POLYGON((234776.9 899563.7,234896.5 899456.7,234914 899436.4,234946.6 899356.9,
234872.5 899328.7,234891 899285.4,234992.5 899145,234890.6 899069,
234755.2 899255.4,234612.7 899379.4,234776.9 899563.7))
Topology Processing Examples
AddEdge
SELECT topology.AddEdge('ma_topo', ST_GeomFromText('LINESTRING(227575.8 893917.2,227591.9 893900.4)', 26986) ) As edgeid;
-- result-
edgeid
--------
1

SELECT topology.AddEdge('ma_topo', ST_GeomFromText('LINESTRING(227591.9 893900.4,227622.6 893844.2,227641.6 893816.5,
227704.5 893778.5)', 26986) ) As edgeid;
-- result --
edgeid
--------
2

SELECT topology.AddEdge('ma_topo', ST_GeomFromText('LINESTRING(227591.2 893900, 227591.9 893900.4,
227704.5 893778.5)', 26986) ) As edgeid;
-- gives error --
ERROR: Edge intersects (not on endpoints) with existing edge 1
AddFace

-- first add the edges we use generate_series as an iterator (the below
-- will only work for polygons with < 10000 points because of our max in gs)
SELECT topology.AddEdge('ma_topo', ST_MakeLine(ST_PointN(geom,i), ST_PointN(geom, i + 1) )) As edgeid
FROM (SELECT ST_NPoints(geom) AS npt, geom
FROM
(SELECT ST_Boundary(ST_GeomFromText('POLYGON((234896.5 899456.7,234914 899436.4,234946.6 899356.9,234872.5 899328.7,
234891 899285.4,234992.5 899145, 234890.6 899069,234755.2 899255.4,
234612.7 899379.4,234776.9 899563.7,234896.5 899456.7))', 26986) ) As geom
) As geoms) As facen CROSS JOIN generate_series(1,10000) As i
WHERE i < npt;
-- result --
edgeid
--------
3
4
5
6
7
8
9
10
11
12
(10 rows)
-- then add the face -

SELECT topology.AddFace('ma_topo',
ST_GeomFromText('POLYGON((234896.5 899456.7,234914 899436.4,234946.6 899356.9,234872.5 899328.7,
234891 899285.4,234992.5 899145, 234890.6 899069,234755.2 899255.4,
234612.7 899379.4,234776.9 899563.7,234896.5 899456.7))', 26986) ) As faceid;
-- result --
faceid
--------
1

AddNode
SELECT topology.AddNode('ma_topo', ST_GeomFromText('POINT(227641.6 893816.5)', 26986) ) As nodeid;
-- result --
nodeid
--------
4

TopoGeometry Constructors Examples
CreateTopoGeom
INSERT INTO ri.ri_roads(road_name, topo) VALUES('Unknown', topology.CreateTopoGeom('ri_topo',2,2,'{{1,2}}'::topology.topoelementarray);
TopoElementArray_Agg
SELECT topology.TopoElementArray_Agg(ARRAY[e,t]) As tea
FROM generate_series(1,3) As e CROSS JOIN generate_series(1,4) As t;
tea
--------------------------------------------------------------------------
{{1,1},{1,2},{1,3},{1,4},{2,1},{2,2},{2,3},{2,4},{3,1},{3,2},{3,3},{3,4}}
TopoGeometry Accessors Examples
TopoGeometry Outputs Examples
AsGML
SELECT topology.AsGML(topo) As rdgml
FROM ri.roads
WHERE road_name = 'Unknown';

-- rdgml--
<gml:TopoCurve>
<gml:directedEdge>
<gml:Edge gml:id="E1">
<gml:directedNode orientation="-">
<gml:Node gml:id="N1"/>
</gml:directedNode>
<gml:directedNode></gml:directedNode>
<gml:curveProperty>
<gml:Curve srsName="urn:ogc:def:crs:EPSG::3438">
<gml:segments>
<gml:LineStringSegment>
<gml:posList srsDimension="2">384744 236928 384750 236923 384769 236911 384799 236895 384811 236890
384833 236884 384844 236882 384866 236881 384879 236883 384954 236898 385087 236932 385117 236938
385167 236938 385203 236941 385224 236946 385233 236950 385241 236956 385254 236971
385260 236979 385268 236999 385273 237018 385273 237037 385271 237047 385267 237057 385225 237125
385210 237144 385192 237161 385167 237192 385162 237202 385159 237214 385159 237227 385162 237241
385166 237256 385196 237324 385209 237345 385234 237375 385237 237383 385238 237399 385236 237407
385227 237419 385213 237430 385193 237439 385174 237451 385170 237455 385169 237460 385171 237475
385181 237503 385190 237521 385200 237533 385206 237538 385213 237541 385221 237542 385235 237540 385242 237541
385249 237544 385260 237555 385270 237570 385289 237584 385292 237589 385291 237596 385284 237630</gml:posList>
</gml:LineStringSegment>
</gml:segments>
</gml:Curve>
</gml:curveProperty>
</gml:Edge>
</gml:directedEdge>
</gml:TopoCurve>