我正在尝试构建一个查询,但遇到了一些困难。
我有一个SQL Server 2008数据库,其中包括一个描述路段的地理字段,以及其他字段。(这些数据是从美国人口普查的TIGER/Line数据导入的)
我有另一个固定点,描述了用户的位置。我想找到数据库中距离该点最近的路段,但似乎无法实现。此外,我还想找到该路段上距离用户位置点最近的点。这就是我要在查询中选择和返回的内容。
有没有任何具有地理/几何功能经验的人能够帮助我?
谢谢!
我正在尝试构建一个查询,但遇到了一些困难。
我有一个SQL Server 2008数据库,其中包括一个描述路段的地理字段,以及其他字段。(这些数据是从美国人口普查的TIGER/Line数据导入的)
我有另一个固定点,描述了用户的位置。我想找到数据库中距离该点最近的路段,但似乎无法实现。此外,我还想找到该路段上距离用户位置点最近的点。这就是我要在查询中选择和返回的内容。
有没有任何具有地理/几何功能经验的人能够帮助我?
谢谢!
你可以将你的对象存储在一个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
ORDER BY
,这可能是我最终会实现的方法。 - David Pfeffer