Debian Codegraph @

mysql-test/suite/innodb_gis/r/geometry.result

raw
1 SET default_storage_engine=InnoDB;
2 SET innodb_strict_mode=OFF;
3 SET SESSION debug="+d,row_print_geometry_data";
4 SHOW VARIABLES LIKE '%storage_engine%';
5 Variable_name Value
6 default_storage_engine InnoDB
7 default_tmp_storage_engine InnoDB
8 disabled_storage_engines
9 internal_tmp_mem_storage_engine TempTable
10 USE test;
11 DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon;
12 DROP TABLE IF EXISTS gis_multi_point, gis_multi_line, gis_multi_polygon;
13 DROP TABLE IF EXISTS gis_geometrycollection, gis_geometry;
14 DROP TABLE IF EXISTS tab,tab2,tab3,parent,emp2;
15 DROP PROCEDURE IF EXISTS geominout;
16 DROP PROCEDURE IF EXISTS geom_insert;
17 DROP TRIGGER IF EXISTS geom_trigger;
18 DROP PROCEDURE IF EXISTS geom_cursor;
19 CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT) ENGINE=InnoDB;
20 CREATE TABLE gis_line (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING) ENGINE=InnoDB;
21 CREATE TABLE gis_polygon (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON) ENGINE=InnoDB;
22 CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT) ENGINE=InnoDB;
23 CREATE TABLE gis_multi_line (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING) ENGINE=InnoDB;
24 CREATE TABLE gis_multi_polygon (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON) ENGINE=InnoDB;
25 CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION) ENGINE=InnoDB;
26 CREATE TABLE gis_geometry (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY) ENGINE=InnoDB;
27 SHOW FIELDS FROM gis_point;
28 Field Type Null Key Default Extra
29 fid int NO PRI NULL
30 g point YES NULL
31 SHOW FIELDS FROM gis_line;
32 Field Type Null Key Default Extra
33 fid int NO PRI NULL
34 g linestring YES NULL
35 SHOW FIELDS FROM gis_polygon;
36 Field Type Null Key Default Extra
37 fid int NO PRI NULL
38 g polygon YES NULL
39 SHOW FIELDS FROM gis_multi_point;
40 Field Type Null Key Default Extra
41 fid int NO PRI NULL
42 g multipoint YES NULL
43 SHOW FIELDS FROM gis_multi_line;
44 Field Type Null Key Default Extra
45 fid int NO PRI NULL
46 g multilinestring YES NULL
47 SHOW FIELDS FROM gis_multi_polygon;
48 Field Type Null Key Default Extra
49 fid int NO PRI NULL
50 g multipolygon YES NULL
51 SHOW FIELDS FROM gis_geometrycollection;
52 Field Type Null Key Default Extra
53 fid int NO PRI NULL
54 g geomcollection YES NULL
55 SHOW FIELDS FROM gis_geometry;
56 Field Type Null Key Default Extra
57 fid int NO PRI NULL
58 g geometry YES NULL
59 #INSERT using all WKT & WKB functions
60 INSERT INTO gis_point VALUES
61 (101, ST_PointFromText('POINT(10 10)')),
62 (102, ST_PointFromText('POINT(20 10)')),
63 (103, ST_PointFromText('POINT(20 20)')),
64 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
65 INSERT INTO gis_line VALUES
66 (105, ST_LineFromText('LINESTRING(0 0,0 10,10 0)')),
67 (106, ST_LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
68 (107, ST_LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(40, 10)))));
69 INSERT INTO gis_polygon VALUES
70 (108, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
71 (109, ST_PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
72 (110, ST_PolyFromWKB(ST_AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))));
73 INSERT INTO gis_multi_point VALUES
74 (111, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
75 (112, ST_MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
76 (113, ST_MPointFromWKB(ST_AsWKB(MultiPoint(Point(3, 6), Point(4, 10)))));
77 INSERT INTO gis_multi_line VALUES
78 (114, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
79 (115, ST_MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
80 (116, ST_MLineFromWKB(ST_AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7))))));
81 INSERT INTO gis_multi_polygon VALUES
82 (117, ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),
83 ((59 18,67 18,67 13,59 13,59 18)))')),
84 (118, ST_MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),
85 (52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')),
86 (119, ST_MPolyFromWKB(ST_AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))));
87 INSERT INTO gis_geometrycollection VALUES
88 (120, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
89 (121, ST_GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(10 10), LINESTRING(10 10,20 20))')),
90 (122, ST_GeometryFromWKB(ST_AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))))));
91 #check INSERT using SELECT query
92 INSERT into gis_geometry SELECT * FROM gis_point;
93 INSERT into gis_geometry SELECT * FROM gis_line;
94 INSERT into gis_geometry SELECT * FROM gis_polygon;
95 INSERT into gis_geometry SELECT * FROM gis_multi_point;
96 INSERT into gis_geometry SELECT * FROM gis_multi_line;
97 INSERT into gis_geometry SELECT * FROM gis_multi_polygon;
98 INSERT into gis_geometry SELECT * FROM gis_geometrycollection;
99 #check format coversion functions & spatial values
100 SELECT fid, ST_AsText(g) FROM gis_point;
101 fid ST_AsText(g)
102 101 POINT(10 10)
103 102 POINT(20 10)
104 103 POINT(20 20)
105 104 POINT(10 20)
106 SELECT fid, ST_AsText(g) FROM gis_line;
107 fid ST_AsText(g)
108 105 LINESTRING(0 0,0 10,10 0)
109 106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
110 107 LINESTRING(10 10,40 10)
111 SELECT fid, ST_AsText(g) FROM gis_polygon;
112 fid ST_AsText(g)
113 108 POLYGON((10 10,20 10,20 20,10 20,10 10))
114 109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
115 110 POLYGON((0 0,30 0,30 30,0 0))
116 SELECT fid, ST_AsText(g) FROM gis_multi_point;
117 fid ST_AsText(g)
118 111 MULTIPOINT((0 0),(10 10),(10 20),(20 20))
119 112 MULTIPOINT((1 1),(11 11),(11 21),(21 21))
120 113 MULTIPOINT((3 6),(4 10))
121 SELECT fid, ST_AsText(g) FROM gis_multi_line;
122 fid ST_AsText(g)
123 114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
124 115 MULTILINESTRING((10 48,10 21,10 0))
125 116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
126 SELECT fid, ST_AsText(g) FROM gis_multi_polygon;
127 fid ST_AsText(g)
128 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)))
129 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)))
130 119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
131 SELECT fid, ST_AsText(g) FROM gis_geometrycollection;
132 fid ST_AsText(g)
133 120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
134 121 GEOMETRYCOLLECTION(POINT(10 10),LINESTRING(10 10,20 20))
135 122 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
136 SELECT fid, ST_AsText(g) FROM gis_geometry;
137 fid ST_AsText(g)
138 101 POINT(10 10)
139 102 POINT(20 10)
140 103 POINT(20 20)
141 104 POINT(10 20)
142 105 LINESTRING(0 0,0 10,10 0)
143 106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
144 107 LINESTRING(10 10,40 10)
145 108 POLYGON((10 10,20 10,20 20,10 20,10 10))
146 109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
147 110 POLYGON((0 0,30 0,30 30,0 0))
148 111 MULTIPOINT((0 0),(10 10),(10 20),(20 20))
149 112 MULTIPOINT((1 1),(11 11),(11 21),(21 21))
150 113 MULTIPOINT((3 6),(4 10))
151 114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
152 115 MULTILINESTRING((10 48,10 21,10 0))
153 116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
154 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)))
155 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)))
156 119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
157 120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
158 121 GEOMETRYCOLLECTION(POINT(10 10),LINESTRING(10 10,20 20))
159 122 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
160 #2.1 check DDL functionality on GIS datatypes
161 CREATE TABLE tab(c1 POINT,c2 LINESTRING,c3 POLYGON,C4 MULTIPOINT,c5 MULTILINESTRING ,
162 c6 MULTIPOLYGON,c7 GEOMETRYCOLLECTION ,c8 GEOMETRY) ENGINE=InnoDB;
163 #check information schema for all the columns refer to 14, except POINT which is 15
164 SELECT sc.name, sc.pos, sc.mtype
165 FROM information_schema.innodb_columns sc
166 INNER JOIN information_schema.innodb_tables st
167 ON sc.TABLE_ID=st.TABLE_ID
168 WHERE st.NAME='test/tab'
169 ORDER BY sc.name;
170 name pos mtype
171 c1 0 14
172 c2 1 14
173 c3 2 14
174 C4 3 14
175 c5 4 14
176 c6 5 14
177 c7 6 14
178 c8 7 14
179 #check Perform convesrion before INSERT using WKT functions
180 SET @c1=ST_PointFromText('POINT(10 10)');
181 SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
182 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))');
183 SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
184 SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
185 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)))');
186 SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
187 SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
188 #Insert the spatial values
189 INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
190 #check index on GIS datatypes
191 CREATE INDEX idx1 on tab(c2(5) DESC) USING BTREE;
192 ERROR HY000: The index type BTREE is not supported for spatial indexes.
193 CREATE INDEX idx3 on tab(c3(5) ASC) USING BTREE;
194 ERROR HY000: The index type BTREE is not supported for spatial indexes.
195 CREATE UNIQUE INDEX idx2 on tab(c8(5) ASC) ;
196 ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
197 #check equality predicate on the index columns
198 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
199 WHERE c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
200 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
201 1 SIMPLE tab NULL ALL NULL NULL NULL NULL # 100.00 Using where
202 Warnings:
203 Note 1003 /* select#1 */ select st_astext(`test`.`tab`.`c1`) AS `ST_ASText(c1)`,st_astext(`test`.`tab`.`c2`) AS `ST_AsText(c2)`,st_astext(`test`.`tab`.`c8`) AS `ST_AsText(c8)` from `test`.`tab` where (`test`.`tab`.`c2` = <cache>(st_linefromtext('LINESTRING(10 10,20 20,30 30)')))
204 #check index with WKB function
205 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
206 FROM tab WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
207 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
208 1 SIMPLE tab NULL ALL NULL NULL NULL NULL # 100.00 Using where
209 Warnings:
210 Note 1003 /* select#1 */ select st_astext(`test`.`tab`.`c1`) AS `ST_ASText(c1)`,st_astext(`test`.`tab`.`c2`) AS `ST_AsText(c2)`,st_astext(`test`.`tab`.`c8`) AS `ST_AsText(c8)` from `test`.`tab` where (`test`.`tab`.`c2` = <cache>(st_linestringfromwkb(st_aswkb(st_linefromtext('LINESTRING(10 10,20 20,30 30)')))))
211 #check index with WKT function
212 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
213 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))');
214 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
215 1 SIMPLE tab NULL ALL NULL NULL NULL NULL # 100.00 Using where
216 Warnings:
217 Note 1003 /* select#1 */ select st_astext(`test`.`tab`.`c1`) AS `ST_ASText(c1)`,st_astext(`test`.`tab`.`c2`) AS `ST_AsText(c2)`,st_astext(`test`.`tab`.`c8`) AS `ST_AsText(c8)` from `test`.`tab` where (`test`.`tab`.`c3` = <cache>(st_polyfromtext('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))')))
218 #check index with WKB function
219 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
220 FROM tab
221 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))')));
222 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
223 1 SIMPLE tab NULL ALL NULL NULL NULL NULL # 100.00 Using where
224 Warnings:
225 Note 1003 /* select#1 */ select st_astext(`test`.`tab`.`c1`) AS `ST_ASText(c1)`,st_astext(`test`.`tab`.`c2`) AS `ST_AsText(c2)`,st_astext(`test`.`tab`.`c8`) AS `ST_AsText(c8)` from `test`.`tab` where (`test`.`tab`.`c3` = <cache>(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))')))))
226 #check index with WKT function
227 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
228 WHERE c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
229 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
230 1 SIMPLE tab NULL ALL NULL NULL NULL NULL # 100.00 Using where
231 Warnings:
232 Note 1003 /* select#1 */ select st_astext(`test`.`tab`.`c1`) AS `ST_ASText(c1)`,st_astext(`test`.`tab`.`c2`) AS `ST_AsText(c2)`,st_astext(`test`.`tab`.`c8`) AS `ST_AsText(c8)` from `test`.`tab` where (`test`.`tab`.`c8` = <cache>(st_geomcollfromtext('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')))
233 #check index with WKB function
234 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
235 FROM tab
236 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
237 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
238 1 SIMPLE tab NULL ALL NULL NULL NULL NULL # 100.00 Using where
239 Warnings:
240 Note 1003 /* select#1 */ select st_astext(`test`.`tab`.`c1`) AS `ST_ASText(c1)`,st_astext(`test`.`tab`.`c2`) AS `ST_AsText(c2)`,st_astext(`test`.`tab`.`c8`) AS `ST_AsText(c8)` from `test`.`tab` where (`test`.`tab`.`c8` = <cache>(st_geometryfromwkb(st_aswkb(st_geomcollfromtext('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')))))
241 #check range predicate on the index columns
242 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
243 WHERE c2>=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
244 ERROR HY000: Incorrect arguments to >=
245 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
246 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))');
247 ERROR HY000: Incorrect arguments to >=
248 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
249 WHERE c8>=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
250 ERROR HY000: Incorrect arguments to >=
251 #check index with DELETE operation
252 EXPLAIN DELETE FROM tab
253 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
254 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
255 1 DELETE tab NULL ALL NULL NULL NULL NULL # 100.00 Using where
256 Warnings:
257 Note 1003 delete from `test`.`tab` where (`test`.`tab`.`c8` = st_geometryfromwkb(st_aswkb(st_geomcollfromtext('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'))))
258 #check the spatial values
259 SELECT ST_AsText(c1) FROM tab;
260 ST_AsText(c1)
261 POINT(10 10)
262 SELECT ST_AsText(c2) FROM tab;
263 ST_AsText(c2)
264 LINESTRING(10 10,20 20,30 30)
265 SELECT ST_AsText(c3) FROM tab;
266 ST_AsText(c3)
267 POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
268 SELECT ST_AsText(c4) FROM tab;
269 ST_AsText(c4)
270 MULTIPOINT((0 0),(5 5),(10 10),(20 20))
271 SELECT ST_AsText(c5) FROM tab;
272 ST_AsText(c5)
273 MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
274 SELECT ST_AsText(c6) FROM tab;
275 ST_AsText(c6)
276 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)))
277 SELECT ST_AsText(c7) FROM tab;
278 ST_AsText(c7)
279 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
280 SELECT ST_AsText(c8) From tab;
281 ST_AsText(c8)
282 GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
283 #check by modify the column type
284 ALTER TABLE tab MODIFY COLUMN c1 Geometry;
285 ALTER TABLE tab MODIFY COLUMN c2 Geometry;
286 ALTER TABLE tab MODIFY COLUMN c3 Geometry;
287 ALTER TABLE tab MODIFY COLUMN c4 Geometry;
288 ALTER TABLE tab MODIFY COLUMN c5 Geometry;
289 ALTER TABLE tab MODIFY COLUMN c6 Geometry;
290 ALTER TABLE tab MODIFY COLUMN c7 Geometry;
291 #check column datatypes
292 SHOW FIELDS FROM tab;
293 Field Type Null Key Default Extra
294 c1 geometry YES NULL
295 c2 geometry YES NULL
296 c3 geometry YES NULL
297 c4 geometry YES NULL
298 c5 geometry YES NULL
299 c6 geometry YES NULL
300 c7 geometry YES NULL
301 c8 geometry YES NULL
302 #check the data after modify
303 SELECT ST_AsText(c1) FROM tab;
304 ST_AsText(c1)
305 POINT(10 10)
306 SELECT ST_AsText(c2) FROM tab;
307 ST_AsText(c2)
308 LINESTRING(10 10,20 20,30 30)
309 SELECT ST_AsText(c3) FROM tab;
310 ST_AsText(c3)
311 POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
312 SELECT ST_AsText(c4) FROM tab;
313 ST_AsText(c4)
314 MULTIPOINT((0 0),(5 5),(10 10),(20 20))
315 SELECT ST_AsText(c5) FROM tab;
316 ST_AsText(c5)
317 MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
318 SELECT ST_AsText(c6) FROM tab;
319 ST_AsText(c6)
320 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)))
321 SELECT ST_AsText(c7) FROM tab;
322 ST_AsText(c7)
323 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
324 SELECT ST_AsText(c8) From tab;
325 ST_AsText(c8)
326 GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
327 #check by modify the column type
328 ALTER TABLE tab MODIFY COLUMN c1 POINT;
329 ALTER TABLE tab MODIFY COLUMN c2 LINESTRING;
330 ALTER TABLE tab MODIFY COLUMN c3 POLYGON;
331 ALTER TABLE tab MODIFY COLUMN c4 MULTIPOINT;
332 ALTER TABLE tab MODIFY COLUMN c5 MULTILINESTRING;
333 ALTER TABLE tab MODIFY COLUMN c6 MULTIPOLYGON;
334 ALTER TABLE tab MODIFY COLUMN c7 GEOMETRYCOLLECTION;
335 #check column datatypes
336 SHOW FIELDS FROM tab;
337 Field Type Null Key Default Extra
338 c1 point YES NULL
339 c2 linestring YES NULL
340 c3 polygon YES NULL
341 c4 multipoint YES NULL
342 c5 multilinestring YES NULL
343 c6 multipolygon YES NULL
344 c7 geomcollection YES NULL
345 c8 geometry YES NULL
346 #check the data after modify
347 SELECT ST_AsText(c1) FROM tab;
348 ST_AsText(c1)
349 POINT(10 10)
350 SELECT ST_AsText(c2) FROM tab;
351 ST_AsText(c2)
352 LINESTRING(10 10,20 20,30 30)
353 SELECT ST_AsText(c3) FROM tab;
354 ST_AsText(c3)
355 POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
356 SELECT ST_AsText(c4) FROM tab;
357 ST_AsText(c4)
358 MULTIPOINT((0 0),(5 5),(10 10),(20 20))
359 SELECT ST_AsText(c5) FROM tab;
360 ST_AsText(c5)
361 MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
362 SELECT ST_AsText(c6) FROM tab;
363 ST_AsText(c6)
364 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)))
365 SELECT ST_AsText(c7) FROM tab;
366 ST_AsText(c7)
367 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
368 SELECT ST_AsText(c8) From tab;
369 ST_AsText(c8)
370 GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
371 #check change the column type
372 ALTER TABLE tab MODIFY COLUMN c1 BLOB;
373 #check column datatypes
374 SHOW CREATE TABLE tab;
375 Table Create Table
376 tab CREATE TABLE `tab` (
377 `c1` blob,
378 `c2` linestring DEFAULT NULL,
379 `c3` polygon DEFAULT NULL,
380 `c4` multipoint DEFAULT NULL,
381 `c5` multilinestring DEFAULT NULL,
382 `c6` multipolygon DEFAULT NULL,
383 `c7` geomcollection DEFAULT NULL,
384 `c8` geometry DEFAULT NULL
385 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
386 #check the data after modify
387 SELECT ST_AsText(c1) FROM tab;
388 ST_AsText(c1)
389 POINT(10 10)
390 #change the column name
391 ALTER TABLE tab CHANGE COLUMN c1 c0 GEOMETRY ;
392 #check column datatypes
393 SHOW FIELDS FROM tab;
394 Field Type Null Key Default Extra
395 c0 geometry YES NULL
396 c2 linestring YES NULL
397 c3 polygon YES NULL
398 c4 multipoint YES NULL
399 c5 multilinestring YES NULL
400 c6 multipolygon YES NULL
401 c7 geomcollection YES NULL
402 c8 geometry YES NULL
403 #add primary key
404 ALTER TABLE tab ADD PRIMARY KEY pk2(c8(5));
405 ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
406 #check columns
407 SHOW FIELDS FROM tab;
408 Field Type Null Key Default Extra
409 c0 geometry YES NULL
410 c2 linestring YES NULL
411 c3 polygon YES NULL
412 c4 multipoint YES NULL
413 c5 multilinestring YES NULL
414 c6 multipolygon YES NULL
415 c7 geomcollection YES NULL
416 c8 geometry YES NULL
417 #check columns
418 SHOW FIELDS FROM tab;
419 Field Type Null Key Default Extra
420 c0 geometry YES NULL
421 c2 linestring YES NULL
422 c3 polygon YES NULL
423 c4 multipoint YES NULL
424 c5 multilinestring YES NULL
425 c6 multipolygon YES NULL
426 c7 geomcollection YES NULL
427 c8 geometry YES NULL
428 #cleanup the table
429 TRUNCATE TABLE tab;
430 #check with procedures
431 #crate proc with INOUT params
432 CREATE PROCEDURE geom_insert(IN c1 POINT,IN c2 LINESTRING,IN c3 POLYGON,
433 IN c4 MULTIPOINT,IN c5 MULTILINESTRING, IN c6 MULTIPOLYGON,IN c7 GEOMETRYCOLLECTION,
434 IN c8 GEOMETRY)
435 BEGIN
436 INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
437 END |
438 #set the spatial values
439 SET @c1=ST_PointFromText('POINT(10 10)');
440 SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
441 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))');
442 SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
443 SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
444 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)))');
445 SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
446 SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
447 #call the proc
448 CALL geom_insert(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
449 #check the values ofter proc call
450 SELECT ST_AsText(c0) FROM tab;
451 ST_AsText(c0)
452 POINT(10 10)
453 SELECT ST_AsText(c2) FROM tab;
454 ST_AsText(c2)
455 LINESTRING(10 10,20 20,30 30)
456 SELECT ST_AsText(c3) FROM tab;
457 ST_AsText(c3)
458 POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
459 SELECT ST_AsText(c4) FROM tab;
460 ST_AsText(c4)
461 MULTIPOINT((0 0),(5 5),(10 10),(20 20))
462 SELECT ST_AsText(c5) FROM tab;
463 ST_AsText(c5)
464 MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
465 SELECT ST_AsText(c6) FROM tab;
466 ST_AsText(c6)
467 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)))
468 SELECT ST_AsText(c7) FROM tab;
469 ST_AsText(c7)
470 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
471 SELECT ST_AsText(c8) From tab;
472 ST_AsText(c8)
473 GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
474 #set the input spatial value
475 SET @c9=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
476 #crate a proc with INOUT params and pass a value.
477 CREATE PROCEDURE geominout(INOUT c9 GEOMETRY)
478 BEGIN
479 SELECT ST_AsText(@c9);
480 SET c9=ST_GeomCollFromText('GeometryCollection(Point(10 10),LineString(20 20, 30 30))');
481 END|
482 #call the proc and then change the spatial value of c8
483 CALL geominout(@c9);
484 ST_AsText(@c9)
485 GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
486 #now check the out spatial values should be changed to new values
487 SELECT ST_AsText(@c9);
488 ST_AsText(@c9)
489 GEOMETRYCOLLECTION(POINT(10 10),LINESTRING(20 20,30 30))
490 #delete the records
491 TRUNCATE TABLE tab;
492 #create another table same as tab
493 CREATE TABLE tab2 AS SELECT * FROM tab;
494 #check the table definition
495 SHOW CREATE TABLE tab2;
496 Table Create Table
497 tab2 CREATE TABLE `tab2` (
498 `c0` geometry DEFAULT NULL,
499 `c2` linestring DEFAULT NULL,
500 `c3` polygon DEFAULT NULL,
501 `c4` multipoint DEFAULT NULL,
502 `c5` multilinestring DEFAULT NULL,
503 `c6` multipolygon DEFAULT NULL,
504 `c7` geomcollection DEFAULT NULL,
505 `c8` geometry DEFAULT NULL
506 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
507 #create a tigger and populate the values into tab2
508 CREATE TRIGGER geom_trigger AFTER INSERT ON tab
509 FOR EACH ROW
510 BEGIN
511 INSERT INTO tab2 VALUES (@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
512 END|
513 #set the spatial values
514 SET @c1=ST_PointFromText('POINT(10 10)');
515 SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
516 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))');
517 SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
518 SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
519 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)))');
520 SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
521 SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
522 #Insert the spatial values
523 INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
524 #check the values whether populated
525 SELECT ST_AsText(c0) FROM tab2;
526 ST_AsText(c0)
527 POINT(10 10)
528 SELECT ST_AsText(c2) FROM tab2;
529 ST_AsText(c2)
530 LINESTRING(10 10,20 20,30 30)
531 SELECT ST_AsText(c3) FROM tab2;
532 ST_AsText(c3)
533 POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
534 SELECT ST_AsText(c4) FROM tab2;
535 ST_AsText(c4)
536 MULTIPOINT((0 0),(5 5),(10 10),(20 20))
537 SELECT ST_AsText(c5) FROM tab2;
538 ST_AsText(c5)
539 MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
540 SELECT ST_AsText(c6) FROM tab2;
541 ST_AsText(c6)
542 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)))
543 SELECT ST_AsText(c7) FROM tab2;
544 ST_AsText(c7)
545 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
546 SELECT ST_AsText(c8) From tab2;
547 ST_AsText(c8)
548 GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
549 #check Cursor with Geometry
550 CREATE PROCEDURE geom_cursor()
551 BEGIN
552 DECLARE v GEOMETRY;
553 DECLARE c CURSOR FOR SELECT c8 FROM tab2;
554 OPEN c;
555 FETCH c INTO v;
556 CLOSE c;
557 SELECT ST_AsText(v);
558 END|
559 # the following line was commented due the Bug#16282246
560 # Once it is fixed, the comment will be removed.
561 # right now bug fixing date is unknown.
562 check self join
563 SELECT ST_AsText(a.c0),ST_AsText(a.c2),ST_AsText(a.c3)
564 FROM tab a,tab2 b WHERE a.c0=b.c0 AND a.c2=b.c2
565 AND a.c3=b.c3 AND a.c4=b.c4 AND a.c5=b.c5 AND a.c6=b.c6
566 AND a.c7=b.c7 AND a.c8=b.c8;
567 ST_AsText(a.c0) ST_AsText(a.c2) ST_AsText(a.c3)
568 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))
569 #check equi join
570 SELECT ST_AsText(a.c2),ST_AsText(b.c2) FROM tab a,tab2 b WHERE a.c2=b.c2;
571 ST_AsText(a.c2) ST_AsText(b.c2)
572 LINESTRING(10 10,20 20,30 30) LINESTRING(10 10,20 20,30 30)
573 #check DELETE stmt with Where clause and a constant predicate
574 DELETE FROM tab
575 Where c8=c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
576 ERROR 22007: Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x07\x00\x00\x00\x02\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\xF0?\x00\x00\x00\x00\x00\x00\xF'
577 #check the data, should 0
578 SELECT COUNT(*) FROM tab;
579 COUNT(*)
580 1
581 #check UPDATE stmt with Where clause and a constant predicate
582 SET @c8=ST_GeomCollFromText('GeometryCollection(Point(10 10),LineString(20 20, 30 30))');
583 UPDATE tab2 SET c8=@c8
584 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
585 #check the column should be modified to LINESTRING(10 10,20 20,30 30)
586 SELECT ST_AsText(c8) From tab2;
587 ST_AsText(c8)
588 GEOMETRYCOLLECTION(POINT(10 10),LINESTRING(20 20,30 30))
589 #check GIS datatypes with aggregate functions
590 SELECT COUNT(ST_AsText(g)) FROM gis_point;
591 COUNT(ST_AsText(g))
592 4
593 SELECT COUNT(ST_AsText(g)) FROM gis_line;
594 COUNT(ST_AsText(g))
595 3
596 SELECT COUNT(ST_AsText(g)) FROM gis_polygon;
597 COUNT(ST_AsText(g))
598 3
599 SELECT COUNT(ST_AsText(g)) FROM gis_multi_point;
600 COUNT(ST_AsText(g))
601 3
602 SELECT COUNT(ST_AsText(g)) FROM gis_multi_line;
603 COUNT(ST_AsText(g))
604 3
605 SELECT COUNT(ST_AsText(g)) FROM gis_multi_polygon;
606 COUNT(ST_AsText(g))
607 3
608 SELECT COUNT(ST_AsText(g)) FROM gis_geometrycollection;
609 COUNT(ST_AsText(g))
610 3
611 SELECT COUNT(ST_AsText(g)) FROM gis_geometry;
612 COUNT(ST_AsText(g))
613 22
614 SELECT SUM(ST_AsText(g)) FROM gis_point;
615 SUM(ST_AsText(g))
616 0
617 Warnings:
618 Warning 1292 Truncated incorrect DOUBLE value: 'POINT(10 10)'
619 Warning 1292 Truncated incorrect DOUBLE value: 'POINT(20 10)'
620 Warning 1292 Truncated incorrect DOUBLE value: 'POINT(20 20)'
621 Warning 1292 Truncated incorrect DOUBLE value: 'POINT(10 20)'
622 SELECT SUM(ST_AsText(g)) FROM gis_line;
623 SUM(ST_AsText(g))
624 0
625 Warnings:
626 Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(0 0,0 10,10 0)'
627 Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(10 10,20 10,20 20,10 20,10 10)'
628 Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(10 10,40 10)'
629 SELECT AVG(ST_AsText(g)) FROM gis_polygon;
630 AVG(ST_AsText(g))
631 0
632 Warnings:
633 Warning 1292 Truncated incorrect DOUBLE value: 'POLYGON((10 10,20 10,20 20,10 20,10 10))'
634 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))'
635 Warning 1292 Truncated incorrect DOUBLE value: 'POLYGON((0 0,30 0,30 30,0 0))'
636 #here it show some string value no meaning
637 SELECT MAX(ST_AsText(g)) FROM gis_multi_point;
638 MAX(ST_AsText(g))
639 MULTIPOINT((3 6),(4 10))
640 #here it show some string value no meaning
641 SELECT MIN(ST_AsText(g)) FROM gis_multi_line;
642 MIN(ST_AsText(g))
643 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
644 SELECT STD(ST_AsText(g)) FROM gis_multi_polygon;
645 STD(ST_AsText(g))
646 0
647 Warnings:
648 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)))'
649 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)))'
650 Warning 1292 Truncated incorrect DOUBLE value: 'MULTIPOLYGON(((0 3,3 3,3 0,0 3)))'
651 SELECT COUNT(ST_AsText(g)) FROM gis_geometrycollection;
652 COUNT(ST_AsText(g))
653 3
654 SELECT AVG(ST_AsText(g)) FROM gis_geometry;
655 AVG(ST_AsText(g))
656 0
657 Warnings:
658 Warning 1292 Truncated incorrect DOUBLE value: 'POINT(10 10)'
659 Warning 1292 Truncated incorrect DOUBLE value: 'POINT(20 10)'
660 Warning 1292 Truncated incorrect DOUBLE value: 'POINT(20 20)'
661 Warning 1292 Truncated incorrect DOUBLE value: 'POINT(10 20)'
662 Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(0 0,0 10,10 0)'
663 Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(10 10,20 10,20 20,10 20,10 10)'
664 Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(10 10,40 10)'
665 Warning 1292 Truncated incorrect DOUBLE value: 'POLYGON((10 10,20 10,20 20,10 20,10 10))'
666 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))'
667 Warning 1292 Truncated incorrect DOUBLE value: 'POLYGON((0 0,30 0,30 30,0 0))'
668 Warning 1292 Truncated incorrect DOUBLE value: 'MULTIPOINT((0 0),(10 10),(10 20),(20 20))'
669 Warning 1292 Truncated incorrect DOUBLE value: 'MULTIPOINT((1 1),(11 11),(11 21),(21 21))'
670 Warning 1292 Truncated incorrect DOUBLE value: 'MULTIPOINT((3 6),(4 10))'
671 Warning 1292 Truncated incorrect DOUBLE value: 'MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))'
672 Warning 1292 Truncated incorrect DOUBLE value: 'MULTILINESTRING((10 48,10 21,10 0))'
673 Warning 1292 Truncated incorrect DOUBLE value: 'MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))'
674 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)))'
675 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)))'
676 Warning 1292 Truncated incorrect DOUBLE value: 'MULTIPOLYGON(((0 3,3 3,3 0,0 3)))'
677 Warning 1292 Truncated incorrect DOUBLE value: 'GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))'
678 Warning 1292 Truncated incorrect DOUBLE value: 'GEOMETRYCOLLECTION(POINT(10 10),LINESTRING(10 10,20 20))'
679 Warning 1292 Truncated incorrect DOUBLE value: 'GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))'
680 #check Datatypes with compression tables
681 CREATE TABLE tab3(c1 POINT,c2 LINESTRING,c3 POLYGON,C4 MULTIPOINT,c5 MULTILINESTRING ,
682 c6 MULTIPOLYGON,c7 GEOMETRYCOLLECTION ,c8 GEOMETRY) ENGINE=InnoDB
683 KEY_BLOCK_SIZE=8 ROW_FORMAT=COMPRESSED;
684 #check file format
685 SHOW CREATE TABLE tab3;
686 Table Create Table
687 tab3 CREATE TABLE `tab3` (
688 `c1` point DEFAULT NULL,
689 `c2` linestring DEFAULT NULL,
690 `c3` polygon DEFAULT NULL,
691 `C4` multipoint DEFAULT NULL,
692 `c5` multilinestring DEFAULT NULL,
693 `c6` multipolygon DEFAULT NULL,
694 `c7` geomcollection DEFAULT NULL,
695 `c8` geometry DEFAULT NULL
696 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=key_block_size
697 ALTER TABLE tab3 KEY_BLOCK_SIZE=16 ROW_FORMAT=Dynamic;
698 #check file format
699 SHOW CREATE TABLE tab3;
700 Table Create Table
701 tab3 CREATE TABLE `tab3` (
702 `c1` point DEFAULT NULL,
703 `c2` linestring DEFAULT NULL,
704 `c3` polygon DEFAULT NULL,
705 `C4` multipoint DEFAULT NULL,
706 `c5` multilinestring DEFAULT NULL,
707 `c6` multipolygon DEFAULT NULL,
708 `c7` geomcollection DEFAULT NULL,
709 `c8` geometry DEFAULT NULL
710 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
711 #check information schema for all the columns refer to 14, except POINT which is 15
712 SELECT sc.name, sc.pos, sc.mtype
713 FROM information_schema.innodb_columns sc
714 INNER JOIN information_schema.innodb_tables st
715 ON sc.TABLE_ID=st.TABLE_ID
716 WHERE st.NAME='test/tab3'
717 ORDER BY sc.name;
718 name pos mtype
719 c1 0 14
720 c2 1 14
721 c3 2 14
722 C4 3 14
723 c5 4 14
724 c6 5 14
725 c7 6 14
726 c8 7 14
727 #check Perform convesrion before INSERT using WKT functions
728 SET @c1=ST_PointFromText('POINT(10 10)');
729 SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
730 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))');
731 SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
732 SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
733 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)))');
734 SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
735 SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
736 #Insert the spatial values
737 INSERT INTO tab3 VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
738 #check index on GIS datatypes
739 CREATE INDEX idx1 on tab3(c2(5) DESC) USING BTREE;
740 ERROR HY000: The index type BTREE is not supported for spatial indexes.
741 CREATE INDEX idx3 on tab3(c3(5) ASC) USING BTREE;
742 ERROR HY000: The index type BTREE is not supported for spatial indexes.
743 CREATE UNIQUE INDEX idx2 on tab3(c8(5) ASC) ;
744 ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
745 #check equality predicate on the index columns
746 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
747 WHERE c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
748 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
749 1 SIMPLE tab3 NULL ALL NULL NULL NULL NULL # 100.00 Using where
750 Warnings:
751 Note 1003 /* select#1 */ select st_astext(`test`.`tab3`.`c1`) AS `ST_ASText(c1)`,st_astext(`test`.`tab3`.`c2`) AS `ST_AsText(c2)`,st_astext(`test`.`tab3`.`c8`) AS `ST_AsText(c8)` from `test`.`tab3` where (`test`.`tab3`.`c2` = <cache>(st_linefromtext('LINESTRING(10 10,20 20,30 30)')))
752 #check index with WKB function
753 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
754 FROM tab3 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
755 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
756 1 SIMPLE tab3 NULL ALL NULL NULL NULL NULL # 100.00 Using where
757 Warnings:
758 Note 1003 /* select#1 */ select st_astext(`test`.`tab3`.`c1`) AS `ST_ASText(c1)`,st_astext(`test`.`tab3`.`c2`) AS `ST_AsText(c2)`,st_astext(`test`.`tab3`.`c8`) AS `ST_AsText(c8)` from `test`.`tab3` where (`test`.`tab3`.`c2` = <cache>(st_linestringfromwkb(st_aswkb(st_linefromtext('LINESTRING(10 10,20 20,30 30)')))))
759 #check index with WKT function
760 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
761 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))');
762 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
763 1 SIMPLE tab3 NULL ALL NULL NULL NULL NULL # 100.00 Using where
764 Warnings:
765 Note 1003 /* select#1 */ select st_astext(`test`.`tab3`.`c1`) AS `ST_ASText(c1)`,st_astext(`test`.`tab3`.`c2`) AS `ST_AsText(c2)`,st_astext(`test`.`tab3`.`c8`) AS `ST_AsText(c8)` from `test`.`tab3` where (`test`.`tab3`.`c3` = <cache>(st_polyfromtext('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))')))
766 #check index with WKB function
767 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
768 FROM tab3
769 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))')));
770 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
771 1 SIMPLE tab3 NULL ALL NULL NULL NULL NULL # 100.00 Using where
772 Warnings:
773 Note 1003 /* select#1 */ select st_astext(`test`.`tab3`.`c1`) AS `ST_ASText(c1)`,st_astext(`test`.`tab3`.`c2`) AS `ST_AsText(c2)`,st_astext(`test`.`tab3`.`c8`) AS `ST_AsText(c8)` from `test`.`tab3` where (`test`.`tab3`.`c3` = <cache>(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))')))))
774 #check index with WKT function
775 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
776 WHERE c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
777 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
778 1 SIMPLE tab3 NULL ALL NULL NULL NULL NULL # 100.00 Using where
779 Warnings:
780 Note 1003 /* select#1 */ select st_astext(`test`.`tab3`.`c1`) AS `ST_ASText(c1)`,st_astext(`test`.`tab3`.`c2`) AS `ST_AsText(c2)`,st_astext(`test`.`tab3`.`c8`) AS `ST_AsText(c8)` from `test`.`tab3` where (`test`.`tab3`.`c8` = <cache>(st_geomcollfromtext('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')))
781 #check index with WKB function
782 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
783 FROM tab3
784 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
785 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
786 1 SIMPLE tab3 NULL ALL NULL NULL NULL NULL # 100.00 Using where
787 Warnings:
788 Note 1003 /* select#1 */ select st_astext(`test`.`tab3`.`c1`) AS `ST_ASText(c1)`,st_astext(`test`.`tab3`.`c2`) AS `ST_AsText(c2)`,st_astext(`test`.`tab3`.`c8`) AS `ST_AsText(c8)` from `test`.`tab3` where (`test`.`tab3`.`c8` = <cache>(st_geometryfromwkb(st_aswkb(st_geomcollfromtext('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')))))
789 #check range predicate on the index columns
790 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
791 WHERE c2>=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
792 ERROR HY000: Incorrect arguments to >=
793 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
794 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))');
795 ERROR HY000: Incorrect arguments to >=
796 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
797 WHERE c8>=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
798 ERROR HY000: Incorrect arguments to >=
799 #check index with DELETE operation
800 EXPLAIN DELETE FROM tab3
801 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
802 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
803 1 DELETE tab3 NULL ALL NULL NULL NULL NULL # 100.00 Using where
804 Warnings:
805 Note 1003 delete from `test`.`tab3` where (`test`.`tab3`.`c8` = st_geometryfromwkb(st_aswkb(st_geomcollfromtext('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'))))
806 #check the spatial values
807 SELECT ST_AsText(c1) FROM tab3;
808 ST_AsText(c1)
809 POINT(10 10)
810 SELECT ST_AsText(c2) FROM tab3;
811 ST_AsText(c2)
812 LINESTRING(10 10,20 20,30 30)
813 SELECT ST_AsText(c3) FROM tab3;
814 ST_AsText(c3)
815 POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
816 SELECT ST_AsText(c4) FROM tab3;
817 ST_AsText(c4)
818 MULTIPOINT((0 0),(5 5),(10 10),(20 20))
819 SELECT ST_AsText(c5) FROM tab3;
820 ST_AsText(c5)
821 MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
822 SELECT ST_AsText(c6) FROM tab3;
823 ST_AsText(c6)
824 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)))
825 SELECT ST_AsText(c7) FROM tab3;
826 ST_AsText(c7)
827 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
828 SELECT ST_AsText(c8) From tab3;
829 ST_AsText(c8)
830 GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
831 #check with Row_format = Dynamic
832 ALTER TABLE tab3 ROW_FORMAT=Dynamic;
833 #check file format
834 SHOW CREATE TABLE tab3;
835 Table Create Table
836 tab3 CREATE TABLE `tab3` (
837 `c1` point DEFAULT NULL,
838 `c2` linestring DEFAULT NULL,
839 `c3` polygon DEFAULT NULL,
840 `C4` multipoint DEFAULT NULL,
841 `c5` multilinestring DEFAULT NULL,
842 `c6` multipolygon DEFAULT NULL,
843 `c7` geomcollection DEFAULT NULL,
844 `c8` geometry DEFAULT NULL
845 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
846 #check index with WKB function
847 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
848 FROM tab3 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
849 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
850 1 SIMPLE tab3 NULL ALL NULL NULL NULL NULL # 100.00 Using where
851 Warnings:
852 Note 1003 /* select#1 */ select st_astext(`test`.`tab3`.`c1`) AS `ST_ASText(c1)`,st_astext(`test`.`tab3`.`c2`) AS `ST_AsText(c2)`,st_astext(`test`.`tab3`.`c8`) AS `ST_AsText(c8)` from `test`.`tab3` where (`test`.`tab3`.`c2` = <cache>(st_linestringfromwkb(st_aswkb(st_linefromtext('LINESTRING(10 10,20 20,30 30)')))))
853 #check index with WKT function
854 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
855 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))');
856 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
857 1 SIMPLE tab3 NULL ALL NULL NULL NULL NULL # 100.00 Using where
858 Warnings:
859 Note 1003 /* select#1 */ select st_astext(`test`.`tab3`.`c1`) AS `ST_ASText(c1)`,st_astext(`test`.`tab3`.`c2`) AS `ST_AsText(c2)`,st_astext(`test`.`tab3`.`c8`) AS `ST_AsText(c8)` from `test`.`tab3` where (`test`.`tab3`.`c3` = <cache>(st_polyfromtext('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))')))
860 #check the Geometry property functions
861 SELECT fid, ST_Dimension(g) FROM gis_geometry;
862 fid ST_Dimension(g)
863 101 0
864 102 0
865 103 0
866 104 0
867 105 1
868 106 1
869 107 1
870 108 2
871 109 2
872 110 2
873 111 0
874 112 0
875 113 0
876 114 1
877 115 1
878 116 1
879 117 2
880 118 2
881 119 2
882 120 1
883 121 1
884 122 1
885 SELECT fid, ST_GeometryType(g) FROM gis_geometry;
886 fid ST_GeometryType(g)
887 101 POINT
888 102 POINT
889 103 POINT
890 104 POINT
891 105 LINESTRING
892 106 LINESTRING
893 107 LINESTRING
894 108 POLYGON
895 109 POLYGON
896 110 POLYGON
897 111 MULTIPOINT
898 112 MULTIPOINT
899 113 MULTIPOINT
900 114 MULTILINESTRING
901 115 MULTILINESTRING
902 116 MULTILINESTRING
903 117 MULTIPOLYGON
904 118 MULTIPOLYGON
905 119 MULTIPOLYGON
906 120 GEOMCOLLECTION
907 121 GEOMCOLLECTION
908 122 GEOMCOLLECTION
909 SELECT fid, ST_IsEmpty(g) FROM gis_geometry;
910 fid ST_IsEmpty(g)
911 101 0
912 102 0
913 103 0
914 104 0
915 105 0
916 106 0
917 107 0
918 108 0
919 109 0
920 110 0
921 111 0
922 112 0
923 113 0
924 114 0
925 115 0
926 116 0
927 117 0
928 118 0
929 119 0
930 120 0
931 121 0
932 122 0
933 SELECT fid, ST_AsText(ST_Envelope(g)) FROM gis_geometry;
934 fid ST_AsText(ST_Envelope(g))
935 101 POINT(10 10)
936 102 POINT(20 10)
937 103 POINT(20 20)
938 104 POINT(10 20)
939 105 POLYGON((0 0,10 0,10 10,0 10,0 0))
940 106 POLYGON((10 10,20 10,20 20,10 20,10 10))
941 107 LINESTRING(10 10,40 10)
942 108 POLYGON((10 10,20 10,20 20,10 20,10 10))
943 109 POLYGON((0 0,50 0,50 50,0 50,0 0))
944 110 POLYGON((0 0,30 0,30 30,0 30,0 0))
945 111 POLYGON((0 0,20 0,20 20,0 20,0 0))
946 112 POLYGON((1 1,21 1,21 21,1 21,1 1))
947 113 POLYGON((3 6,4 6,4 10,3 10,3 6))
948 114 POLYGON((10 0,16 0,16 48,10 48,10 0))
949 115 LINESTRING(10 0,10 48)
950 116 POLYGON((1 2,21 2,21 8,1 8,1 2))
951 117 POLYGON((28 0,84 0,84 42,28 42,28 0))
952 118 POLYGON((28 0,84 0,84 42,28 42,28 0))
953 119 POLYGON((0 0,3 0,3 3,0 3,0 0))
954 120 POLYGON((0 0,10 0,10 10,0 10,0 0))
955 121 POLYGON((10 10,20 10,20 20,10 20,10 10))
956 122 POLYGON((3 6,44 6,44 9,3 9,3 6))
957 explain select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry;
958 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
959 1 SIMPLE gis_geometry NULL ALL NULL NULL NULL NULL # 100.00 NULL
960 Warnings:
961 Note 1003 /* select#1 */ 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`
962 #check Geometry point functions
963 SELECT fid, ST_X(g) FROM gis_point;
964 fid ST_X(g)
965 101 10
966 102 20
967 103 20
968 104 10
969 SELECT fid, ST_Y(g) FROM gis_point;
970 fid ST_Y(g)
971 101 10
972 102 10
973 103 20
974 104 20
975 explain select ST_X(g),ST_Y(g) FROM gis_point;
976 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
977 1 SIMPLE gis_point NULL ALL NULL NULL NULL NULL # 100.00 NULL
978 Warnings:
979 Note 1003 /* select#1 */ 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`
980 SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line;
981 fid ST_AsText(ST_StartPoint(g))
982 105 POINT(0 0)
983 106 POINT(10 10)
984 107 POINT(10 10)
985 SELECT fid, ST_AsText(ST_EndPoint(g)) FROM gis_line;
986 fid ST_AsText(ST_EndPoint(g))
987 105 POINT(10 0)
988 106 POINT(10 10)
989 107 POINT(40 10)
990 SELECT fid, ST_Length(g) FROM gis_line;
991 fid ST_Length(g)
992 105 24.14213562373095
993 106 40
994 107 30
995 SELECT fid, ST_NumPoints(g) FROM gis_line;
996 fid ST_NumPoints(g)
997 105 3
998 106 5
999 107 2
1000 SELECT fid, ST_AsText(ST_PointN(g, 2)) FROM gis_line;
1001 fid ST_AsText(ST_PointN(g, 2))
1002 105 POINT(0 10)
1003 106 POINT(20 10)
1004 107 POINT(40 10)
1005 SELECT fid, ST_IsClosed(g) FROM gis_line;
1006 fid ST_IsClosed(g)
1007 105 0
1008 106 1
1009 107 0
1010 explain select ST_AsText(ST_StartPoint(g)),ST_AsText(ST_EndPoint(g)),
1011 ST_Length(g),ST_NumPoints(g),ST_AsText(ST_PointN(g, 2)),ST_IsClosed(g) FROM gis_line;
1012 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1013 1 SIMPLE gis_line NULL ALL NULL NULL NULL NULL # 100.00 NULL
1014 Warnings:
1015 Note 1003 /* select#1 */ 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`
1016 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon;
1017 fid ST_AsText(ST_Centroid(g))
1018 108 POINT(15 15)
1019 109 POINT(25.416666666666668 25.416666666666668)
1020 110 POINT(20 10)
1021 SELECT fid, ST_Area(g) FROM gis_polygon;
1022 fid ST_Area(g)
1023 108 100
1024 109 2400
1025 110 450
1026 SELECT fid, ST_AsText(ST_ExteriorRing(g)) FROM gis_polygon;
1027 fid ST_AsText(ST_ExteriorRing(g))
1028 108 LINESTRING(10 10,20 10,20 20,10 20,10 10)
1029 109 LINESTRING(0 0,50 0,50 50,0 50,0 0)
1030 110 LINESTRING(0 0,30 0,30 30,0 0)
1031 SELECT fid, ST_NumInteriorRings(g) FROM gis_polygon;
1032 fid ST_NumInteriorRings(g)
1033 108 0
1034 109 1
1035 110 0
1036 SELECT fid, ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
1037 fid ST_AsText(ST_InteriorRingN(g, 1))
1038 108 NULL
1039 109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
1040 110 NULL
1041 explain select ST_AsText(ST_Centroid(g)),ST_Area(g),ST_AsText(ST_ExteriorRing(g)),
1042 ST_NumInteriorRings(g),ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
1043 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1044 1 SIMPLE gis_polygon NULL ALL NULL NULL NULL NULL # 100.00 NULL
1045 Warnings:
1046 Note 1003 /* select#1 */ 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`
1047 SELECT fid, ST_IsClosed(g) FROM gis_multi_line;
1048 fid ST_IsClosed(g)
1049 114 0
1050 115 0
1051 116 0
1052 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_multi_polygon;
1053 fid ST_AsText(ST_Centroid(g))
1054 117 POINT(57.98031067576927 17.854754130800437)
1055 118 POINT(57.98031067576927 17.854754130800437)
1056 119 POINT(2 2)
1057 SELECT fid, ST_Area(g) FROM gis_multi_polygon;
1058 fid ST_Area(g)
1059 117 1684.5
1060 118 1684.5
1061 119 4.5
1062 SELECT fid, ST_NumGeometries(g) from gis_multi_point;
1063 fid ST_NumGeometries(g)
1064 111 4
1065 112 4
1066 113 2
1067 SELECT fid, ST_NumGeometries(g) from gis_multi_line;
1068 fid ST_NumGeometries(g)
1069 114 2
1070 115 1
1071 116 2
1072 SELECT fid, ST_NumGeometries(g) from gis_multi_polygon;
1073 fid ST_NumGeometries(g)
1074 117 2
1075 118 2
1076 119 1
1077 SELECT fid, ST_NumGeometries(g) from gis_geometrycollection;
1078 fid ST_NumGeometries(g)
1079 120 2
1080 121 2
1081 122 2
1082 explain SELECT fid, ST_NumGeometries(g) from gis_multi_point;
1083 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1084 1 SIMPLE gis_multi_point NULL ALL NULL NULL NULL NULL # 100.00 NULL
1085 Warnings:
1086 Note 1003 /* select#1 */ select `test`.`gis_multi_point`.`fid` AS `fid`,st_numgeometries(`test`.`gis_multi_point`.`g`) AS `ST_NumGeometries(g)` from `test`.`gis_multi_point`
1087 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
1088 fid ST_AsText(ST_GeometryN(g, 2))
1089 111 POINT(10 10)
1090 112 POINT(11 11)
1091 113 POINT(4 10)
1092 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_line;
1093 fid ST_AsText(ST_GeometryN(g, 2))
1094 114 LINESTRING(16 0,16 23,16 48)
1095 115 NULL
1096 116 LINESTRING(2 5,5 8,21 7)
1097 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_polygon;
1098 fid ST_AsText(ST_GeometryN(g, 2))
1099 117 POLYGON((59 18,67 18,67 13,59 13,59 18))
1100 118 POLYGON((59 18,67 18,67 13,59 13,59 18))
1101 119 NULL
1102 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_geometrycollection;
1103 fid ST_AsText(ST_GeometryN(g, 2))
1104 120 LINESTRING(0 0,10 10)
1105 121 LINESTRING(10 10,20 20)
1106 122 LINESTRING(3 6,7 9)
1107 SELECT fid, ST_AsText(ST_GeometryN(g, 1)) from gis_geometrycollection;
1108 fid ST_AsText(ST_GeometryN(g, 1))
1109 120 POINT(0 0)
1110 121 POINT(10 10)
1111 122 POINT(44 6)
1112 explain SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
1113 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1114 1 SIMPLE gis_multi_point NULL ALL NULL NULL NULL NULL # 100.00 NULL
1115 Warnings:
1116 Note 1003 /* select#1 */ 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`
1117 SELECT g1.fid as first, g2.fid as second,
1118 MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
1119 MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
1120 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
1121 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
1122 first second w c o e d t i r
1123 120 120 1 1 0 1 0 0 1 0
1124 120 121 0 0 0 0 0 1 1 0
1125 120 122 0 0 1 0 0 0 1 0
1126 121 120 0 0 0 0 0 1 1 0
1127 121 121 1 1 0 1 0 0 1 0
1128 121 122 0 0 0 0 1 0 0 0
1129 122 120 0 0 1 0 0 0 1 0
1130 122 121 0 0 0 0 1 0 0 0
1131 122 122 1 1 0 1 0 0 1 0
1132 explain SELECT g1.fid as first, g2.fid as second,
1133 MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
1134 MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
1135 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
1136 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
1137 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1138 1 SIMPLE g1 NULL ALL NULL NULL NULL NULL # 100.00 Using temporary; Using filesort
1139 1 SIMPLE g2 NULL ALL NULL NULL NULL NULL # 100.00 Using join buffer (hash join)
1140 Warnings:
1141 Note 1003 /* select#1 */ 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 `first`,`second`
1142 # check support of Foreign Key constraint
1143 CREATE TABLE parent (id GEOMETRY NOT NULL,PRIMARY KEY (id(10))) ENGINE=INNODB;
1144 ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
1145 CREATE TABLE child (id GEOMETRY, parent_id GEOMETRY,
1146 INDEX par_ind (parent_id(10)),
1147 FOREIGN KEY (parent_id(10)) REFERENCES parent(id)
1148 ON DELETE CASCADE
1149 ) ENGINE=INNODB;
1150 ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
1151 #check partition table support
1152 CREATE TABLE emp2(
1153 id GEOMETRY NOT NULL,
1154 store_name VARCHAR(30),
1155 parts VARCHAR(30),
1156 store_id GEOMETRY
1157 )
1158 PARTITION BY LIST(store_id) (
1159 PARTITION pNorth VALUES IN (10,20,30) ,
1160 PARTITION pEast VALUES IN (40,50,60) ,
1161 PARTITION pWest VALUES IN (70,80,100)
1162 );
1163 ERROR 42000: The storage engine for the table doesn't support GEOMETRY
1164 #check start transaction commit & Rollback
1165 START TRANSACTION;
1166 DELETE FROM tab3;
1167 ROLLBACK;
1168 SELECT COUNT(*) FROM tab3;
1169 COUNT(*)
1170 1
1171 START TRANSACTION;
1172 DELETE FROM tab3;
1173 COMMIT;
1174 SELECT COUNT(*) FROM tab3;
1175 COUNT(*)
1176 0
1177 DROP TABLE IF EXISTS gis_point, gis_line, gis_polygon, gis_multi_point;
1178 DROP TABLE IF EXISTS gis_multi_line, gis_multi_polygon;
1179 DROP TABLE IF EXISTS gis_geometrycollection, gis_geometry;
1180 DROP TABLE IF EXISTS tab,tab2,tab3,parent,emp2;
1181 Warnings:
1182 Note 1051 Unknown table 'test.parent'
1183 Note 1051 Unknown table 'test.emp2'
1184 DROP PROCEDURE IF EXISTS geominout;
1185 DROP PROCEDURE IF EXISTS geom_insert;
1186 DROP TRIGGER IF EXISTS geom_trigger;
1187 Warnings:
1188 Note 1360 Trigger does not exist
1189 DROP PROCEDURE IF EXISTS geom_cursor;