如何使用PostGIS按指定距离将几何线段偏移至目标线段旁？

在使用PostGIS的ST_OffsetCurve进行线段偏移时，常遇到偏移方向与预期不符的问题。比如需要将短线段精准偏移到长线段旁的场景：

基础偏移示例代码 #

先通过基础代码生成正负方向的偏移结果，这里将坐标系转换为Web墨卡托（3857）以米为单位计算偏移，最后转回原始地理坐标系（4326）：

with p as (
    select
    ST_GeomFromText('POINT (-80.3553991 25.5572392)', 4326) as pts,
    ST_GeomFromText('LINESTRING (-80.35535931777203 25.55723937468992, 
    -80.35543880163212 25.557238244164587)', 4326)  line_to_move,
    ST_GeomFromText('LINESTRING (-80.3556436 25.5573315, 
    -80.3553202 25.5573361)', 4326) as line_target  
)
select 
    pts, 
    line_to_move, 
    line_target,
    ST_Transform(ST_OffsetCurve(ST_Transform(line_to_move,3857), 2), 4326) as plus_2m,
    ST_Transform(ST_OffsetCurve(ST_Transform(line_to_move,3857), -2), 4326) as less_2m
from p;

自动匹配目标方向的偏移方案 #

要确保偏移方向始终朝向目标线段，可通过计算线段相对位置自动判断偏移符号，无需手动试错：

with p as (
    select
    ST_GeomFromText('POINT (-80.3553991 25.5572392)', 4326) as pts,
    ST_GeomFromText('LINESTRING (-80.35535931777203 25.55723937468992, 
    -80.35543880163212 25.557238244164587)', 4326)  line_to_move,
    ST_GeomFromText('LINESTRING (-80.3556436 25.5573315, 
    -80.3553202 25.5573361)', 4326) as line_target,
    2 as offset_distance -- 指定偏移距离（单位：米）
),
transformed as (
    select
        line_to_move,
        line_target,
        ST_Transform(line_to_move, 3857) as line_to_move_3857,
        ST_Transform(line_target, 3857) as line_target_3857,
        offset_distance
    from p
),
offset_direction as (
    select
        line_to_move,
        line_target,
        line_to_move_3857,
        offset_distance,
        -- 生成线段起点处的左右侧法向量点
        ST_Translate(ST_StartPoint(line_to_move_3857), dy, -dx) as left_point,
        ST_Translate(ST_StartPoint(line_to_move_3857), -dy, dx) as right_point
    from (
        select
            *,
            -- 计算线段方向向量的分量
            ST_X(ST_EndPoint(line_to_move_3857)) - ST_X(ST_StartPoint(line_to_move_3857)) as dx,
            ST_Y(ST_EndPoint(line_to_move_3857)) - ST_Y(ST_StartPoint(line_to_move_3857)) as dy
        from transformed
    ) t
),
direction_check as (
    select
        line_to_move,
        line_target,
        offset_distance,
        -- 判断哪一侧离目标线段更近，确定正确偏移符号
        case
            when ST_Distance(left_point, line_target_3857) < ST_Distance(right_point, line_target_3857)
            then offset_distance
            else -offset_distance
        end as correct_offset
    from offset_direction
)
select
    pts,
    line_to_move,
    line_target,
    -- 执行偏移并转换回原始坐标系
    ST_Transform(ST_OffsetCurve(ST_Transform(line_to_move, 3857), correct_offset), 4326) as target_offset_line
from direction_check, p;

关键规则说明 #

• ST_OffsetCurve的偏移方向：正数为沿线段前进方向的左侧偏移，负数为右侧偏移，方向由线段顶点顺序决定
• 必须使用投影坐标系（如3857）计算偏移：地理坐标系（4326）单位为度，无法直接按米进行距离偏移

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