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