在SQL Server 2008中沿路径移动点 [英] Moving a Point along a Path in SQL Server 2008
问题描述
我的数据库中存储了一个地理位置字段，其中包含一个线串路径.
I have a geography field stored in my database, holding a linestring path.
我想沿着该线串移动一个点n米，然后返回目的地.
I want to move a point n meters along this linestring, and return the destination.
例如，我希望终点从线头开始沿线串500米.
For example, I want the destination point 500 meters along the linestring starting from its beginning.
这是一个例子-YourFunctionHere是什么?或者，还有另一种方法吗?
Here's an example -- what is the YourFunctionHere? Or, is there another way?
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:
- 使用
STPointN()遍历线串的每个点，从x = 1到x =STNumPoints(). - 使用
STDistance()查找距离在迭代中直至下一个点:@linestring.STPointN(x).STDistance(@linestring.STPointN(x+1)) -
如果上述距离>您的输入距离'n':
- Iterate through each point of your linestring with
STPointN(), from x=1 to x=STNumPoints(). - Find the distance with
STDistance()between the current point in the iteration to the next point:@linestring.STPointN(x).STDistance(@linestring.STPointN(x+1)) If the above distance > your input distance 'n':
...然后目标点在此点和下一个点之间.只需将func_MoveTowardsPoint通过点x用作起点，将点x + 1用作终点，并应用距离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.
其他:
...目标点位于距离迭代中下一个点更远的路径中.从距离'n'中减去点x和点x + 1之间的距离.修改后的距离，继续进行迭代.
...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.
让我们这样做:
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处请求目标点:
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)
请注意，我们请求的最后距离(7000m)超出了线串的长度，因此我们返回了最后一点.在这种情况下，您可以根据需要轻松地修改该函数以返回NULL.
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.
这篇关于在SQL Server 2008中沿路径移动点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
