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