如何在SQL Server中裁剪geometry线段至原线段范围内？

我不想把数据库中的geometry数据提取到C#变量处理后再写回，希望直接用SQL实现需求。

数据库表结构 #

CREATE TABLE dbo.MyTable (
   [Name] nvarchar(250),
   [From] numeric(21,6),
   [To] numeric(21,6),
   [Mapping] geometry,
   [Cost] float
);

现有处理逻辑 #

现有脚本会从该表读取数据，计算出新的From和To值（始终满足[From] < [To] AND @newFrom >= [From] AND @newFrom < @newTo AND @newTo <= [To]），然后写入同结构的新表（原表保持不变）。其中Cost值已通过公式((@newTo-@newFrom)/([To]-[From]))*[Cost]完成正确调整。

需要实现的Geometry裁剪需求 #

现在要通过SQL调整geometry字段：原geometry为连续线段（不一定是直线），需按比例裁剪掉原线段从[From]到@newFrom的起始部分，以及从@newTo到[To]的结束部分。对应的裁剪比例分别是(@newFrom-[From])/([To]-[From])和([To]-@newTo)/([To]-[From])，数据SRID为3857。

举个例子：10公里长的曲线道路，需获取2公里到8公里段的geometry数据。以下是数据前后示例（结果由现有C#代码生成）：

DECLARE @newFrom NUMERIC(21,6)
DECLARE @newTo NUMERIC(21,6)
SET @newFrom = 957
SET @newTo = 963

CREATE TABLE [MySourceTable] (
   [Name] nvarchar(250),
   [From] numeric(21,6),
   [To] numeric(21,6),
   [Mapping] geometry,
   [Cost] float
)

INSERT INTO [MySourceTable] VALUES ('MS Segment 1', 955, 965, 0x110F0000010403000000A4BB379356C260C18E0893A6DFC754413C4CBF5856C260C11E20FCC9DFC754413FD76FE054C260C177001A57E0C7544101000000010000000001000000FFFFFFFF0000000002, 990)

SELECT TOP(0) * INTO [MyTargetTable] FROM [MySourceTable]

INSERT INTO [MyTargetTable] SELECT [Name], @newFrom, @newTo, 0x110F00000114C347863D56C260C18C8531D4DFC75441AF40AE3755C260C10D9A6236E0C75441, ((@newTo-@newFrom)/([To]-[From]))*[Cost] FROM [MySourceTable]

注：@newFrom和@newTo并非硬编码，而是在复杂SQL脚本中动态计算得到。

SQL解决方案 #

可以利用SQL Server的STLineSubstring方法实现线段的比例截取，具体代码如下：

DECLARE @newFrom NUMERIC(21,6)
DECLARE @newTo NUMERIC(21,6)
SET @newFrom = 957
SET @newTo = 963

-- 若目标表不存在则创建
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'MyTargetTable')
SELECT TOP(0) * INTO [MyTargetTable] FROM [MySourceTable]

-- 插入裁剪后的数据
INSERT INTO [MyTargetTable]
SELECT 
    [Name],
    @newFrom,
    @newTo,
    -- 按比例截取子线段，确保几何有效性并设置SRID
    [Mapping].STLineSubstring(
        (@newFrom - [From])/([To] - [From]),
        (@newTo - [From])/([To] - [From])
    ).MakeValid().SetSRID(3857) AS [Mapping],
    ((@newTo-@newFrom)/([To]-[From]))*[Cost] AS [Cost]
FROM [MySourceTable]

关键说明 #

• STLineSubstring(startRatio, endRatio)：接受0-1之间的两个比例值，分别对应原线段起始到截取起点的比例、原线段起始到截取终点的比例，完全匹配需求中的裁剪逻辑。
• MakeValid()：避免截取操作产生无效几何对象，保证数据合法性。
• SetSRID(3857)：显式设置坐标系，确保输出的geometry数据SRID正确。

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