Moving a Point along a Path in SQL Server 2008

I have a geography field stored in my database, holding a linestring path.

I want to move a point n meters along this linestring, and return the destination.

For example, I want the destination point 500 meters along the linestring starting from its beginning.

Here's an example -- what is the YourFunctionHere? Or, is there another way?开发者_如何学C

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();

This is a little bit tricky, but it is certainly possible.

Let's start by calculating the bearing from one point to another. Given a starting point, a bearing, and a distance, the following function will return the destination point:

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

I understand that you require a function that takes a linestring as input, not just start and end points. The point has to move along a path of concatenated line segments, and must continue moving around the "corners" of the path. This might seem complicated at first, but I think it can be tackled as follows:

1. Iterate through each point of your linestring with STPointN(), from x=1 to x=STNumPoints().
2. Find the distance with STDistance() between the current point in the iteration to the next point: @linestring.STPointN(x).STDistance(@linestring.STPointN(x+1))

3. If the above distance > your input distance 'n':

   ...then the destination point is between this point and the next. Simply apply func_MoveTowardsPoint passing point x as start point, point x+1 as end point, and distance n. Return the result and break the iteration.

   Else:

   ...the destination point is further in the path from the next point in the iteration. Subtract the distance between point x and point x+1 from your distance 'n'. Continue through the iteration with the modified distance.

You may have noticed that we can easily implement the above recursively, instead of iteratively.

Let's do it:

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

With that in place, it's time to do some tests. Let's use the original linestring that was provided in the question, and we'll request the destination points at 350m, at 3500m and at 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();

Our test returns the following results:

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

Note that the last distance we requested (7000m) exceeded the length of the linestring, so we were returned the last point. In this case, you can easily modify the function to return NULL, if you prefer.

There is also the function LocateAlongGeog in the SQL Spatial Tools libarary on CodePlex http://sqlspatialtools.codeplex.com/wikipage?title=Current%20Contents&referringTitle=Home

I used Daniel's answer from above, but I had to fix the "func_MoveAlongPath" signature to

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

The int would returned wrong results, because it would round the values in the recursive calls. I then converted it into an iterative version, since the recursive one couldn't handle larger distances in the sample data I had:

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