SQL中如何查找多个相交多边形的唯一公共点

我懂你现在的困扰——你想找出和areas_map_shapes表中shape_id=206的图形相交的所有map_shapes图形的唯一公共点，但之前用STCentroid()取质心、用UnionAggregate()聚合图形的方法都没拿到想要的结果，这是因为这两个方法的逻辑和你的需求不匹配：

• STCentroid()返回的是几何图形的中心，它不一定在两个图形的相交区域上，更不是实际的交点；
• UnionAggregate()是把所有相交图形合并成一个整体，它的质心是合并后图形的中心，和你要的公共交点完全不是一回事。

针对单个相交对的公共点 #

如果你的需求是每个和shape_id=206相交的图形，各自与它的公共交点，可以用下面的SQL：

SELECT 
    -- 根据相交结果的类型返回对应点：如果是点直接用，否则取相交区域上的实际点
    CASE 
        WHEN p1.shape_data.STIntersection(p2.shape_data).STGeometryType() = 'Point' 
        THEN p1.shape_data.STIntersection(p2.shape_data)
        ELSE p1.shape_data.STIntersection(p2.shape_data).STPointOnSurface()
    END AS common_point,
    -- 提取经度和纬度
    CASE 
        WHEN p1.shape_data.STIntersection(p2.shape_data).STGeometryType() = 'Point' 
        THEN p1.shape_data.STIntersection(p2.shape_data).STX
        ELSE p1.shape_data.STIntersection(p2.shape_data).STPointOnSurface().STX
    END AS longitude,
    CASE 
        WHEN p1.shape_data.STIntersection(p2.shape_data).STGeometryType() = 'Point' 
        THEN p1.shape_data.STIntersection(p2.shape_data).STY
        ELSE p1.shape_data.STIntersection(p2.shape_data).STPointOnSurface().STY
    END AS latitude
FROM map_shapes p1
INNER JOIN areas_map_shapes p2 
    ON p2.shape_data.STIntersects(p1.shape_data) = 1
WHERE 
    p2.shape_id = 206
    AND p1.status = 1
    AND p2.status = 1
    AND p2.country_id = 4

这段代码的逻辑是：

1. 先计算两个图形的相交部分（STIntersection()）；
2. 判断相交部分是不是点，如果是直接返回；
3. 如果相交部分是线或多边形，用STPointOnSurface()获取相交区域上的一个实际存在的点（这个点一定在相交区域内，比质心更符合“公共点”的定义）。

针对所有相交图形的共同公共点 #

如果你的需求是所有和shape_id=206相交的图形，它们之间共同的那个公共点（也就是所有图形都交汇的同一个点），可以用聚合交集的方法：

WITH intersecting_shapes AS (
    -- 先筛选出所有和shape_id=206相交的map_shapes图形
    SELECT ss.shape_data
    FROM areas_map_shapes T
    INNER JOIN map_shapes SS 
        ON SS.shape_data.STIntersects(T.shape_data) = 1
    WHERE 
        T.shape_id = 206
        AND T.status = 1
        AND SS.status = 1
        AND T.country_id = 4
)
SELECT 
    -- 计算所有相交图形的共同交集，再取交集中的点
    geometry::IntersectionAggregate(shape_data).STPointOnSurface() AS shared_common_point,
    geometry::IntersectionAggregate(shape_data).STPointOnSurface().STX AS longitude,
    geometry::IntersectionAggregate(shape_data).STPointOnSurface().STY AS latitude
FROM intersecting_shapes

IntersectionAggregate()函数会计算所有输入图形的交集，也就是所有图形都共同覆盖的区域，再用STPointOnSurface()提取这个区域上的点，就能得到所有图形的唯一公共点（如果存在的话）。

补充说明 #

从你的相交图形示意图来看（中心多边形与多个外围多边形相交，每个外围都和中心有重叠区域），如果是要每个外围图形和中心图形的相交点，用第一种方法；如果是要所有外围图形和中心图形共同的那个交点，用第二种方法。

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