地图四色问题——PostGIS生成染色字段

-- -- 步骤0: 准备工作 - 确保有颜色字段 -- ALTER TABLE ChinaProvinces ADD COLUMN IF NOT EXISTS color_id INTEGER; -- -- 步骤1: 构建邻接关系临时表基于空间相交判断相邻 -- 注意: 使用 ST_Intersects 且排除自身边界接触(DE-9IM)也算相邻 -- DROP TABLE IF EXISTS tmp_province_adjacency; CREATE TEMP TABLE tmp_province_adjacency AS SELECT a.id AS id_a, b.id AS id_b FROM ChinaProvinces a JOIN ChinaProvinces b ON ST_Intersects(a.geometry, b.geometry) AND a.id ! b.id; -- 为邻接表建索引加速查询 CREATE INDEX idx_adj_a ON tmp_province_adjacency(id_a); CREATE INDEX idx_adj_b ON tmp_province_adjacency(id_b); ANALYZE tmp_province_adjacency; -- -- 步骤2: 贪心着色算法PL/pgSQL存储过程 -- 按邻居数量降序排列优先着色提高着色成功率 -- DO $$ DECLARE v_rec RECORD; v_neighbor_colors INTEGER[]; v_color INTEGER; v_max_colors CONSTANT INTEGER : 6; -- 使用6种颜色(1~6)可改为5 BEGIN -- 初始化所有颜色为NULL UPDATE ChinaProvinces SET color_id NULL; -- 按邻居数降序遍历每个省份度大的节点先着色减少冲突概率 FOR v_rec IN SELECT p.id, COUNT(adj.id_b) AS neighbor_cnt FROM ChinaProvinces p LEFT JOIN tmp_province_adjacency adj ON p.id adj.id_a GROUP BY p.id ORDER BY neighbor_cnt DESC, p.id LOOP -- 获取该省份所有已着色邻居的颜色值 SELECT ARRAY_AGG(DISTINCT cp.color_id) INTO v_neighbor_colors FROM tmp_province_adjacency adj JOIN ChinaProvinces cp ON adj.id_b cp.id WHERE adj.id_a v_rec.id AND cp.color_id IS NOT NULL; -- 从1~v_max_colors中选第一个未被邻居使用的颜色 v_color : NULL; FOR i IN 1..v_max_colors LOOP IF v_neighbor_colors IS NULL OR NOT (i ANY(v_neighbor_colors)) THEN v_color : i; EXIT; END IF; END LOOP; -- 如果6种颜色都不够用理论上中国地图不会发生回退到1 IF v_color IS NULL THEN RAISE WARNING Province id% exceeded % colors, fallback to 1, v_rec.id, v_max_colors; v_color : 1; END IF; -- 写入颜色 UPDATE ChinaProvinces SET color_id v_color WHERE id v_rec.id; END LOOP; RAISE NOTICE 着色完成; END $$; -- -- 步骤3: 验证结果 - 检查是否存在相邻同色 -- SELECT COUNT(*) AS conflict_count FROM tmp_province_adjacency adj JOIN ChinaProvinces a ON adj.id_a a.id JOIN ChinaProvinces b ON adj.id_b b.id WHERE a.color_id b.color_id; -- 查看各颜色分配统计 SELECT color_id, COUNT(*) AS province_count FROM ChinaProvinces GROUP BY color_id ORDER BY color_id; -- 清理临时表 DROP TABLE IF EXISTS tmp_province_adjacency;如果是其它的图层把ChinaProvince和对应图层的主键字段改了即可。