SQL Server Geography数据类型在线上最近的点 [英] SQL Server Geography datatype nearest point on line
问题描述
我正在尝试建立查询,但是遇到了一些困难.
I am trying to build a query, but I am having some difficulty.
我有一个SQL Server 2008数据库,该数据库的表除其他字段外还包含描述路段的地理字段. (此数据是从美国人口普查的TIGER/Line数据中导入的.)
I have a SQL Server 2008 database with a table that includes, among other fields, a geography field that describes road segments. (This data has been imported from TIGER/Line data from the U.S. Census.)
我还有一个描述用户位置的固定点.我想在数据库中找到最接近这一点的路段,但似乎无法弄清楚该如何完成.此外,我想找到该段上与用户位置点最接近的点.这就是我要选择的内容并返回我的查询.
I have another fixed point describing a user's location. I want to find the closest road segment in the database to that point, but I can't seem to figure out how to accomplish this. Furthermore, I want to find the closest point on that segment to the user location point. This is what I want to select and return back in my query.
有人对可以帮助我的地理/几何功能有任何经验吗?
Does anyone have any experience with the geography/geometry functionality that can help me?
谢谢!
推荐答案
您可以将对象存储在GEOGRAPHY
列中,并在此列上创建SPATIAL INDEX
.
You can store your objects in a GEOGRAPHY
column and create a SPATIAL INDEX
over this column.
不幸的是,SQL Server
通过平铺表面并将图块标识符存储在普通的B-Tree
索引中来实现空间索引,因此普通的ORDER BY STDistance
将不起作用(嗯,它可以工作,但不会使用索引).
Unfortunately, SQL Server
implements spatial indexes by tiling the surface and storing the tile identifiers in a plain B-Tree
index, so plain ORDER BY STDistance
won't work (well, it will work but won't use the index).
相反,您必须进行与此类似的查询:
Instead, you'll have to make a query similar to this:
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
公里以内等地方搜索道路.
This way, SQL Server
will first search roads within 1
kilometer from your point, then within 2
kilometers, etc., each time using the index.
更新:
如果表中有多个点,并希望找到每个点的最近点:
If you have multiple points in a table and want to find the closest point for each of them:
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
这篇关于SQL Server Geography数据类型在线上最近的点的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!