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