在Amazon Redshift中查询相互连接的Linestring几何ID列表

我有一个包含Linestring类型几何数据的ID列表，想要在Amazon Redshift中编写SQL查询，把相互连接的几何对象ID用LISTAGG聚合返回。期望输出是分组后的连接ID列表，示例如下：

现有表结构及示例数据：

我是Amazon Redshift新手，尝试用ST_Buffer和提取几何端点的方法，但后续逻辑卡住了，现有代码如下：

CREATE TEMP TABLE geoList
(ID BIGINT,
AB_Coords VARCHAR);

INSERT INTO geoList
SELECT ID, 
CONCAT(SPLIT_PART(vector,',',1),')') AS startEndPoint
FROM geometry
WHERE ID IN (0001, 0002, 0003, 0004, 0005, 0006, 0007, 0008, 0009, 0010, 0011, 0012, 0013, 0014, 0015, 0016, 0017, 0018, 0019);

INSERT INTO geoList
SELECT ID, 
CONCAT('LINESTRING (',TRIM(SPLIT_PART(vector,',',LEN(vector)-LEN(REPLACE(vector,',',''))+1))) AS startEndPoint
FROM geometry
WHERE ID IN (0001, 0002, 0003, 0004, 0005, 0006, 0007, 0008, 0009, 0010, 0011, 0012, 0013, 0014, 0015, 0016, 0017, 0018, 0019);

SELECT *, g1.ID = g2.ID AS sameGeo FROM geoList g1
LEFT JOIN geoList g2
ON g1.AB_Coords = g2.AB_Coords

请求帮助完成该查询。

要实现这个需求，核心是识别出所有端点相连的线串组成的连通组，再对每个组的ID进行聚合。Redshift的空间函数和递归CTE可以高效完成这个任务，具体代码如下：

WITH geo_endpoints AS (
    SELECT 
        ID,
        -- 把字符串格式的Linestring转换为几何类型，提取起点
        ST_StartPoint(ST_GeomFromText(Vector)) AS start_point,
        -- 提取终点
        ST_EndPoint(ST_GeomFromText(Vector)) AS end_point
    FROM geometry
    WHERE ID IN (0001, 0002, 0003, 0004, 0005, 0006, 0007, 0008, 0009, 0010, 0011, 0012, 0013, 0014, 0015, 0016, 0017, 0018, 0019)
),
recursive_connections AS (
    -- 初始：每个ID单独作为一个组
    SELECT 
        ID AS root_id,
        ID,
        start_point,
        end_point
    FROM geo_endpoints
    
    UNION ALL
    
    -- 递归：找到与当前组内任意端点相连的其他线串，加入同一组
    SELECT 
        rc.root_id,
        ge.ID,
        ge.start_point,
        ge.end_point
    FROM recursive_connections rc
    JOIN geo_endpoints ge 
        -- 判断端点是否完全重合（精确连接）
        ON (ST_Equals(rc.end_point, ge.start_point) OR ST_Equals(rc.start_point, ge.end_point))
        -- 避免重复添加同一个ID
        AND ge.ID NOT IN (SELECT ID FROM recursive_connections WHERE root_id = rc.root_id)
),
unique_groups AS (
    -- 去重，确保每个ID只属于一个组
    SELECT DISTINCT root_id, ID
    FROM recursive_connections
)
-- 聚合每个连通组的ID
SELECT 
    LISTAGG(ID::VARCHAR, ', ') WITHIN GROUP (ORDER BY ID) AS IDs
FROM unique_groups
GROUP BY root_id
ORDER BY root_id;

关键说明 #

1. 几何类型转换：如果你的Vector列已经是Redshift的GEOMETRY类型，直接去掉ST_GeomFromText，用ST_StartPoint(Vector)即可。
2. 近似连接处理：若线串是端点近似重合的连接，把ST_Equals替换为ST_DWithin，设置合适的距离阈值，比如：ST_DWithin(rc.end_point, ge.start_point, 0.001)（单位根据数据坐标系调整）。
3. 递归深度调整：若连通链很长，Redshift默认递归深度100可能不够，执行前先设置：SET max_recursion_depth = 1000;（可按需调整数值）。

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