summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/innodb_gis/r/geometry.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/innodb_gis/r/geometry.result')
-rw-r--r--mysql-test/suite/innodb_gis/r/geometry.result1118
1 files changed, 1118 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb_gis/r/geometry.result b/mysql-test/suite/innodb_gis/r/geometry.result
new file mode 100644
index 00000000..c38e9e28
--- /dev/null
+++ b/mysql-test/suite/innodb_gis/r/geometry.result
@@ -0,0 +1,1118 @@
+SET default_storage_engine=InnoDB;
+SET innodb_strict_mode=OFF;
+SET @save_innodb_stats_persistent=@@GLOBAL.innodb_stats_persistent;
+SET GLOBAL innodb_stats_persistent=0;
+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;
+Field Type Null Key Default Extra
+fid int(11) NO PRI NULL
+g point YES NULL
+SHOW FIELDS FROM gis_line;
+Field Type Null Key Default Extra
+fid int(11) NO PRI NULL
+g linestring YES NULL
+SHOW FIELDS FROM gis_polygon;
+Field Type Null Key Default Extra
+fid int(11) NO PRI NULL
+g polygon YES NULL
+SHOW FIELDS FROM gis_multi_point;
+Field Type Null Key Default Extra
+fid int(11) NO PRI NULL
+g multipoint YES NULL
+SHOW FIELDS FROM gis_multi_line;
+Field Type Null Key Default Extra
+fid int(11) NO PRI NULL
+g multilinestring YES NULL
+SHOW FIELDS FROM gis_multi_polygon;
+Field Type Null Key Default Extra
+fid int(11) NO PRI NULL
+g multipolygon YES NULL
+SHOW FIELDS FROM gis_geometrycollection;
+Field Type Null Key Default Extra
+fid int(11) NO PRI NULL
+g geometrycollection YES NULL
+SHOW FIELDS FROM gis_geometry;
+Field Type Null Key Default Extra
+fid int(11) NO PRI NULL
+g geometry YES NULL
+#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))))));
+#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;
+#check format coversion functions & spatial values
+SELECT fid, ST_AsText(g) FROM gis_point;
+fid ST_AsText(g)
+101 POINT(10 10)
+102 POINT(20 10)
+103 POINT(20 20)
+104 POINT(10 20)
+SELECT fid, ST_AsText(g) FROM gis_line;
+fid ST_AsText(g)
+105 LINESTRING(0 0,0 10,10 0)
+106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+107 LINESTRING(10 10,40 10)
+SELECT fid, ST_AsText(g) FROM gis_polygon;
+fid ST_AsText(g)
+108 POLYGON((10 10,20 10,20 20,10 20,10 10))
+109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
+110 POLYGON((0 0,30 0,30 30,0 0))
+SELECT fid, ST_AsText(g) FROM gis_multi_point;
+fid ST_AsText(g)
+111 MULTIPOINT(0 0,10 10,10 20,20 20)
+112 MULTIPOINT(1 1,11 11,11 21,21 21)
+113 MULTIPOINT(3 6,4 10)
+SELECT fid, ST_AsText(g) FROM gis_multi_line;
+fid ST_AsText(g)
+114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
+115 MULTILINESTRING((10 48,10 21,10 0))
+116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
+SELECT fid, ST_AsText(g) FROM gis_multi_polygon;
+fid ST_AsText(g)
+117 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 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 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
+SELECT fid, ST_AsText(g) FROM gis_geometrycollection;
+fid ST_AsText(g)
+120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
+121 GEOMETRYCOLLECTION(POINT(10 10),LINESTRING(10 10,20 20))
+122 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
+SELECT fid, ST_AsText(g) FROM gis_geometry;
+fid ST_AsText(g)
+101 POINT(10 10)
+102 POINT(20 10)
+103 POINT(20 20)
+104 POINT(10 20)
+105 LINESTRING(0 0,0 10,10 0)
+106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+107 LINESTRING(10 10,40 10)
+108 POLYGON((10 10,20 10,20 20,10 20,10 10))
+109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
+110 POLYGON((0 0,30 0,30 30,0 0))
+111 MULTIPOINT(0 0,10 10,10 20,20 20)
+112 MULTIPOINT(1 1,11 11,11 21,21 21)
+113 MULTIPOINT(3 6,4 10)
+114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
+115 MULTILINESTRING((10 48,10 21,10 0))
+116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
+117 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 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 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
+120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
+121 GEOMETRYCOLLECTION(POINT(10 10),LINESTRING(10 10,20 20))
+122 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
+#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;
+#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;
+name pos mtype
+c1 0 14
+c2 1 14
+c3 2 14
+C4 3 14
+c5 4 14
+c6 5 14
+c7 6 14
+c8 7 14
+#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))');
+#Insert the spatial values
+INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
+#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) ;
+#check equality predicate on the index columns
+EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
+WHERE c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tab ref idx1 idx1 8 const # Using where
+#check index with WKB function
+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)')));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tab ref idx1 idx1 8 const # Using where
+#check index with WKT function
+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))');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tab ref idx3 idx3 8 const # Using where
+#check index with WKB function
+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))')));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tab ref idx3 idx3 8 const # Using where
+#check index with WKT function
+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))');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tab const idx2 idx2 8 const #
+#check index with WKB function
+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))')));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tab const idx2 idx2 8 const #
+#check index with DELETE operation
+EXPLAIN DELETE FROM tab
+WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tab range idx2 idx2 8 NULL # Using where
+#check the spatial values
+SELECT ST_AsText(c1) FROM tab;
+ST_AsText(c1)
+POINT(10 10)
+SELECT ST_AsText(c2) FROM tab;
+ST_AsText(c2)
+LINESTRING(10 10,20 20,30 30)
+SELECT ST_AsText(c3) FROM tab;
+ST_AsText(c3)
+POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
+SELECT ST_AsText(c4) FROM tab;
+ST_AsText(c4)
+MULTIPOINT(0 0,5 5,10 10,20 20)
+SELECT ST_AsText(c5) FROM tab;
+ST_AsText(c5)
+MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
+SELECT ST_AsText(c6) FROM tab;
+ST_AsText(c6)
+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)))
+SELECT ST_AsText(c7) FROM tab;
+ST_AsText(c7)
+GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
+SELECT ST_AsText(c8) From tab;
+ST_AsText(c8)
+GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
+#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;
+#check column datatypes
+SHOW FIELDS FROM tab;
+Field Type Null Key Default Extra
+c1 geometry YES NULL
+c2 geometry YES MUL NULL
+c3 geometry YES MUL NULL
+c4 geometry YES NULL
+c5 geometry YES NULL
+c6 geometry YES NULL
+c7 geometry YES NULL
+c8 geometry YES UNI NULL
+#check the data after modify
+SELECT ST_AsText(c1) FROM tab;
+ST_AsText(c1)
+POINT(10 10)
+SELECT ST_AsText(c2) FROM tab;
+ST_AsText(c2)
+LINESTRING(10 10,20 20,30 30)
+SELECT ST_AsText(c3) FROM tab;
+ST_AsText(c3)
+POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
+SELECT ST_AsText(c4) FROM tab;
+ST_AsText(c4)
+MULTIPOINT(0 0,5 5,10 10,20 20)
+SELECT ST_AsText(c5) FROM tab;
+ST_AsText(c5)
+MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
+SELECT ST_AsText(c6) FROM tab;
+ST_AsText(c6)
+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)))
+SELECT ST_AsText(c7) FROM tab;
+ST_AsText(c7)
+GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
+SELECT ST_AsText(c8) From tab;
+ST_AsText(c8)
+GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
+#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;
+#check column datatypes
+SHOW FIELDS FROM tab;
+Field Type Null Key Default Extra
+c1 point YES NULL
+c2 linestring YES MUL NULL
+c3 polygon YES MUL NULL
+c4 multipoint YES NULL
+c5 multilinestring YES NULL
+c6 multipolygon YES NULL
+c7 geometrycollection YES NULL
+c8 geometry YES UNI NULL
+#check the data after modify
+SELECT ST_AsText(c1) FROM tab;
+ST_AsText(c1)
+POINT(10 10)
+SELECT ST_AsText(c2) FROM tab;
+ST_AsText(c2)
+LINESTRING(10 10,20 20,30 30)
+SELECT ST_AsText(c3) FROM tab;
+ST_AsText(c3)
+POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
+SELECT ST_AsText(c4) FROM tab;
+ST_AsText(c4)
+MULTIPOINT(0 0,5 5,10 10,20 20)
+SELECT ST_AsText(c5) FROM tab;
+ST_AsText(c5)
+MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
+SELECT ST_AsText(c6) FROM tab;
+ST_AsText(c6)
+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)))
+SELECT ST_AsText(c7) FROM tab;
+ST_AsText(c7)
+GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
+SELECT ST_AsText(c8) From tab;
+ST_AsText(c8)
+GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
+#check change the column type
+ALTER TABLE tab MODIFY COLUMN c1 BLOB;
+#check column datatypes
+SHOW CREATE TABLE tab;
+Table Create Table
+tab CREATE TABLE `tab` (
+ `c1` blob DEFAULT NULL,
+ `c2` linestring DEFAULT NULL,
+ `c3` polygon DEFAULT NULL,
+ `c4` multipoint DEFAULT NULL,
+ `c5` multilinestring DEFAULT NULL,
+ `c6` multipolygon DEFAULT NULL,
+ `c7` geometrycollection DEFAULT NULL,
+ `c8` geometry DEFAULT NULL,
+ UNIQUE KEY `idx2` (`c8`(5)),
+ KEY `idx1` (`c2`(5) DESC) USING BTREE,
+ KEY `idx3` (`c3`(5)) USING BTREE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+#check the data after modify
+SELECT ST_AsText(c1) FROM tab;
+ST_AsText(c1)
+POINT(10 10)
+#change the column name
+ALTER TABLE tab CHANGE COLUMN c1 c0 GEOMETRY ;
+#check column datatypes
+SHOW FIELDS FROM tab;
+Field Type Null Key Default Extra
+c0 geometry YES NULL
+c2 linestring YES MUL NULL
+c3 polygon YES MUL NULL
+c4 multipoint YES NULL
+c5 multilinestring YES NULL
+c6 multipolygon YES NULL
+c7 geometrycollection YES NULL
+c8 geometry YES UNI NULL
+#add primary key
+ALTER TABLE tab ADD PRIMARY KEY pk2(c8(5));
+Warnings:
+Warning 1280 Name 'pk2' ignored for PRIMARY key.
+#check columns
+SHOW FIELDS FROM tab;
+Field Type Null Key Default Extra
+c0 geometry YES NULL
+c2 linestring YES MUL NULL
+c3 polygon YES MUL NULL
+c4 multipoint YES NULL
+c5 multilinestring YES NULL
+c6 multipolygon YES NULL
+c7 geometrycollection YES NULL
+c8 geometry NO PRI NULL
+#drop key
+ALTER TABLE tab DROP PRIMARY KEY;
+#check columns
+SHOW FIELDS FROM tab;
+Field Type Null Key Default Extra
+c0 geometry YES NULL
+c2 linestring YES MUL NULL
+c3 polygon YES MUL NULL
+c4 multipoint YES NULL
+c5 multilinestring YES NULL
+c6 multipolygon YES NULL
+c7 geometrycollection YES NULL
+c8 geometry NO UNI NULL
+#cleanup the table
+TRUNCATE TABLE tab;
+#check with procedures
+#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 |
+#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))');
+#call the proc
+CALL geom_insert(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
+#check the values ofter proc call
+SELECT ST_AsText(c0) FROM tab;
+ST_AsText(c0)
+POINT(10 10)
+SELECT ST_AsText(c2) FROM tab;
+ST_AsText(c2)
+LINESTRING(10 10,20 20,30 30)
+SELECT ST_AsText(c3) FROM tab;
+ST_AsText(c3)
+POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
+SELECT ST_AsText(c4) FROM tab;
+ST_AsText(c4)
+MULTIPOINT(0 0,5 5,10 10,20 20)
+SELECT ST_AsText(c5) FROM tab;
+ST_AsText(c5)
+MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
+SELECT ST_AsText(c6) FROM tab;
+ST_AsText(c6)
+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)))
+SELECT ST_AsText(c7) FROM tab;
+ST_AsText(c7)
+GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
+SELECT ST_AsText(c8) From tab;
+ST_AsText(c8)
+GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
+#set the input spatial value
+SET @c9=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
+#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|
+#call the proc and then change the spatial value of c8
+CALL geominout(@c9);
+ST_AsText(@c9)
+GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
+#now check the out spatial values should be changed to new values
+SELECT ST_AsText(@c9);
+ST_AsText(@c9)
+GEOMETRYCOLLECTION(POINT(10 10),LINESTRING(20 20,30 30))
+#delete the records
+TRUNCATE TABLE tab;
+#create another table same as tab
+CREATE TABLE tab2 AS SELECT * FROM tab;
+#check the table definition
+SHOW CREATE TABLE tab2;
+Table Create Table
+tab2 CREATE TABLE `tab2` (
+ `c0` geometry DEFAULT NULL,
+ `c2` linestring DEFAULT NULL,
+ `c3` polygon DEFAULT NULL,
+ `c4` multipoint DEFAULT NULL,
+ `c5` multilinestring DEFAULT NULL,
+ `c6` multipolygon DEFAULT NULL,
+ `c7` geometrycollection DEFAULT NULL,
+ `c8` geometry NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+#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|
+#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))');
+#Insert the spatial values
+INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
+#check the values whether populated
+SELECT ST_AsText(c0) FROM tab2;
+ST_AsText(c0)
+POINT(10 10)
+SELECT ST_AsText(c2) FROM tab2;
+ST_AsText(c2)
+LINESTRING(10 10,20 20,30 30)
+SELECT ST_AsText(c3) FROM tab2;
+ST_AsText(c3)
+POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
+SELECT ST_AsText(c4) FROM tab2;
+ST_AsText(c4)
+MULTIPOINT(0 0,5 5,10 10,20 20)
+SELECT ST_AsText(c5) FROM tab2;
+ST_AsText(c5)
+MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
+SELECT ST_AsText(c6) FROM tab2;
+ST_AsText(c6)
+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)))
+SELECT ST_AsText(c7) FROM tab2;
+ST_AsText(c7)
+GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
+SELECT ST_AsText(c8) From tab2;
+ST_AsText(c8)
+GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
+#check Cursor with Geometry
+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|
+# the following line was commented due the Bug#16282246
+# Once it is fixed, the comment will be removed.
+# right now bug fixing date is unknown.
+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;
+ST_AsText(a.c0) ST_AsText(a.c2) ST_AsText(a.c3)
+POINT(10 10) LINESTRING(10 10,20 20,30 30) POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
+#check equi join
+SELECT ST_AsText(a.c2),ST_AsText(b.c2) FROM tab a,tab2 b WHERE a.c2=b.c2;
+ST_AsText(a.c2) ST_AsText(b.c2)
+LINESTRING(10 10,20 20,30 30) LINESTRING(10 10,20 20,30 30)
+#check DELETE stmt with Where clause and a constant predicate
+DELETE FROM tab
+WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
+SELECT * FROM tab;
+c0 c2 c3 c4 c5 c6 c7 c8
+#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)')));
+#check the column should be modified to LINESTRING(10 10,20 20,30 30)
+SELECT ST_AsText(c8) From tab2;
+ST_AsText(c8)
+GEOMETRYCOLLECTION(POINT(10 10),LINESTRING(20 20,30 30))
+#check GIS datatypes with aggregate functions
+SELECT COUNT(ST_AsText(g)) FROM gis_point;
+COUNT(ST_AsText(g))
+4
+SELECT COUNT(ST_AsText(g)) FROM gis_line;
+COUNT(ST_AsText(g))
+3
+SELECT COUNT(ST_AsText(g)) FROM gis_polygon;
+COUNT(ST_AsText(g))
+3
+SELECT COUNT(ST_AsText(g)) FROM gis_multi_point;
+COUNT(ST_AsText(g))
+3
+SELECT COUNT(ST_AsText(g)) FROM gis_multi_line;
+COUNT(ST_AsText(g))
+3
+SELECT COUNT(ST_AsText(g)) FROM gis_multi_polygon;
+COUNT(ST_AsText(g))
+3
+SELECT COUNT(ST_AsText(g)) FROM gis_geometrycollection;
+COUNT(ST_AsText(g))
+3
+SELECT COUNT(ST_AsText(g)) FROM gis_geometry;
+COUNT(ST_AsText(g))
+22
+SELECT SUM(ST_AsText(g)) FROM gis_point;
+SUM(ST_AsText(g))
+0
+Warnings:
+Warning 1292 Truncated incorrect DOUBLE value: 'POINT(10 10)'
+Warning 1292 Truncated incorrect DOUBLE value: 'POINT(20 10)'
+Warning 1292 Truncated incorrect DOUBLE value: 'POINT(20 20)'
+Warning 1292 Truncated incorrect DOUBLE value: 'POINT(10 20)'
+SELECT SUM(ST_AsText(g)) FROM gis_line;
+SUM(ST_AsText(g))
+0
+Warnings:
+Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(0 0,0 10,10 0)'
+Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(10 10,20 10,20 20,10 20,10 10)'
+Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(10 10,40 10)'
+SELECT AVG(ST_AsText(g)) FROM gis_polygon;
+AVG(ST_AsText(g))
+0
+Warnings:
+Warning 1292 Truncated incorrect DOUBLE value: 'POLYGON((10 10,20 10,20 20,10 20,10 10))'
+Warning 1292 Truncated incorrect DOUBLE value: 'POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))'
+Warning 1292 Truncated incorrect DOUBLE value: 'POLYGON((0 0,30 0,30 30,0 0))'
+#here it show some string value no meaning
+SELECT MAX(ST_AsText(g)) FROM gis_multi_point;
+MAX(ST_AsText(g))
+MULTIPOINT(3 6,4 10)
+#here it show some string value no meaning
+SELECT MIN(ST_AsText(g)) FROM gis_multi_line;
+MIN(ST_AsText(g))
+MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
+SELECT STD(ST_AsText(g)) FROM gis_multi_polygon;
+STD(ST_AsText(g))
+0
+Warnings:
+Warning 1292 Truncated incorrect DOUBLE value: '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)))'
+Warning 1292 Truncated incorrect DOUBLE value: '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)))'
+Warning 1292 Truncated incorrect DOUBLE value: 'MULTIPOLYGON(((0 3,3 3,3 0,0 3)))'
+SELECT COUNT(ST_AsText(g)) FROM gis_geometrycollection;
+COUNT(ST_AsText(g))
+3
+SELECT AVG(ST_AsText(g)) FROM gis_geometry;
+AVG(ST_AsText(g))
+0
+Warnings:
+Warning 1292 Truncated incorrect DOUBLE value: 'POINT(10 10)'
+Warning 1292 Truncated incorrect DOUBLE value: 'POINT(20 10)'
+Warning 1292 Truncated incorrect DOUBLE value: 'POINT(20 20)'
+Warning 1292 Truncated incorrect DOUBLE value: 'POINT(10 20)'
+Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(0 0,0 10,10 0)'
+Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(10 10,20 10,20 20,10 20,10 10)'
+Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(10 10,40 10)'
+Warning 1292 Truncated incorrect DOUBLE value: 'POLYGON((10 10,20 10,20 20,10 20,10 10))'
+Warning 1292 Truncated incorrect DOUBLE value: 'POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))'
+Warning 1292 Truncated incorrect DOUBLE value: 'POLYGON((0 0,30 0,30 30,0 0))'
+Warning 1292 Truncated incorrect DOUBLE value: 'MULTIPOINT(0 0,10 10,10 20,20 20)'
+Warning 1292 Truncated incorrect DOUBLE value: 'MULTIPOINT(1 1,11 11,11 21,21 21)'
+Warning 1292 Truncated incorrect DOUBLE value: 'MULTIPOINT(3 6,4 10)'
+Warning 1292 Truncated incorrect DOUBLE value: 'MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))'
+Warning 1292 Truncated incorrect DOUBLE value: 'MULTILINESTRING((10 48,10 21,10 0))'
+Warning 1292 Truncated incorrect DOUBLE value: 'MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))'
+Warning 1292 Truncated incorrect DOUBLE value: '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)))'
+Warning 1292 Truncated incorrect DOUBLE value: '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)))'
+Warning 1292 Truncated incorrect DOUBLE value: 'MULTIPOLYGON(((0 3,3 3,3 0,0 3)))'
+Warning 1292 Truncated incorrect DOUBLE value: 'GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))'
+Warning 1292 Truncated incorrect DOUBLE value: 'GEOMETRYCOLLECTION(POINT(10 10),LINESTRING(10 10,20 20))'
+Warning 1292 Truncated incorrect DOUBLE value: 'GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))'
+#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;
+#check file format
+SHOW CREATE TABLE tab3;
+Table Create Table
+tab3 CREATE TABLE `tab3` (
+ `c1` point DEFAULT NULL,
+ `c2` linestring DEFAULT NULL,
+ `c3` polygon DEFAULT NULL,
+ `C4` multipoint DEFAULT NULL,
+ `c5` multilinestring DEFAULT NULL,
+ `c6` multipolygon DEFAULT NULL,
+ `c7` geometrycollection DEFAULT NULL,
+ `c8` geometry DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
+ALTER TABLE tab3 KEY_BLOCK_SIZE=16 ROW_FORMAT=Dynamic;
+#check file format
+SHOW CREATE TABLE tab3;
+Table Create Table
+tab3 CREATE TABLE `tab3` (
+ `c1` point DEFAULT NULL,
+ `c2` linestring DEFAULT NULL,
+ `c3` polygon DEFAULT NULL,
+ `C4` multipoint DEFAULT NULL,
+ `c5` multilinestring DEFAULT NULL,
+ `c6` multipolygon DEFAULT NULL,
+ `c7` geometrycollection DEFAULT NULL,
+ `c8` geometry DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=16
+#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;
+name pos mtype
+c1 0 14
+c2 1 14
+c3 2 14
+C4 3 14
+c5 4 14
+c6 5 14
+c7 6 14
+c8 7 14
+#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))');
+#Insert the spatial values
+INSERT INTO tab3 VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
+#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) ;
+#check equality predicate on the index columns
+EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
+WHERE c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tab3 ref idx1 idx1 8 const # Using where
+#check index with WKB function
+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)')));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tab3 ref idx1 idx1 8 const # Using where
+#check index with WKT function
+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))');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tab3 ref idx3 idx3 8 const # Using where
+#check index with WKB function
+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))')));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tab3 ref idx3 idx3 8 const # Using where
+#check index with WKT function
+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))');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tab3 const idx2 idx2 8 const #
+#check index with WKB function
+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))')));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tab3 const idx2 idx2 8 const #
+#check index with DELETE operation
+EXPLAIN DELETE FROM tab3
+WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tab3 range idx2 idx2 8 NULL # Using where
+#check the spatial values
+SELECT ST_AsText(c1) FROM tab3;
+ST_AsText(c1)
+POINT(10 10)
+SELECT ST_AsText(c2) FROM tab3;
+ST_AsText(c2)
+LINESTRING(10 10,20 20,30 30)
+SELECT ST_AsText(c3) FROM tab3;
+ST_AsText(c3)
+POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
+SELECT ST_AsText(c4) FROM tab3;
+ST_AsText(c4)
+MULTIPOINT(0 0,5 5,10 10,20 20)
+SELECT ST_AsText(c5) FROM tab3;
+ST_AsText(c5)
+MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
+SELECT ST_AsText(c6) FROM tab3;
+ST_AsText(c6)
+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)))
+SELECT ST_AsText(c7) FROM tab3;
+ST_AsText(c7)
+GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
+SELECT ST_AsText(c8) From tab3;
+ST_AsText(c8)
+GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
+#check with Row_format = Dynamic
+ALTER TABLE tab3 ROW_FORMAT=Dynamic;
+#check file format
+SHOW CREATE TABLE tab3;
+Table Create Table
+tab3 CREATE TABLE `tab3` (
+ `c1` point DEFAULT NULL,
+ `c2` linestring DEFAULT NULL,
+ `c3` polygon DEFAULT NULL,
+ `C4` multipoint DEFAULT NULL,
+ `c5` multilinestring DEFAULT NULL,
+ `c6` multipolygon DEFAULT NULL,
+ `c7` geometrycollection DEFAULT NULL,
+ `c8` geometry DEFAULT NULL,
+ UNIQUE KEY `idx2` (`c8`(5)),
+ KEY `idx1` (`c2`(5) DESC) USING BTREE,
+ KEY `idx3` (`c3`(5)) USING BTREE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=16
+#check index with WKB function
+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)')));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tab3 ref idx1 idx1 8 const # Using where
+#check index with WKT function
+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))');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tab3 ref idx3 idx3 8 const # Using where
+#check the Geometry property functions
+SELECT fid, ST_Dimension(g) FROM gis_geometry;
+fid ST_Dimension(g)
+101 0
+102 0
+103 0
+104 0
+105 1
+106 1
+107 1
+108 2
+109 2
+110 2
+111 0
+112 0
+113 0
+114 1
+115 1
+116 1
+117 2
+118 2
+119 2
+120 1
+121 1
+122 1
+SELECT fid, ST_GeometryType(g) FROM gis_geometry;
+fid ST_GeometryType(g)
+101 POINT
+102 POINT
+103 POINT
+104 POINT
+105 LINESTRING
+106 LINESTRING
+107 LINESTRING
+108 POLYGON
+109 POLYGON
+110 POLYGON
+111 MULTIPOINT
+112 MULTIPOINT
+113 MULTIPOINT
+114 MULTILINESTRING
+115 MULTILINESTRING
+116 MULTILINESTRING
+117 MULTIPOLYGON
+118 MULTIPOLYGON
+119 MULTIPOLYGON
+120 GEOMETRYCOLLECTION
+121 GEOMETRYCOLLECTION
+122 GEOMETRYCOLLECTION
+SELECT fid, ST_IsEmpty(g) FROM gis_geometry;
+fid ST_IsEmpty(g)
+101 0
+102 0
+103 0
+104 0
+105 0
+106 0
+107 0
+108 0
+109 0
+110 0
+111 0
+112 0
+113 0
+114 0
+115 0
+116 0
+117 0
+118 0
+119 0
+120 0
+121 0
+122 0
+SELECT fid, ST_AsText(ST_Envelope(g)) FROM gis_geometry;
+fid ST_AsText(ST_Envelope(g))
+101 POLYGON((10 10,10 10,10 10,10 10,10 10))
+102 POLYGON((20 10,20 10,20 10,20 10,20 10))
+103 POLYGON((20 20,20 20,20 20,20 20,20 20))
+104 POLYGON((10 20,10 20,10 20,10 20,10 20))
+105 POLYGON((0 0,10 0,10 10,0 10,0 0))
+106 POLYGON((10 10,20 10,20 20,10 20,10 10))
+107 POLYGON((10 10,40 10,40 10,10 10,10 10))
+108 POLYGON((10 10,20 10,20 20,10 20,10 10))
+109 POLYGON((0 0,50 0,50 50,0 50,0 0))
+110 POLYGON((0 0,30 0,30 30,0 30,0 0))
+111 POLYGON((0 0,20 0,20 20,0 20,0 0))
+112 POLYGON((1 1,21 1,21 21,1 21,1 1))
+113 POLYGON((3 6,4 6,4 10,3 10,3 6))
+114 POLYGON((10 0,16 0,16 48,10 48,10 0))
+115 POLYGON((10 0,10 0,10 48,10 48,10 0))
+116 POLYGON((1 2,21 2,21 8,1 8,1 2))
+117 POLYGON((28 0,84 0,84 42,28 42,28 0))
+118 POLYGON((28 0,84 0,84 42,28 42,28 0))
+119 POLYGON((0 0,3 0,3 3,0 3,0 0))
+120 POLYGON((0 0,10 0,10 10,0 10,0 0))
+121 POLYGON((10 10,20 10,20 20,10 20,10 10))
+122 POLYGON((3 6,44 6,44 9,3 9,3 6))
+explain extended select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_geometry ALL NULL NULL NULL NULL # 100.00
+Warnings:
+Note 1003 select st_dimension(`test`.`gis_geometry`.`g`) AS `ST_Dimension(g)`,st_geometrytype(`test`.`gis_geometry`.`g`) AS `ST_GeometryType(g)`,st_isempty(`test`.`gis_geometry`.`g`) AS `ST_IsEmpty(g)`,st_astext(st_envelope(`test`.`gis_geometry`.`g`)) AS `ST_AsText(ST_Envelope(g))` from `test`.`gis_geometry`
+#check Geometry point functions
+SELECT fid, ST_X(g) FROM gis_point;
+fid ST_X(g)
+101 10
+102 20
+103 20
+104 10
+SELECT fid, ST_Y(g) FROM gis_point;
+fid ST_Y(g)
+101 10
+102 10
+103 20
+104 20
+explain extended select ST_X(g),ST_Y(g) FROM gis_point;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_point ALL NULL NULL NULL NULL # 100.00
+Warnings:
+Note 1003 select st_x(`test`.`gis_point`.`g`) AS `ST_X(g)`,st_y(`test`.`gis_point`.`g`) AS `ST_Y(g)` from `test`.`gis_point`
+SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line;
+fid ST_AsText(ST_StartPoint(g))
+105 POINT(0 0)
+106 POINT(10 10)
+107 POINT(10 10)
+SELECT fid, ST_AsText(ST_EndPoint(g)) FROM gis_line;
+fid ST_AsText(ST_EndPoint(g))
+105 POINT(10 0)
+106 POINT(10 10)
+107 POINT(40 10)
+SELECT fid, ST_Length(g) FROM gis_line;
+fid ST_Length(g)
+105 24.14213562373095
+106 40
+107 30
+SELECT fid, ST_NumPoints(g) FROM gis_line;
+fid ST_NumPoints(g)
+105 3
+106 5
+107 2
+SELECT fid, ST_AsText(ST_PointN(g, 2)) FROM gis_line;
+fid ST_AsText(ST_PointN(g, 2))
+105 POINT(0 10)
+106 POINT(20 10)
+107 POINT(40 10)
+SELECT fid, ST_IsClosed(g) FROM gis_line;
+fid ST_IsClosed(g)
+105 0
+106 1
+107 0
+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;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_line ALL NULL NULL NULL NULL # 100.00
+Warnings:
+Note 1003 select st_astext(st_startpoint(`test`.`gis_line`.`g`)) AS `ST_AsText(ST_StartPoint(g))`,st_astext(st_endpoint(`test`.`gis_line`.`g`)) AS `ST_AsText(ST_EndPoint(g))`,st_length(`test`.`gis_line`.`g`) AS `ST_Length(g)`,st_numpoints(`test`.`gis_line`.`g`) AS `ST_NumPoints(g)`,st_astext(st_pointn(`test`.`gis_line`.`g`,2)) AS `ST_AsText(ST_PointN(g, 2))`,st_isclosed(`test`.`gis_line`.`g`) AS `ST_IsClosed(g)` from `test`.`gis_line`
+SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon;
+fid ST_AsText(ST_Centroid(g))
+108 POINT(15 15)
+109 POINT(25.416666666666668 25.416666666666668)
+110 POINT(20 10)
+SELECT fid, ST_Area(g) FROM gis_polygon;
+fid ST_Area(g)
+108 100
+109 2400
+110 450
+SELECT fid, ST_AsText(ST_ExteriorRing(g)) FROM gis_polygon;
+fid ST_AsText(ST_ExteriorRing(g))
+108 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+109 LINESTRING(0 0,50 0,50 50,0 50,0 0)
+110 LINESTRING(0 0,30 0,30 30,0 0)
+SELECT fid, ST_NumInteriorRings(g) FROM gis_polygon;
+fid ST_NumInteriorRings(g)
+108 0
+109 1
+110 0
+SELECT fid, ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
+fid ST_AsText(ST_InteriorRingN(g, 1))
+108 NULL
+109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+110 NULL
+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;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_polygon ALL NULL NULL NULL NULL # 100.00
+Warnings:
+Note 1003 select st_astext(st_centroid(`test`.`gis_polygon`.`g`)) AS `ST_AsText(ST_Centroid(g))`,st_area(`test`.`gis_polygon`.`g`) AS `ST_Area(g)`,st_astext(st_exteriorring(`test`.`gis_polygon`.`g`)) AS `ST_AsText(ST_ExteriorRing(g))`,st_numinteriorrings(`test`.`gis_polygon`.`g`) AS `ST_NumInteriorRings(g)`,st_astext(st_interiorringn(`test`.`gis_polygon`.`g`,1)) AS `ST_AsText(ST_InteriorRingN(g, 1))` from `test`.`gis_polygon`
+SELECT fid, ST_IsClosed(g) FROM gis_multi_line;
+fid ST_IsClosed(g)
+114 0
+115 0
+116 0
+SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_multi_polygon;
+fid ST_AsText(ST_Centroid(g))
+117 POINT(57.98031067576927 17.854754130800433)
+118 POINT(57.98031067576927 17.854754130800433)
+119 POINT(2 2)
+SELECT fid, ST_Area(g) FROM gis_multi_polygon;
+fid ST_Area(g)
+117 1684.5
+118 1684.5
+119 4.5
+SELECT fid, ST_NumGeometries(g) from gis_multi_point;
+fid ST_NumGeometries(g)
+111 4
+112 4
+113 2
+SELECT fid, ST_NumGeometries(g) from gis_multi_line;
+fid ST_NumGeometries(g)
+114 2
+115 1
+116 2
+SELECT fid, ST_NumGeometries(g) from gis_multi_polygon;
+fid ST_NumGeometries(g)
+117 2
+118 2
+119 1
+SELECT fid, ST_NumGeometries(g) from gis_geometrycollection;
+fid ST_NumGeometries(g)
+120 2
+121 2
+122 2
+explain extended SELECT fid, ST_NumGeometries(g) from gis_multi_point;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL # 100.00
+Warnings:
+Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,st_numgeometries(`test`.`gis_multi_point`.`g`) AS `ST_NumGeometries(g)` from `test`.`gis_multi_point`
+SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
+fid ST_AsText(ST_GeometryN(g, 2))
+111 POINT(10 10)
+112 POINT(11 11)
+113 POINT(4 10)
+SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_line;
+fid ST_AsText(ST_GeometryN(g, 2))
+114 LINESTRING(16 0,16 23,16 48)
+115 NULL
+116 LINESTRING(2 5,5 8,21 7)
+SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_polygon;
+fid ST_AsText(ST_GeometryN(g, 2))
+117 POLYGON((59 18,67 18,67 13,59 13,59 18))
+118 POLYGON((59 18,67 18,67 13,59 13,59 18))
+119 NULL
+SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_geometrycollection;
+fid ST_AsText(ST_GeometryN(g, 2))
+120 LINESTRING(0 0,10 10)
+121 LINESTRING(10 10,20 20)
+122 LINESTRING(3 6,7 9)
+SELECT fid, ST_AsText(ST_GeometryN(g, 1)) from gis_geometrycollection;
+fid ST_AsText(ST_GeometryN(g, 1))
+120 POINT(0 0)
+121 POINT(10 10)
+122 POINT(44 6)
+explain extended SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL # 100.00
+Warnings:
+Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,st_astext(st_geometryn(`test`.`gis_multi_point`.`g`,2)) AS `ST_AsText(ST_GeometryN(g, 2))` from `test`.`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;
+first second w c o e d t i r
+120 120 1 1 0 1 0 0 1 0
+120 121 0 0 0 0 0 1 1 1
+120 122 0 0 1 0 0 0 1 0
+121 120 0 0 0 0 0 1 1 1
+121 121 1 1 0 1 0 0 1 0
+121 122 0 0 0 0 1 0 0 0
+122 120 0 0 1 0 0 0 1 0
+122 121 0 0 0 0 1 0 0 0
+122 122 1 1 0 1 0 0 1 0
+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;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE g1 ALL NULL NULL NULL NULL # 100.00 Using temporary; Using filesort
+1 SIMPLE g2 ALL NULL NULL NULL NULL # 100.00 Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,mbrwithin(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,mbrcontains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,mbroverlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,mbrequals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,mbrdisjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,st_touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,mbrintersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,st_crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`
+# check support of Foreign Key constraint
+CREATE TABLE parent (id GEOMETRY NOT NULL,PRIMARY KEY (id(10))) ENGINE=INNODB;
+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;
+ERROR HY000: Can't create table `test`.`child` (errno: 150 "Foreign key constraint is incorrectly formed")
+#check partition table support
+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)
+);
+ERROR 42000: The storage engine for the table doesn't support GEOMETRY
+#check start transaction commit & Rollback
+START TRANSACTION;
+DELETE FROM tab3;
+SELECT * FROM tab3;
+c1 c2 c3 C4 c5 c6 c7 c8
+ROLLBACK;
+SELECT COUNT(*) FROM tab3;
+COUNT(*)
+1
+START TRANSACTION;
+DELETE FROM tab3;
+COMMIT;
+SELECT * FROM tab3;
+c1 c2 c3 C4 c5 c6 c7 c8
+DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point;
+DROP TABLE gis_multi_line, gis_multi_polygon;
+DROP TABLE gis_geometrycollection, gis_geometry;
+DROP TABLE tab,tab2,tab3,parent;
+DROP PROCEDURE geominout;
+DROP PROCEDURE geom_insert;
+DROP PROCEDURE geom_cursor;
+SET GLOBAL innodb_stats_persistent=@save_innodb_stats_persistent;
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-30 02:18:13 +0000