postgresql - 使用 st_dwithin 限制将 table raw 分成 Postgres 中的 block

Question

我有一个包含线串的表，我想将其分成 block ，这些 block 的 ID 列表不高于为每个 block 提供的编号，并且仅存储在一定距离内的线。

比如我有一个14行的表

create table lines ( id integer primary key, geom geometry(linestring) );
insert into lines (id, geom) values ( 1, 'LINESTRING(0 0, 0 1)');
insert into lines (id, geom) values ( 2, 'LINESTRING(0 1, 1 1)');
insert into lines (id, geom) values ( 3, 'LINESTRING(1 1, 1 2)');
insert into lines (id, geom) values ( 4, 'LINESTRING(1 2, 2 2)');
insert into lines (id, geom) values ( 11, 'LINESTRING(2 2, 2 3)');
insert into lines (id, geom) values ( 12, 'LINESTRING(2 3, 3 3)');
insert into lines (id, geom) values ( 13, 'LINESTRING(3 3, 3 4)');
insert into lines (id, geom) values ( 14, 'LINESTRING(3 4, 4 4)');
create index lines_gix on lines using gist(geom);

我想将它分成 block ，每个 block 有 3 个 id，每个 block 的行距彼此或第一个在 2 米以内。

我想从这个例子中得到的结果是:

| Chunk No.|  Id chunk list |
|----------|----------------|
|     1    |    1, 2, 3     |
|     2    |    4, 5, 6     |
|     3    |    7, 8, 9     |
|     4    |   10, 11, 12   |
|     5    |      13, 14    |

我尝试使用 st_clusterwithin但是当行彼此靠近时，它将返回所有未拆分成 block 的行。

我还尝试使用一些具有递归魔法的方法，例如 Paul Ramsey 提供的答案中的那个here .但我不知道如何修改查询以返回有限的分组 ID 列表。

Answer 1

我不确定这是否是最佳答案，所以如果有人有更好的方法或知道如何改进提供的答案，请随时更新它。通过对 Paul 的回答稍作修改，我成功地创建了以下查询，这些查询正在执行我的要求。

    -- Create function for easier interaction
CREATE OR REPLACE FUNCTION find_connected(integer, double precision, integer, integer[])
  returns integer[] AS
$$
WITH RECURSIVE lines_r AS -- Recursive allow to use the same query on the output - is like continues append to result and use it inside a query
    (SELECT ARRAY[id] AS idlist,
            geom, id
           FROM lines
           WHERE id = $1
    UNION ALL
    SELECT array_append(lines_r.idlist, lines.id) AS idlist, -- append id list to array
           lines.geom                             AS geom,   -- keep geometry
           lines.id                               AS id -- keep source table id
    FROM (SELECT * FROM lines WHERE NOT $4 @> array[id]) lines, lines_r -- from source table and recursive table
    WHERE ST_DWITHIN(lines.geom, lines_r.geom, $2) -- where lines are within 2 meters
      AND NOT lines_r.idlist @> ARRAY[lines.id] -- recursive id list array not contain lines array
     AND array_length(idlist, 1) <= $3
    )
SELECT idlist
FROM lines_r WHERE array_length(idlist, 1) <= $3 ORDER BY array_length(idlist, 1) DESC LIMIT 1;
$$
LANGUAGE 'sql';

-- Create id chunks
WITH RECURSIVE groups_r AS (
    (SELECT find_connected(id, 2, 3, ARRAY[id]) AS idlist, find_connected(id, 2, 3, ARRAY[id]) AS grouplist, id
                             FROM lines WHERE id = 1)
    UNION ALL
    (SELECT array_cat(groups_r.idlist, find_connected(lines.id, 2, 3, groups_r.idlist)) AS idlist,
            find_connected(lines.id, 2, 3, groups_r.idlist)            AS grouplist,
            lines.id
     FROM lines,
          groups_r
     WHERE NOT groups_r.idlist @> ARRAY[lines.id]
     LIMIT 1))
SELECT 
--        (SELECT array_agg(DISTINCT x) FROM unnest(idlist) t (x))    idlist, -- left for better understanding what is happening
       row_number() OVER () chunk_id,
       (SELECT array_agg(DISTINCT x) FROM unnest(grouplist) t (x)) grouplist,
       id input_line_id
FROM groups_r;

唯一的问题是，当 chunk 中的 id 数量增加时，性能是相当纯粹的。对于一个包含 300 行和每个 block 20 个 ID 的表，执行时间约为 15 分钟，即使在几何和 ID 列上有索引也是如此。