⚝
One Hat Cyber Team
⚝
Your IP:
216.73.216.124
Server IP:
50.28.103.30
Server:
Linux host.jcukjv-lwsites.com 4.18.0-553.22.1.el8_10.x86_64 #1 SMP Tue Sep 24 05:16:59 EDT 2024 x86_64
Server Software:
nginx/1.28.0
PHP Version:
8.3.12
Buat File
|
Buat Folder
Eksekusi
Dir :
~
/
www
/
server
/
mysql
/
mysql-test
/
suite
/
gis
/
r
/
View File Name :
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;