postgis_topology

pig repo add pgdg -u          # 添加 PGDG 仓库并更新缓存

CREATE EXTENSION postgis_topology CASCADE;  -- 依赖: postgis

CREATE EXTENSION postgis_topology;

-- 创建名为 'city_topo' 的拓扑，SRID 4326，容差 0.00001 度
SELECT topology.CreateTopology('city_topo', 4326, 0.00001);

SELECT * FROM topology.topology;

-- 添加孤立节点
SELECT topology.ST_AddIsoNode('city_topo', NULL, ST_Point(-73.98, 40.75));

-- 添加两点之间的边（如需要会自动创建节点）
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)));

-- 列出节点
SELECT node_id, ST_AsText(geom) FROM city_topo.node;

-- 列出边
SELECT edge_id, ST_AsText(geom) FROM city_topo.edge_data;

-- 列出面（face_id 0 是全局面）
SELECT face_id, ST_AsText(mbr) FROM city_topo.face;

-- 获取面的几何形状
SELECT topology.ST_GetFaceGeometry('city_topo', 1);

-- 创建包含拓扑几何列的表
CREATE TABLE city_topo.blocks (
    id serial PRIMARY KEY,
    name text
);

-- 注册拓扑几何列（返回 layer_id）
SELECT topology.AddTopoGeometryColumn('city_topo', 'city_topo', 'blocks', 'topo', 'POLYGON');

-- 从常规几何创建拓扑几何（捕捉到已有拓扑）
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));

-- 将拓扑几何转换回常规几何
SELECT name, topo::geometry FROM city_topo.blocks;

-- 验证整个拓扑
SELECT * FROM topology.ValidateTopology('city_topo');

-- 检查拓扑错误
SELECT error FROM topology.ValidateTopology('city_topo')
WHERE error IS NOT NULL;

-- 删除拓扑及其所有对象
SELECT topology.DropTopology('city_topo');