sql - 使用 postgis 将转储的多部分几何体选择到数组中

Question

我正在对多部分多边形表运行以下查询:

SELECT id, surface_cnrtd, array_agg(igeom)
FROM (select id, st_astext(st_pointonsurface(geom)) as surface_cnrtd, 
          st_dump(geom) as igeom from my_table where id = '10020080') sub
GROUP BY dauid, surface_cnrtd;

我想返回一行(我已经指出，pointonsurface 的点几何，多部分多边形的每个单独部分的路径和几何的数组)。我知道多边形 10020080 是多部分的，因为如果我将查询更改为:

SELECT id, surface_cnrtd, array_agg(igeom)
 FROM (select id, st_astext(st_pointonsurface(geom)) as surface_cnrtd, 
          (st_dump(geom)).path as igeom from my_table where id = '10020080') sub
 GROUP BY dauid, surface_cnrtd;

它返回这个:

('10020080', 'POINT(-54.3834654151038 47.6947475)', [[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19]])

但是当我运行原始查询时，我的数组只有第一个几何图形，如下所示:

('10020080', 'POINT(-54.3834654151038 47.6947475)', '{"({1},0103000020E61000000..........................BC0F110C64FE3E64740)"}

谁能帮我把转储的多边形的路径和几何形状放到一个数组中？

Answer 1

您可以将第一个查询放入 CTE (又名 WITH 子句)然后根据需要聚合转储的几何图形:

WITH j AS(
SELECT 1 AS id, 'POINT(-54.3834654151038 47.6947475)'::geometry AS surface_cnrtd,
   ST_Dump('MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)),
           ((15 5, 40 10, 10 20, 5 10, 15 5)))') AS geom 
)
SELECT j.id, ST_AsText(j.surface_cnrtd), array_agg(j.geom)
FROM j
GROUP BY j.id, j.surface_cnrtd;


 id |              st_astext              |                                                                                                                                                                                array_agg                                                                                                                                                                                
----+-------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 | POINT(-54.3834654151038 47.6947475) | {"({1},010300000001000000040000000000000000003E40000000000000344000000000008046400000000000004440000000000000244000000000000044400000000000003E400000000000003440)","({2},010300000001000000050000000000000000002E4000000000000014400000000000004440000000000000244000000000000024400000000000003440000000000000144000000000000024400000000000002E400000000000001440)"}
(1 Zeile)

如果你想坚持你的语法......

SELECT id, ST_AsText(surface_cnrtd), array_agg(igeom)
FROM (SELECT 1 AS id, 'POINT(-54.3834654151038 47.6947475)'::geometry AS surface_cnrtd, 
      ST_Dump('MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)),
           ((15 5, 40 10, 10 20, 5 10, 15 5)))') as igeom) sub
GROUP BY id, surface_cnrtd;

id |              st_astext              |                                                                                                                                                                                array_agg                                                                                                                                                                                
----+-------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 | POINT(-54.3834654151038 47.6947475) | {"({1},010300000001000000040000000000000000003E40000000000000344000000000008046400000000000004440000000000000244000000000000044400000000000003E400000000000003440)","({2},010300000001000000050000000000000000002E4000000000000014400000000000004440000000000000244000000000000024400000000000003440000000000000144000000000000024400000000000002E400000000000001440)"}
(1 Zeile)