sql - 空间聚类 - 将聚类属性 (id) 关联到属于聚类的几何体

Question

我在将一组几何图形与其自身属性相关联时遇到了一些问题。

数据

我有一张包含一组几何图形的表格，

buildings {
gid integer,
geom geometry(Multipoligon,4326)
}

我已经运行了函数 ST_ClusterWithin在“建筑物”表上有一定的阈值。从聚类分析中，我得到了一个名为“聚类”的表，

clusters {
cid Integer,
geom geometry(GeometryCollection,4326)
}

问题

我很想将所有几何图形及其自身的集群信息提取到一个表中。

clustered_building {
gid Integer
cid Integer
geom geometry(Multipoligon,4326)
}

 gid |    cid     |       geom            |   
-----+------------+-----------------------+
  1  |     1      | multypoligon(...)     |
  2  |     1      | multypoligon(...)     |
  3  |     1      | multypoligon(...)     |
  4  |     2      | multypoligon(...)     |
  5  |     3      | multypoligon(...)     |
  6  |     3      | multypoligon(...)     |

我做了什么(但不起作用)

我一直在尝试使用这两个函数 ST_GeometryN/ST_NumGeometries解析每个 MultiGeometry 并使用从 ST_Geometry 手册页的标准示例之一派生的此查询提取集群的信息。

INSERT INTO  clustered_building (cid, c_item , geom)
SELECT sel.cid, n, ST_GeometryN(sel.geom, n) as singlegeom
FROM ( SELECT cid, geom, ST_NumGeometries(geom) as num
       FROM clusters") AS sel
       CROSS JOIN generate_series(1,sel.num) n
WHERE n <= ST_NumGeometries(sel.geom);

查询，如果我强制使用10个系列，它需要几秒钟。

CROSS JOIN generate_series(1,10)

但是当我要求根据每个 GeometryCollection 中的项目数生成一个系列时，它卡住了。而且，这个查询不允许我将单个几何体链接到他自己的特征到建筑表中，因为我丢失了“gid”

谁能帮帮我，谢谢

斯特凡诺

Answer 1

我没有你的数据，但使用一些虚拟值，其中 id 1、2 和 3 相交，4 和 5 相交，你可以执行如下操作:

WITH 
  temp (id, geom) AS 
    (VALUES (1, ST_Buffer(ST_Makepoint(0, 0), 2)),
    (2, ST_Buffer(ST_MakePoint(1, 1), 2)),
    (3, ST_Buffer(ST_MakePoint(2, 2), 2)), 
    (4, ST_Buffer(ST_MakePoint(9, 9), 2)), 
    (5, ST_Buffer(ST_MakePoint(10, 10), 2))),
  clusters(geom) as 
    (SELECT 
        ST_Makevalid(
          ST_CollectionExtract(
              unnest(ST_ClusterIntersecting(geom)), 3)) 
      FROM temp
    )
 SELECT array_agg(temp.id), cl.geom 
   FROM clusters cl, temp 
  WHERE ST_Intersects(cl.geom, temp.geom) 
GROUP BY cl.geom;

如果将最终的 cl.geom 包装为 ST_AsText，您将看到如下内容:

{1,2,3} | MULTIPOLYGON(((2.81905966523328 0.180940334766718,2.66293922460509 -0.111140466039203,2.4142135623731 -0.414213562373094,2.11114046603921 -0.662939224605089,1.81905966523328 -0.819059665233282,1.84775906502257 -0.765366864730179,1.96157056080646 -0.390180644032256,2 0,2 3.08780778723872e-16,2 0,2.39018064403226 0.0384294391935396,2.76536686473018 0.152240934977427,2.81905966523328 0.180940334766718))......

{4,5} | MULTIPOLYGON(((10.8190596652333 8.18094033476672,10.6629392246051 7.8888595339608,10.4142135623731 7.58578643762691,10.1111404660392 7.33706077539491,9.76536686473018 7.15224093497743,9.39018064403226 7.03842943919354,9 7,8.60981935596775 7.03842943919354,8.23463313526982 7.15224093497743,7.8888595339608 7.33706077539491,7.58578643762691 7.5857864376269,7.33706077539491 7.88885953396079,7.15224093497743 8.23463313526982

您可以在第一个多边形下方看到 id 1,2,3，另一个多边形 4,5。

总体思路是对数据进行聚类，然后使用 array_agg 将返回的聚类与原始数据相交。将 ID 组合在一起，以便返回的多边形现在包含原始 ID。 ST_CollectionExtract的使用以 3 作为第二个参数，结合 unnest ，它将 ST_ClusterIntersecting 返回的几何集合拆分回行，将每个连续的簇作为 (Multi)Polygon 返回。 ST_MakeValid是因为有时当您将几何与其他相关几何相交时，例如原始多边形与您的聚类多边形，您会得到奇怪的舍入效果和关于非节点交叉等的 GEOS 错误。

我回答了一个similar question最近在 gis.stackexchange 上，您可能会觉得有用。