path: root/mysql-test/suite/innodb_gis/r/geometry.result

SET default_storage_engine=InnoDB;
SET innodb_strict_mode=OFF;
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	1	#
#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	1	#
#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	1	#
#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	1	#
#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	1	#
#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	1	#
#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	1	#
#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)) USING BTREE,
  KEY `idx3` (`c3`(5)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
#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
#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 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 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	1	#
#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	1	#
#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	1	#
#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	1	#
#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	1	#
#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	1	#
#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	1	#
#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)) USING BTREE,
  KEY `idx3` (`c3`(5)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 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	1	#
#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	1	#
#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	22	#	
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	4	#	
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	3	#	
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	3	#	
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	3	#	
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	3	#	
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	3	#	Using temporary; Using filesort
1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	3	#	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 HY000: A BLOB field is not allowed in partition function
#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;