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