SQL Server Geography数据类型线上最近的点

Question

我正在尝试建立一个查询，但我遇到了一些困难。

我有一个SQL Server 2008数据库，其中有一个表，其中包括一个描述路段的地理字段。(此数据来自美国人口普查的TIGER/Line数据。)

我有另一个固定点来描述用户的位置。我想在数据库中找到离这一点最近的路段，但我似乎想不出如何实现这一点。此外，我希望在该线段上找到与用户位置点最近的点。这就是我希望在查询中选择并返回的内容。

有没有人有地理/几何功能方面的经验，可以帮助我？

谢谢!

Answer 1

您可以将对象存储在GEOGRAPHY列中，并在此列上创建SPATIAL INDEX。

不幸的是，SQL Server通过平铺表面并将平铺标识符存储在普通的B-Tree索引中来实现空间索引，因此普通的ORDER BY STDistance将无法工作(当然，它可以工作，但不会使用索引)。

取而代之的是，您必须进行类似如下的查询：

DECLARE @mypoint GEOGRAPHY
SET @mypoint = geography::STGeomFromText('POINT(@mylat, @mylon)', 4326);

WITH    num (distance) AS
        (
        SELECT  1000
        UNION ALL
        SELECT  distance + 1000
        FROM    num
        WHERE   distance <= 50000
        )
SELECT  TOP 1 m.*
FROM    num
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    mytable
        WHERE   myroad.STDistance(@mypoint) <= distance
        ORDER BY
                STDistance(@mypoint)
        ) m

这样，SQL Server将首先搜索离你的点在1公里内的道路，然后搜索2公里内的道路，等等，每次都使用索引。

更新：

如果您在一个表中有多个点，并且想要为每个点找到最近的点：

WITH    num (distance) AS
        (
        SELECT  1000
        UNION ALL
        SELECT  distance + 1000
        FROM    num
        WHERE   distance <= 50000
        )
SELECT  mp.mypoint, m.*
FROM    @mypoints mp
CROSS APPLY
        (
        SELECT  TOP 1 m.*
        FROM    num
        CROSS APPLY
                (
                SELECT  TOP 1 *
                FROM    mytable
                WHERE   myroad.STDistance(@mypoint) <= distance
                ORDER BY
                        STDistance(@mypoint)
                ) m
        ) m

Answer 2

您的路段是如何存储的？lat和long？如果是这样，您可以将它们转换为弧度，并进行数学计算：

• http://www.zipcodeworld.com/samples/distance.mssql.html
• http://www.petefreitag.com/item/622.cfm
• http://johndyer.name/post/2005/07/11/LatitudeLongitude-Distance-Calculation-in-SQL-Server.aspx