Pregunta:
Tengo alrededor de 75 millones de registros en una base de datos SQL Server 2008 R2 Express. Cada uno tiene una latitud correspondiente a algún valor. La tabla tiene columna de geografía. Estoy tratando de encontrar un vecino más cercano para una determinada latitud y longitud (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 tardar entre 3 y 30 segundos en encontrar el vecino más cercano. Siento que esto se puede optimizar para dar un resultado mucho más rápido optimizando la consulta o el índice espacial. En este momento se aplicó algo del índice espacial con la configuración predeterminada. Así es como se ve mi tabla y 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 long en mi base de datos. para dar una idea de precisión y densidad. Todos los 70 millones de registros son para una ciudad (datos Lidar).
POINT (-95.669434934023087 30.049513838913736)
Ahora, esta consulta me da los resultados que describí anteriormente, pero quiero mejorar el rendimiento tanto como sea posible. Supongo que modificando los valores predeterminados del índice espacial que puedo estar por encima 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 se agradece cualquier otra sugerencia para mejorar el rendimiento.
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
Respuesta:
Intente ejecutar el procedimiento almacenado sp_help_spatial_geography_index para obtener detalles sobre cómo se utiliza su índice espacial. Debería 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 se destaca. El significado de cada uno de los elementos se puede encontrar aquí .
Si se proyectaron sus coordenadas, también podría hacer una consulta no espacial simple en los campos X, Y calculados y verificar X <MinX y X> MaxX, etc.
Proyectar sus coordenadas (en un campo de tipo GEOMETRÍA) también le permite limitar su índice espacial a la extensión de los datos, lo que puede acelerar considerablemente el rendimiento. Reemplace las extensiones mundiales con las extensiones 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]