PostgreSQL中如何为单一路段关联不同限速值？

错误原因 #

你遇到的SQL Error [21000]本质是：mtl_road_network中部分seg_id在speed_signs_geob表中对应多条记录（即同一路段存在多个限速值），而赋值用的子查询要求必须返回单行单值，多行结果直接赋值就会触发报错。

针对性解决方案 #

根据你的业务需求，可选择以下几种处理方式：

1. 取路段的代表性限速值 #

如果不需要区分路段首尾的限速，只想给路段设置一个统一的参考限速，可以用聚合函数（如MAX/MIN）从多个限速值中选一个：

-- 示例：取该路段的最高限速
UPDATE mtl_road_network r
SET speed = (SELECT MAX(s.speed) FROM speed_signs_geob s WHERE s.seg_id = r.seg_id)
-- 加上WHERE EXISTS避免无匹配的路段被更新为NULL（如果原字段不允许NULL，可结合DEFAULT）
WHERE EXISTS (SELECT 1 FROM speed_signs_geob s WHERE s.seg_id = r.seg_id);

如果需要按时间优先级选择（比如最新录入的限速），可以结合时间字段排序后取第一条：

UPDATE mtl_road_network r
SET speed = (SELECT s.speed 
             FROM speed_signs_geob s 
             WHERE s.seg_id = r.seg_id
             ORDER BY s.create_time DESC 
             LIMIT 1)
WHERE EXISTS (SELECT 1 FROM speed_signs_geob s WHERE s.seg_id = r.seg_id);

2. 拆分路段匹配对应限速 #

如果需要严格区分路段首尾的不同限速，应该将原路段按限速标志的位置切割，让每个子路段对应一个限速值（需要PostGIS支持）：

-- 步骤1：切割路段并关联限速
WITH split_segments AS (
    SELECT
        r.seg_id AS original_seg_id,
        -- 用限速点集合切割原路段
        (ST_Dump(ST_Split(r.geom, ST_Collect(s.geom)))).geom AS split_geom,
        s.speed
    FROM mtl_road_network r
    JOIN speed_signs_geob s ON r.seg_id = s.seg_id
    -- 只处理有多限速的路段
    WHERE (SELECT COUNT(*) FROM speed_signs_geob s2 WHERE s2.seg_id = r.seg_id) > 1
)
-- 步骤2：将切割后的子路段插入新表（或替换原表数据）
INSERT INTO split_road_network (original_seg_id, geom, speed)
SELECT original_seg_id, split_geom, speed
FROM split_segments;

注：切割后可能需要额外处理子路段与限速的精准匹配（比如用ST_ClosestPoint判断每个子路段对应的限速标志）。

3. 存储所有限速值 #

如果需要保留该路段的所有限速信息，可以将speed字段改为数组类型：

-- 先修改字段类型
ALTER TABLE mtl_road_network ALTER COLUMN speed TYPE SMALLINT[];

-- 批量更新路段的所有限速值（自动去重）
UPDATE mtl_road_network r
SET speed = (SELECT ARRAY_AGG(DISTINCT s.speed) FROM speed_signs_geob s WHERE s.seg_id = r.seg_id)
WHERE EXISTS (SELECT 1 FROM speed_signs_geob s WHERE s.seg_id = r.seg_id);

更新后，路段的speed字段会是类似{30,50}的数组，包含该路段所有不同的限速值。

前置排查建议 #

在执行更新前，先找出所有存在多限速的路段，方便针对性调整：

SELECT r.seg_id, COUNT(DISTINCT s.speed) AS distinct_speed_count
FROM mtl_road_network r
JOIN speed_signs_geob s ON r.seg_id = s.seg_id
GROUP BY r.seg_id
HAVING COUNT(DISTINCT s.speed) > 1;

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