Debian Codegraph @

mysql-test/suite/innodb_gis/t/create_spatial_index.test

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