Question

我有一组线和多边形对象（SqlGeometry类型）和一个点对象（SqlGeometry类型）。我们如何找到每条线上距离给定点对象最近的点？是否有适用于执行此操作的API？

Answer 1

我不确定在 SQL Server 2008 中是否可以直接实现此功能：

将此翻译为中文：http://social.msdn.microsoft.com/Forums/en/sqlspatial/thread/cb094fb8-07ba-4219-8d3d-572874c271b5

那个帖子中建议的解决方法是：

declare @g geometry =  LINESTRING(0 0, 10 10)  
declare @h geometry =  POINT(0 10)  

select @h.STBuffer(@h.STDistance(@g)).STIntersection(@g).ToString()

否则你将不得不编写脚本从数据库中读取几何信息并使用单独的空间库。

Answer 2

这里提供一个样本，使用SqlGeometry和C#，无需SQL Server即可呈现可能的解决方案：

using System;
using Microsoft.SqlServer.Types;
namespace MySqlGeometryTest
{
    class ReportNearestPointTest
    {
        static void ReportNearestPoint(string wktPoint, string wktGeom)
        {
            SqlGeometry point = SqlGeometry.Parse(wktPoint);
            SqlGeometry geom = SqlGeometry.Parse(wktGeom);
            double distance = point.STDistance(geom).Value;
            SqlGeometry pointBuffer = point.STBuffer(distance);
            SqlGeometry pointResult = pointBuffer.STIntersection(geom);
            string wktResult = new string(pointResult.STAsText().Value);
            Console.WriteLine(wktResult);
        }

        static void Main(string[] args)
        {
            ReportNearestPoint("POINT(10 10)", "MULTIPOINT (80 70, 20 20, 200 170, 140 120)");
            ReportNearestPoint("POINT(110 200)", "LINESTRING (90 80, 160 150, 300 150, 340 150, 340 240)");
            ReportNearestPoint("POINT(0 0)", "POLYGON((10 20, 10 10, 20 10, 20 20, 10 20))");
            ReportNearestPoint("POINT(70 170)", "POLYGON ((110 230, 80 160, 20 160, 20 20, 200 20, 200 160, 140 160, 110 230))");
        }
    }
}

程序输出：

POINT (20 20)
POINT (160 150)
POINT (10 10)
POINT (70 160)

Answer 3

如果您有兴趣在实际中找到线上最近的点（也称为节点），您可以将每条线转换为具有相同线路ID的一组点。然后查询最接近的点并计算距离。

If instead you are trying to calc the distance from a point to the nearest line - stdistance http://msdn.microsoft.com/en-us/library/bb933808.aspx I guess the problem that the other answer addresses is what to put in your where clause though you could use stdistance to specify a distance above which you don t care such as

在 pointGeom.stdistance(lineGeom) < "您关心的距离" 时。

友情链接