Debian Codegraph @

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

raw
1 #
2 # Test for basic POINT operation
3 #
4 CREATE TABLE gis_point(p1 POINT, p2 POINT) ENGINE=InnoDB;
5 INSERT INTO gis_point VALUES
6 (ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(0 0)')),
7 (ST_PointFromText('POINT(10 -20)'), ST_PointFromText('POINT(10 -20)')),
8 (ST_PointFromText('POINT(3.1415926 3.535897)'), ST_PointFromText('POINT(-3.932626 -3.488272)')),
9 (ST_PointFromText('POINT(-111.9876 234.1357)'), ST_PointFromText('POINT(-957.1914 958.1919)'));
10 SELECT ST_X(p1), ST_Y(p2) FROM gis_point;
11 ST_X(p1) ST_Y(p2)
12 0 0
13 10 -20
14 3.1415926 -3.488272
15 -111.9876 958.1919
16 DROP TABLE gis_point;
17 #
18 # Test when POINT is not on any indexes
19 #
20 CREATE TABLE gis_point(p1 POINT, p2 POINT) ENGINE=InnoDB;
21 INSERT INTO gis_point VALUES
22 (ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(100.32374832 101.23741821)')),
23 (ST_PointFromText('POINT(105.34523342 103.18492302)'), ST_PointFromText('POINT(100.32374832 101.23741821)')),
24 (ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(200.32247328 101.86728201)')),
25 (ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(100.32374832 101.98527111)'));
26 EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
27 id select_type table type possible_keys key key_len ref rows Extra
28 1 SIMPLE gis_point ALL NULL NULL NULL NULL # Using temporary; Using filesort
29 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
30 ST_AsText(p1) ST_AsText(p2)
31 POINT(100.32374832 101.23741821) POINT(100.32374832 101.98527111)
32 POINT(100.32374832 101.23741821) POINT(100.32374832 101.23741821)
33 POINT(100.32374832 101.23741821) POINT(200.32247328 101.86728201)
34 POINT(105.34523342 103.18492302) POINT(100.32374832 101.23741821)
35 SELECT ST_AsText(p1) FROM gis_point WHERE p1 = p2;
36 ST_AsText(p1)
37 POINT(100.32374832 101.23741821)
38 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE p1 = ST_PointFromText('POINT(100.32374832 101.23741821)');
39 ST_AsText(p1) ST_AsText(p2)
40 POINT(100.32374832 101.23741821) POINT(100.32374832 101.23741821)
41 POINT(100.32374832 101.23741821) POINT(200.32247328 101.86728201)
42 POINT(100.32374832 101.23741821) POINT(100.32374832 101.98527111)
43 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE p2 = ST_PointFromText('POINT(200.32247328 101.86728201)');
44 ST_AsText(p1) ST_AsText(p2)
45 POINT(100.32374832 101.23741821) POINT(200.32247328 101.86728201)
46 DROP TABLE gis_point;
47 #
48 # Test some ALTER TABLE operations on POINT tables
49 #
50 CREATE TABLE t1 (
51 p POINT NOT NULL,
52 g GEOMETRY NOT NULL
53 ) ENGINE=InnoDB;
54 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 11)'));
55 INSERT INTO t1 VALUES(ST_PointFromText('POINT(1 1)'), ST_PointFromText('POINT(10 12)'));
56 INSERT INTO t1 VALUES(ST_PointFromText('POINT(1 0)'), ST_PointFromText('POINT(10 13)'));
57 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(10 14)'));
58 SELECT ST_AsText(p), ST_AsText(g) FROM t1;
59 ST_AsText(p) ST_AsText(g)
60 POINT(0 1) POINT(10 11)
61 POINT(1 1) POINT(10 12)
62 POINT(1 0) POINT(10 13)
63 POINT(0 0) POINT(10 14)
64 SELECT c.name, c.mtype, c.prtype, c.len
65 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
66 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
67 ON c.table_id = t.table_id
68 WHERE t.name='test/t1' AND c.name IN ('p','g');
69 name mtype prtype len
70 p 14 1535 12
71 g 14 1535 12
72 ALTER TABLE t1 ADD COLUMN p1 POINT, ADD COLUMN p2 POINT, ADD KEY(p);
73 SELECT c.name, c.mtype, c.prtype, c.len
74 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
75 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
76 ON c.table_id = t.table_id
77 WHERE t.name='test/t1' AND c.name IN ('p','g','p1','p2');
78 name mtype prtype len
79 p 14 1535 12
80 g 14 1535 12
81 p1 14 1279 12
82 p2 14 1279 12
83 # NOT NULL POINT will use ''
84 SELECT count(*) AS `Expect 4` FROM t1 WHERE p1 = '';
85 Expect 4
86 0
87 SELECT count(*) AS `Expect 4` FROM t1 WHERE p2 = '';
88 Expect 4
89 0
90 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p1;
91 ST_AsText(p) ST_AsText(p1)
92 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p2;
93 ST_AsText(p) ST_AsText(p1)
94 ALTER TABLE t1 DROP COLUMN p2;
95 # NULLABLE POINT will use NULL
96 ALTER TABLE t1 ADD COLUMN p2 POINT, ADD KEY(p2);
97 SELECT c.name, c.mtype, c.prtype, c.len
98 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
99 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
100 ON c.table_id = t.table_id
101 WHERE t.name='test/t1' AND c.name IN ('p','g','p1','p2');
102 name mtype prtype len
103 p 14 1535 12
104 g 14 1535 12
105 p1 14 1279 12
106 p2 14 1279 12
107 SELECT count(*) AS `Expect 4` FROM t1 WHERE p2 IS NULL;
108 Expect 4
109 4
110 UPDATE t1 SET p2 = ST_PointFromText('POINT(10 20)');
111 UPDATE t1 SET p1 = ST_PointFromText('POINT(10 20)');
112 ALTER TABLE t1 DROP COLUMN p2;
113 SELECT c.name, c.mtype, c.prtype, c.len
114 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
115 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
116 ON c.table_id = t.table_id
117 WHERE t.name='test/t1' AND c.name IN ('p','g','p1','p2');
118 name mtype prtype len
119 p 14 1535 12
120 g 14 1535 12
121 p1 14 1279 12
122 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p1;
123 ST_AsText(p) ST_AsText(p1)
124 INSERT INTO t1 VALUES (ST_PointFromText('POINT(0.5 0.5)'), ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 19)'));
125 SELECT ST_AsText(p1) FROM t1;
126 ST_AsText(p1)
127 POINT(10 20)
128 POINT(10 20)
129 POINT(10 20)
130 POINT(10 20)
131 POINT(10 19)
132 DELETE FROM t1 WHERE p1 = ST_PointFromText('POINT(10 19)');
133 SELECT ST_AsText(p1) FROM t1;
134 ST_AsText(p1)
135 POINT(10 20)
136 POINT(10 20)
137 POINT(10 20)
138 POINT(10 20)
139 # Add spatial keys on the table
140 ALTER TABLE t1 ADD SPATIAL(p), ADD SPATIAL(p1);
141 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
142 SELECT c.name, c.mtype, c.prtype, c.len
143 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
144 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
145 ON c.table_id = t.table_id
146 WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
147 name mtype prtype len
148 p 14 1535 12
149 g 14 1535 12
150 p1 14 1279 12
151 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
152 ST_AsText(p) ST_AsText(p1)
153 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
154 ST_AsText(p) ST_AsText(p1)
155 POINT(0 1) POINT(10 20)
156 POINT(1 1) POINT(10 20)
157 POINT(1 0) POINT(10 20)
158 POINT(0 0) POINT(10 20)
159 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))'));
160 ST_AsText(p) ST_AsText(p1)
161 POINT(0 1) POINT(10 20)
162 POINT(1 1) POINT(10 20)
163 POINT(1 0) POINT(10 20)
164 POINT(0 0) POINT(10 20)
165 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))'));
166 ST_AsText(p) ST_AsText(p1)
167 POINT(0 0) POINT(10 20)
168 # Drop spatial keys on the table
169 ALTER TABLE t1 DROP KEY p, DROP KEY p1;
170 ERROR 42000: Can't DROP INDEX `p1`; check that it exists
171 SELECT c.name, c.mtype, c.prtype, c.len
172 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
173 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
174 ON c.table_id = t.table_id
175 WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
176 name mtype prtype len
177 p 14 1535 12
178 g 14 1535 12
179 p1 14 1279 12
180 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
181 ST_AsText(p) ST_AsText(p1)
182 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
183 ST_AsText(p) ST_AsText(p1)
184 POINT(0 1) POINT(10 20)
185 POINT(1 1) POINT(10 20)
186 POINT(1 0) POINT(10 20)
187 POINT(0 0) POINT(10 20)
188 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))'));
189 ST_AsText(p) ST_AsText(p1)
190 POINT(0 1) POINT(10 20)
191 POINT(1 1) POINT(10 20)
192 POINT(1 0) POINT(10 20)
193 POINT(0 0) POINT(10 20)
194 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))'));
195 ST_AsText(p) ST_AsText(p1)
196 POINT(0 0) POINT(10 20)
197 TRUNCATE t1;
198 ALTER TABLE t1 DROP COLUMN p, DROP COLUMN p1;
199 SELECT c.name, c.mtype, c.prtype, c.len
200 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
201 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
202 ON c.table_id = t.table_id
203 WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
204 name mtype prtype len
205 g 14 1535 12
206 ALTER TABLE t1 ADD COLUMN p POINT, ADD COLUMN p1 POINT;
207 SELECT c.name, c.mtype, c.prtype, c.len
208 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
209 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
210 ON c.table_id = t.table_id
211 WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
212 name mtype prtype len
213 g 14 1535 12
214 p 14 1279 12
215 p1 14 1279 12
216 SHOW CREATE TABLE t1;
217 Table Create Table
218 t1 CREATE TABLE `t1` (
219 `g` geometry NOT NULL,
220 `p` point DEFAULT NULL,
221 `p1` point DEFAULT NULL
222 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
223 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.5 0.5)'), ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 19)'));
224 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.6 0.6)'), ST_PointFromText('POINT(1 1)'), ST_PointFromText('POINT(10 20)'));
225 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.7 0.7)'), ST_PointFromText('POINT(1 0)'), ST_PointFromText('POINT(10 21)'));
226 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.8 0.8)'), ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(10 22)'));
227 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
228 ST_AsText(p) ST_AsText(p1)
229 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
230 ST_AsText(p) ST_AsText(p1)
231 POINT(1 1) POINT(10 20)
232 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))'));
233 ST_AsText(p) ST_AsText(p1)
234 POINT(0 1) POINT(10 19)
235 POINT(1 1) POINT(10 20)
236 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))'));
237 ST_AsText(p) ST_AsText(p1)
238 POINT(0 0) POINT(10 22)
239 ALTER TABLE t1 DROP COLUMN p1, ADD COLUMN p1 POINT, CHANGE COLUMN p pp POINT AFTER p1;
240 SELECT c.name, c.mtype, c.prtype, c.len
241 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
242 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
243 ON c.table_id = t.table_id
244 WHERE t.name='test/t1' AND c.name IN ('pp','g','p1');
245 name mtype prtype len
246 g 14 1535 12
247 p1 14 1279 12
248 pp 14 1279 12
249 UPDATE t1 SET p1 = ST_PointFromText('POINT(5 5)');
250 SELECT ST_AsText(pp), ST_AsText(p1) FROM t1;
251 ST_AsText(pp) ST_AsText(p1)
252 POINT(0 1) POINT(5 5)
253 POINT(1 1) POINT(5 5)
254 POINT(1 0) POINT(5 5)
255 POINT(0 0) POINT(5 5)
256 ALTER TABLE t1 ADD SPATIAL(p1), ADD SPATIAL(pp), ALGORITHM = COPY;
257 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
258 SELECT c.name, c.mtype, c.prtype, c.len
259 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
260 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
261 ON c.table_id = t.table_id
262 WHERE t.name='test/t1' AND c.name IN ('pp','g','p1');
263 name mtype prtype len
264 g 14 1535 12
265 p1 14 1279 12
266 pp 14 1279 12
267 SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
268 ST_AsText(pp) ST_AsText(p1)
269 SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
270 ST_AsText(pp) ST_AsText(p1)
271 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;
272 ST_AsText(pp) ST_AsText(p1)
273 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;
274 ST_AsText(pp) ST_AsText(p1)
275 POINT(0 0) POINT(5 5)
276 DROP TABLE t1;
277 #
278 # Test when the POINT is on B-TREE
279 #
280 CREATE TABLE gis_point(fid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, p POINT, KEY(p)) ENGINE=InnoDB STATS_PERSISTENT=0;
281 INSERT INTO gis_point VALUES
282 (101, ST_PointFromText('POINT(10 10)')),
283 (102, ST_PointFromText('POINT(20 10)')),
284 (103, ST_PointFromText('POINT(20 20)')),
285 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
286 SELECT ST_AsText(p) FROM gis_point;
287 ST_AsText(p)
288 POINT(10 10)
289 POINT(20 10)
290 POINT(20 20)
291 POINT(10 20)
292 SELECT ST_AsText(p) FROM gis_point WHERE p = ST_PointFromText('POINT(20 20)');
293 ST_AsText(p)
294 POINT(20 20)
295 INSERT INTO gis_point VALUES
296 (201, ST_PointFromText('POINT(100.32374832 101.23741821)')),
297 (202, ST_PointFromText('POINT(102.43287328 100.23489233)')),
298 (203, ST_PointFromText('POINT(101.43284962 100.45892392)')),
299 (204, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(103.43718640 105.248206478)')))),
300 (205, ST_PointFromText('POINT(101.43284962 100.45892392)')),
301 (206, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(103.43718640 105.248206478)'))));
302 'The ORDER BY will use filesort'
303 EXPLAIN SELECT ST_AsText(p) FROM gis_point ORDER BY p;
304 id select_type table type possible_keys key key_len ref rows Extra
305 1 SIMPLE gis_point ALL NULL NULL NULL NULL # Using filesort
306 SELECT ST_AsText(p) FROM gis_point ORDER BY p;
307 ST_AsText(p)
308 POINT(10 10)
309 POINT(10 20)
310 POINT(20 10)
311 POINT(20 20)
312 POINT(101.43284962 100.45892392)
313 POINT(101.43284962 100.45892392)
314 POINT(100.32374832 101.23741821)
315 POINT(102.43287328 100.23489233)
316 POINT(103.4371864 105.248206478)
317 POINT(103.4371864 105.248206478)
318 SELECT ST_AsText(p), COUNT(*) FROM gis_point GROUP BY p;
319 ST_AsText(p) COUNT(*)
320 POINT(10 10) 1
321 POINT(10 20) 1
322 POINT(20 10) 1
323 POINT(20 20) 1
324 POINT(101.43284962 100.45892392) 2
325 POINT(100.32374832 101.23741821) 1
326 POINT(102.43287328 100.23489233) 1
327 POINT(103.4371864 105.248206478) 2
328 TRUNCATE gis_point;
329 INSERT INTO gis_point VALUES
330 (101, ST_PointFromText('POINT(10 10)')),
331 (102, ST_PointFromText('POINT(20 10)')),
332 (103, ST_PointFromText('POINT(20 20)')),
333 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
334 # Check if we can create prefix index on POINT
335 ALTER TABLE gis_point ADD COLUMN g POINT, ADD KEY(g(8));
336 INSERT INTO gis_point VALUES
337 (105, ST_PointFromText('POINT(25 15)'), ST_PointFromText('POINT(100 100)')),
338 (106, ST_PointFromText('POINT(25 25)'), ST_PointFromText('POINT(110 110)'));
339 SELECT ST_AsText(p) FROM gis_point;
340 ST_AsText(p)
341 POINT(10 10)
342 POINT(20 10)
343 POINT(20 20)
344 POINT(10 20)
345 POINT(25 15)
346 POINT(25 25)
347 SELECT ST_AsText(p), ST_AsText(g) FROM gis_point WHERE g IS NULL;
348 ST_AsText(p) ST_AsText(g)
349 POINT(10 10) NULL
350 POINT(20 10) NULL
351 POINT(20 20) NULL
352 POINT(10 20) NULL
353 UPDATE gis_point SET g = ST_PointFromText('POINT(200 200)') WHERE g IS NULL OR g = ST_PointFromText('POINT(110 110)');
354 SELECT ST_AsText(g) FROM gis_point WHERE g IS NULL OR g = ST_PointFromText('POINT(200 200)');
355 ST_AsText(g)
356 POINT(200 200)
357 POINT(200 200)
358 POINT(200 200)
359 POINT(200 200)
360 POINT(200 200)
361 # Check the information schema tables
362 SELECT table_name, column_name, data_type, column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='gis_point' AND (column_name = 'p' OR column_name = 'g');
363 table_name column_name data_type column_type
364 gis_point p point point
365 gis_point g point point
366 SELECT c.name, c.mtype, c.prtype, c.len
367 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
368 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
369 ON c.table_id = t.table_id
370 WHERE t.name LIKE 'test/g%' AND c.name IN ('p','g');
371 name mtype prtype len
372 p 14 1279 12
373 g 14 1279 12
374 SELECT length(p) FROM gis_point;
375 length(p)
376 25
377 25
378 25
379 25
380 25
381 25
382 ALTER TABLE gis_point DROP COLUMN g, ALGORITHM = COPY;
383 TRUNCATE gis_point;
384 INSERT INTO gis_point VALUES
385 (101, ST_PointFromText('POINT(10 10)')),
386 (102, ST_PointFromText('POINT(20 10)')),
387 (103, ST_PointFromText('POINT(20 20)')),
388 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
389 # Check if we can create prefix index on POINT
390 ALTER TABLE gis_point ADD COLUMN g POINT, ADD KEY(g(8));
391 INSERT INTO gis_point VALUES
392 (105, ST_PointFromText('POINT(25 15)'), ST_PointFromText('POINT(100 100)')),
393 (106, ST_PointFromText('POINT(25 25)'), ST_PointFromText('POINT(110 110)'));
394 SELECT ST_AsText(p) FROM gis_point;
395 ST_AsText(p)
396 POINT(10 10)
397 POINT(20 10)
398 POINT(20 20)
399 POINT(10 20)
400 POINT(25 15)
401 POINT(25 25)
402 SELECT ST_AsText(p), ST_AsText(g) FROM gis_point WHERE g IS NULL;
403 ST_AsText(p) ST_AsText(g)
404 POINT(10 10) NULL
405 POINT(20 10) NULL
406 POINT(20 20) NULL
407 POINT(10 20) NULL
408 UPDATE gis_point SET g = ST_PointFromText('POINT(200 200)') WHERE g IS NULL OR g = ST_PointFromText('POINT(110 110)');
409 SELECT ST_AsText(g) FROM gis_point WHERE g IS NULL OR g = ST_PointFromText('POINT(200 200)');
410 ST_AsText(g)
411 POINT(200 200)
412 POINT(200 200)
413 POINT(200 200)
414 POINT(200 200)
415 POINT(200 200)
416 CHECK TABLE gis_point;
417 Table Op Msg_type Msg_text
418 test.gis_point check status OK
419 SELECT c.name, c.mtype, c.prtype, c.len
420 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
421 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
422 ON c.table_id = t.table_id
423 WHERE t.name='test/gis_point' AND c.name IN ('p','g');
424 name mtype prtype len
425 p 14 1279 12
426 g 14 1279 12
427 DROP TABLE gis_point;
428 #
429 # Check the mtype of other geometry data types should be 15
430 #
431 CREATE TABLE g (
432 geom GEOMETRY NOT NULL,
433 l LINESTRING NOT NULL,
434 poly POLYGON NOT NULL,
435 mp MULTIPOINT NOT NULL,
436 ml MULTILINESTRING NOT NULL,
437 mpoly MULTIPOLYGON NOT NULL,
438 gc GEOMETRYCOLLECTION NOT NULL
439 ) ENGINE=InnoDB;
440 SELECT c.name, c.mtype, c.prtype, c.len
441 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
442 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
443 ON c.table_id = t.table_id
444 WHERE t.name='test/g';
445 name mtype prtype len
446 geom 14 1535 12
447 l 14 1535 12
448 poly 14 1535 12
449 mp 14 1535 12
450 ml 14 1535 12
451 mpoly 14 1535 12
452 gc 14 1535 12
453 DROP TABLE g;
454 #
455 # check the mtype and len with CREATE TABLE AS
456 #
457 CREATE TABLE t1 (
458 p POINT NOT NULL,
459 g GEOMETRY NOT NULL
460 ) ENGINE=InnoDB;
461 INSERT INTO t1 VALUES(ST_PointFromText('POINT(10 10)'),ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
462 CREATE TABLE t2 ENGINE=InnoDB AS SELECT * FROM t1;
463 SELECT t.name, c.name, c.mtype, c.prtype, c.len
464 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
465 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
466 ON c.table_id = t.table_id
467 WHERE t.name LIKE 'test/t%' AND c.name IN ('p','g');
468 name name mtype prtype len
469 test/t1 p 14 1535 12
470 test/t1 g 14 1535 12
471 test/t2 p 14 1535 12
472 test/t2 g 14 1535 12
473 DROP TABLE t1,t2;
474 #
475 # Test when POINT is used in spatial index
476 #
477 SET @ls1 = ST_GeomFromText('LINESTRING(0 20, 10 0)');
478 SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 10 20)');
479 SET @ls3 = ST_GeomFromText('LINESTRING(20 -40, 21 -42)');
480 SET @ls4 = ST_GeomFromText('LINESTRING(20 -42, 21 -40)');
481 SET @poly1 = ST_GeomFromText('POLYGON((2 2, 2 10, 10 10, 10 2, 2 2))');
482 SET @poly2 = ST_GeomFromText('POLYGON((0 0, -5 0, -4 -1, -6 -15, -3 -15, 0 0))');
483 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))');
484 SET @poly4 = ST_GeomFromText('POLYGON((0 10, -10 10, -10 -10, 0 -10, 0 10))');
485 SET @p1 = ST_PointFromText('POINT(0 0)');
486 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)))');
487 CREATE TABLE gis_point (p1 POINT NOT NULL, p2 POINT NOT NULL, SPATIAL KEY k1 (p1), SPATIAL KEY k2 (p2)) ENGINE=InnoDB;
488 INSERT INTO gis_point VALUES
489 (ST_PointFromText('POINT(1 2)'), ST_PointFromText('POINT(-1 -3)')),
490 (ST_PointFromText('POINT(2 4)'), ST_PointFromText('POINT(-2 -6)')),
491 (ST_PointFromText('POINT(3 6)'), ST_PointFromText('POINT(-3 -9)')),
492 (ST_PointFromText('POINT(4 8)'), ST_PointFromText('POINT(-4 -12)')),
493 (ST_PointFromText('POINT(5 10)'), ST_PointFromText('POINT(-5 -15)')),
494 (ST_PointFromText('POINT(6 12)'), ST_PointFromText('POINT(-6 -18)')),
495 (ST_PointFromText('POINT(7 14)'), ST_PointFromText('POINT(-7 -21)')),
496 (ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)')),
497 (ST_PointFromText('POINT(9 18)'), ST_PointFromText('POINT(-4 2)')),
498 (ST_PointFromText('POINT(10 21)'), ST_PointFromText('POINT(-6 3)')),
499 (ST_PointFromText('POINT(20.5 41)'), ST_PointFromText('POINT(-8 4)')),
500 (ST_PointFromText('POINT(26.25 57)'), ST_PointFromText('POINT(1 2)')),
501 (ST_PointFromText('POINT(32.1234 64.2468)'), ST_PointFromText('POINT(-1 -1)'));
502 CREATE TABLE gis_point1 SELECT * FROM gis_point;
503 SELECT c.name, c.mtype, c.prtype, c.len
504 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
505 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
506 ON c.table_id = t.table_id
507 WHERE t.name='test/gis_point' AND c.name IN ('p1','p2');
508 name mtype prtype len
509 p1 14 1535 12
510 p2 14 1535 12
511 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point1;
512 ST_AsText(p1) ST_AsText(p2)
513 POINT(1 2) POINT(-1 -3)
514 POINT(2 4) POINT(-2 -6)
515 POINT(3 6) POINT(-3 -9)
516 POINT(4 8) POINT(-4 -12)
517 POINT(5 10) POINT(-5 -15)
518 POINT(6 12) POINT(-6 -18)
519 POINT(7 14) POINT(-7 -21)
520 POINT(8 16) POINT(0 0)
521 POINT(9 18) POINT(-4 2)
522 POINT(10 21) POINT(-6 3)
523 POINT(20.5 41) POINT(-8 4)
524 POINT(26.25 57) POINT(1 2)
525 POINT(32.1234 64.2468) POINT(-1 -1)
526 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Intersection(@ls1, @ls2) = p1;
527 ST_AsText(p1)
528 POINT(5 10)
529 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1);
530 ST_AsText(p1)
531 POINT(3 6)
532 POINT(4 8)
533 SELECT ST_AsText(p2) FROM gis_point1 WHERE ST_Contains(@poly2, p2);
534 ST_AsText(p2)
535 POINT(-1 -3)
536 POINT(-2 -6)
537 POINT(-3 -9)
538 POINT(-4 -12)
539 POINT(-5 -15)
540 POINT(0 0)
541 POINT(-1 -1)
542 SELECT ST_AsText(p2) FROM gis_point1 WHERE ST_Equals(p2, @p1);
543 ST_AsText(p2)
544 POINT(0 0)
545 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
546 ST_AsText(p1)
547 POINT(20.5 41)
548 POINT(32.1234 64.2468)
549 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Contains(@mpoly, p1);
550 ST_AsText(p1)
551 POINT(3 6)
552 POINT(4 8)
553 POINT(5 10)
554 POINT(6 12)
555 POINT(7 14)
556 POINT(8 16)
557 POINT(10 21)
558 POINT(20.5 41)
559 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Contains(@mpoly, p1) AND NOT MBRWithin(p1, @mpoly);
560 ST_AsText(p1)
561 POINT(3 6)
562 # Check functions that use MBR, with line type data
563 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @ls1);
564 ST_AsText(p1)
565 POINT(1 2)
566 POINT(2 4)
567 POINT(3 6)
568 POINT(4 8)
569 POINT(5 10)
570 POINT(6 12)
571 POINT(7 14)
572 POINT(8 16)
573 POINT(9 18)
574 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @ls1);
575 ST_AsText(p1)
576 POINT(1 2)
577 POINT(2 4)
578 POINT(3 6)
579 POINT(4 8)
580 POINT(5 10)
581 POINT(6 12)
582 POINT(7 14)
583 POINT(8 16)
584 POINT(9 18)
585 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @ls1);
586 ST_AsText(p1)
587 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @ls1);
588 ST_AsText(p1)
589 POINT(10 21)
590 POINT(20.5 41)
591 POINT(26.25 57)
592 POINT(32.1234 64.2468)
593 # No matching records
594 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @ls1);
595 ST_AsText(p1)
596 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @ls1);
597 ST_AsText(p1)
598 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @ls3);
599 ST_AsText(p1)
600 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @ls1);
601 ST_AsText(p1)
602 # Check functions that use MBR, with polygon type data
603 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @poly1);
604 ST_AsText(p1)
605 POINT(2 4)
606 POINT(3 6)
607 POINT(4 8)
608 POINT(5 10)
609 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1);
610 ST_AsText(p1)
611 POINT(3 6)
612 POINT(4 8)
613 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @poly1);
614 ST_AsText(p1)
615 POINT(2 4)
616 POINT(5 10)
617 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @poly1);
618 ST_AsText(p1)
619 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @poly1);
620 ST_AsText(p1)
621 POINT(1 2)
622 POINT(6 12)
623 POINT(7 14)
624 POINT(8 16)
625 POINT(9 18)
626 POINT(10 21)
627 POINT(20.5 41)
628 POINT(26.25 57)
629 POINT(32.1234 64.2468)
630 # No matching records
631 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @poly1);
632 ST_AsText(p1)
633 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @poly1);
634 ST_AsText(p1)
635 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @poly1);
636 ST_AsText(p1)
637 # Check functions that use MBR, with point type data
638 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1);
639 ST_AsText(p1)
640 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1);
641 ST_AsText(p1)
642 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1);
643 ST_AsText(p1)
644 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @p1);
645 ST_AsText(p1)
646 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1);
647 ST_AsText(p1)
648 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1);
649 ST_AsText(p1)
650 POINT(1 2)
651 POINT(2 4)
652 POINT(3 6)
653 POINT(4 8)
654 POINT(5 10)
655 POINT(6 12)
656 POINT(7 14)
657 POINT(8 16)
658 POINT(9 18)
659 POINT(10 21)
660 POINT(20.5 41)
661 POINT(26.25 57)
662 POINT(32.1234 64.2468)
663 # No matching records
664 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1);
665 ST_AsText(p1)
666 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @p1);
667 ST_AsText(p1)
668 # Check with MBR functions point type data
669 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1);
670 ST_AsText(p1)
671 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1);
672 ST_AsText(p1)
673 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1);
674 ST_AsText(p1)
675 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1);
676 ST_AsText(p1)
677 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1);
678 ST_AsText(p1)
679 POINT(1 2)
680 POINT(2 4)
681 POINT(3 6)
682 POINT(4 8)
683 POINT(5 10)
684 POINT(6 12)
685 POINT(7 14)
686 POINT(8 16)
687 POINT(9 18)
688 POINT(10 21)
689 POINT(20.5 41)
690 POINT(26.25 57)
691 POINT(32.1234 64.2468)
692 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @p1);
693 ST_AsText(p1)
694 # No matching records
695 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1);
696 ST_AsText(p1)
697 # Check with MBR functions point polygon data
698 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1);
699 ST_AsText(p1)
700 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1);
701 ST_AsText(p1)
702 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1);
703 ST_AsText(p1)
704 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1);
705 ST_AsText(p1)
706 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1);
707 ST_AsText(p1)
708 POINT(1 2)
709 POINT(2 4)
710 POINT(3 6)
711 POINT(4 8)
712 POINT(5 10)
713 POINT(6 12)
714 POINT(7 14)
715 POINT(8 16)
716 POINT(9 18)
717 POINT(10 21)
718 POINT(20.5 41)
719 POINT(26.25 57)
720 POINT(32.1234 64.2468)
721 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @p1);
722 ST_AsText(p1)
723 # No matching records
724 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1);
725 ST_AsText(p1)
726 # Check with MBR functions polygon type data
727 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @poly1);
728 ST_AsText(p1)
729 POINT(2 4)
730 POINT(3 6)
731 POINT(4 8)
732 POINT(5 10)
733 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1);
734 ST_AsText(p1)
735 POINT(3 6)
736 POINT(4 8)
737 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @poly1);
738 ST_AsText(p1)
739 POINT(2 4)
740 POINT(5 10)
741 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @poly1);
742 ST_AsText(p1)
743 POINT(1 2)
744 POINT(6 12)
745 POINT(7 14)
746 POINT(8 16)
747 POINT(9 18)
748 POINT(10 21)
749 POINT(20.5 41)
750 POINT(26.25 57)
751 POINT(32.1234 64.2468)
752 # No matching records
753 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @poly1);
754 ST_AsText(p1)
755 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @poly1);
756 ST_AsText(p1)
757 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @poly1);
758 ST_AsText(p1)
759 # Check with MBR functions line type data
760 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @ls1);
761 ST_AsText(p1)
762 POINT(1 2)
763 POINT(2 4)
764 POINT(3 6)
765 POINT(4 8)
766 POINT(5 10)
767 POINT(6 12)
768 POINT(7 14)
769 POINT(8 16)
770 POINT(9 18)
771 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @ls1);
772 ST_AsText(p1)
773 POINT(1 2)
774 POINT(2 4)
775 POINT(3 6)
776 POINT(4 8)
777 POINT(5 10)
778 POINT(6 12)
779 POINT(7 14)
780 POINT(8 16)
781 POINT(9 18)
782 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @ls1);
783 ST_AsText(p1)
784 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @ls1);
785 ST_AsText(p1)
786 POINT(10 21)
787 POINT(20.5 41)
788 POINT(26.25 57)
789 POINT(32.1234 64.2468)
790 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @ls1);
791 ST_AsText(p1)
792 # No matching records
793 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @ls1);
794 ST_AsText(p1)
795 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @ls1);
796 ST_AsText(p1)
797 DROP TABLE gis_point1;
798 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point;
799 ST_AsText(p1) ST_AsText(p2)
800 POINT(1 2) POINT(-1 -3)
801 POINT(2 4) POINT(-2 -6)
802 POINT(3 6) POINT(-3 -9)
803 POINT(4 8) POINT(-4 -12)
804 POINT(5 10) POINT(-5 -15)
805 POINT(6 12) POINT(-6 -18)
806 POINT(7 14) POINT(-7 -21)
807 POINT(8 16) POINT(0 0)
808 POINT(9 18) POINT(-4 2)
809 POINT(10 21) POINT(-6 3)
810 POINT(20.5 41) POINT(-8 4)
811 POINT(26.25 57) POINT(1 2)
812 POINT(32.1234 64.2468) POINT(-1 -1)
813 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Intersection(@ls1, @ls2) = p1;
814 ST_AsText(p1)
815 POINT(5 10)
816 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
817 ST_AsText(p1)
818 POINT(3 6)
819 POINT(4 8)
820 SELECT ST_AsText(p2) FROM gis_point WHERE ST_Contains(@poly2, p2);
821 ST_AsText(p2)
822 POINT(-1 -3)
823 POINT(-2 -6)
824 POINT(-3 -9)
825 POINT(-4 -12)
826 POINT(-5 -15)
827 POINT(0 0)
828 POINT(-1 -1)
829 SELECT ST_AsText(p2) FROM gis_point WHERE ST_Equals(p2, @p1);
830 ST_AsText(p2)
831 POINT(0 0)
832 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
833 ST_AsText(p1)
834 POINT(20.5 41)
835 POINT(32.1234 64.2468)
836 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Contains(@mpoly, p1);
837 ST_AsText(p1)
838 POINT(3 6)
839 POINT(4 8)
840 POINT(5 10)
841 POINT(6 12)
842 POINT(7 14)
843 POINT(8 16)
844 POINT(10 21)
845 POINT(20.5 41)
846 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Contains(@mpoly, p1) AND NOT MBRWithin(p1, @mpoly);
847 ST_AsText(p1)
848 POINT(3 6)
849 # Check functions that use MBR, with line type data
850 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
851 ST_AsText(p1)
852 POINT(1 2)
853 POINT(2 4)
854 POINT(3 6)
855 POINT(4 8)
856 POINT(5 10)
857 POINT(6 12)
858 POINT(7 14)
859 POINT(8 16)
860 POINT(9 18)
861 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
862 ST_AsText(p1)
863 POINT(1 2)
864 POINT(2 4)
865 POINT(3 6)
866 POINT(4 8)
867 POINT(5 10)
868 POINT(6 12)
869 POINT(7 14)
870 POINT(8 16)
871 POINT(9 18)
872 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
873 ST_AsText(p1)
874 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
875 ST_AsText(p1)
876 POINT(32.1234 64.2468)
877 POINT(26.25 57)
878 POINT(20.5 41)
879 POINT(10 21)
880 # No matching records
881 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @ls1);
882 ST_AsText(p1)
883 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
884 ST_AsText(p1)
885 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls3);
886 ST_AsText(p1)
887 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @ls1);
888 ST_AsText(p1)
889 # Check functions that use MBR, with polygon type data
890 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
891 ST_AsText(p1)
892 POINT(2 4)
893 POINT(3 6)
894 POINT(4 8)
895 POINT(5 10)
896 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
897 ST_AsText(p1)
898 POINT(3 6)
899 POINT(4 8)
900 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
901 ST_AsText(p1)
902 POINT(2 4)
903 POINT(5 10)
904 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @poly1);
905 ST_AsText(p1)
906 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
907 ST_AsText(p1)
908 POINT(32.1234 64.2468)
909 POINT(26.25 57)
910 POINT(20.5 41)
911 POINT(10 21)
912 POINT(9 18)
913 POINT(8 16)
914 POINT(7 14)
915 POINT(6 12)
916 POINT(1 2)
917 # No matching records
918 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @poly1);
919 ST_AsText(p1)
920 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
921 ST_AsText(p1)
922 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
923 ST_AsText(p1)
924 # Check functions that use MBR, with point type data
925 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
926 ST_AsText(p1)
927 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
928 ST_AsText(p1)
929 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
930 ST_AsText(p1)
931 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @p1);
932 ST_AsText(p1)
933 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
934 ST_AsText(p1)
935 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
936 ST_AsText(p1)
937 POINT(32.1234 64.2468)
938 POINT(26.25 57)
939 POINT(20.5 41)
940 POINT(10 21)
941 POINT(9 18)
942 POINT(8 16)
943 POINT(7 14)
944 POINT(6 12)
945 POINT(5 10)
946 POINT(4 8)
947 POINT(3 6)
948 POINT(2 4)
949 POINT(1 2)
950 # No matching records
951 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
952 ST_AsText(p1)
953 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @p1);
954 ST_AsText(p1)
955 # Check with MBR functions point type data
956 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
957 ST_AsText(p1)
958 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
959 ST_AsText(p1)
960 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
961 ST_AsText(p1)
962 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
963 ST_AsText(p1)
964 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
965 ST_AsText(p1)
966 POINT(32.1234 64.2468)
967 POINT(26.25 57)
968 POINT(20.5 41)
969 POINT(10 21)
970 POINT(9 18)
971 POINT(8 16)
972 POINT(7 14)
973 POINT(6 12)
974 POINT(5 10)
975 POINT(4 8)
976 POINT(3 6)
977 POINT(2 4)
978 POINT(1 2)
979 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
980 ST_AsText(p1)
981 # No matching records
982 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
983 ST_AsText(p1)
984 # Check with MBR functions point polygon data
985 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
986 ST_AsText(p1)
987 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
988 ST_AsText(p1)
989 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
990 ST_AsText(p1)
991 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
992 ST_AsText(p1)
993 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
994 ST_AsText(p1)
995 POINT(32.1234 64.2468)
996 POINT(26.25 57)
997 POINT(20.5 41)
998 POINT(10 21)
999 POINT(9 18)
1000 POINT(8 16)
1001 POINT(7 14)
1002 POINT(6 12)
1003 POINT(5 10)
1004 POINT(4 8)
1005 POINT(3 6)
1006 POINT(2 4)
1007 POINT(1 2)
1008 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
1009 ST_AsText(p1)
1010 # No matching records
1011 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
1012 ST_AsText(p1)
1013 # Check with MBR functions polygon type data
1014 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
1015 ST_AsText(p1)
1016 POINT(2 4)
1017 POINT(3 6)
1018 POINT(4 8)
1019 POINT(5 10)
1020 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
1021 ST_AsText(p1)
1022 POINT(3 6)
1023 POINT(4 8)
1024 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
1025 ST_AsText(p1)
1026 POINT(2 4)
1027 POINT(5 10)
1028 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
1029 ST_AsText(p1)
1030 POINT(32.1234 64.2468)
1031 POINT(26.25 57)
1032 POINT(20.5 41)
1033 POINT(10 21)
1034 POINT(9 18)
1035 POINT(8 16)
1036 POINT(7 14)
1037 POINT(6 12)
1038 POINT(1 2)
1039 # No matching records
1040 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
1041 ST_AsText(p1)
1042 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
1043 ST_AsText(p1)
1044 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @poly1);
1045 ST_AsText(p1)
1046 # Check with MBR functions line type data
1047 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
1048 ST_AsText(p1)
1049 POINT(1 2)
1050 POINT(2 4)
1051 POINT(3 6)
1052 POINT(4 8)
1053 POINT(5 10)
1054 POINT(6 12)
1055 POINT(7 14)
1056 POINT(8 16)
1057 POINT(9 18)
1058 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
1059 ST_AsText(p1)
1060 POINT(1 2)
1061 POINT(2 4)
1062 POINT(3 6)
1063 POINT(4 8)
1064 POINT(5 10)
1065 POINT(6 12)
1066 POINT(7 14)
1067 POINT(8 16)
1068 POINT(9 18)
1069 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
1070 ST_AsText(p1)
1071 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
1072 ST_AsText(p1)
1073 POINT(32.1234 64.2468)
1074 POINT(26.25 57)
1075 POINT(20.5 41)
1076 POINT(10 21)
1077 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @ls1);
1078 ST_AsText(p1)
1079 # No matching records
1080 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls1);
1081 ST_AsText(p1)
1082 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
1083 ST_AsText(p1)
1084 # Modify the storage engine to Myisam, Check the spatial functions
1085 ALTER TABLE gis_point ENGINE Myisam;
1086 # Check functions that use MBR, with line type data
1087 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
1088 ST_AsText(p1)
1089 POINT(1 2)
1090 POINT(2 4)
1091 POINT(3 6)
1092 POINT(4 8)
1093 POINT(5 10)
1094 POINT(6 12)
1095 POINT(7 14)
1096 POINT(8 16)
1097 POINT(9 18)
1098 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
1099 ST_AsText(p1)
1100 POINT(1 2)
1101 POINT(2 4)
1102 POINT(3 6)
1103 POINT(4 8)
1104 POINT(5 10)
1105 POINT(6 12)
1106 POINT(7 14)
1107 POINT(8 16)
1108 POINT(9 18)
1109 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
1110 ST_AsText(p1)
1111 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
1112 ST_AsText(p1)
1113 POINT(10 21)
1114 POINT(20.5 41)
1115 POINT(26.25 57)
1116 POINT(32.1234 64.2468)
1117 # No matching records
1118 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @ls1);
1119 ST_AsText(p1)
1120 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
1121 ST_AsText(p1)
1122 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls3);
1123 ST_AsText(p1)
1124 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @ls1);
1125 ST_AsText(p1)
1126 # Check functions that use MBR, with polygon type data
1127 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
1128 ST_AsText(p1)
1129 POINT(2 4)
1130 POINT(3 6)
1131 POINT(4 8)
1132 POINT(5 10)
1133 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
1134 ST_AsText(p1)
1135 POINT(3 6)
1136 POINT(4 8)
1137 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
1138 ST_AsText(p1)
1139 POINT(2 4)
1140 POINT(5 10)
1141 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @poly1);
1142 ST_AsText(p1)
1143 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
1144 ST_AsText(p1)
1145 POINT(1 2)
1146 POINT(6 12)
1147 POINT(7 14)
1148 POINT(8 16)
1149 POINT(9 18)
1150 POINT(10 21)
1151 POINT(20.5 41)
1152 POINT(26.25 57)
1153 POINT(32.1234 64.2468)
1154 # No matching records
1155 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @poly1);
1156 ST_AsText(p1)
1157 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
1158 ST_AsText(p1)
1159 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
1160 ST_AsText(p1)
1161 # Check functions that use MBR, with point type data
1162 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
1163 ST_AsText(p1)
1164 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
1165 ST_AsText(p1)
1166 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
1167 ST_AsText(p1)
1168 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @p1);
1169 ST_AsText(p1)
1170 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
1171 ST_AsText(p1)
1172 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
1173 ST_AsText(p1)
1174 POINT(1 2)
1175 POINT(2 4)
1176 POINT(3 6)
1177 POINT(4 8)
1178 POINT(5 10)
1179 POINT(6 12)
1180 POINT(7 14)
1181 POINT(8 16)
1182 POINT(9 18)
1183 POINT(10 21)
1184 POINT(20.5 41)
1185 POINT(26.25 57)
1186 POINT(32.1234 64.2468)
1187 # No matching records
1188 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
1189 ST_AsText(p1)
1190 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @p1);
1191 ST_AsText(p1)
1192 # Check with MBR functions point type data
1193 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
1194 ST_AsText(p1)
1195 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
1196 ST_AsText(p1)
1197 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
1198 ST_AsText(p1)
1199 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
1200 ST_AsText(p1)
1201 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
1202 ST_AsText(p1)
1203 POINT(1 2)
1204 POINT(2 4)
1205 POINT(3 6)
1206 POINT(4 8)
1207 POINT(5 10)
1208 POINT(6 12)
1209 POINT(7 14)
1210 POINT(8 16)
1211 POINT(9 18)
1212 POINT(10 21)
1213 POINT(20.5 41)
1214 POINT(26.25 57)
1215 POINT(32.1234 64.2468)
1216 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
1217 ST_AsText(p1)
1218 # No matching records
1219 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
1220 ST_AsText(p1)
1221 # Check with MBR functions point polygon data
1222 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
1223 ST_AsText(p1)
1224 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
1225 ST_AsText(p1)
1226 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
1227 ST_AsText(p1)
1228 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
1229 ST_AsText(p1)
1230 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
1231 ST_AsText(p1)
1232 POINT(1 2)
1233 POINT(2 4)
1234 POINT(3 6)
1235 POINT(4 8)
1236 POINT(5 10)
1237 POINT(6 12)
1238 POINT(7 14)
1239 POINT(8 16)
1240 POINT(9 18)
1241 POINT(10 21)
1242 POINT(20.5 41)
1243 POINT(26.25 57)
1244 POINT(32.1234 64.2468)
1245 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
1246 ST_AsText(p1)
1247 # No matching records
1248 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
1249 ST_AsText(p1)
1250 # Check with MBR functions polygon type data
1251 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
1252 ST_AsText(p1)
1253 POINT(2 4)
1254 POINT(3 6)
1255 POINT(4 8)
1256 POINT(5 10)
1257 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
1258 ST_AsText(p1)
1259 POINT(3 6)
1260 POINT(4 8)
1261 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
1262 ST_AsText(p1)
1263 POINT(2 4)
1264 POINT(5 10)
1265 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
1266 ST_AsText(p1)
1267 POINT(1 2)
1268 POINT(6 12)
1269 POINT(7 14)
1270 POINT(8 16)
1271 POINT(9 18)
1272 POINT(10 21)
1273 POINT(20.5 41)
1274 POINT(26.25 57)
1275 POINT(32.1234 64.2468)
1276 # No matching records
1277 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
1278 ST_AsText(p1)
1279 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
1280 ST_AsText(p1)
1281 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @poly1);
1282 ST_AsText(p1)
1283 # Check with MBR functions line type data
1284 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
1285 ST_AsText(p1)
1286 POINT(1 2)
1287 POINT(2 4)
1288 POINT(3 6)
1289 POINT(4 8)
1290 POINT(5 10)
1291 POINT(6 12)
1292 POINT(7 14)
1293 POINT(8 16)
1294 POINT(9 18)
1295 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
1296 ST_AsText(p1)
1297 POINT(1 2)
1298 POINT(2 4)
1299 POINT(3 6)
1300 POINT(4 8)
1301 POINT(5 10)
1302 POINT(6 12)
1303 POINT(7 14)
1304 POINT(8 16)
1305 POINT(9 18)
1306 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
1307 ST_AsText(p1)
1308 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
1309 ST_AsText(p1)
1310 POINT(10 21)
1311 POINT(20.5 41)
1312 POINT(26.25 57)
1313 POINT(32.1234 64.2468)
1314 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @ls1);
1315 ST_AsText(p1)
1316 # No matching records
1317 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls1);
1318 ST_AsText(p1)
1319 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
1320 ST_AsText(p1)
1321 # Modify the storage engine to InnoDB again, do following testing
1322 ALTER TABLE gis_point ENGINE InnoDB;
1323 CHECK TABLE gis_point;
1324 Table Op Msg_type Msg_text
1325 test.gis_point check status OK
1326 The ORDER BY for spatial index will use filesort
1327 EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
1328 id select_type table type possible_keys key key_len ref rows Extra
1329 1 SIMPLE gis_point ALL NULL NULL NULL NULL # Using filesort
1330 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
1331 ST_AsText(p1) ST_AsText(p2)
1332 POINT(2 4) POINT(-2 -6)
1333 POINT(3 6) POINT(-3 -9)
1334 POINT(4 8) POINT(-4 -12)
1335 POINT(5 10) POINT(-5 -15)
1336 POINT(6 12) POINT(-6 -18)
1337 POINT(7 14) POINT(-7 -21)
1338 POINT(8 16) POINT(0 0)
1339 POINT(9 18) POINT(-4 2)
1340 POINT(10 21) POINT(-6 3)
1341 POINT(1 2) POINT(-1 -3)
1342 POINT(26.25 57) POINT(1 2)
1343 POINT(20.5 41) POINT(-8 4)
1344 POINT(32.1234 64.2468) POINT(-1 -1)
1345 Try to do IDU on the table and verify the result
1346 DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
1347 INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
1348 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
1349 Expect (32.1234 64.2468)
1350 POINT(32.1234 64.2468)
1351 UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 41)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
1352 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);
1353 Expect (32.1234 64.2468) AND (20.5 41)
1354 POINT(32.1234 64.2468)
1355 POINT(20.5 41)
1356 CHECK TABLE gis_point;
1357 Table Op Msg_type Msg_text
1358 test.gis_point check status OK
1359 Use a trx to test the IDU on the table and verify the result
1360 START TRANSACTION;
1361 DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
1362 INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
1363 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
1364 Expect (32.1234 64.2468)
1365 POINT(32.1234 64.2468)
1366 UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 49)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
1367 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);
1368 Expect (32.1234 64.2468) AND (20.5 49)
1369 POINT(32.1234 64.2468)
1370 POINT(20.5 49)
1371 ROLLBACK;
1372 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);
1373 Expect (32.1234 64.2468) AND (20.5 41)
1374 POINT(32.1234 64.2468)
1375 POINT(20.5 41)
1376 CHECK TABLE gis_point;
1377 Table Op Msg_type Msg_text
1378 test.gis_point check status OK
1379 DROP TABLE gis_point;
1380 #
1381 # Test inserting/updating different type data into POINT field
1382 #
1383 CREATE TABLE gis_point (i INT, p POINT) ENGINE=InnoDB;
1384 CREATE TABLE geom (i INT, g GEOMETRY NOT NULL, SPATIAL KEY(g)) ENGINE=InnoDB;
1385 INSERT INTO gis_point VALUES(0, ST_PointFromText('POINT(1 1)'));
1386 INSERT INTO gis_point VALUES(1, ST_PointFromText('POINT(2 2)'));
1387 INSERT INTO gis_point VALUES(2, NULL);
1388 ALTER TABLE gis_point ADD COLUMN j INT, ALGORITHM = COPY;
1389 SELECT t.name, c.name, c.mtype, c.prtype, c.len
1390 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
1391 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
1392 ON c.table_id = t.table_id
1393 WHERE t.name='test/gis_point' AND c.name='p';
1394 name name mtype prtype len
1395 test/gis_point p 14 1279 12
1396 SELECT i, ST_AsText(p) FROM gis_point;
1397 i ST_AsText(p)
1398 0 POINT(1 1)
1399 1 POINT(2 2)
1400 2 NULL
1401 UPDATE gis_point SET p = NULL WHERE p = ST_PointFromText('POINT(1 1)');
1402 UPDATE gis_point SET p = ST_PointFromText('POINT(1 2)') WHERE p = ST_PointFromText('POINT(2 2)');
1403 UPDATE gis_point SET p = ST_PointFromText('POINT(1 1)') WHERE p IS NULL;
1404 SELECT i, ST_AsText(p) FROM gis_point;
1405 i ST_AsText(p)
1406 0 POINT(1 1)
1407 1 POINT(1 2)
1408 2 POINT(1 1)
1409 INSERT INTO geom VALUES(0, ST_PointFromText('POINT(0 0)'));
1410 INSERT INTO geom VALUES(1, ST_PointFromText('POINT(10 10)'));
1411 INSERT INTO geom VALUES(2, ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'));
1412 SELECT ST_AsText(g) FROM geom;
1413 ST_AsText(g)
1414 POINT(0 0)
1415 POINT(10 10)
1416 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
1417 SELECT ST_AsText(p) FROM gis_point;
1418 ST_AsText(p)
1419 POINT(1 1)
1420 POINT(1 2)
1421 POINT(1 1)
1422 DELETE FROM geom WHERE g = ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
1423 From GEOMETRY to POINT, now ALL the data are POINT
1424 ALTER TABLE geom MODIFY g POINT NOT NULL;
1425 SHOW CREATE TABLE geom;
1426 Table Create Table
1427 geom CREATE TABLE `geom` (
1428 `i` int(11) DEFAULT NULL,
1429 `g` point NOT NULL,
1430 SPATIAL KEY `g` (`g`)
1431 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1432 SELECT ST_AsText(g) FROM geom;
1433 ST_AsText(g)
1434 POINT(0 0)
1435 POINT(10 10)
1436 From POINT to GEOMETRY, all data are POINT
1437 ALTER TABLE geom MODIFY g GEOMETRY NOT NULL;
1438 SHOW CREATE TABLE geom;
1439 Table Create Table
1440 geom CREATE TABLE `geom` (
1441 `i` int(11) DEFAULT NULL,
1442 `g` geometry NOT NULL,
1443 SPATIAL KEY `g` (`g`)
1444 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1445 TRUNCATE TABLE geom;
1446 From GEOMETRY to POINT, the table is empty
1447 ALTER TABLE geom MODIFY g POINT NOT NULL;
1448 SHOW CREATE TABLE geom;
1449 Table Create Table
1450 geom CREATE TABLE `geom` (
1451 `i` int(11) DEFAULT NULL,
1452 `g` point NOT NULL,
1453 SPATIAL KEY `g` (`g`)
1454 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1455 SELECT ST_AsText(g) FROM geom;
1456 ST_AsText(g)
1457 CHECK TABLE gis_point;
1458 Table Op Msg_type Msg_text
1459 test.gis_point check status OK
1460 CHECK TABLE geom;
1461 Table Op Msg_type Msg_text
1462 test.geom check status OK
1463 DROP TABLE gis_point, geom;
1464 #
1465 # Test when a geom field, such as POINT, is also a primary key
1466 #
1467 CREATE TABLE t1 (
1468 a INT NOT NULL,
1469 p POINT NOT NULL,
1470 l LINESTRING NOT NULL,
1471 g GEOMETRY NOT NULL,
1472 PRIMARY KEY(p),
1473 SPATIAL KEY `idx2` (p),
1474 SPATIAL KEY `idx3` (l),
1475 SPATIAL KEY `idx4` (g)
1476 ) ENGINE=InnoDB;
1477 INSERT INTO t1 VALUES(
1478 1, ST_GeomFromText('POINT(10 10)'),
1479 ST_GeomFromText('LINESTRING(1 1, 5 5, 10 10)'),
1480 ST_GeomFromText('POLYGON((30 30, 40 40, 50 50, 30 50, 30 40, 30 30))'));
1481 INSERT INTO t1 VALUES(
1482 2, ST_GeomFromText('POINT(20 20)'),
1483 ST_GeomFromText('LINESTRING(2 3, 7 8, 9 10, 15 16)'),
1484 ST_GeomFromText('POLYGON((10 30, 30 40, 40 50, 40 30, 30 20, 10 30))'));
1485 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
1486 a ST_AsText(p) ST_AsText(l) ST_AsText(g)
1487 1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
1488 2 POINT(20 20) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30))
1489 EXPLAIN UPDATE t1 SET p = ST_GeomFromText('POINT(30 30)') WHERE p = ST_GeomFromText('POINT(20 20)');
1490 id select_type table type possible_keys key key_len ref rows Extra
1491 1 SIMPLE t1 range PRIMARY PRIMARY 27 NULL # Using where
1492 UPDATE t1 SET p = ST_GeomFromText('POINT(30 30)') WHERE p = ST_GeomFromText('POINT(20 20)');
1493 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
1494 a ST_AsText(p) ST_AsText(l) ST_AsText(g)
1495 1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
1496 2 POINT(30 30) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30))
1497 ALTER TABLE t1 DROP PRIMARY KEY;
1498 ALTER TABLE t1 ADD PRIMARY KEY(a);
1499 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
1500 a ST_AsText(p) ST_AsText(l) ST_AsText(g)
1501 1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
1502 2 POINT(30 30) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30))
1503 ALTER TABLE t1 DROP PRIMARY KEY;
1504 ALTER TABLE t1 ADD PRIMARY KEY(p);
1505 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
1506 a ST_AsText(p) ST_AsText(l) ST_AsText(g)
1507 1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
1508 2 POINT(30 30) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30))
1509 SHOW CREATE TABLE t1;
1510 Table Create Table
1511 t1 CREATE TABLE `t1` (
1512 `a` int(11) NOT NULL,
1513 `p` point NOT NULL,
1514 `l` linestring NOT NULL,
1515 `g` geometry NOT NULL,
1516 PRIMARY KEY (`p`(25)),
1517 SPATIAL KEY `idx2` (`p`),
1518 SPATIAL KEY `idx3` (`l`),
1519 SPATIAL KEY `idx4` (`g`)
1520 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1521 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
1522 a ST_AsText(p) ST_AsText(l) ST_AsText(g)
1523 1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
1524 2 POINT(30 30) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30))
1525 ALTER TABLE t1 DROP PRIMARY KEY;
1526 ALTER TABLE t1 ADD PRIMARY KEY(p);
1527 EXPLAIN SELECT a, ST_AsText(p) FROM t1 WHERE a = 2 AND p = ST_GeomFromText('POINT(30 30)');
1528 id select_type table type possible_keys key key_len ref rows Extra
1529 1 SIMPLE t1 const PRIMARY,idx2 PRIMARY 27 const #
1530 SELECT a, ST_AsText(p) FROM t1 WHERE a = 2 AND p = ST_GeomFromText('POINT(30 30)');
1531 a ST_AsText(p)
1532 2 POINT(30 30)
1533 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
1534 a ST_AsText(p) ST_AsText(l) ST_AsText(g)
1535 1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
1536 2 POINT(30 30) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30))
1537 CHECK TABLE t1;
1538 Table Op Msg_type Msg_text
1539 test.t1 check status OK
1540 DROP TABLE t1;
1541 #
1542 # Test for foreign keys.
1543 #
1544 CREATE TABLE parent(p POINT, PRIMARY KEY(p)) ENGINE=InnoDB;
1545 CREATE TABLE child(p POINT NOT NULL) ENGINE=InnoDB;
1546 ALTER TABLE parent ADD SPATIAL INDEX idx1(p);
1547 ALTER TABLE child ADD SPATIAL INDEX idx2(p);
1548 SHOW CREATE TABLE parent;
1549 Table Create Table
1550 parent CREATE TABLE `parent` (
1551 `p` point NOT NULL,
1552 PRIMARY KEY (`p`(25)),
1553 SPATIAL KEY `idx1` (`p`)
1554 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1555 SHOW CREATE TABLE child;
1556 Table Create Table
1557 child CREATE TABLE `child` (
1558 `p` point NOT NULL,
1559 SPATIAL KEY `idx2` (`p`)
1560 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1561 ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
1562 ERROR HY000: Can't create table `test`.`child` (errno: 150 "Foreign key constraint is incorrectly formed")
1563 show warnings;
1564 Level Code Message
1565 Warning 150 Alter table `test`.`child` with foreign key (p) constraint failed. There is only prefix index in the referenced table where the referenced columns appear as the first columns.
1566 Error 1005 Can't create table `test`.`child` (errno: 150 "Foreign key constraint is incorrectly formed")
1567 Warning 1215 Cannot add foreign key constraint for `child`
1568 ALTER TABLE parent DROP INDEX idx1;
1569 ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
1570 Got one of the listed errors
1571 show warnings;
1572 Level Code Message
1573 Warning 150 Alter table `test`.`child` with foreign key (p) constraint failed. There is only prefix index in the referenced table where the referenced columns appear as the first columns.
1574 Error 1005 Can't create table `test`.`child` (errno: 150 "Foreign key constraint is incorrectly formed")
1575 Warning 1215 Cannot add foreign key constraint for `child`
1576 ALTER TABLE child DROP INDEX idx2;
1577 ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
1578 Got one of the listed errors
1579 show warnings;
1580 Level Code Message
1581 Warning 150 Alter table `test`.`child` with foreign key (p) constraint failed. There is only prefix index in the referenced table where the referenced columns appear as the first columns.
1582 Error 1005 Can't create table `test`.`child` (errno: 150 "Foreign key constraint is incorrectly formed")
1583 Warning 1215 Cannot add foreign key constraint for `child`
1584 DROP TABLE child, parent;
1585 #
1586 # Bug#28763: Selecting geometry fields in UNION caused server crash.
1587 #
1588 CREATE TABLE t1(f1 GEOMETRY, f2 POINT, f3 GEOMETRY) ENGINE=InnoDB;
1589 SELECT f1 FROM t1 UNION SELECT f1 FROM t1;
1590 f1
1591 INSERT INTO t1 (f2,f3) VALUES (ST_GeomFromText('POINT(1 1)'),
1592 ST_GeomFromText('POINT(2 2)'));
1593 SELECT ST_AsText(f2),ST_AsText(f3) FROM t1;
1594 ST_AsText(f2) ST_AsText(f3)
1595 POINT(1 1) POINT(2 2)
1596 SELECT ST_AsText(a) FROM (SELECT f2 AS a FROM t1 UNION SELECT f3 FROM t1) t;
1597 ST_AsText(a)
1598 POINT(1 1)
1599 POINT(2 2)
1600 CREATE TABLE t2 AS SELECT f2 AS a FROM t1 UNION SELECT f3 FROM t1;
1601 DESC t2;
1602 Field Type Null Key Default Extra
1603 a geometry YES NULL
1604 SELECT ST_AsText(a) FROM t2;
1605 ST_AsText(a)
1606 POINT(1 1)
1607 POINT(2 2)
1608 DROP TABLE t1, t2;