Para finalizar la serie de posts relativos a los tipos de datos espaciales , nos queda la forma de definir los índices sobre columnas geometry y geography, una vez que ya hemos visto la teoría que existe por detras.
Teniendo en cuenta las siguientes figuras en un espacio geométrico
Las vamos a almacenar en una tabla que contendrá una columna geometry
|
IF OBJECT_ID ( ‘dbo.SpatialTable’, ‘U’ ) IS NOT NULL DROP TABLE dbo.SpatialTable; GO CREATE TABLE SpatialTable ( id int IDENTITY (1,1), GeomCol1 geometry, GeomCol2 AS GeomCol1.STAsText(), GeogCol1 geography, GeogCol2 as GeogCol1.STAsText() ); GO alter table SpatialTable add constraint pk_spatialTable primary key (id) go |
Una vez definida la tabla, vamos a insertar en dicha tabla, las figuras representadas en la imagen anterior
|
— F1 INSERT INTO SpatialTable (GeomCol1,GeogCol1) VALUES (geometry::Parse(‘LINESTRING (71 361, 72 359, 75 354, 77 351, 81 345, 85 343, 88 343, 98 343, 109 345, 118 351, 121 353, 127 358, 131 364, 131 367, 131 371, 131 375, 131 381, 130 385, 128 387, 125 390, 122 392, 116 392, 112 392, 104 392, 99 392, 94 391, 86 388, 80 384, 77 383, 75 380, 72 377, 69 374, 69 373, 69 369, 70 364, 71 363, 71 362, 71 361, 70 360)’) ,geography::Parse(‘LINESTRING(47.656 -122.360, 47.656 -122.343)’));
— F2 INSERT INTO SpatialTable (GeomCol1,GeogCol1) VALUES (geometry::Parse(‘LINESTRING (380 358, 378 356, 370 351, 363 343, 358 334, 345 322, 339 316, 332 308, 320 297, 309 290, 299 282, 291 274, 280 266, 274 263, 271 260, 264 253, 261 250, 257 247, 253 243, 249 237, 244 233, 236 221, 229 208, 226 198, 222 188, 219 176, 218 164, 217 151, 218 130, 223 115, 237 96, 249 82, 262 70, 270 64, 285 57, 293 54, 294 54, 295 54)’) ,geography::Parse(‘POLYGON((47.653 -122.358, 47.649 -122.348, 47.658 -122.348, 47.658 -122.358, 47.653 -122.358))’));
— F3 INSERT INTO SpatialTable (GeomCol1) VALUES (geometry::Parse(‘POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))’));
— F4 INSERT INTO SpatialTable (GeomCol1) VALUES (geometry::Parse(‘POLYGON ((222 85, 221 85, 215 85, 202 86, 189 86, 172 91, 155 100, 142 108, 129 121, 113 134, 91 153, 85 162, 79 174, 76 183, 70 214, 67 234, 67 247, 73 256, 77 270, 86 283, 95 289, 108 296, 122 298, 132 300, 143 300, 149 301, 150 301, 156 301, 179 297, 189 291, 197 283, 200 280, 206 273, 211 267, 217 260, 225 250, 229 239, 233 230, 237 222, 240 215, 244 207, 248 200, 257 191, 261 183, 267 159, 268 141, 268 131, 263 119, 250 106, 239 98, 234 92, 229 89, 228 89, 222 85))’));
— F5 INSERT INTO SpatialTable (GeomCol1) VALUES (geometry::Parse(‘POLYGON ((157 300, 157 297, 157 295, 161 287, 163 282, 169 272, 174 262, 176 259, 180 253, 187 244, 196 232, 206 222, 219 209, 232 197, 243 188, 245 186, 248 184, 255 184, 260 185, 263 186, 282 196, 295 202, 315 211, 322 214, 332 225, 337 236, 344 247, 346 254, 350 264, 350 274, 341 285, 337 289, 321 297, 312 302, 310 303, 308 304, 305 305, 296 308, 295 308, 157 300))’));
— F6 INSERT INTO SpatialTable (GeomCol1) VALUES (geometry::Parse(‘POLYGON ((243 228, 243 230, 246 240, 248 246, 250 257, 253 265, 255 264, 261 259, 266 256, 266 255, 266 251, 263 235, 261 227, 259 224, 257 224, 257 223, 255 223, 253 222, 249 221, 248 221, 247 222, 245 223, 243 228))’));
GO
— Ahora insertamos 1000 polígonos para llenar un poco mas la tabla y que el planificador , pueda optar por un clustered index seek en vez de un index scan por tener pocos datos — declare @contador as integer set @contador= 0 while (@contador <= 1000 ) begin insert into SpatialTable(GeomCol1) values (geometry::Parse(‘POLYGON ((994 824, 986 828, 976 830, 914 814, 866 762, 862 722, 888 674, 922 672, 960 680, 972 698, 974 702, 994 824))’)) set @contador = @contador + 1 end |
Ahora vamos a crear un índice espacial que cubrirá a todos los datos que tenemos en la imagen, dando además, el mayor grado de precisión en todos los niveles.
|
CREATE SPATIAL INDEX SI_idx_prueba1 on SpatialTable(GeomCol1) WITH ( BOUNDING_BOX = (0, 0, 500, 500 ), GRIDS = (LEVEL_1 = HIGH, LEVEL_2 = HIGH, LEVEL_3 = HIGH, LEVEL_4 = HIGH) ); |
Los índices cubren una serie de operaciones siempre y cuando se cumplan algunas restricciones.
Algunas de las restricciones son las siguientes:
-
Las instancias han de utilizar el mismo SRID
-
Los métodos han de utilizarse en el WHERE de la siguiente forma
-
geometry1.STContains(geometry2) = 1
-
geometry1.STDistance(geometry2) < number
-
geometry1.STDistance(geometry2) <= number
-
geometry1.STEquals(geometry2) = 1
-
geometry1.STIntersects(geometry2) = 1
-
geometry1. STOverlaps (geometry2) = 1
-
geometry1.STTouches(geometry2) = 1
-
geometry1.STWithin(geometry2) = 1
-
Podemos pues, probar la utilización del índice de la siguiente forma:
|
DECLARE @geom1 geometry; SELECT @geom2 = GeomCol1 FROM SpatialTable WHERE id = 2; — Ahora buscamos los polígonos que se cruzan con la figura F2 select * from SpatialTable where GeomCol1.STIntersects(@geom2) = 1; |
Si vemos el plan de ejecución creado, vemos que el planificador de consultas ha optado por un clustered index seek, de forma que la intersección se ha realizado desde la información proporcionada por el propio índice:
| Clustered Index Seek(OBJECT:([SQM_Spatial].[dbo].[SpatialTable].[pk_spatialTable]), SEEK:([SQM_Spatial].[dbo].[SpatialTable].[id]=[SQM_Spatial].[sys].[extended_index_245575913_64000].[pk0]) ORDERED FORWARD) |
Esto es debido a que tiene información suficiente para realizar la búsqueda indexada por un lado , y hemos cumplido los requisitos.
Si no existiera el índice , al repetir la operación podríamos ver como se opta por un Clustered index scan. Podemos eliminar el índice para repetir la consulta con la siguiente sentencia
|
drop index si_idx_prueba1 on SpatialTable |
Por supuesto, podemos trabajar mucho mas con los datos, de forma que podemos resolver preguntas de origen espacial como las siguientes:
¿Con qué objetos se cruza la figura F4?
|
— Intersección entre ambas estructuras DECLARE @geom4 geometry; SELECT @geom4 = GeomCol1 FROM SpatialTable WHERE id = 4; select * from SpatialTable where GeomCol1.STIntersects(@geom4) = 1; |
¿La figura F1 es cerrada?
|
select GeomCol1.STIsClosed() from SpatialTable where id=1 |
¿La figura F4 forma un anillo?
|
select GeomCol1.STIsRing() from SpatialTable where id=4 |
NOTA: Solo aplicable a LineStrings, evidentemente un polígono es cerrado
¿Qué área existe del solapamiento entre las figuras 4 y 5?
|
— Intersección entre ambas estructuras DECLARE @geom1 geometry; DECLARE @geom2 geometry; DECLARE @result geometry; SELECT @geom1 = GeomCol1 FROM SpatialTable WHERE id = 3; SELECT @geom2 = GeomCol1 FROM SpatialTable WHERE id = 4; SELECT @result = @geom1.STIntersection(@geom2); SELECT @result.STAsText(); — polígono de intersección SELECT @geom1.STIntersection(@geom2).STArea() as Area; |
