如何基于PostGIS的ST_MEMSIZE函数拆分大几何对象为指定大小分片

问题原因 #

你之前执行select st_subdivide(geom, 200) from regions where region = 'R';得到的几何不完整，是因为ST_Subdivide的第二个参数为单分片允许的最大顶点数，而非你需要的ST_MEMSIZE内存阈值，和你的拆分需求不匹配。

实现逻辑 #

要实现按内存大小拆分为固定数量分片，可通过递归CTE实现单条查询完成需求，逻辑如下：

• 先计算原始几何的总内存大小，算出单分片的目标内存阈值（总大小/分片数，比如总大小100、拆10片的话阈值为10）
• 递归调用ST_Subdivide拆分超过阈值的分片，直到所有分片内存低于阈值，或达到目标分片数

可用代码示例 #

步骤1：先确认原始几何总内存 #

SELECT ST_MemSize(geom) AS total_mem_size FROM regions WHERE region = 'R';

得到总大小后，即可计算单分片阈值 = 总大小 / 10。

步骤2：单条查询完成拆分 #

WITH RECURSIVE geom_shard AS (
    -- 初始加载待拆分的原始几何
    SELECT 
        geom AS shard_geom,
        ST_MemSize(geom) AS shard_mem
    FROM regions 
    WHERE region = 'R'

    UNION ALL

    -- 递归拆分超过内存阈值的分片
    SELECT 
        subdivided.shard,
        ST_MemSize(subdivided.shard)
    FROM geom_shard s
    -- 拆分顶点阈值可根据需求调整，值越小拆分粒度越细
    CROSS JOIN LATERAL ST_Subdivide(s.shard_geom, 64) AS subdivided(shard)
    WHERE 
        -- 替换为你计算得到的单分片内存阈值
        s.shard_mem > (SELECT ST_MemSize(geom)/10 FROM regions WHERE region = 'R')
        -- 避免顶点数过少的几何重复拆分
        AND ST_NPoints(s.shard_geom) > 64
)
-- 输出符合要求的分片，最多取10个
SELECT shard_geom 
FROM geom_shard
WHERE shard_mem <= (SELECT ST_MemSize(geom)/10 FROM regions WHERE region = 'R')
LIMIT 10;

拓展：直接生成新表存储分片 #

如果需要将拆分结果存入新表，只需在查询开头加建表语句即可：

CREATE TABLE regions_sharded AS
-- 上面的完整拆分查询

注意事项 #

• 拆分顶点阈值可根据你的几何复杂度调整，如果拆分后分片内存误差太大，可调低ST_Subdivide的第二个参数，获得更细的拆分粒度
• 如果递归后符合阈值的分片不足10个，可适当调低内存阈值，多拆分出部分分片后再做简单合并即可

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