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