在 SQL Server 2008 中沿路径移动点

Question

我的数据库中存储了一个地理字段，其中包含线串路径。

我想沿着该线串移动一个点 n 米，并返回目的地。

例如，我希望目标点从起点开始沿着线串 500 米。

这是一个示例 - 什么是 YourFunctionHere？或者，还有其他办法吗？

DECLARE @g geography;
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656, -122.310 47.690)', 4326);
SELECT @g.YourFunctionHere(100).ToString();

Answer 1

这有点棘手，但肯定是可能的。

让我们首先计算从一点到另一点的方位角。给定起点、方位和距离，以下函数将返回目标点：

CREATE FUNCTION [dbo].[func_MoveTowardsPoint](@start_point geography,
                                              @end_point   geography,  
                                              @distance    int)  /* Meters */   
RETURNS geography
AS
BEGIN
    DECLARE @ang_dist float = @distance / 6371000.0;  /* Earth's radius */
    DECLARE @bearing  decimal(18,15);
    DECLARE @lat_1    decimal(18,15) = Radians(@start_point.Lat);
    DECLARE @lon_1    decimal(18,15) = Radians(@start_point.Long);
    DECLARE @lat_2    decimal(18,15) = Radians(@end_point.Lat);
    DECLARE @lon_diff decimal(18,15) = Radians(@end_point.Long - @start_point.Long);
    DECLARE @new_lat  decimal(18,15);
    DECLARE @new_lon  decimal(18,15);
    DECLARE @result   geography;

    /* First calculate the bearing */

    SET @bearing = ATN2(sin(@lon_diff) * cos(@lat_2),
                        (cos(@lat_1) * sin(@lat_2)) - 
                        (sin(@lat_1) * cos(@lat_2) * 
                        cos(@lon_diff)));

    /* Then use the bearing and the start point to find the destination */

    SET @new_lat = asin(sin(@lat_1) * cos(@ang_dist) + 
                        cos(@lat_1) * sin(@ang_dist) * cos(@bearing));

    SET @new_lon = @lon_1 + atn2( sin(@bearing) * sin(@ang_dist) * cos(@lat_1), 
                                  cos(@ang_dist) - sin(@lat_1) * sin(@lat_2));

    /* Convert from Radians to Decimal */

    SET @new_lat = Degrees(@new_lat);
    SET @new_lon = Degrees(@new_lon);

    /* Return the geography result */

    SET @result = 
        geography::STPointFromText('POINT(' + CONVERT(varchar(64), @new_lon) + ' ' + 
                                              CONVERT(varchar(64), @new_lat) + ')', 
                                   4326);

    RETURN @result;
END

我知道您需要一个将线串作为输入的函数，而不仅仅是起点和终点。该点必须沿着连接线段的路径移动，并且必须继续围绕路径的“角”移动。一开始这可能看起来很复杂，但我认为可以按如下方式解决：

1. 使用 STPointN()，从 x=1 到 x=STNumPoints()。
2. 使用 STDistance() 查找距离迭代中当前点到下一个点之间： @linestring.STPointN(x).STDistance(@linestring.STPointN(x+1))

3. 如果上述距离 >您的输入距离“n”：

   ...那么目标点就在这个点和下一个点之间。只需应用func_MoveTowardsPoint，传递点x作为起点，点x+1作为终点，距离n。返回结果并中断迭代。

   其他：

   ...目标点距离迭代中的下一个点更远。从距离“n”中减去点 x 和点 x+1 之间的距离。继续使用修改后的距离进行迭代。

您可能已经注意到，我们可以轻松地递归实现上述内容，而不是迭代。

让我们开始吧：

CREATE FUNCTION [dbo].[func_MoveAlongPath](@path geography, 
                                           @distance int, 
                                           @index int = 1)   
RETURNS geography
AS
BEGIN
    DECLARE @result       geography = null;
    DECLARE @num_points   int = @path.STNumPoints();
    DECLARE @dist_to_next float;

    IF @index < @num_points
    BEGIN
        /* There is still at least one point further from the point @index
           in the linestring. Find the distance to the next point. */

        SET @dist_to_next = @path.STPointN(@index).STDistance(@path.STPointN(@index + 1));

        IF @distance <= @dist_to_next 
        BEGIN
            /* @dist_to_next is within this point and the next. Return
              the destination point with func_MoveTowardsPoint(). */

            SET @result = [dbo].[func_MoveTowardsPoint](@path.STPointN(@index),
                                                        @path.STPointN(@index + 1),
                                                        @distance);
        END
        ELSE
        BEGIN
            /* The destination is further from the next point. Subtract
               @dist_to_next from @distance and continue recursively. */

            SET @result = [dbo].[func_MoveAlongPath](@path, 
                                                     @distance - @dist_to_next,
                                                     @index + 1);
        END
    END
    ELSE
    BEGIN
        /* There is no further point. Our distance exceeds the length 
           of the linestring. Return the last point of the linestring.
           You may prefer to return NULL instead. */

        SET @result = @path.STPointN(@index);
    END

    RETURN @result;
END

准备好之后，就可以进行一些测试了。让我们使用问题中提供的原始线串，我们将请求 350m、3500m 和 7000m 处的目标点：

DECLARE @g geography;
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, 
                                               -122.343 47.656, 
                                               -122.310 47.690)', 4326);

SELECT [dbo].[func_MoveAlongPath](@g, 350, DEFAULT).ToString();
SELECT [dbo].[func_MoveAlongPath](@g, 3500, DEFAULT).ToString();
SELECT [dbo].[func_MoveAlongPath](@g, 7000, DEFAULT).ToString();

我们的测试返回以下结果：

POINT (-122.3553270591861 47.6560002502638)
POINT (-122.32676470116748 47.672728464582583)
POINT (-122.31 47.69)

请注意，我们请求的最后一个距离 (7000m) 超出了线串，所以我们返回了最后一个点。在这种情况下，如果您愿意，可以轻松修改该函数以返回 NULL。

Answer 2

CodePlex 上的 SQL Spatial Tools 库中还有 LocateAlongGeog 函数
http://sqlspatialtools.codeplex.com/wikipage?title=Current% 20Contents&referringTitle=首页

Answer 3

我使用了上面丹尼尔的答案，但我必须将“func_MoveAlongPath”签名修复为

CREATE FUNCTION [dbo].[func_MoveAlongPath](@path geography, 
                                       @distance **float**, 
                                       @index int = 1)

int 会返回错误的结果，因为它会舍入递归调用中的值。
然后我将其转换为迭代版本，因为递归版本无法处理我拥有的样本数据中的较大距离：

CREATE FUNCTION [dbo].[func_MoveAlongPathIter](@path geography, 
                                               @distance float)   
RETURNS geography
AS
BEGIN
    DECLARE @index          int = 1;
    DECLARE @result         geography = null;
    DECLARE @num_points     int = @path.STNumPoints();
    DECLARE @dist_to_next   float;
    DECLARE @comul_distance float = 0;

    WHILE (@index < @num_points - 1) AND (@comul_distance < @distance)
    BEGIN
        SET @dist_to_next = @path.STPointN(@index).STDistance(@path.STPointN(@index + 1));
        SET @comul_distance += @dist_to_next;
        SET @index += 1;
    END

    SET @result = [dbo].[func_MoveTowardsPoint](@path.STPointN(@index - 1),
                                                        @path.STPointN(@index),
                                                        @distance - (@comul_distance - @dist_to_next));
    RETURN @result;
END