diff options
Diffstat (limited to 'mysql-test/suite/innodb_gis/t/geometry.test')
| -rw-r--r-- | mysql-test/suite/innodb_gis/t/geometry.test | 704 |
1 files changed, 704 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb_gis/t/geometry.test b/mysql-test/suite/innodb_gis/t/geometry.test new file mode 100644 index 00000000000..6450e82e2e4 --- /dev/null +++ b/mysql-test/suite/innodb_gis/t/geometry.test @@ -0,0 +1,704 @@ +#******************************************************** +# wl6455: GIS Datatypes covers the following tests. +# DDL: ALTER PRIMARY, DROP KEY,INDEX,COLUMN TYPE etc +# INSERT/SELECT operations with WKT & WKB functions +# SELECT/DELETE/UPDATE with =,>,< predicate conditions +# Datatypes with Procedure (IN/INOUT/OUT,Cursors,Trigger) +# Datatypes with Aggregate functions +# File Formts Compressed & Dynamic +# All Geometric functions. +#******************************************************** +--source include/have_geometry.inc +--source include/not_embedded.inc +--source include/have_debug.inc +--source include/have_innodb.inc + +SET default_storage_engine=InnoDB; +SET innodb_strict_mode=OFF; + +# Turn on the geometry data print. +SET SESSION debug="+d,row_print_geometry_data"; + +SHOW VARIABLES LIKE '%engine%'; + +# +# Spatial objects +# + +USE test; + +--disable_warnings +DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon; +DROP TABLE IF EXISTS gis_multi_point, gis_multi_line, gis_multi_polygon; +DROP TABLE IF EXISTS gis_geometrycollection, gis_geometry; +DROP TABLE IF EXISTS tab,tab2,tab3,parent,emp2; +DROP PROCEDURE IF EXISTS geominout; +DROP PROCEDURE IF EXISTS geom_insert; +DROP TRIGGER IF EXISTS geom_trigger; +DROP PROCEDURE IF EXISTS geom_cursor; + + +--enable_warnings + +CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT) ENGINE=InnoDB; +CREATE TABLE gis_line (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING) ENGINE=InnoDB; +CREATE TABLE gis_polygon (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON) ENGINE=InnoDB; +CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT) ENGINE=InnoDB; +CREATE TABLE gis_multi_line (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING) ENGINE=InnoDB; +CREATE TABLE gis_multi_polygon (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON) ENGINE=InnoDB; +CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION) ENGINE=InnoDB; +CREATE TABLE gis_geometry (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY) ENGINE=InnoDB; + +SHOW FIELDS FROM gis_point; +SHOW FIELDS FROM gis_line; +SHOW FIELDS FROM gis_polygon; +SHOW FIELDS FROM gis_multi_point; +SHOW FIELDS FROM gis_multi_line; +SHOW FIELDS FROM gis_multi_polygon; +SHOW FIELDS FROM gis_geometrycollection; +SHOW FIELDS FROM gis_geometry; + +--echo #INSERT using all WKT & WKB functions +INSERT INTO gis_point VALUES +(101, ST_PointFromText('POINT(10 10)')), +(102, ST_PointFromText('POINT(20 10)')), +(103, ST_PointFromText('POINT(20 20)')), +(104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)')))); + + +INSERT INTO gis_line VALUES +(105, ST_LineFromText('LINESTRING(0 0,0 10,10 0)')), +(106, ST_LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')), +(107, ST_LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(40, 10))))); + + +INSERT INTO gis_polygon VALUES +(108, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')), +(109, ST_PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')), +(110, ST_PolyFromWKB(ST_AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))))); + + +INSERT INTO gis_multi_point VALUES +(111, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')), +(112, ST_MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')), +(113, ST_MPointFromWKB(ST_AsWKB(MultiPoint(Point(3, 6), Point(4, 10))))); + + +INSERT INTO gis_multi_line VALUES +(114, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')), +(115, ST_MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')), +(116, ST_MLineFromWKB(ST_AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))))); + + +INSERT INTO gis_multi_polygon VALUES +(117, ST_MultiPolygonFromText('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)))')), +(118, 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)))')), +(119, ST_MPolyFromWKB(ST_AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))))); + + +INSERT INTO gis_geometrycollection VALUES +(120, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')), +(121, ST_GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(10 10), LINESTRING(10 10,20 20))')), +(122, ST_GeometryFromWKB(ST_AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))))); + +--echo #check INSERT using SELECT query +INSERT into gis_geometry SELECT * FROM gis_point; +INSERT into gis_geometry SELECT * FROM gis_line; +INSERT into gis_geometry SELECT * FROM gis_polygon; +INSERT into gis_geometry SELECT * FROM gis_multi_point; +INSERT into gis_geometry SELECT * FROM gis_multi_line; +INSERT into gis_geometry SELECT * FROM gis_multi_polygon; +INSERT into gis_geometry SELECT * FROM gis_geometrycollection; + +--echo #check format coversion functions & spatial values +SELECT fid, ST_AsText(g) FROM gis_point; +SELECT fid, ST_AsText(g) FROM gis_line; +SELECT fid, ST_AsText(g) FROM gis_polygon; +SELECT fid, ST_AsText(g) FROM gis_multi_point; +SELECT fid, ST_AsText(g) FROM gis_multi_line; +SELECT fid, ST_AsText(g) FROM gis_multi_polygon; +SELECT fid, ST_AsText(g) FROM gis_geometrycollection; +SELECT fid, ST_AsText(g) FROM gis_geometry; + +--echo #2.1 check DDL functionality on GIS datatypes +CREATE TABLE tab(c1 POINT,c2 LINESTRING,c3 POLYGON,C4 MULTIPOINT,c5 MULTILINESTRING , +c6 MULTIPOLYGON,c7 GEOMETRYCOLLECTION ,c8 GEOMETRY) ENGINE=InnoDB; + +--echo #check information schema for all the columns refer to 14, except POINT which is 15 +SELECT sc.name, sc.pos, sc.mtype +FROM information_schema.innodb_sys_columns sc +INNER JOIN information_schema.innodb_sys_tables st +ON sc.TABLE_ID=st.TABLE_ID +WHERE st.NAME='test/tab' +ORDER BY sc.name; + +--echo #check Perform convesrion before INSERT using WKT functions +SET @c1=ST_PointFromText('POINT(10 10)'); +SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)'); +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))'); +SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)'); +SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))'); +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)))'); +SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'); +SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'); + +--echo #Insert the spatial values +INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8); + +--echo #check index on GIS datatypes +CREATE INDEX idx1 on tab(c2(5) DESC) USING BTREE; +CREATE INDEX idx3 on tab(c3(5) ASC) USING BTREE; +CREATE UNIQUE INDEX idx2 on tab(c8(5) ASC) ; + +--echo #check equality predicate on the index columns +--replace_column 10 # +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab +WHERE c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)'); + +--echo #check index with WKB function +--replace_column 10 # +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) +FROM tab WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)'))); + +--echo #check index with WKT function +--replace_column 10 # +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab +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))'); + +--echo #check index with WKB function +--replace_column 10 # +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) +FROM tab +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))'))); + +--echo #check index with WKT function +--replace_column 10 # +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab +WHERE c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'); + +--echo #check index with WKB function +--replace_column 10 # +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) +FROM tab +WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'))); + +--echo #check range predicate on the index columns +--replace_column 10 # +--error ER_WRONG_ARGUMENTS +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab +WHERE c2>=ST_LineFromText('LINESTRING(10 10,20 20,30 30)'); + +--replace_column 10 # +--error ER_WRONG_ARGUMENTS +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab +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))'); + +--replace_column 10 # +--error ER_WRONG_ARGUMENTS +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab +WHERE c8>=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'); + +--echo #check index with DELETE operation +--replace_column 10 # +EXPLAIN DELETE FROM tab +WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'))); + + +--echo #check the spatial values +SELECT ST_AsText(c1) FROM tab; +SELECT ST_AsText(c2) FROM tab; +SELECT ST_AsText(c3) FROM tab; +SELECT ST_AsText(c4) FROM tab; +SELECT ST_AsText(c5) FROM tab; +SELECT ST_AsText(c6) FROM tab; +SELECT ST_AsText(c7) FROM tab; +SELECT ST_AsText(c8) From tab; + +--echo #check by modify the column type +ALTER TABLE tab MODIFY COLUMN c1 Geometry; +ALTER TABLE tab MODIFY COLUMN c2 Geometry; +ALTER TABLE tab MODIFY COLUMN c3 Geometry; +ALTER TABLE tab MODIFY COLUMN c4 Geometry; +ALTER TABLE tab MODIFY COLUMN c5 Geometry; +ALTER TABLE tab MODIFY COLUMN c6 Geometry; +ALTER TABLE tab MODIFY COLUMN c7 Geometry; + +--echo #check column datatypes +SHOW FIELDS FROM tab; + +--echo #check the data after modify +SELECT ST_AsText(c1) FROM tab; +SELECT ST_AsText(c2) FROM tab; +SELECT ST_AsText(c3) FROM tab; +SELECT ST_AsText(c4) FROM tab; +SELECT ST_AsText(c5) FROM tab; +SELECT ST_AsText(c6) FROM tab; +SELECT ST_AsText(c7) FROM tab; +SELECT ST_AsText(c8) From tab; + +--echo #check by modify the column type +ALTER TABLE tab MODIFY COLUMN c1 POINT; +ALTER TABLE tab MODIFY COLUMN c2 LINESTRING; +ALTER TABLE tab MODIFY COLUMN c3 POLYGON; +ALTER TABLE tab MODIFY COLUMN c4 MULTIPOINT; +ALTER TABLE tab MODIFY COLUMN c5 MULTILINESTRING; +ALTER TABLE tab MODIFY COLUMN c6 MULTIPOLYGON; +ALTER TABLE tab MODIFY COLUMN c7 GEOMETRYCOLLECTION; + +--echo #check column datatypes +SHOW FIELDS FROM tab; + +--echo #check the data after modify +SELECT ST_AsText(c1) FROM tab; +SELECT ST_AsText(c2) FROM tab; +SELECT ST_AsText(c3) FROM tab; +SELECT ST_AsText(c4) FROM tab; +SELECT ST_AsText(c5) FROM tab; +SELECT ST_AsText(c6) FROM tab; +SELECT ST_AsText(c7) FROM tab; +SELECT ST_AsText(c8) From tab; + +--echo #check change the column type +ALTER TABLE tab MODIFY COLUMN c1 BLOB; + +--echo #check column datatypes +SHOW CREATE TABLE tab; + +--echo #check the data after modify +SELECT ST_AsText(c1) FROM tab; + +--echo #change the column name +ALTER TABLE tab CHANGE COLUMN c1 c0 GEOMETRY ; + + +--echo #check column datatypes +SHOW FIELDS FROM tab; + +--echo #add primary key +ALTER TABLE tab ADD PRIMARY KEY pk2(c8(5)); + +--echo #check columns +SHOW FIELDS FROM tab; + +--echo #drop key +ALTER TABLE tab DROP PRIMARY KEY; + +--echo #check columns +SHOW FIELDS FROM tab; + +--echo #cleanup the table +TRUNCATE TABLE tab; + +--echo #check with procedures + +delimiter |; + +--echo #crate proc with INOUT params +CREATE PROCEDURE geom_insert(IN c1 POINT,IN c2 LINESTRING,IN c3 POLYGON, +IN c4 MULTIPOINT,IN c5 MULTILINESTRING, IN c6 MULTIPOLYGON,IN c7 GEOMETRYCOLLECTION, +IN c8 GEOMETRY) +BEGIN +INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8); +END | + +delimiter ;| + +--echo #set the spatial values +SET @c1=ST_PointFromText('POINT(10 10)'); +SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)'); +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))'); +SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)'); +SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))'); +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)))'); +SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'); +SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'); + +--echo #call the proc +CALL geom_insert(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8); + +--echo #check the values ofter proc call +SELECT ST_AsText(c0) FROM tab; +SELECT ST_AsText(c2) FROM tab; +SELECT ST_AsText(c3) FROM tab; +SELECT ST_AsText(c4) FROM tab; +SELECT ST_AsText(c5) FROM tab; +SELECT ST_AsText(c6) FROM tab; +SELECT ST_AsText(c7) FROM tab; +SELECT ST_AsText(c8) From tab; + + +--echo #set the input spatial value +SET @c9=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'); + +delimiter |; + +--echo #crate a proc with INOUT params and pass a value. +CREATE PROCEDURE geominout(INOUT c9 GEOMETRY) +BEGIN + +SELECT ST_AsText(@c9); +SET c9=ST_GeomCollFromText('GeometryCollection(Point(10 10),LineString(20 20, 30 30))'); + +END| + +delimiter ;| + +--echo #call the proc and then change the spatial value of c8 +CALL geominout(@c9); + +--echo #now check the out spatial values should be changed to new values +SELECT ST_AsText(@c9); + +#check GIS datatype with Triggers + +--echo #delete the records +TRUNCATE TABLE tab; + +--echo #create another table same as tab +CREATE TABLE tab2 AS SELECT * FROM tab; + +--echo #check the table definition +SHOW CREATE TABLE tab2; + + +delimiter |; + +--echo #create a tigger and populate the values into tab2 +CREATE TRIGGER geom_trigger AFTER INSERT ON tab +FOR EACH ROW +BEGIN + INSERT INTO tab2 VALUES (@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8); +END| + +delimiter ;| + +--echo #set the spatial values +SET @c1=ST_PointFromText('POINT(10 10)'); +SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)'); +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))'); +SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)'); +SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))'); +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)))'); +SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'); +SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'); + +--echo #Insert the spatial values +INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8); + +--echo #check the values whether populated +SELECT ST_AsText(c0) FROM tab2; +SELECT ST_AsText(c2) FROM tab2; +SELECT ST_AsText(c3) FROM tab2; +SELECT ST_AsText(c4) FROM tab2; +SELECT ST_AsText(c5) FROM tab2; +SELECT ST_AsText(c6) FROM tab2; +SELECT ST_AsText(c7) FROM tab2; +SELECT ST_AsText(c8) From tab2; + +--echo #check Cursor with Geometry + +delimiter |; + +CREATE PROCEDURE geom_cursor() +BEGIN + DECLARE v GEOMETRY; + DECLARE c CURSOR FOR SELECT c8 FROM tab2; + + OPEN c; + FETCH c INTO v; + CLOSE c; + + SELECT ST_AsText(v); +END| + +delimiter ;| + +--echo # the following line was commented due the Bug#16282246 +--echo # Once it is fixed, the comment will be removed. +--echo # right now bug fixing date is unknown. +#CALL geom_cursor(); + +--echo check self join +SELECT ST_AsText(a.c0),ST_AsText(a.c2),ST_AsText(a.c3) +FROM tab a,tab2 b WHERE a.c0=b.c0 AND a.c2=b.c2 +AND a.c3=b.c3 AND a.c4=b.c4 AND a.c5=b.c5 AND a.c6=b.c6 +AND a.c7=b.c7 AND a.c8=b.c8; + +--echo #check equi join +SELECT ST_AsText(a.c2),ST_AsText(b.c2) FROM tab a,tab2 b WHERE a.c2=b.c2; + +--echo #check DELETE stmt with Where clause and a constant predicate +DELETE FROM tab +Where c8=c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'))); + +--echo #check the data, should 0 +SELECT COUNT(*) FROM tab; + +--echo #check UPDATE stmt with Where clause and a constant predicate +SET @c8=ST_GeomCollFromText('GeometryCollection(Point(10 10),LineString(20 20, 30 30))'); +UPDATE tab2 SET c8=@c8 +WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)'))); + +--echo #check the column should be modified to LINESTRING(10 10,20 20,30 30) +SELECT ST_AsText(c8) From tab2; + +--echo #check GIS datatypes with aggregate functions +SELECT COUNT(ST_AsText(g)) FROM gis_point; +SELECT COUNT(ST_AsText(g)) FROM gis_line; +SELECT COUNT(ST_AsText(g)) FROM gis_polygon; +SELECT COUNT(ST_AsText(g)) FROM gis_multi_point; +SELECT COUNT(ST_AsText(g)) FROM gis_multi_line; +SELECT COUNT(ST_AsText(g)) FROM gis_multi_polygon; +SELECT COUNT(ST_AsText(g)) FROM gis_geometrycollection; +SELECT COUNT(ST_AsText(g)) FROM gis_geometry; + +SELECT SUM(ST_AsText(g)) FROM gis_point; +SELECT SUM(ST_AsText(g)) FROM gis_line; +SELECT AVG(ST_AsText(g)) FROM gis_polygon; +--echo #here it show some string value no meaning +SELECT MAX(ST_AsText(g)) FROM gis_multi_point; +--echo #here it show some string value no meaning +SELECT MIN(ST_AsText(g)) FROM gis_multi_line; +SELECT STD(ST_AsText(g)) FROM gis_multi_polygon; +SELECT COUNT(ST_AsText(g)) FROM gis_geometrycollection; +SELECT AVG(ST_AsText(g)) FROM gis_geometry; + +--disable_warnings +--echo #check Datatypes with compression tables + +CREATE TABLE tab3(c1 POINT,c2 LINESTRING,c3 POLYGON,C4 MULTIPOINT,c5 MULTILINESTRING , +c6 MULTIPOLYGON,c7 GEOMETRYCOLLECTION ,c8 GEOMETRY) ENGINE=InnoDB +KEY_BLOCK_SIZE=8 ROW_FORMAT=COMPRESSED; + +--echo #check file format +SHOW CREATE TABLE tab3; + +ALTER TABLE tab3 KEY_BLOCK_SIZE=16 ROW_FORMAT=Dynamic; + +--echo #check file format +SHOW CREATE TABLE tab3; + +--echo #check information schema for all the columns refer to 14, except POINT which is 15 +SELECT sc.name, sc.pos, sc.mtype +FROM information_schema.innodb_sys_columns sc +INNER JOIN information_schema.innodb_sys_tables st +ON sc.TABLE_ID=st.TABLE_ID +WHERE st.NAME='test/tab3' +ORDER BY sc.name; + +--echo #check Perform convesrion before INSERT using WKT functions +SET @c1=ST_PointFromText('POINT(10 10)'); +SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)'); +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))'); +SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)'); +SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))'); +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)))'); +SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'); +SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'); + +--echo #Insert the spatial values +INSERT INTO tab3 VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8); + +--echo #check index on GIS datatypes +CREATE INDEX idx1 on tab3(c2(5) DESC) USING BTREE; +CREATE INDEX idx3 on tab3(c3(5) ASC) USING BTREE; +CREATE UNIQUE INDEX idx2 on tab3(c8(5) ASC) ; + +--enable_warnings + +--echo #check equality predicate on the index columns +--replace_column 10 # +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3 +WHERE c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)'); + +--echo #check index with WKB function +--replace_column 10 # +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) +FROM tab3 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)'))); + +--echo #check index with WKT function +--replace_column 10 # +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3 +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))'); + +--echo #check index with WKB function +--replace_column 10 # +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) +FROM tab3 +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))'))); + +--echo #check index with WKT function +--replace_column 10 # +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3 +WHERE c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'); + +--echo #check index with WKB function +--replace_column 10 # +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) +FROM tab3 +WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'))); + +--echo #check range predicate on the index columns +--replace_column 10 # +--error ER_WRONG_ARGUMENTS +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3 +WHERE c2>=ST_LineFromText('LINESTRING(10 10,20 20,30 30)'); + +--replace_column 10 # +--error ER_WRONG_ARGUMENTS +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3 +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))'); + +--replace_column 10 # +--error ER_WRONG_ARGUMENTS +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3 +WHERE c8>=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'); + +--echo #check index with DELETE operation +--replace_column 10 # +EXPLAIN DELETE FROM tab3 +WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'))); + + +--echo #check the spatial values +SELECT ST_AsText(c1) FROM tab3; +SELECT ST_AsText(c2) FROM tab3; +SELECT ST_AsText(c3) FROM tab3; +SELECT ST_AsText(c4) FROM tab3; +SELECT ST_AsText(c5) FROM tab3; +SELECT ST_AsText(c6) FROM tab3; +SELECT ST_AsText(c7) FROM tab3; +SELECT ST_AsText(c8) From tab3; + +--echo #check with Row_format = Dynamic +--disable_warnings +ALTER TABLE tab3 ROW_FORMAT=Dynamic; +--enable_warnings + +--echo #check file format +SHOW CREATE TABLE tab3; + +--echo #check index with WKB function +--replace_column 10 # +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) +FROM tab3 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)'))); + +--echo #check index with WKT function +--replace_column 10 # +EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3 +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))'); + + + +--echo #check the Geometry property functions +SELECT fid, ST_Dimension(g) FROM gis_geometry; +SELECT fid, ST_GeometryType(g) FROM gis_geometry; +SELECT fid, ST_IsEmpty(g) FROM gis_geometry; +SELECT fid, ST_AsText(ST_Envelope(g)) FROM gis_geometry; +--replace_column 10 # +explain extended select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry; + +--echo #check Geometry point functions +SELECT fid, ST_X(g) FROM gis_point; +SELECT fid, ST_Y(g) FROM gis_point; +--replace_column 10 # +explain extended select ST_X(g),ST_Y(g) FROM gis_point; + +SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line; +SELECT fid, ST_AsText(ST_EndPoint(g)) FROM gis_line; +SELECT fid, ST_Length(g) FROM gis_line; +SELECT fid, ST_NumPoints(g) FROM gis_line; +SELECT fid, ST_AsText(ST_PointN(g, 2)) FROM gis_line; +SELECT fid, ST_IsClosed(g) FROM gis_line; +--replace_column 10 # +explain extended select ST_AsText(ST_StartPoint(g)),ST_AsText(ST_EndPoint(g)), +ST_Length(g),ST_NumPoints(g),ST_AsText(ST_PointN(g, 2)),ST_IsClosed(g) FROM gis_line; + +SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon; +SELECT fid, ST_Area(g) FROM gis_polygon; +SELECT fid, ST_AsText(ST_ExteriorRing(g)) FROM gis_polygon; +SELECT fid, ST_NumInteriorRings(g) FROM gis_polygon; +SELECT fid, ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon; +--replace_column 10 # +explain extended select ST_AsText(ST_Centroid(g)),ST_Area(g),ST_AsText(ST_ExteriorRing(g)), +ST_NumInteriorRings(g),ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon; + +SELECT fid, ST_IsClosed(g) FROM gis_multi_line; + +SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_multi_polygon; +SELECT fid, ST_Area(g) FROM gis_multi_polygon; + +SELECT fid, ST_NumGeometries(g) from gis_multi_point; +SELECT fid, ST_NumGeometries(g) from gis_multi_line; +SELECT fid, ST_NumGeometries(g) from gis_multi_polygon; +SELECT fid, ST_NumGeometries(g) from gis_geometrycollection; +--replace_column 10 # +explain extended SELECT fid, ST_NumGeometries(g) from gis_multi_point; + +SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point; +SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_line; +SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_polygon; +SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_geometrycollection; +SELECT fid, ST_AsText(ST_GeometryN(g, 1)) from gis_geometrycollection; +--replace_column 10 # +explain extended SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point; + +SELECT g1.fid as first, g2.fid as second, +MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o, +MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t, +MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r +FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; +--replace_column 10 # +explain extended SELECT g1.fid as first, g2.fid as second, +MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o, +MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t, +MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r +FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; + +--echo # check support of Foreign Key constraint +CREATE TABLE parent (id GEOMETRY NOT NULL,PRIMARY KEY (id(10))) ENGINE=INNODB; + +--error 1215 +CREATE TABLE child (id GEOMETRY, parent_id GEOMETRY, + INDEX par_ind (parent_id(10)), + FOREIGN KEY (parent_id(10)) REFERENCES parent(id) + ON DELETE CASCADE +) ENGINE=INNODB; + +--echo #check partition table support +--error 1178 +CREATE TABLE emp2( + id GEOMETRY NOT NULL, + store_name VARCHAR(30), + parts VARCHAR(30), + store_id GEOMETRY +) +PARTITION BY LIST(store_id) ( + PARTITION pNorth VALUES IN (10,20,30) , + PARTITION pEast VALUES IN (40,50,60) , + PARTITION pWest VALUES IN (70,80,100) +); + +--echo #check start transaction commit & Rollback +START TRANSACTION; +DELETE FROM tab3; +ROLLBACK; + +SELECT COUNT(*) FROM tab3; + +START TRANSACTION; +DELETE FROM tab3; +COMMIT; + +SELECT COUNT(*) FROM tab3; + +DROP TABLE IF EXISTS gis_point, gis_line, gis_polygon, gis_multi_point; +DROP TABLE IF EXISTS gis_multi_line, gis_multi_polygon; +DROP TABLE IF EXISTS gis_geometrycollection, gis_geometry; +DROP TABLE IF EXISTS tab,tab2,tab3,parent,emp2; +DROP PROCEDURE IF EXISTS geominout; +DROP PROCEDURE IF EXISTS geom_insert; +DROP TRIGGER IF EXISTS geom_trigger; +DROP PROCEDURE IF EXISTS geom_cursor; |