PostGIS 空间分析优化方案

前端提交坐标 → Controller 参数解析

    → HTTP POST ArcGIS Server SOE（spatialAnalysis_Intersect / Query / Buffer 等）

    → 等待 ArcGIS Server 计算（通常 5~30 秒）

    → 返回大 JSON（features + analysisResult）

    → 写入 Redis（TTL 10 分钟 ~ 5 小时）

    → Java 内存中 groupBy + sum 聚合

    → 返回前端

前端提交坐标

    │

    ├─ 简单分析（面积、相交、缓冲、统计）

    │   → REST API → PostGIS SQL → 直接返回聚合结果（< 1 秒）

    └─ 复杂分析（多图层联合、大数据量）

        → REST API → PostGIS 预计算 + ArcGIS SOE 补充（3~5 秒）

-- 扩展现有 ServicePhysical 表的空间能力
CREATE TABLE spatial_layer (
    id              VARCHAR(32) PRIMARY KEY,
    service_id      VARCHAR(32) NOT NULL,          -- 关联 DataService
    physical_id     VARCHAR(32) NOT NULL,          -- 关联 ServicePhysical
    layer_name      VARCHAR(128),                   -- 图层名称
    geometry_type   VARCHAR(32),                    -- POINT/LINESTRING/POLYGON/MULTIPOLYGON
    srid            INTEGER DEFAULT 4490,           -- 空间参考系（CGCS2000 / 国家大地2000）
    feature_count   BIGINT,                         -- 要素数量
    bbox            GEOMETRY(POLYGON, 4490),       -- 外包矩形
    created_at      TIMESTAMP DEFAULT NOW(),
    updated_at      TIMESTAMP DEFAULT NOW()
);

-- 空间索引
CREATE INDEX idx_spatial_layer_bbox ON spatial_layer USING GIST (bbox);

-- 每个空间图层对应一张要素表，由 DataService 初始化时自动创建
CREATE TABLE IF NOT EXISTS feature_{physicalId} (
    id              SERIAL PRIMARY KEY,
    objectid        INTEGER UNIQUE NOT NULL,         -- ArcGIS OBJECTID（UNIQUE 约束用于 ON CONFLICT 更新）
    geom            GEOMETRY(GEOMETRY, 4490),       -- 几何字段
    properties      JSONB,                           -- 属性字段（原 ArcGIS Feature attributes）
    created_at      TIMESTAMP DEFAULT NOW()
);

-- 空间索引（核心）
CREATE INDEX idx_feature_{physicalId}_geom ON feature_{physicalId} USING GIST (geom);

-- 属性索引（用于统计 GROUP BY）
CREATE INDEX idx_feature_{physicalId}_props ON feature_{physicalId} USING GIN (properties);

-- 常用分析的预计算结果
CREATE MATERIALIZED VIEW mv_compliance_analysis AS
SELECT
    f.objectid,
    f.geom,
    f.properties ->> 'DLMC'  AS dlmc,       -- 地类名称（JSONB 字段提取）
    f.properties ->> 'QSXZ'  AS qsxz,       -- 权属性质
    ST_Area(f.geom::geography) AS area_m2    -- 面积（平方米）
FROM feature_{physicalId} f;

CREATE INDEX idx_mv_compliance_geom ON mv_compliance_analysis USING GIST (geom);

@Service
public class PostGisAnalysisService {

    private final JdbcTemplate jdbcTemplate;

    /**
     * 合规性分析：计算用户绘制范围与各图层的相交面积，按属性分组聚合
     */
    public List<AnalysisResult> complianceAnalysis(
            String geomWkt,        // 用户绘制多边形（WKT 格式）
            String layerId,        // 目标图层
            List<String> groupBy   // 分组字段，仅使用第一个元素
    ) {
        String sql = "WITH user_geom AS ( "
            + "    SELECT ST_GeomFromText(?, 4490) AS geom "
            + "), "
            + "intersect_result AS ( "
            + "    SELECT "
            + "        f.properties, "
            + "        ST_Area( "
            + "            ST_Intersection(f.geom, u.geom)::geography "
            + "        ) AS overlap_area "
            + "    FROM feature_" + layerId + " f, user_geom u "
            + "    WHERE ST_Intersects(f.geom, u.geom) "
            + "      AND f.properties IS NOT NULL "
            + ") "
            + "SELECT "
            + "    properties ->> ? AS group_key, "
            + "    SUM(overlap_area) AS total_area, "
            + "    COUNT(*) AS feature_count "
            + "FROM intersect_result "
            + "GROUP BY properties ->> ? "
            + "ORDER BY total_area DESC";

        return jdbcTemplate.query(sql, new Object[]{geomWkt, groupBy.get(0), groupBy.get(0)},
            (rs, rowNum) -> AnalysisResult.builder()
                .groupKey(rs.getString("group_key"))
                .totalArea(rs.getDouble("total_area"))
                .featureCount(rs.getInt("feature_count"))
                .build()
        );
    }
}

/**
 * 空间范围查询：查找与指定范围相交的所有图层
 */
public List<SpatialLayerVO> queryByBoundingBox(
        double minLng, double minLat,
        double maxLng, double maxLat
) {
    String sql = "SELECT sl.*, sp.name AS physical_name, ds.display_name "
        + "FROM spatial_layer sl "
        + "JOIN service_physical sp ON sl.physical_id = sp.id "
        + "JOIN data_service ds ON sl.service_id = ds.id "
        + "WHERE ST_Intersects( "
        + "    sl.bbox, "
        + "    ST_MakeEnvelope(?, ?, ?, ?, 4490) "
        + ")";
    return jdbcTemplate.query(sql, new Object[]{minLng, minLat, maxLng, maxLat},
        spatialLayerRowMapper);
}

/**
 * 点查询：查找覆盖某个坐标的所有图斑
 */
public List<FeatureVO> pointQuery(double lng, double lat, String layerId) {
    String sql = "SELECT f.*, ST_AsGeoJSON(f.geom) AS geojson "
        + "FROM feature_" + layerId + " f "
        + "WHERE ST_Contains(f.geom, ST_SetSRID(ST_MakePoint(?, ?), 4490))";
    return jdbcTemplate.query(sql, new Object[]{lng, lat}, featureRowMapper);
}

/**
 * 优化后：一条 SQL 完成空间裁剪 + 属性分组 + 面积聚合 + 面积修正
 * 注意：groupByFields 来自空间分析配置表，非用户直接输入，可安全拼接
 */
public List<Map<String, Object>> postgisStatistics(
        String geomWkt,
        String layerId,
        List<String> groupByFields
) {
    // 构建 JSONB 字段提取的 SQL 片段（如：properties->>'DLMC' || '-' || properties->>'QSXZ'）
    String groupExpr = groupByFields.stream()
        .map(f -> "properties ->> '" + f + "'")
        .collect(Collectors.joining(" || '-' || "));

    // 表名使用 layerId 白名单校验过的值拼接（来自 ServicePhysical.id，非用户输入）
    String sql = "WITH user_geom AS ( "
        + "    SELECT ST_GeomFromText(?, 4490) AS geom "
        + "), "
        + "overlap AS ( "
        + "    SELECT "
        + "        f.properties, "
        + "        CASE "
        + "            WHEN ST_Area(ST_Intersection(f.geom, u.geom)::geography) < 0.4 THEN 0.00 "
        + "            WHEN ST_Area(ST_Intersection(f.geom, u.geom)::geography) BETWEEN 0.4 AND 1 THEN 1.00 "
        + "            ELSE ROUND(ST_Area(ST_Intersection(f.geom, u.geom)::geography)::numeric, 2) "
        + "        END AS overlap_area "
        + "    FROM feature_" + layerId + " f, user_geom u "
        + "    WHERE ST_Intersects(f.geom, u.geom) "
        + ") "
        + "SELECT "
        + "    (" + groupExpr + ") AS group_key, "
        + "    SUM(overlap_area) AS total_area, "
        + "    COUNT(*) AS count "
        + "FROM overlap "
        + "GROUP BY group_key "
        + "ORDER BY total_area DESC";

    return jdbcTemplate.queryForList(sql, geomWkt);
}

/**
 * 缓冲区分析：为指定图层创建缓冲区，计算与目标图层的相交面积
 */
public List<BufferResult> bufferAnalysis(
        String sourceLayerId,   // 源图层
        String targetLayerId,   // 目标图层
        double bufferRadius     // 缓冲半径（米）
) {
    String sql = "WITH buffered AS ( "
        + "    SELECT "
        + "        f.objectid, "
        + "        ST_Buffer(f.geom::geography, ?)::geometry AS buffer_geom "
        + "    FROM feature_" + sourceLayerId + " f "
        + "), "
        + "intersect_result AS ( "
        + "    SELECT "
        + "        b.objectid, "
        + "        t.properties, "
        + "        ST_Area(ST_Intersection(b.buffer_geom, t.geom)::geography) AS overlap_area "
        + "    FROM buffered b "
        + "    JOIN feature_" + targetLayerId + " t "
        + "      ON ST_Intersects(b.buffer_geom, t.geom) "
        + ") "
        + "SELECT "
        + "    objectid, "
        + "    properties ->> 'DLMC' AS land_type, "
        + "    SUM(overlap_area) AS total_area "
        + "FROM intersect_result "
        + "GROUP BY objectid, properties ->> 'DLMC'";

    return jdbcTemplate.query(sql, new Object[]{bufferRadius}, bufferResultRowMapper);
}

/**
 * 多控制线检测：一个用户范围同时与多个控制线图层求交
 */
public MultiCheckResult multiLayerCheck(String geomWkt, List<String> layerIds) {
    // 构建 UNION ALL 子查询（layerIds 来自配置表，非用户输入）
    String unionQueries = layerIds.stream()
        .map(id -> "SELECT '" + id + "' AS layer_id, f.properties, "
            + "       ST_Area(ST_Intersection(f.geom, u.geom)::geography) AS overlap_area "
            + "FROM feature_" + id + " f, user_geom u "
            + "WHERE ST_Intersects(f.geom, u.geom)")
        .collect(Collectors.joining(" UNION ALL "));

    String sql = "WITH user_geom AS ( "
        + "    SELECT ST_GeomFromText(?, 4490) AS geom "
        + ") "
        + unionQueries;

    return jdbcTemplate.query(sql, new Object[]{geomWkt}, multiCheckRowMapper);
}

-- 示例：CGCS2000 (4490) → Web Mercator (3857) 用于前端展示
SELECT ST_AsGeoJSON(ST_Transform(geom, 3857)) FROM feature_{id};

-- 示例：CGCS2000 (4490) → WGS84 (4326) 用于对接外部 GPS 数据
SELECT ST_Transform(geom, 4326) FROM feature_{id};

-- 基础空间索引
CREATE INDEX idx_feature_geom ON feature_{id} USING GIST (geom);

-- 带条件的分区索引（按行政区划）
CREATE INDEX idx_feature_geom_{district} ON feature_{id}
    USING GIST (geom)
    WHERE properties ->> 'REGION_CODE' = '{district}';

-- 批量插入后重建索引（比逐条插入快 10~50 倍）
BEGIN;
DROP INDEX IF EXISTS idx_feature_geom;
INSERT INTO feature_{id} (objectid, geom, properties) VALUES ...;
CREATE INDEX idx_feature_geom ON feature_{id} USING GIST (geom);
COMMIT;

-- 定期 VACUUM 分析
VACUUM ANALYZE feature_{id};

-- 设置 GiST 索引查询的预估返回行数（影响执行计划）
ALTER TABLE feature_{id} ALTER COLUMN geom SET STATISTICS 1000;

-- 强制使用空间索引
SET enable_seqscan = off;  -- 调试用，生产环境依赖查询优化器自动选择

/**
 * 从 ArcGIS MapServer 同步全量要素到 PostGIS
 */
@Service
public class FeatureSyncService {

    private final JdbcTemplate jdbcTemplate;
    private final DataServiceService dataServiceService;

    @Transactional
    public void syncFeatures(String dataServiceId) {
        DataService ds = dataServiceService.find(dataServiceId);
        String layerId = ds.getPhysicalId();

        // 1. 从 ArcGIS MapServer 分页查询全量要素
        String url = ds.getServiceUrl() + "/query?where=1=1&outFields=*&returnGeometry=true&f=json";

        List<Feature> features = fetchAllFeatures(url);

        // 2. 批量 INSERT 到 PostGIS（依赖 feature_{id} 表的 objectid UNIQUE 约束）
        String sql = "INSERT INTO feature_" + layerId
            + " (objectid, geom, properties) VALUES (?, ST_GeomFromGeoJSON(?), ?::jsonb)"
            + " ON CONFLICT (objectid) DO UPDATE SET"
            + "     geom = EXCLUDED.geom,"
            + "     properties = EXCLUDED.properties";

        jdbcTemplate.batchUpdate(sql, features, 500, (ps, feature) -> {
            ps.setInt(1, feature.getObjectId());
            ps.setString(2, feature.toGeoJSON());
            ps.setString(3, feature.toJSON());
        });

        // 3. 更新空间元数据（表名直接拼接 layerId，参数值通过 ? 绑参）
        String metaSql = "UPDATE spatial_layer SET "
            + "    feature_count = ?,"
            + "    bbox = (SELECT ST_Extent(geom) FROM feature_" + layerId + "),"
            + "    updated_at = NOW() "
            + "WHERE physical_id = ?";

        jdbcTemplate.update(metaSql, features.size(), layerId);
    }
}

-- 增量同步日志表
CREATE TABLE spatial_sync_log (
    id              SERIAL PRIMARY KEY,
    physical_id     VARCHAR(32),
    operation       VARCHAR(16),    -- INSERT/UPDATE/DELETE
    objectid        INTEGER,
    synced_at       TIMESTAMP DEFAULT NOW()
);

-- 增量更新触发器（简化示例，需处理 INSERT/UPDATE/DELETE 三种情况）
CREATE OR REPLACE FUNCTION feature_sync_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO spatial_sync_log (physical_id, operation, objectid)
        VALUES (TG_TABLE_NAME, TG_OP, OLD.objectid);
        RETURN OLD;
    ELSE
        INSERT INTO spatial_sync_log (physical_id, operation, objectid)
        VALUES (TG_TABLE_NAME, TG_OP, NEW.objectid);
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

Quartz Job: FeatureSyncJob（每天凌晨 3:00 全量 + 每小时增量）
    │
    ├─ 全量同步：truncate + batch insert（周末/夜间）
    │
    └─ 增量同步：按 ArcGIS Server 的 editDate 字段筛选
        └─ lastModified > lastSyncTime → UPSERT

-- PostgreSQL 11 安装 PostGIS
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;    -- 拓扑扩展（可选）
CREATE EXTENSION fuzzystrmatch;       -- 模糊匹配（可选）

-- 验证安装
SELECT PostGIS_Version();
SELECT ST_SetSRID(ST_MakePoint(116.4, 39.9), 4490);

# application-dev.yml 新增

spring:

  datasource:

    url: jdbc:postgresql://192.168.179.106:5432/giscloud

    hikari:

      connection-init-sql: SELECT 1  # 初始化连接时检查

      maximum-pool-size: 20          # 空间查询需更大连接池

<!-- PostGIS JDBC（可选，用于 Geometry 类型映射） -->

<dependency>

    <groupId>net.postgis</groupId>

    <artifactId>postgis-jdbc</artifactId>

    <version>2.5.1</version>

</dependency>

<!-- 或使用 Hibernate Spatial -->

<dependency>

    <groupId>org.hibernate</groupId>

    <artifactId>hibernate-spatial</artifactId>

</dependency>