Optimizar la consulta del vecino más cercano en una nube de 70 millones de puntos en SQL Server 2008

16

Tengo alrededor de 75 millones de registros en una base de datos de SQL Server 2008 R2 Express. Cada uno es un tiempo de latencia correspondiente a algún valor. La tabla tiene columna de geografía. Estoy tratando de encontrar un vecino más cercano para una longitud de latitud dada (punto). Ya tengo una consulta con índice espacial en su lugar. Pero dependiendo de dónde se encuentre el registro en la base de datos, digamos el primer trimestre o el último trimestre, la consulta puede demorar entre 3 y 30 segundos para encontrar al vecino más cercano. Siento que esto se puede optimizar para dar un resultado mucho más rápido al optimizar la consulta o el índice espacial. En este momento aplicó algo del índice espacial con ajustes por defecto. Aquí es cómo se ve mi tabla y mi consulta.

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]

El índice espacial que estoy usando:

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]

Aquí está la consulta que estoy usando:

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

Aquí hay una muestra de lat longs en mi base de datos. Para dar una idea de exactitud y densidad. Todos los 70 millones de registros corresponden a una ciudad (datos Lidar).

POINT (-95.669434934023087 30.049513838913736)

Ahora, esta consulta me da resultados como lo describí anteriormente, pero quiero mejorar el rendimiento tanto como sea posible. Mi conjetura es al ajustar los valores predeterminados del índice espacial que pueden estar arriba para optimizar mejor el rendimiento. ¿Alguna pista sobre esto?

Intenté variar el búfer de 10 a 1000 pero con casi los mismos resultados.

También cualquier otra sugerencia para mejorar el rendimiento es bienvenida.

Aquí está el sistema que estoy usando en este momento:

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
    
pregunta Shaunak 11.07.2011 - 20:43

3 respuestas

9

Intente ejecutar el sp_help_spatial_geography_index para obtener detalles sobre cómo se está utilizando su índice espacial . Deberías poder usar algo como:

declare @ms_at geography = 'POINT (-95.66 30.04)'
set @ms_at = @ms_at.STBuffer(1000).STAsText()
exec sp_help_spatial_geography_index 'lidar', 'SPATIAL_lidar', 0, @ms_at;

Publique los resultados en su pregunta para ver si algo sobresale. El significado para cada uno de los elementos puede ser que se encuentra aquí .

Si sus coordenadas fueron proyectadas, también podría hacer una consulta simple no espacial en los campos X, Y calculados y verificando X < MinX y X > MaxX etc.

La proyección de sus coordenadas (en un campo de tipo GEOMETRÍA) también le permite limitar su índice espacial al alcance de los datos, lo que puede acelerar considerablemente el rendimiento. Reemplace las extensiones mundiales con la extensión de sus datos:

CREATE SPATIAL INDEX [SPATIAL_lidar] ON [dbo].[lidar] ([geom]) USING  GEOMETRY_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,
BOUNDING_BOX =(-90, -180, 90, 180),) ON [PRIMARY]
    
respondido por el geographika 12.07.2011 - 00:21
1

Considere la posibilidad de simplificar el búfer con BufferwithTolerance . Si los puntos están apretados, el sistema debe identificar si un punto está a un lado del límite. Cuanto más simple es esa línea, menos trabajo tiene que hacer la máquina.

    
respondido por el Matthew Snape 12.07.2011 - 23:30
1

Echa un vistazo a este recurso de Isaac Kunen sobre el uso de una tabla de números para optimizar al vecino más cercano utilizando un índice espacial

enlace

    
respondido por el ellemayo 20.03.2012 - 16:46

Lea otras preguntas en las etiquetas