Debian Codegraph @

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

raw
1 #********************************************************
2 # wl6455: GIS Datatypes covers the following tests.
3 # DDL: ALTER PRIMARY, DROP KEY,INDEX,COLUMN TYPE etc
4 # INSERT/SELECT operations with WKT & WKB functions
5 # SELECT/DELETE/UPDATE with =,>,< predicate conditions
6 # Datatypes with Procedure (IN/INOUT/OUT,Cursors,Trigger)
7 # Datatypes with Aggregate functions
8 # File Formts Compressed & Dynamic
9 # All Geometric functions.
10 #********************************************************
11 --source include/have_geometry.inc
12 --source include/have_innodb.inc
13 --source include/have_partition.inc
14
15 SET default_storage_engine=InnoDB;
16 SET innodb_strict_mode=OFF;
17 SET @save_innodb_stats_persistent=@@GLOBAL.innodb_stats_persistent;
18 SET GLOBAL innodb_stats_persistent=0;
19
20 #
21 # Spatial objects
22 #
23
24 CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT) ENGINE=InnoDB;
25 CREATE TABLE gis_line (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING) ENGINE=InnoDB;
26 CREATE TABLE gis_polygon (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON) ENGINE=InnoDB;
27 CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT) ENGINE=InnoDB;
28 CREATE TABLE gis_multi_line (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING) ENGINE=InnoDB;
29 CREATE TABLE gis_multi_polygon (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON) ENGINE=InnoDB;
30 CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION) ENGINE=InnoDB;
31 CREATE TABLE gis_geometry (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY) ENGINE=InnoDB;
32
33 SHOW FIELDS FROM gis_point;
34 SHOW FIELDS FROM gis_line;
35 SHOW FIELDS FROM gis_polygon;
36 SHOW FIELDS FROM gis_multi_point;
37 SHOW FIELDS FROM gis_multi_line;
38 SHOW FIELDS FROM gis_multi_polygon;
39 SHOW FIELDS FROM gis_geometrycollection;
40 SHOW FIELDS FROM gis_geometry;
41
42 --echo #INSERT using all WKT & WKB functions
43 INSERT INTO gis_point VALUES
44 (101, ST_PointFromText('POINT(10 10)')),
45 (102, ST_PointFromText('POINT(20 10)')),
46 (103, ST_PointFromText('POINT(20 20)')),
47 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
48
49
50 INSERT INTO gis_line VALUES
51 (105, ST_LineFromText('LINESTRING(0 0,0 10,10 0)')),
52 (106, ST_LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
53 (107, ST_LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(40, 10)))));
54
55
56 INSERT INTO gis_polygon VALUES
57 (108, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
58 (109, ST_PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
59 (110, ST_PolyFromWKB(ST_AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))));
60
61
62 INSERT INTO gis_multi_point VALUES
63 (111, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
64 (112, ST_MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
65 (113, ST_MPointFromWKB(ST_AsWKB(MultiPoint(Point(3, 6), Point(4, 10)))));
66
67
68 INSERT INTO gis_multi_line VALUES
69 (114, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
70 (115, ST_MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
71 (116, ST_MLineFromWKB(ST_AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7))))));
72
73
74 INSERT INTO gis_multi_polygon VALUES
75 (117, ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),
76 ((59 18,67 18,67 13,59 13,59 18)))')),
77 (118, ST_MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),
78 (52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')),
79 (119, ST_MPolyFromWKB(ST_AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))));
80
81
82 INSERT INTO gis_geometrycollection VALUES
83 (120, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
84 (121, ST_GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(10 10), LINESTRING(10 10,20 20))')),
85 (122, ST_GeometryFromWKB(ST_AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))))));
86
87 --echo #check INSERT using SELECT query
88 INSERT into gis_geometry SELECT * FROM gis_point;
89 INSERT into gis_geometry SELECT * FROM gis_line;
90 INSERT into gis_geometry SELECT * FROM gis_polygon;
91 INSERT into gis_geometry SELECT * FROM gis_multi_point;
92 INSERT into gis_geometry SELECT * FROM gis_multi_line;
93 INSERT into gis_geometry SELECT * FROM gis_multi_polygon;
94 INSERT into gis_geometry SELECT * FROM gis_geometrycollection;
95
96 --echo #check format coversion functions & spatial values
97 SELECT fid, ST_AsText(g) FROM gis_point;
98 SELECT fid, ST_AsText(g) FROM gis_line;
99 SELECT fid, ST_AsText(g) FROM gis_polygon;
100 SELECT fid, ST_AsText(g) FROM gis_multi_point;
101 SELECT fid, ST_AsText(g) FROM gis_multi_line;
102 SELECT fid, ST_AsText(g) FROM gis_multi_polygon;
103 SELECT fid, ST_AsText(g) FROM gis_geometrycollection;
104 SELECT fid, ST_AsText(g) FROM gis_geometry;
105
106 --echo #2.1 check DDL functionality on GIS datatypes
107 CREATE TABLE tab(c1 POINT,c2 LINESTRING,c3 POLYGON,C4 MULTIPOINT,c5 MULTILINESTRING ,
108 c6 MULTIPOLYGON,c7 GEOMETRYCOLLECTION ,c8 GEOMETRY) ENGINE=InnoDB;
109
110 --echo #check information schema for all the columns refer to 14, except POINT which is 15
111 SELECT sc.name, sc.pos, sc.mtype
112 FROM information_schema.innodb_sys_columns sc
113 INNER JOIN information_schema.innodb_sys_tables st
114 ON sc.TABLE_ID=st.TABLE_ID
115 WHERE st.NAME='test/tab'
116 ORDER BY sc.name;
117
118 --echo #check Perform convesrion before INSERT using WKT functions
119 SET @c1=ST_PointFromText('POINT(10 10)');
120 SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
121 SET @c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
122 SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
123 SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
124 SET @c6=ST_MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))');
125 SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
126 SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
127
128 --echo #Insert the spatial values
129 INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
130
131 --echo #check index on GIS datatypes
132 CREATE INDEX idx1 on tab(c2(5) DESC) USING BTREE;
133 CREATE INDEX idx3 on tab(c3(5) ASC) USING BTREE;
134 CREATE UNIQUE INDEX idx2 on tab(c8(5) ASC) ;
135
136 --echo #check equality predicate on the index columns
137 --replace_column 9 #
138 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
139 WHERE c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
140
141 --echo #check index with WKB function
142 --replace_column 9 #
143 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
144 FROM tab WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
145
146 --echo #check index with WKT function
147 --replace_column 9 #
148 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
149 WHERE c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
150
151 --echo #check index with WKB function
152 --replace_column 9 #
153 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
154 FROM tab
155 WHERE c3=ST_PolyFromWKB(ST_AsWkB(ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))')));
156
157 --echo #check index with WKT function
158 --replace_column 9 #
159 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
160 WHERE c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
161
162 --echo #check index with WKB function
163 --replace_column 9 #
164 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
165 FROM tab
166 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
167
168 #--echo #check range predicate on the index columns
169 #--replace_column 9 #
170 #--error ER_WRONG_ARGUMENTS
171 #EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
172 #WHERE c2>=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
173 #
174 #--replace_column 9 #
175 #--error ER_WRONG_ARGUMENTS
176 #EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
177 #WHERE c3>=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
178 #
179 #--replace_column 9 #
180 #--error ER_WRONG_ARGUMENTS
181 #EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
182 #WHERE c8>=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
183
184 --echo #check index with DELETE operation
185 --replace_column 9 #
186 EXPLAIN DELETE FROM tab
187 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
188
189
190 --echo #check the spatial values
191 SELECT ST_AsText(c1) FROM tab;
192 SELECT ST_AsText(c2) FROM tab;
193 SELECT ST_AsText(c3) FROM tab;
194 SELECT ST_AsText(c4) FROM tab;
195 SELECT ST_AsText(c5) FROM tab;
196 SELECT ST_AsText(c6) FROM tab;
197 SELECT ST_AsText(c7) FROM tab;
198 SELECT ST_AsText(c8) From tab;
199
200 --echo #check by modify the column type
201 ALTER TABLE tab MODIFY COLUMN c1 Geometry;
202 ALTER TABLE tab MODIFY COLUMN c2 Geometry;
203 ALTER TABLE tab MODIFY COLUMN c3 Geometry;
204 ALTER TABLE tab MODIFY COLUMN c4 Geometry;
205 ALTER TABLE tab MODIFY COLUMN c5 Geometry;
206 ALTER TABLE tab MODIFY COLUMN c6 Geometry;
207 ALTER TABLE tab MODIFY COLUMN c7 Geometry;
208
209 --echo #check column datatypes
210 SHOW FIELDS FROM tab;
211
212 --echo #check the data after modify
213 SELECT ST_AsText(c1) FROM tab;
214 SELECT ST_AsText(c2) FROM tab;
215 SELECT ST_AsText(c3) FROM tab;
216 SELECT ST_AsText(c4) FROM tab;
217 SELECT ST_AsText(c5) FROM tab;
218 SELECT ST_AsText(c6) FROM tab;
219 SELECT ST_AsText(c7) FROM tab;
220 SELECT ST_AsText(c8) From tab;
221
222 --echo #check by modify the column type
223 ALTER TABLE tab MODIFY COLUMN c1 POINT;
224 ALTER TABLE tab MODIFY COLUMN c2 LINESTRING;
225 ALTER TABLE tab MODIFY COLUMN c3 POLYGON;
226 ALTER TABLE tab MODIFY COLUMN c4 MULTIPOINT;
227 ALTER TABLE tab MODIFY COLUMN c5 MULTILINESTRING;
228 ALTER TABLE tab MODIFY COLUMN c6 MULTIPOLYGON;
229 ALTER TABLE tab MODIFY COLUMN c7 GEOMETRYCOLLECTION;
230
231 --echo #check column datatypes
232 SHOW FIELDS FROM tab;
233
234 --echo #check the data after modify
235 SELECT ST_AsText(c1) FROM tab;
236 SELECT ST_AsText(c2) FROM tab;
237 SELECT ST_AsText(c3) FROM tab;
238 SELECT ST_AsText(c4) FROM tab;
239 SELECT ST_AsText(c5) FROM tab;
240 SELECT ST_AsText(c6) FROM tab;
241 SELECT ST_AsText(c7) FROM tab;
242 SELECT ST_AsText(c8) From tab;
243
244 --echo #check change the column type
245 ALTER TABLE tab MODIFY COLUMN c1 BLOB;
246
247 --echo #check column datatypes
248 SHOW CREATE TABLE tab;
249
250 --echo #check the data after modify
251 SELECT ST_AsText(c1) FROM tab;
252
253 --echo #change the column name
254 ALTER TABLE tab CHANGE COLUMN c1 c0 GEOMETRY ;
255
256
257 --echo #check column datatypes
258 SHOW FIELDS FROM tab;
259
260 --echo #add primary key
261 ALTER TABLE tab ADD PRIMARY KEY pk2(c8(5));
262
263 --echo #check columns
264 SHOW FIELDS FROM tab;
265
266 --echo #drop key
267 ALTER TABLE tab DROP PRIMARY KEY;
268
269 --echo #check columns
270 SHOW FIELDS FROM tab;
271
272 --echo #cleanup the table
273 TRUNCATE TABLE tab;
274
275 --echo #check with procedures
276
277 delimiter |;
278
279 --echo #crate proc with INOUT params
280 CREATE PROCEDURE geom_insert(IN c1 POINT,IN c2 LINESTRING,IN c3 POLYGON,
281 IN c4 MULTIPOINT,IN c5 MULTILINESTRING, IN c6 MULTIPOLYGON,IN c7 GEOMETRYCOLLECTION,
282 IN c8 GEOMETRY)
283 BEGIN
284 INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
285 END |
286
287 delimiter ;|
288
289 --echo #set the spatial values
290 SET @c1=ST_PointFromText('POINT(10 10)');
291 SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
292 SET @c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
293 SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
294 SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
295 SET @c6=ST_MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))');
296 SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
297 SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
298
299 --echo #call the proc
300 CALL geom_insert(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
301
302 --echo #check the values ofter proc call
303 SELECT ST_AsText(c0) FROM tab;
304 SELECT ST_AsText(c2) FROM tab;
305 SELECT ST_AsText(c3) FROM tab;
306 SELECT ST_AsText(c4) FROM tab;
307 SELECT ST_AsText(c5) FROM tab;
308 SELECT ST_AsText(c6) FROM tab;
309 SELECT ST_AsText(c7) FROM tab;
310 SELECT ST_AsText(c8) From tab;
311
312
313 --echo #set the input spatial value
314 SET @c9=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
315
316 delimiter |;
317
318 --echo #crate a proc with INOUT params and pass a value.
319 CREATE PROCEDURE geominout(INOUT c9 GEOMETRY)
320 BEGIN
321
322 SELECT ST_AsText(@c9);
323 SET c9=ST_GeomCollFromText('GeometryCollection(Point(10 10),LineString(20 20, 30 30))');
324
325 END|
326
327 delimiter ;|
328
329 --echo #call the proc and then change the spatial value of c8
330 CALL geominout(@c9);
331
332 --echo #now check the out spatial values should be changed to new values
333 SELECT ST_AsText(@c9);
334
335 #check GIS datatype with Triggers
336
337 --echo #delete the records
338 TRUNCATE TABLE tab;
339
340 --echo #create another table same as tab
341 CREATE TABLE tab2 AS SELECT * FROM tab;
342
343 --echo #check the table definition
344 SHOW CREATE TABLE tab2;
345
346
347 delimiter |;
348
349 --echo #create a tigger and populate the values into tab2
350 CREATE TRIGGER geom_trigger AFTER INSERT ON tab
351 FOR EACH ROW
352 BEGIN
353 INSERT INTO tab2 VALUES (@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
354 END|
355
356 delimiter ;|
357
358 --echo #set the spatial values
359 SET @c1=ST_PointFromText('POINT(10 10)');
360 SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
361 SET @c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
362 SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
363 SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
364 SET @c6=ST_MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))');
365 SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
366 SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
367
368 --echo #Insert the spatial values
369 INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
370
371 --echo #check the values whether populated
372 SELECT ST_AsText(c0) FROM tab2;
373 SELECT ST_AsText(c2) FROM tab2;
374 SELECT ST_AsText(c3) FROM tab2;
375 SELECT ST_AsText(c4) FROM tab2;
376 SELECT ST_AsText(c5) FROM tab2;
377 SELECT ST_AsText(c6) FROM tab2;
378 SELECT ST_AsText(c7) FROM tab2;
379 SELECT ST_AsText(c8) From tab2;
380
381 --echo #check Cursor with Geometry
382
383 delimiter |;
384
385 CREATE PROCEDURE geom_cursor()
386 BEGIN
387 DECLARE v GEOMETRY;
388 DECLARE c CURSOR FOR SELECT c8 FROM tab2;
389
390 OPEN c;
391 FETCH c INTO v;
392 CLOSE c;
393
394 SELECT ST_AsText(v);
395 END|
396
397 delimiter ;|
398
399 --echo # the following line was commented due the Bug#16282246
400 --echo # Once it is fixed, the comment will be removed.
401 --echo # right now bug fixing date is unknown.
402 #CALL geom_cursor();
403
404 --echo check self join
405 SELECT ST_AsText(a.c0),ST_AsText(a.c2),ST_AsText(a.c3)
406 FROM tab a,tab2 b WHERE a.c0=b.c0 AND a.c2=b.c2
407 AND a.c3=b.c3 AND a.c4=b.c4 AND a.c5=b.c5 AND a.c6=b.c6
408 AND a.c7=b.c7 AND a.c8=b.c8;
409
410 --echo #check equi join
411 SELECT ST_AsText(a.c2),ST_AsText(b.c2) FROM tab a,tab2 b WHERE a.c2=b.c2;
412
413 --echo #check DELETE stmt with Where clause and a constant predicate
414 DELETE FROM tab
415 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
416
417 SELECT * FROM tab;
418
419 --echo #check UPDATE stmt with Where clause and a constant predicate
420 SET @c8=ST_GeomCollFromText('GeometryCollection(Point(10 10),LineString(20 20, 30 30))');
421 UPDATE tab2 SET c8=@c8
422 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
423
424 --echo #check the column should be modified to LINESTRING(10 10,20 20,30 30)
425 SELECT ST_AsText(c8) From tab2;
426
427 --echo #check GIS datatypes with aggregate functions
428 SELECT COUNT(ST_AsText(g)) FROM gis_point;
429 SELECT COUNT(ST_AsText(g)) FROM gis_line;
430 SELECT COUNT(ST_AsText(g)) FROM gis_polygon;
431 SELECT COUNT(ST_AsText(g)) FROM gis_multi_point;
432 SELECT COUNT(ST_AsText(g)) FROM gis_multi_line;
433 SELECT COUNT(ST_AsText(g)) FROM gis_multi_polygon;
434 SELECT COUNT(ST_AsText(g)) FROM gis_geometrycollection;
435 SELECT COUNT(ST_AsText(g)) FROM gis_geometry;
436
437 SELECT SUM(ST_AsText(g)) FROM gis_point;
438 SELECT SUM(ST_AsText(g)) FROM gis_line;
439 SELECT AVG(ST_AsText(g)) FROM gis_polygon;
440 --echo #here it show some string value no meaning
441 SELECT MAX(ST_AsText(g)) FROM gis_multi_point;
442 --echo #here it show some string value no meaning
443 SELECT MIN(ST_AsText(g)) FROM gis_multi_line;
444 SELECT STD(ST_AsText(g)) FROM gis_multi_polygon;
445 SELECT COUNT(ST_AsText(g)) FROM gis_geometrycollection;
446 SELECT AVG(ST_AsText(g)) FROM gis_geometry;
447
448 --disable_warnings
449 --echo #check Datatypes with compression tables
450
451 CREATE TABLE tab3(c1 POINT,c2 LINESTRING,c3 POLYGON,C4 MULTIPOINT,c5 MULTILINESTRING ,
452 c6 MULTIPOLYGON,c7 GEOMETRYCOLLECTION ,c8 GEOMETRY) ENGINE=InnoDB
453 KEY_BLOCK_SIZE=8 ROW_FORMAT=COMPRESSED;
454
455 --echo #check file format
456 SHOW CREATE TABLE tab3;
457
458 ALTER TABLE tab3 KEY_BLOCK_SIZE=16 ROW_FORMAT=Dynamic;
459
460 --echo #check file format
461 SHOW CREATE TABLE tab3;
462
463 --echo #check information schema for all the columns refer to 14, except POINT which is 15
464 SELECT sc.name, sc.pos, sc.mtype
465 FROM information_schema.innodb_sys_columns sc
466 INNER JOIN information_schema.innodb_sys_tables st
467 ON sc.TABLE_ID=st.TABLE_ID
468 WHERE st.NAME='test/tab3'
469 ORDER BY sc.name;
470
471 --echo #check Perform convesrion before INSERT using WKT functions
472 SET @c1=ST_PointFromText('POINT(10 10)');
473 SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
474 SET @c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
475 SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
476 SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
477 SET @c6=ST_MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))');
478 SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
479 SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
480
481 --echo #Insert the spatial values
482 INSERT INTO tab3 VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
483
484 --echo #check index on GIS datatypes
485 CREATE INDEX idx1 on tab3(c2(5) DESC) USING BTREE;
486 CREATE INDEX idx3 on tab3(c3(5) ASC) USING BTREE;
487 CREATE UNIQUE INDEX idx2 on tab3(c8(5) ASC) ;
488
489 --enable_warnings
490
491 --echo #check equality predicate on the index columns
492 --replace_column 9 #
493 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
494 WHERE c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
495
496 --echo #check index with WKB function
497 --replace_column 9 #
498 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
499 FROM tab3 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
500
501 --echo #check index with WKT function
502 --replace_column 9 #
503 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
504 WHERE c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
505
506 --echo #check index with WKB function
507 --replace_column 9 #
508 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
509 FROM tab3
510 WHERE c3=ST_PolyFromWKB(ST_AsWkB(ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))')));
511
512 --echo #check index with WKT function
513 --replace_column 9 #
514 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
515 WHERE c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
516
517 --echo #check index with WKB function
518 --replace_column 9 #
519 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
520 FROM tab3
521 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
522
523 # --echo #check range predicate on the index columns
524 # --replace_column 9 #
525 # --error ER_WRONG_ARGUMENTS
526 # EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
527 # WHERE c2>=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
528 #
529 # --replace_column 9 #
530 # --error ER_WRONG_ARGUMENTS
531 # EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
532 # WHERE c3>=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
533
534 # --replace_column 9 #
535 # --error ER_WRONG_ARGUMENTS
536 # EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
537 # WHERE c8>=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
538
539 --echo #check index with DELETE operation
540 --replace_column 9 #
541 EXPLAIN DELETE FROM tab3
542 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
543
544
545 --echo #check the spatial values
546 SELECT ST_AsText(c1) FROM tab3;
547 SELECT ST_AsText(c2) FROM tab3;
548 SELECT ST_AsText(c3) FROM tab3;
549 SELECT ST_AsText(c4) FROM tab3;
550 SELECT ST_AsText(c5) FROM tab3;
551 SELECT ST_AsText(c6) FROM tab3;
552 SELECT ST_AsText(c7) FROM tab3;
553 SELECT ST_AsText(c8) From tab3;
554
555 --echo #check with Row_format = Dynamic
556 --disable_warnings
557 ALTER TABLE tab3 ROW_FORMAT=Dynamic;
558 --enable_warnings
559
560 --echo #check file format
561 SHOW CREATE TABLE tab3;
562
563 --echo #check index with WKB function
564 --replace_column 9 #
565 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
566 FROM tab3 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
567
568 --echo #check index with WKT function
569 --replace_column 9 #
570 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
571 WHERE c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
572
573
574
575 --echo #check the Geometry property functions
576 SELECT fid, ST_Dimension(g) FROM gis_geometry;
577 SELECT fid, ST_GeometryType(g) FROM gis_geometry;
578 SELECT fid, ST_IsEmpty(g) FROM gis_geometry;
579 SELECT fid, ST_AsText(ST_Envelope(g)) FROM gis_geometry;
580 --replace_column 9 #
581 explain extended select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry;
582
583 --echo #check Geometry point functions
584 SELECT fid, ST_X(g) FROM gis_point;
585 SELECT fid, ST_Y(g) FROM gis_point;
586 --replace_column 9 #
587 explain extended select ST_X(g),ST_Y(g) FROM gis_point;
588
589 SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line;
590 SELECT fid, ST_AsText(ST_EndPoint(g)) FROM gis_line;
591 SELECT fid, ST_Length(g) FROM gis_line;
592 SELECT fid, ST_NumPoints(g) FROM gis_line;
593 SELECT fid, ST_AsText(ST_PointN(g, 2)) FROM gis_line;
594 SELECT fid, ST_IsClosed(g) FROM gis_line;
595 --replace_column 9 #
596 explain extended select ST_AsText(ST_StartPoint(g)),ST_AsText(ST_EndPoint(g)),
597 ST_Length(g),ST_NumPoints(g),ST_AsText(ST_PointN(g, 2)),ST_IsClosed(g) FROM gis_line;
598
599 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon;
600 SELECT fid, ST_Area(g) FROM gis_polygon;
601 SELECT fid, ST_AsText(ST_ExteriorRing(g)) FROM gis_polygon;
602 SELECT fid, ST_NumInteriorRings(g) FROM gis_polygon;
603 SELECT fid, ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
604 --replace_column 9 #
605 explain extended select ST_AsText(ST_Centroid(g)),ST_Area(g),ST_AsText(ST_ExteriorRing(g)),
606 ST_NumInteriorRings(g),ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
607
608 SELECT fid, ST_IsClosed(g) FROM gis_multi_line;
609
610 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_multi_polygon;
611 SELECT fid, ST_Area(g) FROM gis_multi_polygon;
612
613 SELECT fid, ST_NumGeometries(g) from gis_multi_point;
614 SELECT fid, ST_NumGeometries(g) from gis_multi_line;
615 SELECT fid, ST_NumGeometries(g) from gis_multi_polygon;
616 SELECT fid, ST_NumGeometries(g) from gis_geometrycollection;
617 --replace_column 9 #
618 explain extended SELECT fid, ST_NumGeometries(g) from gis_multi_point;
619
620 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
621 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_line;
622 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_polygon;
623 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_geometrycollection;
624 SELECT fid, ST_AsText(ST_GeometryN(g, 1)) from gis_geometrycollection;
625 --replace_column 9 #
626 explain extended SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
627
628 --disable_view_protocol
629 SELECT g1.fid as first, g2.fid as second,
630 MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
631 MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
632 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
633 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
634
635 --replace_column 9 #
636 explain extended SELECT g1.fid as first, g2.fid as second,
637 MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
638 MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
639 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
640 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
641 --enable_view_protocol
642
643 --echo # check support of Foreign Key constraint
644 CREATE TABLE parent (id GEOMETRY NOT NULL,PRIMARY KEY (id(10))) ENGINE=INNODB;
645
646 --error ER_CANT_CREATE_TABLE
647 CREATE TABLE child (id GEOMETRY, parent_id GEOMETRY,
648 INDEX par_ind (parent_id(10)),
649 FOREIGN KEY (parent_id(10)) REFERENCES parent(id)
650 ON DELETE CASCADE
651 ) ENGINE=INNODB;
652
653 --echo #check partition table support
654 --error ER_BLOB_FIELD_IN_PART_FUNC_ERROR
655 CREATE TABLE emp2(
656 id GEOMETRY NOT NULL,
657 store_name VARCHAR(30),
658 parts VARCHAR(30),
659 store_id GEOMETRY
660 )
661 PARTITION BY LIST(store_id) (
662 PARTITION pNorth VALUES IN (10,20,30) ,
663 PARTITION pEast VALUES IN (40,50,60) ,
664 PARTITION pWest VALUES IN (70,80,100)
665 );
666
667 --echo #check start transaction commit & Rollback
668 START TRANSACTION;
669 DELETE FROM tab3;
670 SELECT * FROM tab3;
671 ROLLBACK;
672
673 SELECT COUNT(*) FROM tab3;
674
675 START TRANSACTION;
676 DELETE FROM tab3;
677 COMMIT;
678
679 SELECT * FROM tab3;
680
681 DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point;
682 DROP TABLE gis_multi_line, gis_multi_polygon;
683 DROP TABLE gis_geometrycollection, gis_geometry;
684 DROP TABLE tab,tab2,tab3,parent;
685 DROP PROCEDURE geominout;
686 DROP PROCEDURE geom_insert;
687 DROP PROCEDURE geom_cursor;
688
689 SET GLOBAL innodb_stats_persistent=@save_innodb_stats_persistent;