postgis_topology

pig repo add pgdg -u          # Add PGDG repo and update cache

CREATE EXTENSION postgis_topology CASCADE;  -- requires: postgis

CREATE EXTENSION postgis_topology;

-- Create a topology named 'city_topo' with SRID 4326 and 0.00001 degree tolerance
SELECT topology.CreateTopology('city_topo', 4326, 0.00001);

SELECT * FROM topology.topology;

-- Add isolated nodes
SELECT topology.ST_AddIsoNode('city_topo', NULL, ST_Point(-73.98, 40.75));

-- Add an edge between two points (creates nodes if needed)
SELECT topology.ST_AddEdgeModFace('city_topo',
    ST_MakeLine(ST_Point(-73.99, 40.74), ST_Point(-73.98, 40.74)));

SELECT topology.ST_AddEdgeModFace('city_topo',
    ST_MakeLine(ST_Point(-73.98, 40.74), ST_Point(-73.98, 40.75)));

SELECT topology.ST_AddEdgeModFace('city_topo',
    ST_MakeLine(ST_Point(-73.98, 40.75), ST_Point(-73.99, 40.75)));

SELECT topology.ST_AddEdgeModFace('city_topo',
    ST_MakeLine(ST_Point(-73.99, 40.75), ST_Point(-73.99, 40.74)));

-- List nodes
SELECT node_id, ST_AsText(geom) FROM city_topo.node;

-- List edges
SELECT edge_id, ST_AsText(geom) FROM city_topo.edge_data;

-- List faces (face_id 0 is the universal face)
SELECT face_id, ST_AsText(mbr) FROM city_topo.face;

-- Get the geometry of a face
SELECT topology.ST_GetFaceGeometry('city_topo', 1);

-- Create a table with a topogeometry column
CREATE TABLE city_topo.blocks (
    id serial PRIMARY KEY,
    name text
);

-- Register a topogeometry column (returns a layer_id)
SELECT topology.AddTopoGeometryColumn('city_topo', 'city_topo', 'blocks', 'topo', 'POLYGON');

-- Create a TopoGeometry from a regular geometry (snaps to existing topology)
INSERT INTO city_topo.blocks (name, topo) VALUES
    ('Block A', topology.toTopoGeom(
        ST_GeomFromText('POLYGON((-73.99 40.74,-73.98 40.74,-73.98 40.75,-73.99 40.75,-73.99 40.74))', 4326),
        'city_topo', 1));

-- Convert a TopoGeometry back to a regular geometry
SELECT name, topo::geometry FROM city_topo.blocks;

-- Validate the entire topology
SELECT * FROM topology.ValidateTopology('city_topo');

-- Check for topology errors
SELECT error FROM topology.ValidateTopology('city_topo')
WHERE error IS NOT NULL;

-- Drop a topology and all its objects
SELECT topology.DropTopology('city_topo');