1# ********************************************************* 2# Test Multiple Spatial Indexes on compression table 3# Test spatial index with table having primary key column 4# Test Spatial index with Create Index different clauses 5# Test Spatial index with spatial relationship functions 6# Test Spatial index with MBR spatial relationship functions 7# Test Spatial index columns with DML & SELECT queries 8# Test Spatial index with procedures 9# Test Delete & Update & check status of the table 10# Test spatial index with table have no primary key column 11# Test spatial index with table have no auto_increment 12# Test spatial index with check constraint 13# ********************************************************** 14--source include/have_innodb.inc 15--source include/have_innodb_16k.inc 16--source include/have_geometry.inc 17 18 19# Check spatial index functionality on compress table with Primary key 20CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL, 21c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL) 22ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; 23 24# Check spatial index functionality with Create Index clause options 25CREATE SPATIAL INDEX idx1 on tab(c2 ASC); 26CREATE SPATIAL INDEX idx2 on tab(c3 DESC) COMMENT 'wl6968'; 27CREATE SPATIAL INDEX idx3 on tab(c4 ASC) KEY_BLOCK_SIZE=8 ; 28CREATE SPATIAL INDEX idx4 on tab(c5 DESC) KEY_BLOCK_SIZE=4 29COMMENT 'Spatial index on Geometry type column'; 30 31# Check index type 32SHOW INDEXES FROM tab; 33 34# Populate some spatial data 35INSERT INTO tab(c1,c2,c3,c4,c5) 36VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'), 37ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'), 38ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); 39 40 41INSERT INTO tab(c1,c2,c3,c4,c5) 42VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'), 43ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'), 44ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))')); 45 46 47INSERT INTO tab(c1,c2,c3,c4,c5) 48VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'), 49ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'), 50ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))')); 51 52INSERT INTO tab(c1,c2,c3,c4,c5) 53VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'), 54ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'), 55ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))')); 56 57INSERT INTO tab(c1,c2,c3,c4,c5) 58VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'), 59ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'), 60ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))')); 61 62INSERT INTO tab(c1,c2,c3,c4,c5) 63VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), 64ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'), 65ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))')); 66 67INSERT INTO tab(c1,c2,c3,c4,c5) 68VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), 69ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), 70ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); 71 72INSERT INTO tab(c1,c2,c3,c4,c5) 73VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), 74ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), 75ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); 76 77 78INSERT INTO tab(c1,c2,c3,c4,c5) 79VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'), 80ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'), 81ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))')); 82 83 84INSERT INTO tab(c1,c2,c3,c4,c5) 85VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'), 86ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'), 87ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))')); 88 89ANALYZE TABLE tab; 90 91# Check the spatial relationship between 2 GIS shapes 92 93# Test the MBRContains 94SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); 95 96EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 97 98SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 99 100EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 101WHERE MBRContains(tab.c4, @g1); 102 103EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); 104 105SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)'); 106 107EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 108 109SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 110 111EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 112WHERE MBRContains(tab.c4, @g1); 113 114EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); 115 116# Test the MBRWithin 117SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); 118 119EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; 120 121SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; 122 123EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 124WHERE MBRWithin(tab.c4, @g1); 125 126EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); 127 128# Test the ST_Crosses 129SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))'); 130 131EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; 132 133SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; 134 135EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 136WHERE ST_Crosses(tab.c4, @g1); 137 138EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); 139 140SET @g1 = ST_GeomFromText('LINESTRING( 10 10,30 30,40 40)'); 141 142EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_CRosses(tab.c4, @g1) ORDER BY c1; 143 144SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; 145 146EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 147WHERE ST_Crosses(tab.c4, @g1); 148 149EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); 150 151# Test the MBRDisjoint 152SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); 153 154EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; 155 156SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; 157 158EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 159WHERE MBRDisjoint(tab.c4, @g1); 160 161EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); 162 163# Test the MBREquals 164SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); 165 166EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; 167 168SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; 169 170EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 171WHERE MBREquals(tab.c4, @g1); 172 173EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); 174 175# Test the MBRintersects 176SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); 177 178EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 179 180SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 181 182EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 183WHERE MBRintersects(tab.c4, @g1); 184 185EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); 186 187SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); 188 189EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 190 191SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 192 193EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 194WHERE MBRintersects(tab.c4, @g1); 195 196EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); 197 198# Test the Overelaps 199SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); 200 201EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 202 203SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 204 205EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 206WHERE MBROverlaps(tab.c4, @g1); 207 208EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); 209 210SET @g1 = ST_GeomFromText('LINESTRING(7 1,30 30,1010 3010,1010 2010,3010 3010,4010 4010,5010 5010 )'); 211 212EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 213 214SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 215 216EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 217WHERE MBROverlaps(tab.c4, @g1); 218 219EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); 220 221# Test the ST_Touches 222SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); 223 224EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; 225 226SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; 227 228EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 229WHERE ST_Touches(tab.c4, @g1); 230 231EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); 232 233SET @g1 = ST_GeomFromText('LINESTRING( 100 100,200 200,300 300)'); 234 235EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; 236 237SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; 238 239EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 240WHERE ST_Touches(tab.c4, @g1); 241 242EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); 243 244# Test the MBRContains 245SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); 246 247EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 248 249SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 250 251EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 252WHERE MBRContains(tab.c4, @g1); 253 254EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); 255 256SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); 257 258EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; 259 260SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; 261 262EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 263WHERE MBRWithin(tab.c4, @g1); 264 265EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); 266 267# Test the MBRDisjoint 268SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); 269 270EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; 271 272SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; 273 274EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 275WHERE MBRDisjoint(tab.c4, @g1); 276 277EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); 278 279# Test the MBREquals 280SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); 281 282EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; 283 284SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; 285 286EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 287WHERE MBREquals(tab.c4, @g1); 288 289EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); 290 291# Test the MBRintersects 292SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); 293 294EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 295 296SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 297 298EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 299WHERE MBRintersects(tab.c4, @g1); 300 301EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); 302 303SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); 304 305EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 306 307SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 308 309EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 310WHERE MBRintersects(tab.c4, @g1); 311 312EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); 313 314# Test the MBROverelaps 315SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); 316 317EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 318 319SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 320 321EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 322WHERE MBROverlaps(tab.c4, @g1); 323 324EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); 325 326# Test the MBRTouches 327SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); 328 329EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; 330 331SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; 332 333EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 334WHERE MBRTouches(tab.c4, @g1); 335 336EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1); 337 338# Test with Procedure 339delimiter |; 340 341CREATE PROCEDURE proc_wl6968() 342BEGIN 343 344SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); 345EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 346EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 347WHERE MBRContains(tab.c4, @g1); 348EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); 349 350END | 351 352delimiter ;| 353 354CALL proc_wl6968(); 355 356# Test the Delete & Update 357SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); 358 359SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 360 361DELETE FROM tab WHERE MBRContains(tab.c4, @g1); 362 363SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 364 365CHECK TABLE tab; 366 367SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)'); 368 369SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 370 371DELETE FROM tab WHERE MBRContains(tab.c4, @g1); 372 373SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 374 375CHECK TABLE tab; 376 377SET @g1 = ST_GeomFromText('POLYGON((100 200,1010 1010,1020 1020,500 300,300 200,100 300,100 200))'); 378 379SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; 380 381DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); 382 383SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; 384 385CHECK TABLE tab; 386 387SET @g1 = ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'); 388 389SET @g2 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); 390 391UPDATE tab SET C4 = @g2 WHERE ST_Crosses(tab.c4, @g1); 392 393SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g2) ORDER BY c1; 394 395CHECK TABLE tab; 396 397# Cleanup 398DROP TABLE tab; 399DROP PROCEDURE proc_wl6968; 400 401# End of Testcase compress table with Primary key 402 403# Check spatial index functionality on compress table No Primary key 404CREATE TABLE tab(c1 int ,c2 POINT NOT NULL, 405c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL) 406ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; 407 408# Check spatial index functionality with Create Index clause options 409CREATE SPATIAL INDEX idx1 on tab(c2 ASC); 410CREATE SPATIAL INDEX idx2 on tab(c3 DESC) COMMENT 'wl6968'; 411CREATE SPATIAL INDEX idx3 on tab(c4 ASC) KEY_BLOCK_SIZE=2 ; 412CREATE SPATIAL INDEX idx4 on tab(c5 DESC) KEY_BLOCK_SIZE=8 413COMMENT 'Spatial index on Geometry type column'; 414 415# Check index type 416SHOW INDEXES FROM tab; 417 418# Populate some spatial data 419INSERT INTO tab(c1,c2,c3,c4,c5) 420VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'), 421ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'), 422ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); 423 424 425INSERT INTO tab(c1,c2,c3,c4,c5) 426VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'), 427ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'), 428ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))')); 429 430 431INSERT INTO tab(c1,c2,c3,c4,c5) 432VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'), 433ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'), 434ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))')); 435 436INSERT INTO tab(c1,c2,c3,c4,c5) 437VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'), 438ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'), 439ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))')); 440 441INSERT INTO tab(c1,c2,c3,c4,c5) 442VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'), 443ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'), 444ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))')); 445 446INSERT INTO tab(c1,c2,c3,c4,c5) 447VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), 448ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'), 449ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))')); 450 451INSERT INTO tab(c1,c2,c3,c4,c5) 452VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), 453ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), 454ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); 455 456INSERT INTO tab(c1,c2,c3,c4,c5) 457VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), 458ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), 459ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); 460 461 462INSERT INTO tab(c1,c2,c3,c4,c5) 463VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'), 464ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'), 465ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))')); 466 467 468INSERT INTO tab(c1,c2,c3,c4,c5) 469VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'), 470ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'), 471ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))')); 472 473ANALYZE TABLE tab; 474 475# Check the spatial relationship between 2 GIS shapes 476 477# Test the MBRContains 478SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); 479 480EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 481 482SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 483 484EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 485WHERE MBRContains(tab.c4, @g1); 486 487EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); 488 489SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)'); 490 491EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 492 493SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 494 495EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 496WHERE MBRContains(tab.c4, @g1); 497 498EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); 499 500# Test the MBRWithin 501SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); 502 503EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; 504 505SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; 506 507EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 508WHERE MBRWithin(tab.c4, @g1); 509 510EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); 511 512# Test the ST_Crosses 513SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))'); 514 515EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; 516 517SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; 518 519EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 520WHERE ST_Crosses(tab.c4, @g1); 521 522EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); 523 524SET @g1 = ST_GeomFromText('LINESTRING( 10 10,30 30,40 40)'); 525 526EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_CRosses(tab.c4, @g1) ORDER BY c1; 527 528SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; 529 530EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 531WHERE ST_Crosses(tab.c4, @g1); 532 533EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); 534 535# Test the MBRDisjoint 536SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); 537 538EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; 539 540SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; 541 542EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 543WHERE MBRDisjoint(tab.c4, @g1); 544 545EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); 546 547# Test the MBREquals 548SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); 549 550EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; 551 552SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; 553 554EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 555WHERE MBREquals(tab.c4, @g1); 556 557EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); 558 559# Test the MBRintersects 560SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); 561 562EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 563 564SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 565 566EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 567WHERE MBRintersects(tab.c4, @g1); 568 569EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); 570 571SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); 572 573EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 574 575SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 576 577EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 578WHERE MBRintersects(tab.c4, @g1); 579 580EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); 581 582# Test the Overelaps 583SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); 584 585EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 586 587SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 588 589EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 590WHERE MBROverlaps(tab.c4, @g1); 591 592EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); 593 594SET @g1 = ST_GeomFromText('LINESTRING(7 1,30 30,1010 3010,1010 2010,3010 3010,4010 4010,5010 5010 )'); 595 596EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 597 598SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 599 600EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 601WHERE MBROverlaps(tab.c4, @g1); 602 603EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); 604 605# Test the ST_Touches 606SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); 607 608EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; 609 610SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; 611 612EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 613WHERE ST_Touches(tab.c4, @g1); 614 615EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); 616 617SET @g1 = ST_GeomFromText('LINESTRING( 100 100,200 200,300 300)'); 618 619EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; 620 621SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; 622 623EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 624WHERE ST_Touches(tab.c4, @g1); 625 626EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); 627 628# Test the MBRContains 629SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); 630 631EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 632 633SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 634 635EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 636WHERE MBRContains(tab.c4, @g1); 637 638EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); 639 640SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); 641 642EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; 643 644SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; 645 646EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 647WHERE MBRWithin(tab.c4, @g1); 648 649EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); 650 651# Test the MBRDisjoint 652SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); 653 654EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; 655 656SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; 657 658EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 659WHERE MBRDisjoint(tab.c4, @g1); 660 661EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); 662 663# Test the MBREquals 664SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); 665 666EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; 667 668SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; 669 670EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 671WHERE MBREquals(tab.c4, @g1); 672 673EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); 674 675# Test the MBRintersects 676SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); 677 678EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 679 680SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 681 682EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 683WHERE MBRintersects(tab.c4, @g1); 684 685EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); 686 687SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); 688 689EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 690 691SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 692 693EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 694WHERE MBRintersects(tab.c4, @g1); 695 696EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); 697 698# Test the MBROverelaps 699SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); 700 701EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 702 703SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 704 705EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 706WHERE MBROverlaps(tab.c4, @g1); 707 708EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); 709 710# Test the MBRTouches 711SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); 712 713EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; 714 715SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; 716 717EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 718WHERE MBRTouches(tab.c4, @g1); 719 720EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1); 721 722# Test with Procedure 723delimiter |; 724 725CREATE PROCEDURE proc_wl6968() 726BEGIN 727 728SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); 729EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; 730EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 731WHERE MBREquals(tab.c4, @g1); 732EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); 733 734END | 735 736delimiter ;| 737 738CALL proc_wl6968(); 739 740# Test the Delete & Update 741SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); 742 743SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; 744 745DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); 746 747SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; 748 749CHECK TABLE tab; 750 751SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))'); 752 753SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; 754 755DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); 756 757SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; 758 759SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); 760 761SET @g2 = ST_GeomFromText( 'POLYGON((1 1,2 2,3 3,10 3,5 1,1 1))'); 762 763UPDATE tab SET C4 = @g2 WHERE MBROverlaps(tab.c4, @g1); 764 765SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 766 767CHECK TABLE tab; 768 769# Cleanup 770DROP TABLE tab; 771DROP PROCEDURE proc_wl6968; 772 773# End of Testcase compress table No Primary key 774 775# Check spatial index functionality on compress table with auto_increment 776CREATE TABLE tab(c1 int AUTO_INCREMENT PRIMARY KEY,c2 POINT NOT NULL, 777c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL) 778ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; 779 780# Check spatial index functionality with Create Index clause options 781CREATE SPATIAL INDEX idx1 on tab(c2 ASC); 782CREATE SPATIAL INDEX idx2 on tab(c3 DESC) COMMENT 'wl6968'; 783CREATE SPATIAL INDEX idx3 on tab(c4 ASC) KEY_BLOCK_SIZE=16 ; 784CREATE SPATIAL INDEX idx4 on tab(c5 DESC) KEY_BLOCK_SIZE=16 785COMMENT 'Spatial index on Geometry type column'; 786 787# Check index type 788SHOW INDEXES FROM tab; 789 790# Populate some spatial data 791INSERT INTO tab(c2,c3,c4,c5) 792VALUES(ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'), 793ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'), 794ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); 795 796 797INSERT INTO tab(c2,c3,c4,c5) 798VALUES(ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'), 799ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'), 800ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))')); 801 802 803INSERT INTO tab(c2,c3,c4,c5) 804VALUES(ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'), 805ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'), 806ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))')); 807 808INSERT INTO tab(c2,c3,c4,c5) 809VALUES(ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'), 810ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'), 811ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))')); 812 813INSERT INTO tab(c2,c3,c4,c5) 814VALUES(ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'), 815ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'), 816ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))')); 817 818INSERT INTO tab(c2,c3,c4,c5) 819VALUES(ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), 820ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'), 821ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))')); 822 823INSERT INTO tab(c2,c3,c4,c5) 824VALUES(ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), 825ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), 826ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); 827 828INSERT INTO tab(c2,c3,c4,c5) 829VALUES(ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), 830ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), 831ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); 832 833 834INSERT INTO tab(c2,c3,c4,c5) 835VALUES(ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'), 836ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'), 837ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))')); 838 839 840INSERT INTO tab(c2,c3,c4,c5) 841VALUES(ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'), 842ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'), 843ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))')); 844 845ANALYZE TABLE tab; 846 847# Check the spatial relationship between 2 GIS shapes 848 849# Test the MBRContains 850SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); 851 852EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 853 854SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 855 856EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 857WHERE MBRContains(tab.c4, @g1); 858 859EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); 860 861SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)'); 862 863EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 864 865SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 866 867EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 868WHERE MBRContains(tab.c4, @g1); 869 870EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); 871 872# Test the MBRWithin 873SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); 874 875EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; 876 877SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; 878 879EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 880WHERE MBRWithin(tab.c4, @g1); 881 882EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); 883 884# Test the ST_Crosses 885SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))'); 886 887EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; 888 889SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; 890 891EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 892WHERE ST_Crosses(tab.c4, @g1); 893 894EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); 895 896SET @g1 = ST_GeomFromText('LINESTRING( 10 10,30 30,40 40)'); 897 898EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_CRosses(tab.c4, @g1) ORDER BY c1; 899 900SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; 901 902EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 903WHERE ST_Crosses(tab.c4, @g1); 904 905EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); 906 907# Test the MBRDisjoint 908SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); 909 910EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; 911 912SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; 913 914EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 915WHERE MBRDisjoint(tab.c4, @g1); 916 917EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); 918 919# Test the MBREquals 920SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); 921 922EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; 923 924SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; 925 926EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 927WHERE MBREquals(tab.c4, @g1); 928 929EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); 930 931# Test the MBRintersects 932SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); 933 934EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 935 936SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 937 938EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 939WHERE MBRintersects(tab.c4, @g1); 940 941EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); 942 943SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); 944 945EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 946 947SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 948 949EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 950WHERE MBRintersects(tab.c4, @g1); 951 952EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); 953 954# Test the Overelaps 955SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); 956 957EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 958 959SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 960 961EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 962WHERE MBROverlaps(tab.c4, @g1); 963 964EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); 965 966SET @g1 = ST_GeomFromText('LINESTRING(7 1,30 30,1010 3010,1010 2010,3010 3010,4010 4010,5010 5010 )'); 967 968EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 969 970SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 971 972EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 973WHERE MBROverlaps(tab.c4, @g1); 974 975EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); 976 977# Test the ST_Touches 978SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); 979 980EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; 981 982SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; 983 984EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 985WHERE ST_Touches(tab.c4, @g1); 986 987EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); 988 989SET @g1 = ST_GeomFromText('LINESTRING( 100 100,200 200,300 300)'); 990 991EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; 992 993SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; 994 995EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 996WHERE ST_Touches(tab.c4, @g1); 997 998EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); 999 1000# Test the MBRContains 1001SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); 1002 1003EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 1004 1005SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 1006 1007EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 1008WHERE MBRContains(tab.c4, @g1); 1009 1010EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); 1011 1012SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); 1013 1014EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; 1015 1016SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; 1017 1018EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 1019WHERE MBRWithin(tab.c4, @g1); 1020 1021EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); 1022 1023# Test the MBRDisjoint 1024SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); 1025 1026EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; 1027 1028SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; 1029 1030EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 1031WHERE MBRDisjoint(tab.c4, @g1); 1032 1033EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); 1034 1035# Test the MBREquals 1036SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); 1037 1038EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; 1039 1040SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; 1041 1042EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 1043WHERE MBREquals(tab.c4, @g1); 1044 1045EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); 1046 1047# Test the MBRintersects 1048SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); 1049 1050EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 1051 1052SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 1053 1054EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 1055WHERE MBRintersects(tab.c4, @g1); 1056 1057EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); 1058 1059SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); 1060 1061EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 1062 1063SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 1064 1065EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 1066WHERE MBRintersects(tab.c4, @g1); 1067 1068EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); 1069 1070# Test the MBROverelaps 1071SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); 1072 1073EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 1074 1075SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 1076 1077EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 1078WHERE MBROverlaps(tab.c4, @g1); 1079 1080EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); 1081 1082# Test the MBRTouches 1083SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); 1084 1085EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; 1086 1087SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; 1088 1089EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 1090WHERE MBRTouches(tab.c4, @g1); 1091 1092EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1); 1093 1094# Test the Delete & Update 1095SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); 1096 1097SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; 1098 1099DELETE FROM tab WHERE MBREquals(tab.c4, @g1); 1100 1101SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; 1102 1103CHECK TABLE tab; 1104 1105SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); 1106 1107SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 1108 1109DELETE FROM tab WHERE MBRIntersects(tab.c4, @g1); 1110 1111SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 1112 1113CHECK TABLE tab; 1114 1115# Cleanup 1116DROP TABLE tab; 1117 1118# End of Testcase compress table with Auto_increment 1119 1120# Test check constraint on spatial column 1121--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION 1122CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(c1 > 0) ) ENGINE=InnoDB; 1123CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(CAST(c1 AS BINARY) > 0) ) ENGINE=InnoDB; 1124 1125CREATE SPATIAL INDEX idx1 ON tab(c1) ; 1126 1127SHOW CREATE TABLE tab; 1128 1129SHOW INDEX FROM tab; 1130 1131set @g1 = ST_GeomFromText('POINT(-1 -2)'); 1132 1133SELECT ST_AsText(c1) FROM tab; 1134 1135DROP table tab; 1136 1137# repro case for bug#20451454 - FAILING ASSERTION: LOW_MATCH 1138# < DTUPLE_GET_N_FIELDS_CMP(TUPLE) 1139create table `t1`(`a` geometry not null,`b` linestring not null, 1140primary key (`b`(192),`a`(141)),spatial key (`b`)) engine=innodb; 1141insert into `t1` values( 1142 point(1,1), 1143 linestring(point(1,1),point(1,1)) 1144); 1145 1146--error ER_BAD_NULL_ERROR 1147insert into `t1` values 1148( 1149 polygon( 1150 linestring(point(1,1),point(1,1)), 1151 linestring(point(1,1),point(11,1)) 1152 ), 1153 linestring(point(1,1),point(1,1)) 1154); 1155select 1 from t1 where st_intersects( 1156 geometrycollection(point(1,-1)),b 1157); 1158drop table t1; 1159 1160# Reproduce case for updating statistic after droping stats info table. 1161CREATE TABLE t1(c1 POINT NOT NULL); 1162DROP TABLE mysql.innodb_table_stats; 1163CALL mtr.add_suppression("InnoDB: Table `mysql`.`innodb_table_stats` not found."); 1164CALL mtr.add_suppression("InnoDB: Fetch of persistent statistics requested for table `test`.`t1` but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead."); 1165CREATE SPATIAL INDEX idx2 ON t1(c1); 1166 1167DROP TABLE t1; 1168CREATE TABLE mysql.innodb_table_stats ( 1169 database_name varchar(64) COLLATE utf8_bin NOT NULL, 1170 table_name varchar(199) COLLATE utf8_bin NOT NULL, 1171 last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 1172 n_rows bigint(20) unsigned NOT NULL, 1173 clustered_index_size bigint(20) unsigned NOT NULL, 1174 sum_of_other_index_sizes bigint(20) unsigned NOT NULL, 1175 PRIMARY KEY (`database_name`,`table_name`) 1176) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; 1177