-- 错误: ERROR: could not open extension control file
CREATE EXTENSION postgis;

# Ubuntu/Debian
sudo apt install postgresql-16-postgis-3

# CentOS/RHEL
sudo dnf install postgresql16-postgis3

# 验证安装
dpkg -l | grep postgis   # Debian
rpm -qa | grep postgis   # RHEL

-- 错误: ERROR: function postgis_version() does not exist

-- 在目标数据库中执行
CREATE EXTENSION IF NOT EXISTS postgis;

# 检查依赖
ldd /usr/lib/postgresql/16/lib/postgis-3.so

# 安装缺失依赖
sudo apt install libgeos-dev libproj-dev libgdal-dev

# 重启 PostgreSQL
sudo systemctl restart postgresql

-- 自相交多边形
SELECT ST_IsValid(
    ST_GeomFromText('POLYGON((0 0, 2 2, 2 0, 0 2, 0 0))')
);
-- 输出: FALSE

-- 查看无效原因
SELECT ST_IsValidReason(
    ST_GeomFromText('POLYGON((0 0, 2 2, 2 0, 0 2, 0 0))')
);
-- 输出: Self-intersection [1 1]

-- 使用 ST_MakeValid 修复
SELECT ST_MakeValid(
    ST_GeomFromText('POLYGON((0 0, 2 2, 2 0, 0 2, 0 0))')
);
-- 输出: MULTILINESTRING((0 0,2 2),(2 0,0 2))
-- 注意: 修复后可能变成 MultiPolygon 或 GeometryCollection

-- 批量修复表中所有无效几何
UPDATE my_table
SET geom = ST_MakeValid(geom)
WHERE NOT ST_IsValid(geom);

-- 查看修复前后的类型变化
SELECT
    ST_GeometryType(geom) AS original_type,
    ST_GeometryType(ST_MakeValid(geom)) AS fixed_type,
    ST_IsValid(geom) AS was_valid
FROM my_table
WHERE NOT ST_IsValid(geom);

-- 经纬度搞反了
-- 正确: ST_MakePoint(经度, 纬度) = ST_MakePoint(116.4, 39.9)
-- 错误: ST_MakePoint(39.9, 116.4)

-- 检测方法：查看坐标范围
SELECT
    ST_XMin(ST_Extent(geom)) AS min_x,
    ST_XMax(ST_Extent(geom)) AS max_x,
    ST_YMin(ST_Extent(geom)) AS min_y,
    ST_YMax(ST_Extent(geom)) AS max_y
FROM my_table;

-- WGS84 经度范围: -180 ~ 180
-- WGS84 纬度范围: -90 ~ 90
-- 如果 max_x > 90 且 max_y > 90，很可能是经纬度搞反了

-- 交换经纬度
UPDATE my_table
SET geom = ST_SetSRID(
    ST_MakePoint(ST_Y(geom), ST_X(geom)),
    4326
);

-- 错误: ERROR: Operation on mixed SRID geometries
SELECT ST_Intersects(
    ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326),
    ST_SetSRID(ST_MakePoint(116.4, 39.9), 4547)
);

-- 统一 SRID
-- 方式 1: 转换到统一坐标系
SELECT ST_Intersects(
    ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326),
    ST_Transform(ST_SetSRID(ST_MakePoint(447963, 4418554), 4547), 4326)
);

-- 方式 2: 确保所有数据使用相同 SRID
UPDATE my_table SET geom = ST_Transform(geom, 4326) WHERE ST_SRID(geom) != 4326;

-- 错误: ERROR: lwgeom_intersection: GEOS Error: UnsupportedOperationException
-- 原因: 2D 几何与 3D 几何混合操作

-- 检查维度
SELECT ST_NDims(geom), count(*) FROM my_table GROUP BY ST_NDims(geom);

-- 统一为 2D
UPDATE my_table SET geom = ST_Force2D(geom) WHERE ST_NDims(geom) > 2;

-- 诊断步骤
EXPLAIN (ANALYZE, BUFFERS)
SELECT name FROM stores WHERE ST_Intersects(geom, target_geom);

-- 检查索引是否存在
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'stores' AND indexdef LIKE '%gist%';

-- 检查统计信息
ANALYZE stores;

-- 检查查询计划
SET enable_seqscan = off;  -- 仅调试
EXPLAIN ANALYZE SELECT ...;
RESET enable_seqscan;

-- 错误: ERROR: out of memory
-- 原因: 复杂空间操作需要大量内存

-- 增加 work_mem
SET work_mem = '1GB';

-- 或分批处理
-- 大范围 UNION 操作时分块执行
WITH chunks AS (
    SELECT ST_Union(geom) AS geom
    FROM districts
    WHERE id BETWEEN 1 AND 100
    UNION ALL
    SELECT ST_Union(geom)
    FROM districts
    WHERE id BETWEEN 101 AND 200
)
SELECT ST_Union(geom) FROM chunks;

-- 统计无效几何数量
SELECT
    count(*) FILTER (WHERE ST_IsValid(geom)) AS valid_count,
    count(*) FILTER (WHERE NOT ST_IsValid(geom)) AS invalid_count,
    count(*) FILTER (WHERE ST_IsEmpty(geom)) AS empty_count,
    count(*) FILTER (WHERE geom IS NULL) AS null_count
FROM my_table;

-- 查看无效几何的详细信息
SELECT id, ST_IsValidReason(geom) AS reason
FROM my_table
WHERE NOT ST_IsValid(geom)
LIMIT 20;

-- 空几何
SELECT id FROM my_table WHERE ST_IsEmpty(geom);

-- 零长度线
SELECT id FROM roads WHERE ST_Length(geom) = 0;

-- 零面积面
SELECT id FROM districts WHERE ST_Area(geom::geography) < 1;

-- 线太短（可能是噪声）
SELECT id FROM roads WHERE ST_Length(geom::geography) < 1;  -- < 1 米

-- 重叠检测
SELECT a.id AS id_a, b.id AS id_b
FROM districts a, districts b
WHERE a.id < b.id
  AND ST_Overlaps(a.geom, b.geom);

-- 间隙检测
WITH union_geom AS (
    SELECT ST_Union(geom) AS geom FROM districts
)
SELECT ST_Difference(
    ST_Envelope(geom), geom
) AS gaps
FROM union_geom
WHERE NOT ST_Equals(ST_Envelope(geom), geom);

-- 悬挂点检测（线要素）
SELECT id
FROM roads
WHERE NOT EXISTS (
    SELECT 1 FROM roads r2
    WHERE r2.id != roads.id
      AND ST_Intersects(ST_StartPoint(roads.geom), r2.geom)
)
OR NOT EXISTS (
    SELECT 1 FROM roads r2
    WHERE r2.id != roads.id
      AND ST_Intersects(ST_EndPoint(roads.geom), r2.geom)
);

-- 通用修复函数
CREATE OR REPLACE FUNCTION fix_geometry(
    input_geom GEOMETRY,
    tolerance DOUBLE PRECISION DEFAULT 0.0
) RETURNS GEOMETRY AS $$
DECLARE
    result GEOMETRY;
BEGIN
    result := input_geom;

    -- 步骤 1: 尝试 MakeValid
    IF NOT ST_IsValid(result) THEN
        result := ST_MakeValid(result);
    END IF;

    -- 步骤 2: 如果还有问题，尝试缓冲区修复
    IF NOT ST_IsValid(result) THEN
        result := ST_Buffer(result, 0);
    END IF;

    -- 步骤 3: 如果指定了容差，简化几何
    IF tolerance > 0 AND ST_IsValid(result) THEN
        result := ST_SimplifyPreserveTopology(result, tolerance);
    END IF;

    -- 步骤 4: 确保结果有效
    IF NOT ST_IsValid(result) THEN
        RAISE WARNING 'Cannot fix geometry: %', ST_IsValidReason(result);
        RETURN NULL;
    END IF;

    RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 批量修复
UPDATE my_table
SET geom = fix_geometry(geom, 0.00001)
WHERE NOT ST_IsValid(geom);

-- 降低坐标精度（去除浮点噪声）
UPDATE my_table
SET geom = ST_GeomFromText(ST_AsText(geom, 8), ST_SRID(geom));

-- 统一坐标精度
CREATE OR REPLACE FUNCTION normalize_precision(
    geom GEOMETRY,
    precision INTEGER DEFAULT 8
) RETURNS GEOMETRY AS $$
BEGIN
    RETURN ST_GeomFromText(ST_AsText(geom, precision), ST_SRID(geom));
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- OGC 标准：外环逆时针，内环顺时针
-- PostGIS 自动处理，但某些数据源可能方向不对

-- 强制外环逆时针
UPDATE my_table
SET geom = ST_ForceRHR(geom)
WHERE ST_GeometryType(geom) = 'ST_Polygon';

-- 查看 GEOS 版本（影响几何操作）
SELECT PostGIS_GEOS_Version();

-- 开启详细错误信息
SET client_min_messages = 'debug';

-- 执行有问题的查询
SELECT ST_Intersects(geom_a, geom_b) FROM problem_table;

RESET client_min_messages;

-- 复杂查询拆分为多个步骤
-- 原始查询
SELECT * FROM a JOIN b ON ST_Intersects(a.geom, b.geom) WHERE ...;

-- 拆分排查
-- 步骤 1: 确认数据存在
SELECT count(*) FROM a WHERE ...;
SELECT count(*) FROM b WHERE ...;

-- 步骤 2: 测试边界框过滤
SELECT count(*) FROM a, b WHERE a.geom && b.geom AND ...;

-- 步骤 3: 测试精确空间关系
SELECT count(*) FROM a, b WHERE a.geom && b.geom AND ST_Intersects(a.geom, b.geom) AND ...;

-- 步骤 4: 测试完整查询
EXPLAIN ANALYZE SELECT * FROM a JOIN b ON ST_Intersects(a.geom, b.geom) WHERE ...;

-- 创建诊断视图
CREATE OR REPLACE VIEW geometry_diagnostics AS
SELECT
    'my_table' AS table_name,
    id,
    ST_GeometryType(geom) AS geom_type,
    ST_SRID(geom) AS srid,
    ST_NDims(geom) AS ndims,
    ST_NPoints(geom) AS npoints,
    ST_IsValid(geom) AS is_valid,
    ST_IsValidReason(geom) AS validity_reason,
    ST_IsEmpty(geom) AS is_empty,
    ST_IsSimple(geom) AS is_simple,
    ST_XMin(geom) AS xmin,
    ST_YMin(geom) AS ymin,
    ST_XMax(geom) AS xmax,
    ST_YMax(geom) AS ymax
FROM my_table;

-- 快速定位问题数据
SELECT * FROM geometry_diagnostics
WHERE NOT is_valid OR is_empty OR xmin < -180 OR xmax > 180;

-- 检查约束
SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'my_table'::regclass
  AND contype = 'c';

# 1. 尝试导出数据
pg_dump -U postgres -d gisdb --data-only -Fc -f recovery.dump

# 2. 如果 pg_dump 失败，尝试 COPY
docker exec postgis psql -U postgres -d gisdb -c \
  "COPY (SELECT * FROM my_table) TO STDOUT WITH (FORMAT csv)" > recovery.csv

# 3. 如果数据库无法启动
# 使用 pg_resetwal 重置 WAL（最后手段）
pg_resetwal /var/lib/postgresql/data

# 4. 从备份恢复
pg_restore -U postgres -d gisdb_new recovery.dump

-- 检查表和索引的一致性
SELECT
    schemaname, tablename,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
    pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS index_size,
    n_dead_tup,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_dead_tup DESC;

-- PostGIS 诊断报告
DO $$
DECLARE
    rec RECORD;
BEGIN
    RAISE NOTICE '=== PostGIS 诊断报告 ===';

    -- 版本信息
    RAISE NOTICE 'PostgreSQL: %', version();
    RAISE NOTICE 'PostGIS: %', PostGIS_Full_Version();

    -- 空间表统计
    FOR rec IN
        SELECT f_table_name, f_geometry_column, srid, type, count
        FROM geometry_columns gc
        JOIN (
            SELECT f_table_name, count(*)
            FROM geometry_columns
            GROUP BY f_table_name
        ) c ON gc.f_table_name = c.f_table_name
    LOOP
        RAISE NOTICE '表: %.%, SRID: %, 类型: %',
            rec.f_table_name, rec.f_geometry_column, rec.srid, rec.type;
    END LOOP;

    -- 无效几何统计
    FOR rec IN
        SELECT
            table_name,
            (xpath('/row/cnt/text()', query_to_xml(
                format('SELECT count(*) AS cnt FROM %I WHERE NOT ST_IsValid(geom)', table_name),
                false, true, ''
            )))[1]::text::int AS invalid_count
        FROM information_schema.tables
        WHERE table_schema = 'public'
    LOOP
        IF rec.invalid_count > 0 THEN
            RAISE WARNING '表 % 存在 % 个无效几何!', rec.table_name, rec.invalid_count;
        END IF;
    END LOOP;
END $$;