Debian Codegraph @

mysql-test/suite/innodb_gis/r/create_spatial_index.result

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