如何从空间相交物化视图中选取table1各要素的最强相交匹配记录

问题原因 #

你之前的SQL存在两个核心问题：

• 没有基于已经预计算好相交面积的物化视图做筛选，子查询中不存在intersect_area和geom_area字段，排序逻辑完全不生效
• 关联逻辑错误，直接用table1的ogc_fid匹配table2的ogc_fid不符合空间连接的业务逻辑

前置说明 #

假设你创建的空间连接物化视图命名为mv_intersect_stats，请替换为你实际使用的物化视图名称。
field1为table1的唯一标识字段，如果实际你用的是其他主键（比如ogc_fid）可以自行替换分组字段。

方案1：窗口函数实现（推荐，兼容性好） #

使用ROW_NUMBER()窗口函数按table1要素分组，按相交强度倒序排序后取每组第一条，再关联table2获取描述字段，同时支持直接生成目标表：

-- 直接创建目标表
CREATE TABLE target_intersect_result AS
WITH ranked_intersect AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY field1 
            ORDER BY (intersect_area/geom_area) DESC NULLS LAST
        ) AS rn
    FROM mv_intersect_stats
)
SELECT 
    r.field1,
    r.ogc_fid,
    r.intersect_geom,
    r.geom_area,
    r.intersect_area,
    t."desc"  -- desc是SQL关键字，用双引号包裹避免语法错误
FROM ranked_intersect r
LEFT JOIN table2 t ON r.ogc_fid = t.ogc_fid
WHERE r.rn = 1;

提示：如果需要处理相交强度并列第一的场景，把ROW_NUMBER()替换为RANK()即可返回所有并列最高的记录。

结果验证 #

针对你提供的测试数据，该SQL的输出结果如下，符合预期：

方案2：LATERAL JOIN实现（如果你偏好该语法） #

CREATE TABLE target_intersect_result AS
SELECT 
    g.field1,
    mv.ogc_fid,
    mv.intersect_geom,
    mv.geom_area,
    mv.intersect_area,
    t."desc"
FROM table1 g
LEFT JOIN LATERAL (
    SELECT 
        ogc_fid,
        intersect_geom,
        geom_area,
        intersect_area
    FROM mv_intersect_stats
    WHERE mv_intersect_stats.field1 = g.field1
    ORDER BY (intersect_area/geom_area) DESC NULLS LAST
    LIMIT 1
) mv ON TRUE
LEFT JOIN table2 t ON mv.ogc_fid = t.ogc_fid;

内容的提问来源于stack exchange，提问作者gcj