在SQL Server 2008上优化7000万个极高密度空间点云的最近邻查询

我的 SQL Server 2008 R2 Express 数据库中有大约 7500 万条记录。每个都是对应于某个值的经纬度。该表有地理列。我正在尝试为给定的纬度经度（点）找到一个最近的邻居。我已经有一个带有空间索引的查询。但根据记录在数据库中的位置（例如第一季度或上季度），查询可能需要大约 3 到 30 秒才能找到最近的邻居。我觉得可以通过优化查询或空间索引来优化它以提供更快的结果。 现在应用了一些具有默认设置的空间索引。这是我的表和查询的样子。

CREATE TABLE lidar(
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [POINTID] [int] NOT NULL,
    [GRID_CODE] [numeric](17, 8) NULL,
    [geom] [geography] NULL,
 CONSTRAINT [PK_lidar_1] PRIMARY KEY CLUSTERED ([id] ASC)
 WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

我正在使用的空间索引：

CREATE SPATIAL INDEX [SPATIAL_lidar] ON [dbo].[lidar] ([geom]) USING  GEOGRAPHY_GRID 
WITH (
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), 
CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,  
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

这是我正在使用的查询：

declare @ms_at geography = 'POINT (-95.66 30.04)';
select TOP(1) nearPoints.geom.STAsText()as latlon 
from
(
select r.geom
from lidar r With(Index(SPATIAL_lidar))
where r.geom.STIntersects(@ms_at.STBuffer(1000)) = 1
) nearPoints

这是我的数据库中经纬度的示例。给出准确度和密度的概念。 7000万条记录全部针对一个城市（激光雷达数据）

POINT (-95.669434934023087 30.049513838913736)

现在这个查询给了我如上所述的结果，但我想尽可能地提高性能。我的猜测是，通过调整空间索引的默认值，我也许能够提高性能。这有什么线索吗？

我尝试将缓冲区从 10 更改为 1000，但结果几乎相同。

也欢迎任何其他提高性能的建议。

这是我现在正在使用的系统：

Windows 7 64bit Professional
Intel(R) Core(TM)2 Quad CPU    Q9650  @ 3.00GHz (4 CPUs), ~3.0GHz
Ram: 8 GB
NVIDIA GeForce 9500 GT

抱歉，这不是 SQL 答案，而是一种在数据受到某些约束的情况下获得可预测性能的方法。

数据多久更改一次？如果可能的话，您能否预先计算每个实体 5 个最近邻居的图表，并使用它来加快您的选择速度。

如果这些数据大部分是只读的，那么......

这些点分布的均匀程度如何？如果分布相当均匀且众所周知，那么您可以通过将每个坐标和索引存储在哈希表中来滚动自己的空间映射。

如果不需要数据库中的数据，请将其移出到内存映射文件以进行快速哈希查找。 （70m 记录应该可以轻松放入内存）。

我使用这种架构来生成亚毫秒级的查找，以显示广告和搜索引擎相关性。

==详细阐述==

您只需创建一个固定大小的正方形网格（如棋盘），然后将每个点映射到网格中，然后创建属于每个网格框的对象列表 - 如果您调整每个网格框的大小如果盒子正确的话，每个方格平均应该有 5-50 个点——原则上这是一个四叉树，但为了简单起见没有树。

对于将所有数据分散到存储桶中后为空的存储桶，您添加哪些最近的存储桶包含数据的信息。

现在，您可以从左到右对每个存储桶进行编号，以便每个存储桶都有一个可以根据坐标计算出的唯一编号 - 并且将每个存储桶插入到哈希表中，或者如果空间允许的话，就像直接查找表。

现在，当您进行查询时，您只需计算将映射到哪个存储桶，您将获得该存储桶中的对象列表，或者您将获得一个“空”存储桶，其中包含指向具有内容的最近存储桶的指针。

这将为您提供您正在寻找的第一个对象候选列表，现在您只需遍历并查看哪个是最接近的。

在 99% 的情况下，就是这样——但如果你担心 (a) 下一个桶中是否有一些实际上更接近的候选人，那么只需检查周围的 8 个桶，看看是否可以在那里找到更近的。

如果您现在还想获取所有最接近的对象的列表，那么还可以为每个对象计算 5 个最近邻居的简单网络，因此您最终将得到像 A->{B,C,D 这样的数据结构,E,F},B->{A,D,G,H,I},C->{A,J,K,G,M}....

这将形成一个简单的网络，您现在可以使用以下变量来遍历它Dijkstra在这里获取与您最近的点相邻的所有点。

构建数据结构需要时间，但一旦完成，完成正确的查找并返回数据集可以在亚毫秒内完成（不包括任何 http 或离线通信）

希望这可以帮助。