I need to delete everything (faces, nodes, edges, anything I'm not thinking of) from a PostGIS topology, but I want to keep my database schema (schemas, tables, columns, sequences, etc.) intact. Is there a simple way to do that? If not, what elements do I need to delete and what functions/queries would I use to delete them?

  • pg_dump with schema only postgresql.org/docs/9.3/static/app-pgdump.html or copy your existing database and truncate all tables stackoverflow.com/questions/2829158/… – Mapperz Oct 14 '14 at 18:33
  • @Mapperz I'm looking into fiddling with the underlying topology tables manually, but I've noticed that at least the face table has a row in it when you create a new topology. Since the topology tables are something PostGIS is supposed to manage for me, I'm a bit cautious about just truncating them. – jpmc26 Oct 14 '14 at 18:37

I think this might do the trick. Let's say you created your topology like this:

SELECT topology.CreateTopology('my_topo', 4326, 0.01);
SELECT topology.AddTopoGeometryColumn('my_topo', 'my_layer', 'topogeom', 'MULTIPOLYGON');

where my_layer is an existing table.

  1. Remove any data from tables with a TopoGeometry column using this topology.

    DELETE FROM my_layer;

    Do this for each table you added a column to.

  2. Delete any "topology geometries" by clearing out the relation table.

    DELETE FROM my_topo.relation;
  3. Remove all edges.

    SELECT ST_RemEdgeModFace('my_topo', edge_id) FROM my_topo.edge_data;

    I don't think it matters which ST_RemEdge*Face function you used. I chose this one since it won't create new ones, which seemed like unnecessary overhead. Note that this step will fail if any geometries (represented in relation) cannot be represented after deleting an edge; that's why you have to clear relation first.

  4. Remove all nodes.

    SELECT ST_RemoveIsoNode('my_topo', node_id) FROM my_topo.node;

    We can get away with this since after removing all edges, the nodes are all isolated.

Note that we didn't have to delete any faces manually. I advise against trying to do so; I noticed that the face table in the topology has a row initially, even thought it doesn't count that row as a face when summarizing the topology.

Following this, our topology appears to be empty:

dbname=# SELECT TopologySummary ('my_topo');
 Topology temp_overlay_topo (21), SRID 4326, precision 0.01+
 0 nodes, 0 edges, 0 faces, 0 topogeoms in 0 layers         +
 Layer 1, type Polygonal (3), 0 topogeoms                   +
  Deploy: my_layer.topogeom                +

(1 row)

I'm pretty new to topologies. I welcome any feedback, suggestions, or corrections.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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