Debian Codegraph @

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

raw
1 #********************************************************
2 # wl6942: Store certain GIS data type in the row rather
3 # than as BLOB.
4 #
5 #********************************************************
6
7
8
9 --echo #
10 --echo # Test for basic POINT operation
11 --echo #
12 CREATE TABLE gis_point(p1 POINT, p2 POINT) ENGINE=InnoDB;
13 INSERT INTO gis_point VALUES
14 (ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(0 0)')),
15 (ST_PointFromText('POINT(10 -20)'), ST_PointFromText('POINT(10 -20)')),
16 (ST_PointFromText('POINT(3.1415926 3.535897)'), ST_PointFromText('POINT(-3.932626 -3.488272)')),
17 (ST_PointFromText('POINT(-111.9876 234.1357)'), ST_PointFromText('POINT(-957.1914 958.1919)'));
18 SELECT ST_X(p1), ST_Y(p2) FROM gis_point;
19 DROP TABLE gis_point;
20
21
22 --echo #
23 --echo # Test when POINT is not on any indexes
24 --echo #
25 CREATE TABLE gis_point(p1 POINT, p2 POINT) ENGINE=InnoDB;
26 INSERT INTO gis_point VALUES
27 (ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(100.32374832 101.23741821)')),
28 (ST_PointFromText('POINT(105.34523342 103.18492302)'), ST_PointFromText('POINT(100.32374832 101.23741821)')),
29 (ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(200.32247328 101.86728201)')),
30 (ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(100.32374832 101.98527111)'));
31
32 ANALYZE TABLE gis_point;
33
34 --replace_column 10 #
35 EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
36 --sorted_result
37 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
38
39 EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2 ORDER by p1, p2;
40 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1,p2 ORDER BY p1,p2;
41
42 EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
43 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
44
45 --sorted_result
46 SELECT ST_AsText(p1) FROM gis_point WHERE p1 = p2;
47 --sorted_result
48 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE p1 = ST_PointFromText('POINT(100.32374832 101.23741821)');
49 --sorted_result
50 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE p2 = ST_PointFromText('POINT(200.32247328 101.86728201)');
51
52 DROP TABLE gis_point;
53
54
55 --echo #
56 --echo # Test some ALTER TABLE operations on POINT tables
57 --echo #
58
59 CREATE TABLE t1 (
60 p POINT NOT NULL SRID 0,
61 g GEOMETRY NOT NULL
62 ) ENGINE=InnoDB;
63
64 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 11)'));
65 INSERT INTO t1 VALUES(ST_PointFromText('POINT(1 1)'), ST_PointFromText('POINT(10 12)'));
66 INSERT INTO t1 VALUES(ST_PointFromText('POINT(1 0)'), ST_PointFromText('POINT(10 13)'));
67 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(10 14)'));
68
69 SELECT ST_AsText(p), ST_AsText(g) FROM t1;
70
71 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'p' OR name = 'g';
72
73 ALTER TABLE t1 ADD COLUMN p1 POINT SRID 0, ADD COLUMN p2 POINT, ADD KEY(p);
74 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1' OR name = 'p2';
75
76 --echo # NOT NULL POINT will use ''
77 SELECT count(*) AS `Expect 4` FROM t1 WHERE p1 = '';
78 SELECT count(*) AS `Expect 4` FROM t1 WHERE p2 = '';
79
80 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p1;
81 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p2;
82
83 ALTER TABLE t1 DROP COLUMN p2;
84
85 --echo # NULLABLE POINT will use NULL
86 ALTER TABLE t1 ADD COLUMN p2 POINT;
87 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1' OR name = 'p2';
88
89 SELECT count(*) AS `Expect 4` FROM t1 WHERE p2 IS NULL;
90 UPDATE t1 SET p2 = ST_PointFromText('POINT(10 20)');
91 UPDATE t1 SET p1 = ST_PointFromText('POINT(10 20)');
92
93 ALTER TABLE t1 DROP COLUMN p2;
94 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1';
95
96 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p1;
97
98 INSERT INTO t1 VALUES (ST_PointFromText('POINT(0.5 0.5)'), ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 19)'));
99 SELECT ST_AsText(p1) FROM t1;
100 DELETE FROM t1 WHERE p1 = ST_PointFromText('POINT(10 19)');
101 SELECT ST_AsText(p1) FROM t1;
102
103 --echo # Add spatial keys on the table
104 --error ER_SPATIAL_CANT_HAVE_NULL
105 ALTER TABLE t1 ADD SPATIAL(p), ADD SPATIAL(p1);
106 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1';
107
108 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
109 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
110
111 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))'));
112 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))'));
113
114 --echo # Drop spatial keys on the table
115 --error ER_CANT_DROP_FIELD_OR_KEY
116 ALTER TABLE t1 DROP KEY p, DROP KEY p1;
117 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1';
118
119 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
120 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
121
122 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))'));
123 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))'));
124
125 TRUNCATE t1;
126
127 ALTER TABLE t1 DROP COLUMN p, DROP COLUMN p1;
128 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1';
129
130 ALTER TABLE t1 ADD COLUMN p POINT, ADD COLUMN p1 POINT;
131 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1';
132
133 SHOW CREATE TABLE t1;
134
135 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.5 0.5)'), ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 19)'));
136 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.6 0.6)'), ST_PointFromText('POINT(1 1)'), ST_PointFromText('POINT(10 20)'));
137 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.7 0.7)'), ST_PointFromText('POINT(1 0)'), ST_PointFromText('POINT(10 21)'));
138 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.8 0.8)'), ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(10 22)'));
139
140 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
141 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
142
143 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))'));
144 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))'));
145
146 ALTER TABLE t1 DROP COLUMN p1, ADD COLUMN p1 POINT, CHANGE COLUMN p pp POINT AFTER p1;
147 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'pp' OR name = 'g' OR name = 'p1';
148
149 UPDATE t1 SET p1 = ST_PointFromText('POINT(5 5)');
150
151 SELECT ST_AsText(pp), ST_AsText(p1) FROM t1;
152
153 --error ER_SPATIAL_CANT_HAVE_NULL
154 ALTER TABLE t1 ADD SPATIAL(p1), ADD SPATIAL(pp), ALGORITHM = COPY;
155 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'pp' OR name = 'g' OR name = 'p1';
156
157 SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
158 SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
159
160 SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))')) ORDER BY pp;
161 SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE MBRWithin(pp, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))')) ORDER BY pp;
162
163 DROP TABLE t1;
164
165
166 --echo #
167 --echo # Test when the POINT is on B-TREE
168 --echo #
169 CREATE TABLE gis_point(fid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, p POINT NOT NULL, KEY(p)) ENGINE=InnoDB;
170
171 INSERT INTO gis_point VALUES
172 (101, ST_PointFromText('POINT(10 10)')),
173 (102, ST_PointFromText('POINT(20 10)')),
174 (103, ST_PointFromText('POINT(20 20)')),
175 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
176
177 SELECT ST_AsText(p) FROM gis_point;
178 SELECT ST_AsText(p) FROM gis_point WHERE p = ST_PointFromText('POINT(20 20)');
179
180 INSERT INTO gis_point VALUES
181 (201, ST_PointFromText('POINT(100.32374832 101.23741821)')),
182 (202, ST_PointFromText('POINT(102.43287328 100.23489233)')),
183 (203, ST_PointFromText('POINT(101.43284962 100.45892392)')),
184 (204, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(103.43718640 105.248206478)')))),
185 (205, ST_PointFromText('POINT(101.43284962 100.45892392)')),
186 (206, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(103.43718640 105.248206478)'))));
187
188 ANALYZE TABLE gis_point;
189
190 -- echo 'The ORDER BY will use filesort'
191 --replace_column 10 #
192 EXPLAIN SELECT ST_AsText(p) FROM gis_point ORDER BY p;
193 SELECT ST_AsText(p) FROM gis_point ORDER BY p;
194
195 --sorted_result
196 SELECT ST_AsText(p), COUNT(*) FROM gis_point GROUP BY p;
197
198 TRUNCATE gis_point;
199
200 INSERT INTO gis_point VALUES
201 (101, ST_PointFromText('POINT(10 10)')),
202 (102, ST_PointFromText('POINT(20 10)')),
203 (103, ST_PointFromText('POINT(20 20)')),
204 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
205
206 ALTER TABLE gis_point ADD COLUMN g POINT;
207
208 INSERT INTO gis_point VALUES
209 (105, ST_PointFromText('POINT(25 15)'), ST_PointFromText('POINT(100 100)')),
210 (106, ST_PointFromText('POINT(25 25)'), ST_PointFromText('POINT(110 110)'));
211
212 SELECT ST_AsText(p) FROM gis_point;
213 SELECT ST_AsText(p), ST_AsText(g) FROM gis_point WHERE g IS NULL;
214
215 UPDATE gis_point SET g = ST_PointFromText('POINT(200 200)') WHERE g IS NULL OR g = ST_PointFromText('POINT(110 110)');
216 SELECT ST_AsText(g) FROM gis_point WHERE g IS NULL OR g = ST_PointFromText('POINT(200 200)');
217
218 --echo # Check the information schema tables
219 SELECT table_name, column_name, data_type, column_type
220 FROM INFORMATION_SCHEMA.COLUMNS
221 WHERE table_name='gis_point' AND (column_name = 'p' OR column_name = 'g')
222 ORDER BY table_name, column_name;
223
224 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'p' OR name = 'g';
225
226 SELECT length(p) FROM gis_point;
227
228 ALTER TABLE gis_point DROP COLUMN g, ALGORITHM = COPY;
229
230 TRUNCATE gis_point;
231
232 INSERT INTO gis_point VALUES
233 (101, ST_PointFromText('POINT(10 10)')),
234 (102, ST_PointFromText('POINT(20 10)')),
235 (103, ST_PointFromText('POINT(20 20)')),
236 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
237
238 ALTER TABLE gis_point ADD COLUMN g POINT;
239
240 INSERT INTO gis_point VALUES
241 (105, ST_PointFromText('POINT(25 15)'), ST_PointFromText('POINT(100 100)')),
242 (106, ST_PointFromText('POINT(25 25)'), ST_PointFromText('POINT(110 110)'));
243
244 SELECT ST_AsText(p) FROM gis_point;
245 SELECT ST_AsText(p), ST_AsText(g) FROM gis_point WHERE g IS NULL;
246
247 UPDATE gis_point SET g = ST_PointFromText('POINT(200 200)') WHERE g IS NULL OR g = ST_PointFromText('POINT(110 110)');
248 SELECT ST_AsText(g) FROM gis_point WHERE g IS NULL OR g = ST_PointFromText('POINT(200 200)');
249
250 CHECK TABLE gis_point;
251
252 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'p' OR name = 'g';
253
254 DROP TABLE gis_point;
255
256
257 --echo #
258 --echo # Check the mtype of other geometry data types should be 15
259 --echo #
260 CREATE TABLE g (
261 geom GEOMETRY NOT NULL,
262 l LINESTRING NOT NULL,
263 poly POLYGON NOT NULL,
264 mp MULTIPOINT NOT NULL,
265 ml MULTILINESTRING NOT NULL,
266 mpoly MULTIPOLYGON NOT NULL,
267 gc GEOMETRYCOLLECTION NOT NULL
268 ) ENGINE=InnoDB;
269 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'geom' OR name = 'l' OR name = 'poly' OR name = 'mp' OR name = 'ml' OR name = 'mpoly' OR name = 'gc';
270
271 DROP TABLE g;
272
273 --echo #
274 --echo # check the mtype and len with CREATE TABLE AS
275 --echo #
276
277 CREATE TABLE t1 (
278 p POINT NOT NULL,
279 g GEOMETRY NOT NULL
280 ) ENGINE=InnoDB;
281
282
283 INSERT INTO t1 VALUES(ST_PointFromText('POINT(10 10)'),ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
284
285 # Check the mtype and len of the table
286 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'p' OR name = 'g' AND name='t1';
287
288 CREATE TABLE t2 AS SELECT * FROM t1;
289
290 # Cleanup
291 DROP TABLE t1;
292
293 # Check the mtype and len of the table, should be 14,25
294 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'p' OR name = 'g' AND name='t2';
295
296 # Cleanup
297 DROP table t2;
298
299
300 --echo #
301 --echo # Test when POINT is used in spatial index
302 --echo #
303 SET @ls1 = ST_GeomFromText('LINESTRING(0 20, 10 0)');
304 SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 10 20)');
305 SET @ls3 = ST_GeomFromText('LINESTRING(20 -40, 21 -42)');
306 SET @ls4 = ST_GeomFromText('LINESTRING(20 -42, 21 -40)');
307 SET @poly1 = ST_GeomFromText('POLYGON((2 2, 2 10, 10 10, 10 2, 2 2))');
308 SET @poly2 = ST_GeomFromText('POLYGON((0 0, -5 0, -4 -1, -6 -15, -3 -15, 0 0))');
309 SET @poly3 = ST_GeomFromText('POLYGON((10.0 10.0, 20.5 20, 20.5 50, 32.0 64.0, 32.3 64.6, 5 60, 10 10))');
310 SET @poly4 = ST_GeomFromText('POLYGON((0 10, -10 10, -10 -10, 0 -10, 0 10))');
311 SET @p1 = ST_PointFromText('POINT(0 0)');
312 SET @mpoly = ST_GeomFromText('MULTIPOLYGON(((3 3, 3 16, 16 16, 16 3, 3 3)), ((10 10, 10 50, 50 50, 50 10, 10 10)))');
313
314 CREATE TABLE gis_point (p1 POINT NOT NULL SRID 0, p2 POINT NOT NULL SRID 0, SPATIAL KEY k1 (p1), SPATIAL KEY k2 (p2)) ENGINE=InnoDB;
315
316 INSERT INTO gis_point VALUES
317 (ST_PointFromText('POINT(1 2)'), ST_PointFromText('POINT(-1 -3)')),
318 (ST_PointFromText('POINT(2 4)'), ST_PointFromText('POINT(-2 -6)')),
319 (ST_PointFromText('POINT(3 6)'), ST_PointFromText('POINT(-3 -9)')),
320 (ST_PointFromText('POINT(4 8)'), ST_PointFromText('POINT(-4 -12)')),
321 (ST_PointFromText('POINT(5 10)'), ST_PointFromText('POINT(-5 -15)')),
322 (ST_PointFromText('POINT(6 12)'), ST_PointFromText('POINT(-6 -18)')),
323 (ST_PointFromText('POINT(7 14)'), ST_PointFromText('POINT(-7 -21)')),
324 (ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)')),
325 (ST_PointFromText('POINT(9 18)'), ST_PointFromText('POINT(-4 2)')),
326 (ST_PointFromText('POINT(10 21)'), ST_PointFromText('POINT(-6 3)')),
327 (ST_PointFromText('POINT(20.5 41)'), ST_PointFromText('POINT(-8 4)')),
328 (ST_PointFromText('POINT(26.25 57)'), ST_PointFromText('POINT(1 2)')),
329 (ST_PointFromText('POINT(32.1234 64.2468)'), ST_PointFromText('POINT(-1 -1)'));
330
331 CREATE TABLE gis_point1 SELECT * FROM gis_point;
332
333 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'p1' OR name = 'p2';
334
335 --sorted_result
336 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point1;
337 --sorted_result
338 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Intersection(@ls1, @ls2) = p1;
339 --sorted_result
340 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1);
341 --sorted_result
342 SELECT ST_AsText(p2) FROM gis_point1 WHERE ST_Contains(@poly2, p2);
343 --sorted_result
344 SELECT ST_AsText(p2) FROM gis_point1 WHERE ST_Equals(p2, @p1);
345 --sorted_result
346 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
347 --sorted_result
348 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Contains(@mpoly, p1);
349 --sorted_result
350 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Contains(@mpoly, p1) AND NOT MBRWithin(p1, @mpoly);
351
352 --echo # Check functions that use MBR, with line type data
353 --sorted_result
354 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @ls1);
355 --sorted_result
356 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @ls1);
357 --sorted_result
358 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @ls1);
359 --sorted_result
360 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @ls1);
361
362 --echo # No matching records
363 --sorted_result
364 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @ls1);
365 --sorted_result
366 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @ls1);
367 --sorted_result
368 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @ls3);
369 --sorted_result
370 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @ls1);
371
372 --echo # Check functions that use MBR, with polygon type data
373 --sorted_result
374 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @poly1);
375 --sorted_result
376 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1);
377 --sorted_result
378 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @poly1);
379 --sorted_result
380 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @poly1);
381 --sorted_result
382 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @poly1);
383
384 --echo # No matching records
385 --sorted_result
386 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @poly1);
387 --sorted_result
388 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @poly1);
389 --sorted_result
390 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @poly1);
391
392 --echo # Check functions that use MBR, with point type data
393 --sorted_result
394 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1);
395 --sorted_result
396 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1);
397 --sorted_result
398 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1);
399 --sorted_result
400 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @p1);
401 --sorted_result
402 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1);
403 --sorted_result
404 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1);
405
406 --echo # No matching records
407 --sorted_result
408 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1);
409 --sorted_result
410 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @p1);
411
412 --echo # Check with MBR functions point type data
413 --sorted_result
414 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1);
415 --sorted_result
416 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1);
417 --sorted_result
418 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1);
419 --sorted_result
420 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1);
421 --sorted_result
422 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1);
423 --sorted_result
424 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @p1);
425
426 --echo # No matching records
427 --sorted_result
428 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1);
429
430 --echo # Check with MBR functions point polygon data
431 --sorted_result
432 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1);
433 --sorted_result
434 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1);
435 --sorted_result
436 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1);
437 --sorted_result
438 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1);
439 --sorted_result
440 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1);
441 --sorted_result
442 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @p1);
443
444 --echo # No matching records
445 --sorted_result
446 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1);
447
448 --echo # Check with MBR functions polygon type data
449 --sorted_result
450 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @poly1);
451 --sorted_result
452 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1);
453 --sorted_result
454 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @poly1);
455 --sorted_result
456 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @poly1);
457
458 --echo # No matching records
459 --sorted_result
460 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @poly1);
461 --sorted_result
462 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @poly1);
463 --sorted_result
464 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @poly1);
465
466 --echo # Check with MBR functions line type data
467 --sorted_result
468 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @ls1);
469 --sorted_result
470 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @ls1);
471 --sorted_result
472 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @ls1);
473 --sorted_result
474 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @ls1);
475 --sorted_result
476 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @ls1);
477
478 --echo # No matching records
479 --sorted_result
480 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @ls1);
481 --sorted_result
482 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @ls1);
483
484 DROP TABLE gis_point1;
485
486 --sorted_result
487 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point;
488 --sorted_result
489 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Intersection(@ls1, @ls2) = p1;
490 --sorted_result
491 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
492 --sorted_result
493 SELECT ST_AsText(p2) FROM gis_point WHERE ST_Contains(@poly2, p2);
494 --sorted_result
495 SELECT ST_AsText(p2) FROM gis_point WHERE ST_Equals(p2, @p1);
496 --sorted_result
497 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
498 --sorted_result
499 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Contains(@mpoly, p1);
500 --sorted_result
501 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Contains(@mpoly, p1) AND NOT MBRWithin(p1, @mpoly);
502
503 --echo # Check functions that use MBR, with line type data
504 --sorted_result
505 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
506 --sorted_result
507 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
508 --sorted_result
509 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
510 --sorted_result
511 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
512
513 --echo # No matching records
514 --sorted_result
515 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @ls1);
516 --sorted_result
517 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
518 --sorted_result
519 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls3);
520 --sorted_result
521 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @ls1);
522
523 --echo # Check functions that use MBR, with polygon type data
524 --sorted_result
525 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
526 --sorted_result
527 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
528 --sorted_result
529 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
530 --sorted_result
531 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @poly1);
532 --sorted_result
533 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
534
535 --echo # No matching records
536 --sorted_result
537 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @poly1);
538 --sorted_result
539 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
540 --sorted_result
541 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
542
543 --echo # Check functions that use MBR, with point type data
544 --sorted_result
545 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
546 --sorted_result
547 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
548 --sorted_result
549 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
550 --sorted_result
551 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @p1);
552 --sorted_result
553 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
554 --sorted_result
555 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
556
557 --echo # No matching records
558 --sorted_result
559 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
560 --sorted_result
561 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @p1);
562
563 --echo # Check with MBR functions point type data
564 --sorted_result
565 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
566 --sorted_result
567 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
568 --sorted_result
569 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
570 --sorted_result
571 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
572 --sorted_result
573 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
574 --sorted_result
575 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
576
577 --echo # No matching records
578 --sorted_result
579 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
580
581 --echo # Check with MBR functions point polygon data
582 --sorted_result
583 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
584 --sorted_result
585 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
586 --sorted_result
587 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
588 --sorted_result
589 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
590 --sorted_result
591 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
592 --sorted_result
593 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
594
595 --echo # No matching records
596 --sorted_result
597 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
598
599 --echo # Check with MBR functions polygon type data
600 --sorted_result
601 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
602 --sorted_result
603 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
604 --sorted_result
605 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
606 --sorted_result
607 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
608
609 --echo # No matching records
610 --sorted_result
611 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
612 --sorted_result
613 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
614 --sorted_result
615 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @poly1);
616
617 --echo # Check with MBR functions line type data
618 --sorted_result
619 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
620 --sorted_result
621 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
622 --sorted_result
623 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
624 --sorted_result
625 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
626 --sorted_result
627 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @ls1);
628
629 --echo # No matching records
630 --sorted_result
631 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls1);
632 --sorted_result
633 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
634
635 --echo # Modify the storage engine to Myisam, Check the spatial functions
636 ALTER TABLE gis_point ENGINE Myisam;
637
638 --echo # Check functions that use MBR, with line type data
639 --sorted_result
640 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
641 --sorted_result
642 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
643 --sorted_result
644 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
645 --sorted_result
646 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
647
648 --echo # No matching records
649 --sorted_result
650 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @ls1);
651 --sorted_result
652 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
653 --sorted_result
654 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls3);
655 --sorted_result
656 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @ls1);
657
658 --echo # Check functions that use MBR, with polygon type data
659 --sorted_result
660 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
661 --sorted_result
662 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
663 --sorted_result
664 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
665 --sorted_result
666 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @poly1);
667 --sorted_result
668 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
669
670 --echo # No matching records
671 --sorted_result
672 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @poly1);
673 --sorted_result
674 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
675 --sorted_result
676 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
677
678 --echo # Check functions that use MBR, with point type data
679 --sorted_result
680 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
681 --sorted_result
682 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
683 --sorted_result
684 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
685 --sorted_result
686 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @p1);
687 --sorted_result
688 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
689 --sorted_result
690 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
691
692 --echo # No matching records
693 --sorted_result
694 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
695 --sorted_result
696 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @p1);
697
698 --echo # Check with MBR functions point type data
699 --sorted_result
700 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
701 --sorted_result
702 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
703 --sorted_result
704 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
705 --sorted_result
706 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
707 --sorted_result
708 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
709 --sorted_result
710 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
711
712 --echo # No matching records
713 --sorted_result
714 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
715
716 --echo # Check with MBR functions point polygon data
717 --sorted_result
718 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
719 --sorted_result
720 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
721 --sorted_result
722 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
723 --sorted_result
724 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
725 --sorted_result
726 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
727 --sorted_result
728 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
729
730 --echo # No matching records
731 --sorted_result
732 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
733
734 --echo # Check with MBR functions polygon type data
735 --sorted_result
736 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
737 --sorted_result
738 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
739 --sorted_result
740 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
741 --sorted_result
742 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
743
744 --echo # No matching records
745 --sorted_result
746 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
747 --sorted_result
748 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
749 --sorted_result
750 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @poly1);
751
752 --echo # Check with MBR functions line type data
753 --sorted_result
754 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
755 --sorted_result
756 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
757 --sorted_result
758 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
759 --sorted_result
760 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
761 --sorted_result
762 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @ls1);
763
764 --echo # No matching records
765 --sorted_result
766 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls1);
767 --sorted_result
768 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
769
770 --echo # Modify the storage engine to InnoDB again, do following testing
771 ALTER TABLE gis_point ENGINE InnoDB;
772
773 CHECK TABLE gis_point;
774
775 ANALYZE TABLE gis_point;
776
777 -- echo The ORDER BY for spatial index will use filesort
778 --replace_column 10 #
779 EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
780 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
781
782 -- echo Try to do IDU on the table and verify the result
783 DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
784
785 INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
786
787 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
788
789 UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 41)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
790
791 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 41)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
792
793 CHECK TABLE gis_point;
794
795 -- echo Use a trx to test the IDU on the table and verify the result
796 START TRANSACTION;
797
798 DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
799
800 INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
801
802 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
803
804 UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 49)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
805
806 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 49)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
807
808 ROLLBACK;
809
810 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 41)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
811
812 CHECK TABLE gis_point;
813
814 DROP TABLE gis_point;
815
816
817
818 --echo #
819 --echo # Test inserting/updating different type data into POINT field
820 --echo #
821
822 CREATE TABLE gis_point (i INT, p POINT) ENGINE=InnoDB;
823 CREATE TABLE geom (i INT, g GEOMETRY NOT NULL SRID 0, SPATIAL KEY(g)) ENGINE=InnoDB;
824
825 INSERT INTO gis_point VALUES(0, ST_PointFromText('POINT(1 1)'));
826 INSERT INTO gis_point VALUES(1, ST_PointFromText('POINT(2 2)'));
827 INSERT INTO gis_point VALUES(2, NULL);
828
829 ALTER TABLE gis_point ADD COLUMN j INT, ALGORITHM = COPY;
830
831 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'p';
832
833 SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE name = 'p';
834
835 SELECT i, ST_AsText(p) FROM gis_point;
836
837 UPDATE gis_point SET p = NULL WHERE p = ST_PointFromText('POINT(1 1)');
838 UPDATE gis_point SET p = ST_PointFromText('POINT(1 2)') WHERE p = ST_PointFromText('POINT(2 2)');
839 UPDATE gis_point SET p = ST_PointFromText('POINT(1 1)') WHERE p IS NULL;
840
841 SELECT i, ST_AsText(p) FROM gis_point;
842
843 INSERT INTO geom VALUES(0, ST_PointFromText('POINT(0 0)'));
844 INSERT INTO geom VALUES(1, ST_PointFromText('POINT(10 10)'));
845 INSERT INTO geom VALUES(2, ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'));
846
847 SELECT ST_AsText(g) FROM geom;
848
849 SELECT ST_AsText(p) FROM gis_point;
850
851 DELETE FROM geom WHERE g = ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
852 --echo From GEOMETRY to POINT, now ALL the data are POINT
853 ALTER TABLE geom MODIFY g POINT NOT NULL SRID 0;
854 SHOW CREATE TABLE geom;
855 SELECT ST_AsText(g) FROM geom;
856
857 --echo From POINT to GEOMETRY, all data are POINT
858 ALTER TABLE geom MODIFY g GEOMETRY NOT NULL SRID 0;
859 SHOW CREATE TABLE geom;
860
861 TRUNCATE TABLE geom;
862
863 --echo From GEOMETRY to POINT, the table is empty
864 ALTER TABLE geom MODIFY g POINT NOT NULL SRID 0;
865 SHOW CREATE TABLE geom;
866 SELECT ST_AsText(g) FROM geom;
867
868 CHECK TABLE gis_point;
869 CHECK TABLE geom;
870
871 DROP TABLE gis_point, geom;
872
873
874
875 --echo #
876 --echo # Test when a geom field, such as POINT, is also a primary key
877 --echo #
878
879 CREATE TABLE t1 (
880 a INT NOT NULL,
881 p POINT NOT NULL SRID 0,
882 l LINESTRING NOT NULL SRID 0,
883 g GEOMETRY NOT NULL SRID 0,
884 SPATIAL KEY `idx2` (p),
885 SPATIAL KEY `idx3` (l),
886 SPATIAL KEY `idx4` (g)
887 ) ENGINE=InnoDB;
888
889 INSERT INTO t1 VALUES(
890 1, ST_GeomFromText('POINT(10 10)'),
891 ST_GeomFromText('LINESTRING(1 1, 5 5, 10 10)'),
892 ST_GeomFromText('POLYGON((30 30, 40 40, 50 50, 30 50, 30 40, 30 30))'));
893
894 INSERT INTO t1 VALUES(
895 2, ST_GeomFromText('POINT(20 20)'),
896 ST_GeomFromText('LINESTRING(2 3, 7 8, 9 10, 15 16)'),
897 ST_GeomFromText('POLYGON((10 30, 30 40, 40 50, 40 30, 30 20, 10 30))'));
898
899 ANALYZE TABLE t1;
900
901 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
902
903 --replace_column 10 #
904 EXPLAIN UPDATE t1 SET p = ST_GeomFromText('POINT(30 30)') WHERE p = ST_GeomFromText('POINT(20 20)');
905 UPDATE t1 SET p = ST_GeomFromText('POINT(30 30)') WHERE p = ST_GeomFromText('POINT(20 20)');
906
907 # Once Bug 18265942 - GIS: INNODB WOULD GET POSSIBLE WRONG FIND_FLAG FOR SELECT FROM SERVER is fixed, we can test all the following statements commented out.
908 #SELECT ST_AsText(p) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((25 25, 25 35, 35 35, 35 25, 25 25))'));
909 #UPDATE t1 SET p = ST_GeomFromText('POINT(15 15)') WHERE MBRWithin(p, ST_GeomFromText('POLYGON((25 25, 25 35, 35 35, 35 25, 25 25))'));
910 #DELETE FROM t1 WHERE MBRWithin(p, ST_GeomFromText('LINESTRING(15 15, 15 25, 25 25, 35 15, 15 15)'));
911 #SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
912 #INSERT INTO t1 VALUES(
913 #3, ST_GeomFromText('POINT(25 25)'),
914 #ST_GeomFromText('LINESTRING(2 3, 7 8, 9 10, 15 16)'),
915 #ST_GeomFromText('POLYGON((10 30, 30 40, 40 50, 40 30, 30 20, 10 30))'));
916 #UPDATE t1 SET p = ST_GeomFromText('POINT(50 50)'), l = ST_GeomFromText('LINESTRING(20 20, 21 21, 22 22, 23 23)') WHERE MBRWithin(g, ST_GeomFromText('LINESTRING(25 25, 25 50, 50 50, 50 25, 25 25)'));
917
918 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
919
920 ALTER TABLE t1 ADD PRIMARY KEY(a);
921
922 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
923
924 ALTER TABLE t1 DROP PRIMARY KEY;
925 --error ER_SPATIAL_UNIQUE_INDEX
926 ALTER TABLE t1 ADD PRIMARY KEY(p);
927
928 ANALYZE TABLE t1;
929
930 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
931
932 SHOW CREATE TABLE t1;
933
934 # Also waiting for Bug 18265942
935 #UPDATE t1 SET p = ST_PointFromText('POINT(20 20)') WHERE ST_Equals(p, ST_PointFromText('POINT(30 30)'));
936 #INSERT INTO t1 VALUES(
937 #3, ST_GeomFromText('POINT(25 25)'),
938 #ST_GeomFromText('LINESTRING(2 3, 7 8, 9 10, 15 16)'),
939 #ST_GeomFromText('POLYGON((10 30, 30 40, 40 50, 40 30, 30 20, 10 30))'));
940
941 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
942
943 --replace_column 10 #
944 EXPLAIN SELECT a, ST_AsText(p) FROM t1 WHERE a = 2 AND p = ST_GeomFromText('POINT(30 30)');
945 SELECT a, ST_AsText(p) FROM t1 WHERE a = 2 AND p = ST_GeomFromText('POINT(30 30)');
946
947 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
948
949 CHECK TABLE t1;
950
951 DROP TABLE t1;
952
953 --echo #
954 --echo # Test for foreign keys.
955 --echo #
956 CREATE TABLE parent(p POINT NOT NULL SRID 0) ENGINE=InnoDB;
957 CREATE TABLE child(p POINT NOT NULL SRID 0) ENGINE=InnoDB;
958 ALTER TABLE parent ADD SPATIAL INDEX idx1(p);
959 ALTER TABLE child ADD SPATIAL INDEX idx2(p);
960 SHOW CREATE TABLE parent;
961 SHOW CREATE TABLE child;
962
963 --error ER_FK_NO_INDEX_CHILD
964 ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
965
966 ALTER TABLE parent DROP INDEX idx1;
967 --error ER_FK_NO_INDEX_CHILD
968 ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
969
970 ALTER TABLE child DROP INDEX idx2;
971 --error ER_FK_NO_INDEX_CHILD
972 ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
973
974 DROP TABLE child, parent;
975
976 --echo #
977 --echo # Bug#28763: Selecting geometry fields in UNION caused server crash.
978 --echo #
979 CREATE TABLE t1(f1 GEOMETRY, f2 POINT, f3 GEOMETRY) ENGINE=InnoDB;
980 SELECT f1 FROM t1 UNION SELECT f1 FROM t1;
981 INSERT INTO t1 (f2,f3) VALUES (ST_GeomFromText('POINT(1 1)'),
982 ST_GeomFromText('POINT(2 2)'));
983 SELECT ST_AsText(f2),ST_AsText(f3) FROM t1;
984 SELECT ST_AsText(a) FROM (SELECT f2 AS a FROM t1 UNION SELECT f3 FROM t1) t;
985 CREATE TABLE t2 AS SELECT f2 AS a FROM t1 UNION SELECT f3 FROM t1;
986 DESC t2;
987 SELECT ST_AsText(a) FROM t2;
988 DROP TABLE t1, t2;