Default page

Edit File: all_geometry_types_instantiable.result

# Creating the spatial objects
USE test;
CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT);
CREATE TABLE gis_linestring (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING);
CREATE TABLE gis_polygon (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON);
CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT);
CREATE TABLE gis_multi_linestring (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING);
CREATE TABLE gis_multi_polygon (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON);
CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION);
CREATE TABLE gis_geometry (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
# Displaying the created spatial columns
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_linestring;
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_linestring;
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	
# Inserting POINT Geometry Values 
INSERT INTO gis_point VALUES
(101, ST_POINTFROMTEXT('POINT(0 0)')),
(102, ST_POINTFROMTEXT('POINT(1 0)')),
(103, ST_POINTFROMTEXT('POINT(0 1)')),
(104, ST_POINTFROMTEXT('POINT(1 1)')),
(105, ST_POINTFROMTEXT('POINT(-1 1)'));
INSERT INTO gis_point VALUES
(106, ST_POINTFROMWKB(POINT(0,0))),
(107, ST_POINTFROMWKB(POINT(10,0))),
(108, ST_POINTFROMWKB(POINT(0,10))),
(109, ST_POINTFROMWKB(POINT(-10,0))),
(110, ST_POINTFROMWKB(POINT(0,-10)));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_point VALUES
(111, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(1 1)')))),
(112, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(1e308 1e308)')))),
(113, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(1e308 -1e308)')))),
(114, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(-1e308 1e308)')))),
(115, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(-1e308 -1e308)'))));
# Inserting LINESTRING Geometry Values 
INSERT INTO gis_linestring VALUES
(201, ST_LINEFROMTEXT('LINESTRING(0 0,5 5)')),
(202, ST_LINEFROMTEXT('LINESTRING(0 0,2 2,4 4)')),
(203, ST_LINEFROMTEXT('LINESTRING(0 0,5 5,10 10)'));
INSERT INTO gis_linestring VALUES
(204, ST_LINESTRINGFROMTEXT('LINESTRING(10 10,5 5)')),
(205, ST_LINESTRINGFROMTEXT('LINESTRING(0 0,12 12,24 24)')),
(206, ST_LINESTRINGFROMTEXT('LINESTRING(0 0,50 50,100 100)'));
INSERT INTO gis_linestring VALUES
(207, ST_LINEFROMWKB(LINESTRING(POINT(0,0), POINT(5,5)))),
(208, ST_LINEFROMWKB(LINESTRING(POINT(0,0), POINT(-5,-5), POINT(-10,10)))),
(209, ST_LINEFROMWKB(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6), POINT(8,8))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_linestring VALUES
(210, ST_LINESTRINGFROMWKB(LINESTRING(POINT(0,0), POINT(5,5)))),
(211, ST_LINESTRINGFROMWKB(LINESTRING(POINT(0,0), POINT(-50,-50), POINT(10,-10)))),
(212, ST_LINESTRINGFROMWKB(LINESTRING(POINT(0,0), POINT(1e308,1e308), POINT(1e308,-1e308))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_linestring VALUES
(213, ST_LINEFROMWKB(ST_ASWKB(ST_LINEFROMTEXT('LINESTRING(1e308 1e308,1e308 -1e308)')))),
(214, ST_LINEFROMWKB(ST_ASWKB(ST_LINEFROMTEXT('LINESTRING(1 1,2 2,3 3,4 4,5 5,6 6,7 7,8 8,9 9)')))),
(215, ST_LINESTRINGFROMWKB(ST_ASWKB(ST_LINESTRINGFROMTEXT('LINESTRING(10 10,10 -10,-10 -10,-10 10,10 10)'))));
# Inserting POLYGON Geometry Values 
INSERT INTO gis_polygon VALUES
(301, ST_POLYFROMTEXT('POLYGON((0 0,0 5,5 5, 0 0))')),
(302, ST_POLYFROMTEXT('POLYGON((0 0,0 5,5 5,5 0,0 0))')),
(303, ST_POLYFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0))'));
INSERT INTO gis_polygon VALUES
(304, ST_POLYGONFROMTEXT('POLYGON((0 0,0 50,50 50,50 0,0 0))')),
(305, ST_POLYGONFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))')),
(306, ST_POLYGONFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(2 2,2 4,4 4,4 2,2 2),(6 6,6 8,8 8,8 6,6 6))'));
INSERT INTO gis_polygon VALUES
(307, ST_POLYFROMWKB(POLYGON(LINESTRING(POINT(0,0), POINT(0,5), POINT(5,5), POINT(0,0))))),
(308, ST_POLYFROMWKB(POLYGON(LINESTRING(POINT(0,0), POINT(0,15), POINT(15,15), POINT(15,0), POINT(0,0))))),
(309, ST_POLYFROMWKB(POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4)))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_polygon VALUES
(310, ST_POLYGONFROMWKB(POLYGON(LINESTRING(POINT(0,0), POINT(0,5), POINT(5,5), POINT(0,0))))),
(311, ST_POLYGONFROMWKB(POLYGON(LINESTRING(POINT(10,10), POINT(10,15), POINT(15,15), POINT(15,10), POINT(10,10))))),
(312, ST_POLYGONFROMWKB(POLYGON(LINESTRING(POINT(10,10), POINT(10,20), POINT(20,20), POINT(20,10), POINT(10,10)),
LINESTRING(POINT(14,14), POINT(14,16), POINT(16,16), POINT(16,14), POINT(14,14)))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_polygon VALUES
(313, ST_POLYFROMWKB(ST_ASWKB(ST_POLYFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,5 5,0 0))')))),
(314, ST_POLYFROMWKB(ST_ASWKB(ST_POLYGONFROMTEXT('POLYGON((10 0,10 10,0 10,-10 10,-10 0,-10 -10,0 10,10 -10,10 0))')))),
(315, ST_POLYGONFROMWKB(ST_ASWKB(ST_POLYGONFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(2 2,2 4,4 4,4 2,2 2),(6 6,6 8,8 8,8 6,6 6))'))));
# Inserting MULTIPOINT Geometry Values 
INSERT INTO gis_multi_point VALUES
(401, ST_MPOINTFROMTEXT('MULTIPOINT(0 0)')),
(402, ST_MPOINTFROMTEXT('MULTIPOINT(0 0,2 2,4 4)')),
(403, ST_MPOINTFROMTEXT('MULTIPOINT(0 0,5 5,10 10)'));
INSERT INTO gis_multi_point VALUES
(404, ST_MULTIPOINTFROMTEXT('MULTIPOINT(0 0,100 100)')),
(405, ST_MULTIPOINTFROMTEXT('MULTIPOINT(0 0,1e308 1e308)')),
(406, ST_MULTIPOINTFROMTEXT('MULTIPOINT(1e308 1e308,1e308 -1e308,-1e308 1e308,-1e308 -1e308)'));
INSERT INTO gis_multi_point VALUES
(407, ST_MPOINTFROMWKB(MULTIPOINT(POINT(0,0)))),
(408, ST_MPOINTFROMWKB(MULTIPOINT(POINT(0,0), POINT(10,10)))),
(409, ST_MPOINTFROMWKB(MULTIPOINT(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_multi_point VALUES
(410, ST_MULTIPOINTFROMWKB(MULTIPOINT(POINT(0,0)))),
(411, ST_MULTIPOINTFROMWKB(MULTIPOINT(POINT(0,0), POINT(1e308,1e308)))),
(412, ST_MULTIPOINTFROMWKB(MULTIPOINT(POINT(1e308,1e308), POINT(-1e308,1e308), POINT(1e308,-1e308), POINT(-1e308,-1e308))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_multi_point VALUES
(413, ST_MPOINTFROMWKB(ST_ASWKB(ST_MPOINTFROMTEXT('MULTIPOINT(0 0)')))),
(414, ST_MPOINTFROMWKB(ST_ASWKB(ST_MPOINTFROMTEXT('MULTIPOINT(0 0,1e308 1e308,-1e308 -1e308)')))),
(415, ST_MPOINTFROMWKB(ST_ASWKB(ST_MPOINTFROMTEXT('MULTIPOINT(1e308 1e308,1e308 -1e308,-1e308 1e308,-1e308 -1e308,1e308 1e308)'))));
# Inserting MULTILINESTRING Geometry Values 
INSERT INTO gis_multi_linestring VALUES
(501, ST_MLINEFROMTEXT('MULTILINESTRING((0 0,2 2))')),
(502, ST_MLINEFROMTEXT('MULTILINESTRING((0 0,2 2,4 4))')),
(503, ST_MLINEFROMTEXT('MULTILINESTRING((0 0,2 2,4 4),(6 6,8 8,10 10))'));
INSERT INTO gis_multi_linestring VALUES
(504, ST_MULTILINESTRINGFROMTEXT('MULTILINESTRING((0 0,100 100,-100 -100))')),
(505, ST_MULTILINESTRINGFROMTEXT('MULTILINESTRING((1e308 1e308,-1e308 -1e308))')),
(506, ST_MULTILINESTRINGFROMTEXT('MULTILINESTRING((1e308 1e308,-1e308 -1e308),(1e308 -1e308,-1e308 1e308))'));
INSERT INTO gis_multi_linestring VALUES
(507, ST_MLINEFROMWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2))))),
(508, ST_MLINEFROMWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(12,12), POINT(24,24))))),
(509, ST_MLINEFROMWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4)),
LINESTRING(POINT(6,6), POINT(8,8), POINT(10,10)))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_multi_linestring VALUES
(510, ST_MULTILINESTRINGFROMWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4))))),
(511, ST_MULTILINESTRINGFROMWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(1,1), POINT(2,2))))),
(512, ST_MULTILINESTRINGFROMWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(12,12), POINT(24,24)),
LINESTRING(POINT(36,36), POINT(48,48), POINT(50,50)))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_multi_linestring VALUES
(513, ST_MLINEFROMWKB(ST_ASWKB(ST_MLINEFROMTEXT('MULTILINESTRING((0 0,10 10),(0 10,10 0))')))),
(514, ST_MLINEFROMWKB(ST_ASWKB(ST_MULTILINESTRINGFROMTEXT('MULTILINESTRING((0 0,10 10,-10 10,0 0),(0 0,-10 -10,10 -10,0 0))')))),
(515, ST_MULTILINESTRINGFROMWKB(ST_ASWKB(ST_MULTILINESTRINGFROMTEXT('MULTILINESTRING((0 0,0 100),(0 0,100 0),(0 0,0 -100),(0 0,-100 0))'))));
# Inserting MULTIPOLGYON Geometry Values 
INSERT INTO gis_multi_polygon VALUES
(601, ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,0 0)))')),
(602, ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,0 0)),((5 5,5 10,10 10,5 5)))')),
(603, ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4)))'));
INSERT INTO gis_multi_polygon VALUES
(604, ST_MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5, 0 0)))')),
(605, ST_MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4, 4 4)),((0 0,0 -2,-2 -2, 0 0)))')),
(606, ST_MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((0 0,5 5,-5 5,0 0)),((0 0,-5 -5,5 -5,0 0)))'));
INSERT INTO gis_multi_polygon VALUES
(607, ST_MPOLYFROMWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(5,0), POINT(5,5), POINT(0,5), POINT(0,0)))))),
(608, ST_MPOLYFROMWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(10,0), POINT(10,10), POINT(0,10), POINT(0,0)),
LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4)))))),
(609, ST_MPOLYFROMWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(5,0), POINT(5,5), POINT(0,5), POINT(0,0))),
POLYGON(LINESTRING(POINT(0,0), POINT(-5,0), POINT(-5,-5), POINT(0,-5), POINT(0,0))))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_multi_polygon VALUES
(610, ST_MULTIPOLYGONFROMWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(-5,0), POINT(-5,-5), POINT(0,-5), POINT(0,0)))))),
(611, ST_MULTIPOLYGONFROMWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(10,10), POINT(20,10), POINT(20,20), POINT(10,20), POINT(10,10)),
LINESTRING(POINT(14,14), POINT(14,16), POINT(16,16), POINT(16,14), POINT(14,14)))))),
(612, ST_MULTIPOLYGONFROMWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))),
POLYGON(LINESTRING(POINT(0,0), POINT(-5,0), POINT(-5,-5), POINT(0,-5), POINT(0,0))))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_multi_polygon VALUES
(613, ST_MPOLYFROMWKB(ST_ASWKB(ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,5 5,5 -5,0 0)),((0 0,-5 5,-5 -5,0 0)))')))),
(614, ST_MPOLYFROMWKB(ST_ASWKB(ST_MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((0 0,10 10,-10 10,0 0)),((0 0,-10 -10,10 -10,0 0)))')))),
(615, ST_MULTIPOLYGONFROMWKB(ST_ASWKB(ST_MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((0 0,5 5,10 0,5 -5,0 0)))'))));
# Inserting GEOMETRYCOLLECTION Geometry Values 
INSERT INTO gis_geometrycollection VALUES
(701, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))')),
(702, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
(703, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(5 5), POLYGON((0 0,0 10,10 10,10 0,0 0)))'));
INSERT INTO gis_geometrycollection VALUES
(704, ST_GEOMETRYCOLLECTIONFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
(705, ST_GEOMETRYCOLLECTIONFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),'
                                                           'LINESTRING(0 0,10 10),'
                                                           'POLYGON((0 0,0 10,10 10,10 0, 0 0)))')),
(706, ST_GEOMETRYCOLLECTIONFROMTEXT('GEOMETRYCOLLECTION(MULTIPOINT(0 0,5 5,10 10),'
                                                           'MULTILINESTRING((0 0,10 10),(0 10,10 0)),'
                                                           'MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0)),'
                                                                        '((0 0,-10 0,-10 -10,0 -10,0 0))))'));
INSERT INTO gis_geometrycollection VALUES
(707, ST_GEOMCOLLFROMWKB(GEOMETRYCOLLECTION(POINT(0,0)))),
(708, ST_GEOMCOLLFROMWKB(GEOMETRYCOLLECTION(POINT(0,0),
LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6), POINT(8,8)),
POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4)))))),
(709, ST_GEOMCOLLFROMWKB(GEOMETRYCOLLECTION(MULTIPOINT(POINT(0,0), POINT(5,5), POINT(10,10)),
MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6), POINT(8,8)),
LINESTRING(POINT(10,10), POINT(5,5), POINT(0,10))),
MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))),
POLYGON(LINESTRING(POINT(0,0), POINT(-10,0), POINT(-10,-10), POINT(0,0)))))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_geometrycollection VALUES
(710, ST_GEOMETRYCOLLECTIONFROMWKB(GEOMETRYCOLLECTION(POINT(0,0),
LINESTRING(POINT(0,0), POINT(100,100))))),
(711, ST_GEOMETRYCOLLECTIONFROMWKB(GEOMETRYCOLLECTION(POINT(10,10),
LINESTRING(POINT(10,10), POINT(12,12), POINT(14,14), POINT(16,16), POINT(18,18)),
POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4)))))),
(712, ST_GEOMETRYCOLLECTIONFROMWKB(GEOMETRYCOLLECTION(MULTIPOINT(POINT(10,10), POINT(15,15), POINT(20,20)),
MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6), POINT(8,8)),
LINESTRING(POINT(10,10), POINT(5,5), POINT(0,10))),
MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))),
POLYGON(LINESTRING(POINT(0,0), POINT(-10,0), POINT(-10,-10), POINT(0,0)))))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_geometrycollection VALUES
(713, ST_GEOMCOLLFROMWKB(ST_ASWKB(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION('
                                                                'POINT(0 0),'
                                                                'LINESTRING(0 0,10 10),'
                                                                'POLYGON((0 0,0 10,10 10,10 0, 0 0)),'
                                                                'MULTIPOINT(0 0,2 2,4 4,6 6,8 8,10 10),'
                                                                'MULTILINESTRING((0 0,10 10),(0 10,10 0)),'
                                                                'MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5))))')))),
(714, ST_GEOMCOLLFROMWKB(ST_ASWKB(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION('
                                                                'GEOMETRYCOLLECTION('
                                                                      'POINT(0 0)),'
                                                                'GEOMETRYCOLLECTION('
                                                                      'LINESTRING(0 0,10 10)),'
                                                                'GEOMETRYCOLLECTION('
                                                                      'POLYGON((0 0,0 10,10 10,10 0, 0 0))),'
                                                                'GEOMETRYCOLLECTION('
                                                                       'MULTIPOINT(0 0,2 2,4 4,6 6,8 8,10 10)),'
                                                                'GEOMETRYCOLLECTION('
                                                                       'MULTILINESTRING((0 0,10 10),(0 10,10 0))),'
                                                                'GEOMETRYCOLLECTION('
                                                                       'MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5)))))')))),
(715, ST_GEOMCOLLFROMWKB(ST_ASWKB(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION('
                                                                'GEOMETRYCOLLECTION(),'
                                                                'POINT(0 0),'
                                                                'GEOMETRYCOLLECTION('
                                                                      'LINESTRING(0 0,10 10),'
                                                                      'GEOMETRYCOLLECTION('
                                                                            'GEOMETRYCOLLECTION())),'
                                                                'GEOMETRYCOLLECTION(),'
                                                                'GEOMETRYCOLLECTION('
                                                                       'GEOMETRYCOLLECTION()),'
                                                                'POLYGON((0 0,0 10,10 10,10 0, 0 0)),'
                                                                'MULTIPOINT(0 0,2 2,4 4,6 6,8 8,10 10),'
                                                                'MULTILINESTRING((0 0,10 10),(0 10,10 0)),'
                                                                'MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5))))'))));
# Inserting all kinds of spatial value to the GEOMETRY class
INSERT INTO gis_geometry VALUES
(901, ST_GEOMFROMWKB(POINT(0,0))),
(902, ST_GEOMETRYFROMWKB(POINT(1e308,-1e308)));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_geometry VALUES
(903, ST_GEOMFROMWKB(LINESTRING(POINT(0,0), POINT(5,5), POINT(10,10)))),
(904, ST_GEOMETRYFROMWKB(LINESTRING(POINT(10,0), POINT(0,10), POINT(-10,0), POINT(0,-10))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_geometry VALUES
(905, ST_GEOMFROMWKB(POLYGON(LINESTRING(POINT(0,0), POINT(0,5), POINT(5,5), POINT(5,0), POINT(0,0))))),
(906, ST_GEOMETRYFROMWKB(POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4)))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_geometry VALUES
(907, ST_GEOMFROMTEXT('MULTIPOINT(0 0,2 2,4 4,6 6,8 8,10 10)')),
(908, ST_GEOMETRYFROMTEXT('MULTIPOINT(1e308 1e308, -1e308 1e308, 1e308 -1e308, -1e308 -1e308)'));
INSERT INTO gis_geometry VALUES
(909, ST_GEOMFROMTEXT('MULTILINESTRING((0 0,10 10),(0 0,-10 -10))')),
(910, ST_GEOMETRYFROMTEXT('MULTILINESTRING((0 0,10 0),(0 0,0 10),(0 0,-10 0),(0 0,0 -10))'));
INSERT INTO gis_geometry VALUES
(911, ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)))')),
(912, ST_GEOMETRYFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,1 10,10 5,5 5)))'));
INSERT INTO gis_geometry VALUES
(913, ST_GEOMFROMTEXT('GEOMETRYCOLLECTION('
                                'POINT(0 0),'
                                'LINESTRING(0 0,5 5,10 10),'
                                'POLYGON((0 0,0 10,10 10,10 0,0 0)))')),
(924, ST_GEOMFROMTEXT('GEOMETRYCOLLECTION('
                                'MULTIPOINT(0 0,2 2,4 4),'
                                'MULTILINESTRING((0 0,5 5,0 0),(0 0,-5 -5,0 0)),'
                                'MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5))))')),
(915, ST_GEOMETRYFROMTEXT('GEOMETRYCOLLECTION('
                                    'POINT(0 0),'
                                    'LINESTRING(0 0,5 5,10 10),'
                                    'POLYGON((0 0,0 10,10 10,10 0,0 0)),'
                                    'MULTIPOINT(0 0,2 2,4 4),'
                                    'MULTILINESTRING((0 0,5 5,0 0),(0 0,-5 -5,0 0)),'
                                    'MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5))))'));
# Check the parent class hierarchy by inserting all values to the GEOMETRY parent class.
INSERT INTO gis_geometry SELECT * FROM gis_point;
INSERT INTO gis_geometry SELECT * FROM gis_linestring;
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_linestring;
INSERT INTO gis_geometry SELECT * FROM gis_multi_polygon;
INSERT INTO gis_geometry SELECT * FROM gis_geometrycollection;
# Checking the integrity of the above insert statements 
SELECT COUNT(g) FROM gis_geometry;
COUNT(g)
120
SELECT COUNT(ST_ASTEXT(g)) FROM gis_geometry;
COUNT(ST_ASTEXT(g))
120
SELECT COUNT(DISTINCT(g)) FROM gis_geometry;
COUNT(DISTINCT(g))
96
SELECT COUNT(DISTINCT(ST_ASTEXT(g))) FROM gis_geometry;
COUNT(DISTINCT(ST_ASTEXT(g)))
96
# Displaying the created spatial columns
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_linestring;
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_linestring;
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	
# Analyzing the spatial objets
ANALYZE TABLE gis_point;
Table	Op	Msg_type	Msg_text
test.gis_point	analyze	status	OK
ANALYZE TABLE gis_linestring;
Table	Op	Msg_type	Msg_text
test.gis_linestring	analyze	status	OK
ANALYZE TABLE gis_polygon;
Table	Op	Msg_type	Msg_text
test.gis_polygon	analyze	status	OK
ANALYZE TABLE gis_multi_point;
Table	Op	Msg_type	Msg_text
test.gis_multi_point	analyze	status	OK
ANALYZE TABLE gis_multi_linestring;
Table	Op	Msg_type	Msg_text
test.gis_multi_linestring	analyze	status	OK
ANALYZE TABLE gis_multi_polygon;
Table	Op	Msg_type	Msg_text
test.gis_multi_polygon	analyze	status	OK
ANALYZE TABLE gis_geometrycollection;
Table	Op	Msg_type	Msg_text
test.gis_geometrycollection	analyze	status	OK
ANALYZE TABLE gis_geometry;
Table	Op	Msg_type	Msg_text
test.gis_geometry	analyze	status	OK
# 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);
SELECT sc.COLUMN_NAME, sc.ORDINAL_POSITION, sc.DATA_TYPE, sc.COLUMN_TYPE
FROM information_schema.columns sc INNER JOIN information_schema.tables st
ON sc.TABLE_NAME=st.TABLE_NAME WHERE st.TABLE_NAME='tab' ORDER BY sc.COLUMN_NAME;
COLUMN_NAME	ORDINAL_POSITION	DATA_TYPE	COLUMN_TYPE
c1	1	point	point
c2	2	linestring	linestring
c3	3	polygon	polygon
c4	4	multipoint	multipoint
c5	5	multilinestring	multilinestring
c6	6	multipolygon	multipolygon
c7	7	geometrycollection	geometrycollection
c8	8	geometry	geometry
SET @c1 = ST_GEOMFROMTEXT('POINT(10 10)');
SET @c2 = ST_GEOMFROMTEXT('LINESTRING(10 10,20 20,30 30)');
SET @c3 = ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))');
SET @c4 = ST_GEOMFROMTEXT('MULTIPOINT(0 0,5 5,10 10,20 20)');
SET @c5 = ST_GEOMFROMTEXT('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
SET @c6 = ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5)))');
SET @c7 = ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
SET @c8 = ST_GEOMFROMTEXT('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 above inserted values
SELECT ST_ASTEXT(c1), ST_ASTEXT(c2), ST_ASTEXT(c3), ST_ASTEXT(c4), ST_ASTEXT(c5), ST_ASTEXT(c6), ST_ASTEXT(c7), ST_ASTEXT(c8) FROM tab;
ST_ASTEXT(c1)	ST_ASTEXT(c2)	ST_ASTEXT(c3)	ST_ASTEXT(c4)	ST_ASTEXT(c5)	ST_ASTEXT(c6)	ST_ASTEXT(c7)	ST_ASTEXT(c8)
POINT(10 10)	LINESTRING(10 10,20 20,30 30)	POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))	MULTIPOINT((0 0),(5 5),(10 10),(20 20))	MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))	MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5)))	GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))	GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
# Cleanup the table
TRUNCATE TABLE tab;
# Create a procedure 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 @c1 = ST_GEOMFROMTEXT('POINT(10 10)');
SET @c2 = ST_GEOMFROMTEXT('LINESTRING(10 10,20 20,30 30)');
SET @c3 = ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))');
SET @c4 = ST_GEOMFROMTEXT('MULTIPOINT(0 0,5 5,10 10,20 20)');
SET @c5 = ST_GEOMFROMTEXT('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
SET @c6 = ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5)))');
SET @c7 = ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
SET @c8 = ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))');
# Call the proc
CALL geom_insert(@c1, @c2, @c3, @c4, @c5, @c6, @c7, @c8);
# Set the input spatial value 
SET @c9 = ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(10 10), LINESTRING(12 12,13 13))');
# Crate a procedure with OUT params and pass a value.
CREATE PROCEDURE geom_out(OUT c9 GEOMETRY)
BEGIN
SELECT ST_ASTEXT(@c9);
END|
# Call the proc and then change the spatial value of c8
CALL geom_out(@c9);
ST_ASTEXT(@c9)
GEOMETRYCOLLECTION(POINT(10 10),LINESTRING(12 12,13 13))
# Dropping the created procedures
DROP PROCEDURE geom_insert;
DROP PROCEDURE geom_out;
# 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` (
  `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
# 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 @c1 = ST_GEOMFROMTEXT('POINT(10 10)');
SET @c2 = ST_GEOMFROMTEXT('LINESTRING(10 10,20 20,30 30)');
SET @c3 = ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))');
SET @c4 = ST_GEOMFROMTEXT('MULTIPOINT(0 0,5 5,10 10,20 20)');
SET @c5 = ST_GEOMFROMTEXT('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
SET @c6 = ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5)))');
SET @c7 = ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
SET @c8 = ST_GEOMFROMTEXT('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(c1) FROM tab2;
ST_ASTEXT(c1)
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,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))
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(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5)))
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))
# Cleaning up the trigger
DROP TRIGGER  geom_trigger;
# 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|
# Calling the cursor
CALL geom_cursor();
ST_ASTEXT(v)
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
# Dropping the created cursor
DROP PROCEDURE geom_cursor;
# Check self join
SELECT ST_ASTEXT(tableX.c1), ST_ASTEXT(tableX.c4), ST_ASTEXT(tableX.c3)
FROM tab AS tableX, tab2 AS tableY
WHERE tableX.c1 = tableY.c1 AND tableX.c3 = tableY.c3 AND tableX.c4 = tableY.c4 AND
tableX.c5 = tableY.c5 AND tableX.c7 = tableY.c7 AND tableX.c8 = tableY.c8;
ST_ASTEXT(tableX.c1)	ST_ASTEXT(tableX.c4)	ST_ASTEXT(tableX.c3)
POINT(10 10)	MULTIPOINT((0 0),(5 5),(10 10),(20 20))	POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))
# Check equi join
SELECT ST_ASTEXT(tableX.c3), ST_ASTEXT(tableY.c3) FROM tab AS tableX, tab2 AS tableY WHERE tableX.c3 = tableY.c3;
ST_ASTEXT(tableX.c3)	ST_ASTEXT(tableY.c3)
POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))	POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))
# Check DELETE stmt with WHERE clause and a constant predicate
DELETE FROM tab
WHERE c8 = ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))');
# Check the data
SELECT COUNT(*) FROM tab;
COUNT(*)
0
# Check UPDATE stmt with WHERE clause and a constant predicate
SET @c8 = ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(10 10), LINESTRING(20 20, 30 30))');
UPDATE tab2 SET c8 = @c8
WHERE c3 = ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))');
# 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))
# Creating a table with spatial index column
CREATE TABLE geom_index (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY NOT NULL, SPATIAL INDEX(g));
# Inserting geometry values
INSERT INTO geom_index SELECT * FROM gis_geometry;
# Check column datatypes
SHOW CREATE TABLE geom_index;
Table	Create Table
geom_index	CREATE TABLE `geom_index` (
  `fid` int(11) NOT NULL,
  `g` geometry NOT NULL,
  PRIMARY KEY (`fid`),
  SPATIAL KEY `g` (`g`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SET @polygon = ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0))');
# Sptail index query
SELECT fid, ST_ASTEXT(g) FROM geom_index WHERE MBRCONTAINS(@polygon, g) ORDER BY fid;
fid	ST_ASTEXT(g)
104	POINT(1 1)
111	POINT(1 1)
201	LINESTRING(0 0,5 5)
202	LINESTRING(0 0,2 2,4 4)
203	LINESTRING(0 0,5 5,10 10)
204	LINESTRING(10 10,5 5)
207	LINESTRING(0 0,5 5)
209	LINESTRING(0 0,2 2,4 4,6 6,8 8)
210	LINESTRING(0 0,5 5)
214	LINESTRING(1 1,2 2,3 3,4 4,5 5,6 6,7 7,8 8,9 9)
301	POLYGON((0 0,0 5,5 5,0 0))
302	POLYGON((0 0,0 5,5 5,5 0,0 0))
303	POLYGON((0 0,0 10,10 10,10 0,0 0))
305	POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))
306	POLYGON((0 0,0 10,10 10,10 0,0 0),(2 2,2 4,4 4,4 2,2 2),(6 6,6 8,8 8,8 6,6 6))
307	POLYGON((0 0,0 5,5 5,0 0))
309	POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))
310	POLYGON((0 0,0 5,5 5,0 0))
313	POLYGON((0 0,0 10,10 10,10 0,5 5,0 0))
315	POLYGON((0 0,0 10,10 10,10 0,0 0),(2 2,2 4,4 4,4 2,2 2),(6 6,6 8,8 8,8 6,6 6))
402	MULTIPOINT((0 0),(2 2),(4 4))
403	MULTIPOINT((0 0),(5 5),(10 10))
408	MULTIPOINT((0 0),(10 10))
409	MULTIPOINT((0 0),(2 2),(4 4),(6 6))
501	MULTILINESTRING((0 0,2 2))
502	MULTILINESTRING((0 0,2 2,4 4))
503	MULTILINESTRING((0 0,2 2,4 4),(6 6,8 8,10 10))
507	MULTILINESTRING((0 0,2 2))
509	MULTILINESTRING((0 0,2 2,4 4),(6 6,8 8,10 10))
510	MULTILINESTRING((0 0,2 2,4 4))
511	MULTILINESTRING((0 0,1 1,2 2))
513	MULTILINESTRING((0 0,10 10),(0 10,10 0))
601	MULTIPOLYGON(((0 0,0 5,5 5,0 0)))
602	MULTIPOLYGON(((0 0,0 5,5 5,0 0)),((5 5,5 10,10 10,5 5)))
603	MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4)))
604	MULTIPOLYGON(((0 0,0 5,5 5,0 0)))
607	MULTIPOLYGON(((0 0,5 0,5 5,0 5,0 0)))
608	MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0),(4 4,4 6,6 6,6 4,4 4)))
702	GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
703	GEOMETRYCOLLECTION(POINT(5 5),POLYGON((0 0,0 10,10 10,10 0,0 0)))
704	GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
705	GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10),POLYGON((0 0,0 10,10 10,10 0,0 0)))
708	GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,2 2,4 4,6 6,8 8),POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4)))
713	GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10),POLYGON((0 0,0 10,10 10,10 0,0 0)),MULTIPOINT((0 0),(2 2),(4 4),(6 6),(8 8),(10 10)),MULTILINESTRING((0 0,10 10),(0 10,10 0)),MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5))))
714	GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(0 0)),GEOMETRYCOLLECTION(LINESTRING(0 0,10 10)),GEOMETRYCOLLECTION(POLYGON((0 0,0 10,10 10,10 0,0 0))),GEOMETRYCOLLECTION(MULTIPOINT((0 0),(2 2),(4 4),(6 6),(8 8),(10 10))),GEOMETRYCOLLECTION(MULTILINESTRING((0 0,10 10),(0 10,10 0))),GEOMETRYCOLLECTION(MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5)))))
715	GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(),POINT(0 0),GEOMETRYCOLLECTION(LINESTRING(0 0,10 10),GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())),GEOMETRYCOLLECTION(),GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()),POLYGON((0 0,0 10,10 10,10 0,0 0)),MULTIPOINT((0 0),(2 2),(4 4),(6 6),(8 8),(10 10)),MULTILINESTRING((0 0,10 10),(0 10,10 0)),MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5))))
903	LINESTRING(0 0,5 5,10 10)
905	POLYGON((0 0,0 5,5 5,5 0,0 0))
906	POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))
907	MULTIPOINT((0 0),(2 2),(4 4),(6 6),(8 8),(10 10))
911	MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)))
912	MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,1 10,10 5,5 5)))
913	GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,5 5,10 10),POLYGON((0 0,0 10,10 10,10 0,0 0)))
SELECT COUNT(*) FROM gis_geometry;
COUNT(*)
120
# Check start transaction commit & Rollback
START TRANSACTION;
DELETE FROM gis_geometry;
ROLLBACK;
SELECT COUNT(*) FROM gis_geometry;
COUNT(*)
120
START TRANSACTION;
DELETE FROM gis_geometry;
COMMIT;
SELECT COUNT(*) FROM gis_geometry;
COUNT(*)
0
SET @geometrycollection_text = "GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())";
SET @multilinestring_text = "MULTILINESTRING((10 10, 11 11), (9 9, 10 10))";
SET @multipoint_text = "MULTIPOINT(10 10, 11 11)";
SET @multipolygon_text = "MULTIPOLYGON(((40 40, 20 45, 45 30, 40 40)),
                                       ((20 35, 10 30, 10 10, 30 5, 45 20, 20 35),
                                        (30 20, 20 15, 20 25, 30 20)))";
SELECT ST_ASTEXT(ST_GEOMCOLLFROMTXT(@geometrycollection_text));
ST_ASTEXT(ST_GEOMCOLLFROMTXT(@geometrycollection_text))
GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())
SELECT ST_ASTEXT(ST_MLINEFROMTEXT(@multilinestring_text));
ST_ASTEXT(ST_MLINEFROMTEXT(@multilinestring_text))
MULTILINESTRING((10 10,11 11),(9 9,10 10))
SELECT ST_ASTEXT(ST_MLINEFROMWKB(ST_GEOMFROMTEXT(@multilinestring_text)));
ST_ASTEXT(ST_MLINEFROMWKB(ST_GEOMFROMTEXT(@multilinestring_text)))
MULTILINESTRING((10 10,11 11),(9 9,10 10))
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
SELECT ST_ASTEXT(ST_MPOINTFROMTEXT(@multipoint_text));
ST_ASTEXT(ST_MPOINTFROMTEXT(@multipoint_text))
MULTIPOINT((10 10),(11 11))
SELECT ST_ASTEXT(ST_MPOINTFROMWKB(ST_GEOMFROMTEXT(@multipoint_text)));
ST_ASTEXT(ST_MPOINTFROMWKB(ST_GEOMFROMTEXT(@multipoint_text)))
MULTIPOINT((10 10),(11 11))
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
SELECT ST_ASTEXT(ST_MPOLYFROMTEXT(@multipolygon_text));
ST_ASTEXT(ST_MPOLYFROMTEXT(@multipolygon_text))
MULTIPOLYGON(((40 40,20 45,45 30,40 40)),((20 35,10 30,10 10,30 5,45 20,20 35),(30 20,20 15,20 25,30 20)))
SELECT ST_ASTEXT(ST_MPOLYFROMWKB(ST_GEOMFROMTEXT(@multipolygon_text)));
ST_ASTEXT(ST_MPOLYFROMWKB(ST_GEOMFROMTEXT(@multipolygon_text)))
MULTIPOLYGON(((40 40,20 45,45 30,40 40)),((20 35,10 30,10 10,30 5,45 20,20 35),(30 20,20 15,20 25,30 20)))
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
SELECT ST_ASTEXT(ST_MULTILINESTRINGFROMTEXT(@multilinestring_text));
ST_ASTEXT(ST_MULTILINESTRINGFROMTEXT(@multilinestring_text))
MULTILINESTRING((10 10,11 11),(9 9,10 10))
SELECT ST_ASTEXT(ST_MULTILINESTRINGFROMWKB(ST_GEOMFROMTEXT(@multilinestring_text)));
ST_ASTEXT(ST_MULTILINESTRINGFROMWKB(ST_GEOMFROMTEXT(@multilinestring_text)))
MULTILINESTRING((10 10,11 11),(9 9,10 10))
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
SELECT ST_ASTEXT(ST_MULTIPOINTFROMTEXT(@multipoint_text));
ST_ASTEXT(ST_MULTIPOINTFROMTEXT(@multipoint_text))
MULTIPOINT((10 10),(11 11))
SELECT ST_ASTEXT(ST_MULTIPOINTFROMWKB(ST_GEOMFROMTEXT(@multipoint_text)));
ST_ASTEXT(ST_MULTIPOINTFROMWKB(ST_GEOMFROMTEXT(@multipoint_text)))
MULTIPOINT((10 10),(11 11))
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
SELECT ST_ASTEXT(ST_MULTIPOLYGONFROMTEXT(@multipolygon_text));
ST_ASTEXT(ST_MULTIPOLYGONFROMTEXT(@multipolygon_text))
MULTIPOLYGON(((40 40,20 45,45 30,40 40)),((20 35,10 30,10 10,30 5,45 20,20 35),(30 20,20 15,20 25,30 20)))
SELECT ST_ASTEXT(ST_MULTIPOLYGONFROMWKB(ST_GEOMFROMTEXT(@multipolygon_text)));
ST_ASTEXT(ST_MULTIPOLYGONFROMWKB(ST_GEOMFROMTEXT(@multipolygon_text)))
MULTIPOLYGON(((40 40,20 45,45 30,40 40)),((20 35,10 30,10 10,30 5,45 20,20 35),(30 20,20 15,20 25,30 20)))
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
# Final cleanup
DROP TABLE gis_point;
DROP TABLE gis_linestring;
DROP TABLE gis_polygon;
DROP TABLE gis_multi_point;
DROP TABLE gis_multi_linestring;
DROP TABLE gis_multi_polygon;
DROP TABLE gis_geometrycollection;
DROP TABLE gis_geometry;
DROP TABLE tab;
DROP TABLE tab2;
DROP TABLE geom_index;