SQL Geography 快速最近邻（按距离排序）
我需要根据当前位置和一定半径范围内提取地址列表。我们的默认搜索允许 25 英里半径，如果使用其他选项，则会返回大约 5400 个结果。我们将地址存储为地理点，并创建了相当不错的搜索，但查询需要大约 3 秒的时间来运行。查看执行计划，我发现排序花费的时间最多，这是有道理的。我可以做什么来加快速度？我们不在乎它是否非常精确，只要足够接近并且估计就可以了。我们使用的查询减去了大部分其他过滤和分页逻辑。
declare @currentLatitude float, @CurrentLongitude float
declare @currentPosition geography
declare @radiusBuffer geography
Set @radiusBuffer = @currentPosition.BufferWithTolerance(10 * 1609.344,.9,1);
set @CurrentLatitude = 39.340579
set @CurrentLongitude = -86.697479
SET @currentPosition = geography::Point(@CurrentLatitude, @CurrentLongitude, 4326);
SELECT a.*,
ROW_NUMBER() OVER (ORDER BY rt.Position.STDistance(@currentPosition) ASC) AS RowNum
from [Address] a
where a.Position.Filter(@radiusBuffer) = 1
I need to pull a list of addresses based on a current location and within a certain radius. Our default search allows for a 25 mile radius, and with other options, returns around 5400 results. We have stored the addresses as geography points and have created a pretty decent search but the query takes around 3ish seconds to run. Looking over the execution plan I see that the sort takes the most amount of time, which makes sense. What can I do to speed this up? We don't care that it is super exact, so close enough and estimations are ok. The query we are using is below minus most of the other filtering and paging logic.
declare @currentLatitude float, @CurrentLongitude float
declare @currentPosition geography
declare @radiusBuffer geography
Set @radiusBuffer = @currentPosition.BufferWithTolerance(10 * 1609.344,.9,1);
set @CurrentLatitude = 39.340579
set @CurrentLongitude = -86.697479
SET @currentPosition = geography::Point(@CurrentLatitude, @CurrentLongitude, 4326);
SELECT a.*,
ROW_NUMBER() OVER (ORDER BY rt.Position.STDistance(@currentPosition) ASC) AS RowNum
from [Address] a
where a.Position.Filter(@radiusBuffer) = 1
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
尝试使用“WHERE a.Position.STDistance(@currentPosition) < 16093.44”，使用查询提示强制空间索引并使用“ORDER BY a.Position.STDistance(@currentPosition)”而不是 ROW_NUMBER()。
Try to use "WHERE a.Position.STDistance(@currentPosition) < 16093.44", force spatial index with query hint and use "ORDER BY a.Position.STDistance(@currentPosition)" instead of ROW_NUMBER().