Debian Codegraph @

mysql-test/suite/ndbcluster/gis.result

raw
1 set @save_ndb_read_backup = @@global.ndb_read_backup;
2 set @@global.ndb_read_backup = OFF;
3 SET default_storage_engine=ndbcluster;
4 SET ndb_use_exact_count=1;
5 DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
6 CREATE TABLE gis_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT);
7 CREATE TABLE gis_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g LINESTRING);
8 CREATE TABLE gis_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POLYGON);
9 CREATE TABLE gis_multi_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOINT);
10 CREATE TABLE gis_multi_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTILINESTRING);
11 CREATE TABLE gis_multi_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOLYGON);
12 CREATE TABLE gis_geometrycollection (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRYCOLLECTION);
13 CREATE TABLE gis_geometry (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRY);
14 SHOW CREATE TABLE gis_point;
15 Table Create Table
16 gis_point CREATE TABLE `gis_point` (
17 `fid` int NOT NULL AUTO_INCREMENT,
18 `g` point DEFAULT NULL,
19 PRIMARY KEY (`fid`)
20 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
21 SHOW FIELDS FROM gis_point;
22 Field Type Null Key Default Extra
23 fid int NO PRI NULL auto_increment
24 g point YES NULL
25 SHOW FIELDS FROM gis_line;
26 Field Type Null Key Default Extra
27 fid int NO PRI NULL auto_increment
28 g linestring YES NULL
29 SHOW FIELDS FROM gis_polygon;
30 Field Type Null Key Default Extra
31 fid int NO PRI NULL auto_increment
32 g polygon YES NULL
33 SHOW FIELDS FROM gis_multi_point;
34 Field Type Null Key Default Extra
35 fid int NO PRI NULL auto_increment
36 g multipoint YES NULL
37 SHOW FIELDS FROM gis_multi_line;
38 Field Type Null Key Default Extra
39 fid int NO PRI NULL auto_increment
40 g multilinestring YES NULL
41 SHOW FIELDS FROM gis_multi_polygon;
42 Field Type Null Key Default Extra
43 fid int NO PRI NULL auto_increment
44 g multipolygon YES NULL
45 SHOW FIELDS FROM gis_geometrycollection;
46 Field Type Null Key Default Extra
47 fid int NO PRI NULL auto_increment
48 g geomcollection YES NULL
49 SHOW FIELDS FROM gis_geometry;
50 Field Type Null Key Default Extra
51 fid int NO PRI NULL auto_increment
52 g geometry YES NULL
53 INSERT INTO gis_point VALUES
54 (101, ST_PointFromText('POINT(10 10)')),
55 (102, ST_PointFromText('POINT(20 10)')),
56 (103, ST_PointFromText('POINT(20 20)')),
57 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
58 INSERT INTO gis_line VALUES
59 (105, ST_LineFromText('LINESTRING(0 0,0 10,10 0)')),
60 (106, ST_LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
61 (107, ST_LineStringFromWKB(St_AsWKB(LineString(Point(10, 10), Point(40, 10)))));
62 INSERT INTO gis_polygon VALUES
63 (108, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
64 (109, ST_PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
65 (110, ST_PolyFromWKB(St_AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))));
66 INSERT INTO gis_multi_point VALUES
67 (111, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
68 (112, ST_MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
69 (113, ST_MPointFromWKB(St_AsWKB(MultiPoint(Point(3, 6), Point(4, 10)))));
70 INSERT INTO gis_multi_line VALUES
71 (114, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
72 (115, ST_MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
73 (116, ST_MLineFromWKB(St_AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7))))));
74 INSERT INTO gis_multi_polygon VALUES
75 (117, ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')),
76 (118, ST_MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')),
77 (119, ST_MPolyFromWKB(St_AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))));
78 INSERT INTO gis_geometrycollection VALUES
79 (120, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
80 (121, ST_GeometryFromWKB(St_AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))))));
81 INSERT into gis_geometry SELECT * FROM gis_point;
82 INSERT into gis_geometry SELECT * FROM gis_line;
83 INSERT into gis_geometry SELECT * FROM gis_polygon;
84 INSERT into gis_geometry SELECT * FROM gis_multi_point;
85 INSERT into gis_geometry SELECT * FROM gis_multi_line;
86 INSERT into gis_geometry SELECT * FROM gis_multi_polygon;
87 INSERT into gis_geometry SELECT * FROM gis_geometrycollection;
88 SELECT fid, ST_AsText(g) FROM gis_point ORDER by fid;
89 fid ST_AsText(g)
90 101 POINT(10 10)
91 102 POINT(20 10)
92 103 POINT(20 20)
93 104 POINT(10 20)
94 SELECT fid, ST_AsText(g) FROM gis_line ORDER by fid;
95 fid ST_AsText(g)
96 105 LINESTRING(0 0,0 10,10 0)
97 106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
98 107 LINESTRING(10 10,40 10)
99 SELECT fid, ST_AsText(g) FROM gis_polygon ORDER by fid;
100 fid ST_AsText(g)
101 108 POLYGON((10 10,20 10,20 20,10 20,10 10))
102 109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
103 110 POLYGON((0 0,30 0,30 30,0 0))
104 SELECT fid, ST_AsText(g) FROM gis_multi_point ORDER by fid;
105 fid ST_AsText(g)
106 111 MULTIPOINT((0 0),(10 10),(10 20),(20 20))
107 112 MULTIPOINT((1 1),(11 11),(11 21),(21 21))
108 113 MULTIPOINT((3 6),(4 10))
109 SELECT fid, ST_AsText(g) FROM gis_multi_line ORDER by fid;
110 fid ST_AsText(g)
111 114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
112 115 MULTILINESTRING((10 48,10 21,10 0))
113 116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
114 SELECT fid, ST_AsText(g) FROM gis_multi_polygon ORDER by fid;
115 fid ST_AsText(g)
116 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)))
117 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)))
118 119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
119 SELECT fid, ST_AsText(g) FROM gis_geometrycollection ORDER by fid;
120 fid ST_AsText(g)
121 120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
122 121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
123 SELECT fid, ST_AsText(g) FROM gis_geometry ORDER by fid;
124 fid ST_AsText(g)
125 101 POINT(10 10)
126 102 POINT(20 10)
127 103 POINT(20 20)
128 104 POINT(10 20)
129 105 LINESTRING(0 0,0 10,10 0)
130 106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
131 107 LINESTRING(10 10,40 10)
132 108 POLYGON((10 10,20 10,20 20,10 20,10 10))
133 109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
134 110 POLYGON((0 0,30 0,30 30,0 0))
135 111 MULTIPOINT((0 0),(10 10),(10 20),(20 20))
136 112 MULTIPOINT((1 1),(11 11),(11 21),(21 21))
137 113 MULTIPOINT((3 6),(4 10))
138 114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
139 115 MULTILINESTRING((10 48,10 21,10 0))
140 116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
141 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)))
142 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)))
143 119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
144 120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
145 121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
146 SELECT fid, ST_Dimension(g) FROM gis_geometry ORDER by fid;
147 fid ST_Dimension(g)
148 101 0
149 102 0
150 103 0
151 104 0
152 105 1
153 106 1
154 107 1
155 108 2
156 109 2
157 110 2
158 111 0
159 112 0
160 113 0
161 114 1
162 115 1
163 116 1
164 117 2
165 118 2
166 119 2
167 120 1
168 121 1
169 SELECT fid, ST_GeometryType(g) FROM gis_geometry ORDER by fid;
170 fid ST_GeometryType(g)
171 101 POINT
172 102 POINT
173 103 POINT
174 104 POINT
175 105 LINESTRING
176 106 LINESTRING
177 107 LINESTRING
178 108 POLYGON
179 109 POLYGON
180 110 POLYGON
181 111 MULTIPOINT
182 112 MULTIPOINT
183 113 MULTIPOINT
184 114 MULTILINESTRING
185 115 MULTILINESTRING
186 116 MULTILINESTRING
187 117 MULTIPOLYGON
188 118 MULTIPOLYGON
189 119 MULTIPOLYGON
190 120 GEOMCOLLECTION
191 121 GEOMCOLLECTION
192 SELECT fid, ST_IsEmpty(g) FROM gis_geometry ORDER by fid;
193 fid ST_IsEmpty(g)
194 101 0
195 102 0
196 103 0
197 104 0
198 105 0
199 106 0
200 107 0
201 108 0
202 109 0
203 110 0
204 111 0
205 112 0
206 113 0
207 114 0
208 115 0
209 116 0
210 117 0
211 118 0
212 119 0
213 120 0
214 121 0
215 SELECT fid, ST_AsText(ST_Envelope(g)) FROM gis_geometry ORDER by fid;
216 fid ST_AsText(ST_Envelope(g))
217 101 POINT(10 10)
218 102 POINT(20 10)
219 103 POINT(20 20)
220 104 POINT(10 20)
221 105 POLYGON((0 0,10 0,10 10,0 10,0 0))
222 106 POLYGON((10 10,20 10,20 20,10 20,10 10))
223 107 LINESTRING(10 10,40 10)
224 108 POLYGON((10 10,20 10,20 20,10 20,10 10))
225 109 POLYGON((0 0,50 0,50 50,0 50,0 0))
226 110 POLYGON((0 0,30 0,30 30,0 30,0 0))
227 111 POLYGON((0 0,20 0,20 20,0 20,0 0))
228 112 POLYGON((1 1,21 1,21 21,1 21,1 1))
229 113 POLYGON((3 6,4 6,4 10,3 10,3 6))
230 114 POLYGON((10 0,16 0,16 48,10 48,10 0))
231 115 LINESTRING(10 0,10 48)
232 116 POLYGON((1 2,21 2,21 8,1 8,1 2))
233 117 POLYGON((28 0,84 0,84 42,28 42,28 0))
234 118 POLYGON((28 0,84 0,84 42,28 42,28 0))
235 119 POLYGON((0 0,3 0,3 3,0 3,0 0))
236 120 POLYGON((0 0,10 0,10 10,0 10,0 0))
237 121 POLYGON((3 6,44 6,44 9,3 9,3 6))
238 explain select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry;
239 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
240 1 SIMPLE gis_geometry p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 21 100.00 NULL
241 Warnings:
242 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`
243 SELECT fid, ST_X(g) FROM gis_point ORDER by fid;
244 fid ST_X(g)
245 101 10
246 102 20
247 103 20
248 104 10
249 SELECT fid, ST_Y(g) FROM gis_point ORDER by fid;
250 fid ST_Y(g)
251 101 10
252 102 10
253 103 20
254 104 20
255 explain select ST_X(g),ST_Y(g) FROM gis_point;
256 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
257 1 SIMPLE gis_point p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 4 100.00 NULL
258 Warnings:
259 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`
260 SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line ORDER by fid;
261 fid ST_AsText(ST_StartPoint(g))
262 105 POINT(0 0)
263 106 POINT(10 10)
264 107 POINT(10 10)
265 SELECT fid, ST_AsText(ST_EndPoint(g)) FROM gis_line ORDER by fid;
266 fid ST_AsText(ST_EndPoint(g))
267 105 POINT(10 0)
268 106 POINT(10 10)
269 107 POINT(40 10)
270 SELECT fid, ST_Length(g) FROM gis_line ORDER by fid;
271 fid ST_Length(g)
272 105 24.14213562373095
273 106 40
274 107 30
275 SELECT fid, ST_NumPoints(g) FROM gis_line ORDER by fid;
276 fid ST_NumPoints(g)
277 105 3
278 106 5
279 107 2
280 SELECT fid, ST_AsText(ST_PointN(g, 2)) FROM gis_line ORDER by fid;
281 fid ST_AsText(ST_PointN(g, 2))
282 105 POINT(0 10)
283 106 POINT(20 10)
284 107 POINT(40 10)
285 SELECT fid, ST_IsClosed(g) FROM gis_line ORDER by fid;
286 fid ST_IsClosed(g)
287 105 0
288 106 1
289 107 0
290 explain select ST_AsText(ST_StartPoint(g)),ST_AsText(ST_EndPoint(g)),ST_Length(g),ST_NumPoints(g),ST_AsText(ST_PointN(g, 2)),ST_IsClosed(g) FROM gis_line;
291 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
292 1 SIMPLE gis_line p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 100.00 NULL
293 Warnings:
294 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`
295 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon ORDER by fid;
296 fid ST_AsText(ST_Centroid(g))
297 108 POINT(15 15)
298 109 POINT(25.416666666666668 25.416666666666668)
299 110 POINT(20 10)
300 SELECT fid, ST_Area(g) FROM gis_polygon ORDER by fid;
301 fid ST_Area(g)
302 108 100
303 109 2400
304 110 450
305 SELECT fid, ST_AsText(ST_ExteriorRing(g)) FROM gis_polygon ORDER by fid;
306 fid ST_AsText(ST_ExteriorRing(g))
307 108 LINESTRING(10 10,20 10,20 20,10 20,10 10)
308 109 LINESTRING(0 0,50 0,50 50,0 50,0 0)
309 110 LINESTRING(0 0,30 0,30 30,0 0)
310 SELECT fid, ST_NumInteriorRings(g) FROM gis_polygon ORDER by fid;
311 fid ST_NumInteriorRings(g)
312 108 0
313 109 1
314 110 0
315 SELECT fid, ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid;
316 fid ST_AsText(ST_InteriorRingN(g, 1))
317 108 NULL
318 109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
319 110 NULL
320 explain select ST_AsText(ST_Centroid(g)),ST_Area(g),ST_AsText(ST_ExteriorRing(g)),ST_NumInteriorRings(g),ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
321 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
322 1 SIMPLE gis_polygon p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 100.00 NULL
323 Warnings:
324 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`
325 SELECT fid, ST_IsClosed(g) FROM gis_multi_line ORDER by fid;
326 fid ST_IsClosed(g)
327 114 0
328 115 0
329 116 0
330 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_multi_polygon ORDER by fid;
331 fid ST_AsText(ST_Centroid(g))
332 117 POINT(57.98031067576927 17.854754130800437)
333 118 POINT(57.98031067576927 17.854754130800437)
334 119 POINT(2 2)
335 SELECT fid, ST_Area(g) FROM gis_multi_polygon ORDER by fid;
336 fid ST_Area(g)
337 117 1684.5
338 118 1684.5
339 119 4.5
340 SELECT fid, ST_NumGeometries(g) from gis_multi_point ORDER by fid;
341 fid ST_NumGeometries(g)
342 111 4
343 112 4
344 113 2
345 SELECT fid, ST_NumGeometries(g) from gis_multi_line ORDER by fid;
346 fid ST_NumGeometries(g)
347 114 2
348 115 1
349 116 2
350 SELECT fid, ST_NumGeometries(g) from gis_multi_polygon ORDER by fid;
351 fid ST_NumGeometries(g)
352 117 2
353 118 2
354 119 1
355 SELECT fid, ST_NumGeometries(g) from gis_geometrycollection ORDER by fid;
356 fid ST_NumGeometries(g)
357 120 2
358 121 2
359 explain SELECT fid, ST_NumGeometries(g) from gis_multi_point;
360 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
361 1 SIMPLE gis_multi_point p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 100.00 NULL
362 Warnings:
363 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`
364 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point ORDER by fid;
365 fid ST_AsText(ST_GeometryN(g, 2))
366 111 POINT(10 10)
367 112 POINT(11 11)
368 113 POINT(4 10)
369 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_line ORDER by fid;
370 fid ST_AsText(ST_GeometryN(g, 2))
371 114 LINESTRING(16 0,16 23,16 48)
372 115 NULL
373 116 LINESTRING(2 5,5 8,21 7)
374 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_polygon ORDER by fid;
375 fid ST_AsText(ST_GeometryN(g, 2))
376 117 POLYGON((59 18,67 18,67 13,59 13,59 18))
377 118 POLYGON((59 18,67 18,67 13,59 13,59 18))
378 119 NULL
379 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_geometrycollection ORDER by fid;
380 fid ST_AsText(ST_GeometryN(g, 2))
381 120 LINESTRING(0 0,10 10)
382 121 LINESTRING(3 6,7 9)
383 SELECT fid, ST_AsText(ST_GeometryN(g, 1)) from gis_geometrycollection ORDER by fid;
384 fid ST_AsText(ST_GeometryN(g, 1))
385 120 POINT(0 0)
386 121 POINT(44 6)
387 explain SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
388 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
389 1 SIMPLE gis_multi_point p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 100.00 NULL
390 Warnings:
391 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`
392 SELECT g1.fid as first, g2.fid as second,
393 MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
394 MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
395 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
396 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
397 first second w c o e d t i r
398 120 120 1 1 0 1 0 0 1 0
399 120 121 0 0 1 0 0 0 1 0
400 121 120 0 0 1 0 0 0 1 0
401 121 121 1 1 0 1 0 0 1 0
402 explain SELECT g1.fid as first, g2.fid as second,
403 MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
404 MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
405 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
406 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
407 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
408 1 SIMPLE g1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
409 1 SIMPLE g2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (hash join)
410 Warnings:
411 Note 1003 Table 'g1' is not pushable: select list can't contain BLOB columns
412 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`
413 DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
414 CREATE TABLE t1 (
415 a INTEGER PRIMARY KEY AUTO_INCREMENT,
416 gp point,
417 ln linestring,
418 pg polygon,
419 mp multipoint,
420 mln multilinestring,
421 mpg multipolygon,
422 gc geometrycollection,
423 gm geometry
424 );
425 SHOW FIELDS FROM t1;
426 Field Type Null Key Default Extra
427 a int NO PRI NULL auto_increment
428 gp point YES NULL
429 ln linestring YES NULL
430 pg polygon YES NULL
431 mp multipoint YES NULL
432 mln multilinestring YES NULL
433 mpg multipolygon YES NULL
434 gc geomcollection YES NULL
435 gm geometry YES NULL
436 ALTER TABLE t1 ADD fid INT;
437 Warnings:
438 Warning 1478 Converted FIXED field 'fid' to DYNAMIC to enable online ADD COLUMN
439 SHOW FIELDS FROM t1;
440 Field Type Null Key Default Extra
441 a int NO PRI NULL auto_increment
442 gp point YES NULL
443 ln linestring YES NULL
444 pg polygon YES NULL
445 mp multipoint YES NULL
446 mln multilinestring YES NULL
447 mpg multipolygon YES NULL
448 gc geomcollection YES NULL
449 gm geometry YES NULL
450 fid int YES NULL
451 DROP TABLE t1;
452 create table t1 (pk integer primary key auto_increment, a geometry not null);
453 insert into t1 (a) values (ST_GeomFromText('Point(1 2)'));
454 insert into t1 (a) values ('Garbage');
455 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
456 insert IGNORE into t1 (a) values ('Garbage');
457 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
458 drop table t1;
459 create table t1 (pk integer primary key auto_increment, fl geometry not null);
460 insert into t1 (fl) values (1);
461 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
462 insert into t1 (fl) values (1.11);
463 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
464 insert into t1 (fl) values ("qwerty");
465 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
466 insert into t1 (fl) values (ST_pointfromtext('point(1,1)'));
467 ERROR 22023: Invalid GIS data provided to function st_pointfromtext.
468 drop table t1;
469 End of 4.1 tests
470 CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY);
471 Warnings:
472 Warning 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK'
473 INSERT INTO t1 VALUES("center", ST_GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));
474 INSERT INTO t1 VALUES("small", ST_GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
475 INSERT INTO t1 VALUES("big", ST_GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
476 INSERT INTO t1 VALUES("up", ST_GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));
477 INSERT INTO t1 VALUES("up2", ST_GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));
478 INSERT INTO t1 VALUES("up3", ST_GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));
479 INSERT INTO t1 VALUES("down", ST_GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));
480 INSERT INTO t1 VALUES("down2", ST_GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));
481 INSERT INTO t1 VALUES("down3", ST_GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
482 INSERT INTO t1 VALUES("right", ST_GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
483 INSERT INTO t1 VALUES("right2", ST_GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
484 INSERT INTO t1 VALUES("right3", ST_GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
485 INSERT INTO t1 VALUES("left", ST_GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));
486 INSERT INTO t1 VALUES("left2", ST_GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));
487 INSERT INTO t1 VALUES("left3", ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))'));
488 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrcontains FROM t1 a1 JOIN t1 a2 ON MBRContains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
489 mbrcontains
490 center,small
491 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrdisjoint FROM t1 a1 JOIN t1 a2 ON MBRDisjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
492 mbrdisjoint
493 down3,left3,right3,up3
494 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrequals FROM t1 a1 JOIN t1 a2 ON MBREquals( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
495 mbrequals
496 center
497 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrintersect FROM t1 a1 JOIN t1 a2 ON MBRIntersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
498 mbrintersect
499 big,center,down,down2,left,left2,right,right2,small,up,up2
500 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbroverlaps FROM t1 a1 JOIN t1 a2 ON MBROverlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
501 mbroverlaps
502 down,left,right,up
503 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrtouches FROM t1 a1 JOIN t1 a2 ON MBRTouches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
504 mbrtouches
505 down2,left2,right2,up2
506 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrwithin FROM t1 a1 JOIN t1 a2 ON MBRWithin( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
507 mbrwithin
508 big,center
509 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRcontains FROM t1 a1 JOIN t1 a2 ON MBRContains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
510 MBRcontains
511 center,small
512 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRdisjoint FROM t1 a1 JOIN t1 a2 ON MBRDisjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
513 MBRdisjoint
514 down3,left3,right3,up3
515 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRequals FROM t1 a1 JOIN t1 a2 ON MBREquals( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
516 MBRequals
517 center
518 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS `intersect` FROM t1 a1 JOIN t1 a2 ON MBRIntersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
519 intersect
520 big,center,down,down2,left,left2,right,right2,small,up,up2
521 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRoverlaps FROM t1 a1 JOIN t1 a2 ON MBROverlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
522 MBRoverlaps
523 down,left,right,up
524 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS ST_touches FROM t1 a1 JOIN t1 a2 ON ST_Touches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
525 ST_touches
526 down2,left2,right2,up2
527 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRwithin FROM t1 a1 JOIN t1 a2 ON MBRWithin( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
528 MBRwithin
529 big,center
530 SET @vert1 = ST_GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');
531 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
532 SET @horiz1 = ST_GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');
533 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
534 SET @horiz2 = ST_GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');
535 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
536 SET @horiz3 = ST_GeomFromText('POLYGON ((2 0, 3 0, 2 0))');
537 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
538 SET @point1 = ST_GeomFromText('POLYGON ((0 0))');
539 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
540 SET @point2 = ST_GeomFromText('POLYGON ((-2 0))');
541 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
542 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @vert1) GROUP BY a1.name;
543 MBRoverlaps
544 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @horiz1) GROUP BY a1.name;
545 MBRoverlaps
546 SELECT MBROverlaps(@horiz1, @vert1) FROM DUAL;
547 MBROverlaps(@horiz1, @vert1)
548 NULL
549 SELECT MBROverlaps(@horiz1, @horiz2) FROM DUAL;
550 MBROverlaps(@horiz1, @horiz2)
551 NULL
552 SELECT MBROverlaps(@horiz1, @horiz3) FROM DUAL;
553 MBROverlaps(@horiz1, @horiz3)
554 NULL
555 SELECT MBROverlaps(@horiz1, @point1) FROM DUAL;
556 MBROverlaps(@horiz1, @point1)
557 NULL
558 SELECT MBROverlaps(@horiz1, @point2) FROM DUAL;
559 MBROverlaps(@horiz1, @point2)
560 NULL
561 DROP TABLE t1;
562 End of 5.0 tests
563 set @@optimizer_switch = 'engine_condition_pushdown=on';
564 DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
565 CREATE TABLE gis_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT);
566 CREATE TABLE gis_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g LINESTRING);
567 CREATE TABLE gis_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POLYGON);
568 CREATE TABLE gis_multi_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOINT);
569 CREATE TABLE gis_multi_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTILINESTRING);
570 CREATE TABLE gis_multi_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOLYGON);
571 CREATE TABLE gis_geometrycollection (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRYCOLLECTION);
572 CREATE TABLE gis_geometry (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRY);
573 SHOW CREATE TABLE gis_point;
574 Table Create Table
575 gis_point CREATE TABLE `gis_point` (
576 `fid` int NOT NULL AUTO_INCREMENT,
577 `g` point DEFAULT NULL,
578 PRIMARY KEY (`fid`)
579 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
580 SHOW FIELDS FROM gis_point;
581 Field Type Null Key Default Extra
582 fid int NO PRI NULL auto_increment
583 g point YES NULL
584 SHOW FIELDS FROM gis_line;
585 Field Type Null Key Default Extra
586 fid int NO PRI NULL auto_increment
587 g linestring YES NULL
588 SHOW FIELDS FROM gis_polygon;
589 Field Type Null Key Default Extra
590 fid int NO PRI NULL auto_increment
591 g polygon YES NULL
592 SHOW FIELDS FROM gis_multi_point;
593 Field Type Null Key Default Extra
594 fid int NO PRI NULL auto_increment
595 g multipoint YES NULL
596 SHOW FIELDS FROM gis_multi_line;
597 Field Type Null Key Default Extra
598 fid int NO PRI NULL auto_increment
599 g multilinestring YES NULL
600 SHOW FIELDS FROM gis_multi_polygon;
601 Field Type Null Key Default Extra
602 fid int NO PRI NULL auto_increment
603 g multipolygon YES NULL
604 SHOW FIELDS FROM gis_geometrycollection;
605 Field Type Null Key Default Extra
606 fid int NO PRI NULL auto_increment
607 g geomcollection YES NULL
608 SHOW FIELDS FROM gis_geometry;
609 Field Type Null Key Default Extra
610 fid int NO PRI NULL auto_increment
611 g geometry YES NULL
612 INSERT INTO gis_point VALUES
613 (101, ST_PointFromText('POINT(10 10)')),
614 (102, ST_PointFromText('POINT(20 10)')),
615 (103, ST_PointFromText('POINT(20 20)')),
616 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
617 INSERT INTO gis_line VALUES
618 (105, ST_LineFromText('LINESTRING(0 0,0 10,10 0)')),
619 (106, ST_LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
620 (107, ST_LineStringFromWKB(St_AsWKB(LineString(Point(10, 10), Point(40, 10)))));
621 INSERT INTO gis_polygon VALUES
622 (108, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
623 (109, ST_PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
624 (110, ST_PolyFromWKB(St_AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))));
625 INSERT INTO gis_multi_point VALUES
626 (111, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
627 (112, ST_MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
628 (113, ST_MPointFromWKB(St_AsWKB(MultiPoint(Point(3, 6), Point(4, 10)))));
629 INSERT INTO gis_multi_line VALUES
630 (114, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
631 (115, ST_MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
632 (116, ST_MLineFromWKB(St_AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7))))));
633 INSERT INTO gis_multi_polygon VALUES
634 (117, ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')),
635 (118, ST_MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')),
636 (119, ST_MPolyFromWKB(St_AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))));
637 INSERT INTO gis_geometrycollection VALUES
638 (120, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
639 (121, ST_GeometryFromWKB(St_AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))))));
640 INSERT into gis_geometry SELECT * FROM gis_point;
641 INSERT into gis_geometry SELECT * FROM gis_line;
642 INSERT into gis_geometry SELECT * FROM gis_polygon;
643 INSERT into gis_geometry SELECT * FROM gis_multi_point;
644 INSERT into gis_geometry SELECT * FROM gis_multi_line;
645 INSERT into gis_geometry SELECT * FROM gis_multi_polygon;
646 INSERT into gis_geometry SELECT * FROM gis_geometrycollection;
647 SELECT fid, ST_AsText(g) FROM gis_point ORDER by fid;
648 fid ST_AsText(g)
649 101 POINT(10 10)
650 102 POINT(20 10)
651 103 POINT(20 20)
652 104 POINT(10 20)
653 SELECT fid, ST_AsText(g) FROM gis_line ORDER by fid;
654 fid ST_AsText(g)
655 105 LINESTRING(0 0,0 10,10 0)
656 106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
657 107 LINESTRING(10 10,40 10)
658 SELECT fid, ST_AsText(g) FROM gis_polygon ORDER by fid;
659 fid ST_AsText(g)
660 108 POLYGON((10 10,20 10,20 20,10 20,10 10))
661 109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
662 110 POLYGON((0 0,30 0,30 30,0 0))
663 SELECT fid, ST_AsText(g) FROM gis_multi_point ORDER by fid;
664 fid ST_AsText(g)
665 111 MULTIPOINT((0 0),(10 10),(10 20),(20 20))
666 112 MULTIPOINT((1 1),(11 11),(11 21),(21 21))
667 113 MULTIPOINT((3 6),(4 10))
668 SELECT fid, ST_AsText(g) FROM gis_multi_line ORDER by fid;
669 fid ST_AsText(g)
670 114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
671 115 MULTILINESTRING((10 48,10 21,10 0))
672 116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
673 SELECT fid, ST_AsText(g) FROM gis_multi_polygon ORDER by fid;
674 fid ST_AsText(g)
675 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)))
676 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)))
677 119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
678 SELECT fid, ST_AsText(g) FROM gis_geometrycollection ORDER by fid;
679 fid ST_AsText(g)
680 120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
681 121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
682 SELECT fid, ST_AsText(g) FROM gis_geometry ORDER by fid;
683 fid ST_AsText(g)
684 101 POINT(10 10)
685 102 POINT(20 10)
686 103 POINT(20 20)
687 104 POINT(10 20)
688 105 LINESTRING(0 0,0 10,10 0)
689 106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
690 107 LINESTRING(10 10,40 10)
691 108 POLYGON((10 10,20 10,20 20,10 20,10 10))
692 109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
693 110 POLYGON((0 0,30 0,30 30,0 0))
694 111 MULTIPOINT((0 0),(10 10),(10 20),(20 20))
695 112 MULTIPOINT((1 1),(11 11),(11 21),(21 21))
696 113 MULTIPOINT((3 6),(4 10))
697 114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
698 115 MULTILINESTRING((10 48,10 21,10 0))
699 116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
700 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)))
701 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)))
702 119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
703 120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
704 121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
705 SELECT fid, ST_Dimension(g) FROM gis_geometry ORDER by fid;
706 fid ST_Dimension(g)
707 101 0
708 102 0
709 103 0
710 104 0
711 105 1
712 106 1
713 107 1
714 108 2
715 109 2
716 110 2
717 111 0
718 112 0
719 113 0
720 114 1
721 115 1
722 116 1
723 117 2
724 118 2
725 119 2
726 120 1
727 121 1
728 SELECT fid, ST_GeometryType(g) FROM gis_geometry ORDER by fid;
729 fid ST_GeometryType(g)
730 101 POINT
731 102 POINT
732 103 POINT
733 104 POINT
734 105 LINESTRING
735 106 LINESTRING
736 107 LINESTRING
737 108 POLYGON
738 109 POLYGON
739 110 POLYGON
740 111 MULTIPOINT
741 112 MULTIPOINT
742 113 MULTIPOINT
743 114 MULTILINESTRING
744 115 MULTILINESTRING
745 116 MULTILINESTRING
746 117 MULTIPOLYGON
747 118 MULTIPOLYGON
748 119 MULTIPOLYGON
749 120 GEOMCOLLECTION
750 121 GEOMCOLLECTION
751 SELECT fid, ST_IsEmpty(g) FROM gis_geometry ORDER by fid;
752 fid ST_IsEmpty(g)
753 101 0
754 102 0
755 103 0
756 104 0
757 105 0
758 106 0
759 107 0
760 108 0
761 109 0
762 110 0
763 111 0
764 112 0
765 113 0
766 114 0
767 115 0
768 116 0
769 117 0
770 118 0
771 119 0
772 120 0
773 121 0
774 SELECT fid, ST_AsText(ST_Envelope(g)) FROM gis_geometry ORDER by fid;
775 fid ST_AsText(ST_Envelope(g))
776 101 POINT(10 10)
777 102 POINT(20 10)
778 103 POINT(20 20)
779 104 POINT(10 20)
780 105 POLYGON((0 0,10 0,10 10,0 10,0 0))
781 106 POLYGON((10 10,20 10,20 20,10 20,10 10))
782 107 LINESTRING(10 10,40 10)
783 108 POLYGON((10 10,20 10,20 20,10 20,10 10))
784 109 POLYGON((0 0,50 0,50 50,0 50,0 0))
785 110 POLYGON((0 0,30 0,30 30,0 30,0 0))
786 111 POLYGON((0 0,20 0,20 20,0 20,0 0))
787 112 POLYGON((1 1,21 1,21 21,1 21,1 1))
788 113 POLYGON((3 6,4 6,4 10,3 10,3 6))
789 114 POLYGON((10 0,16 0,16 48,10 48,10 0))
790 115 LINESTRING(10 0,10 48)
791 116 POLYGON((1 2,21 2,21 8,1 8,1 2))
792 117 POLYGON((28 0,84 0,84 42,28 42,28 0))
793 118 POLYGON((28 0,84 0,84 42,28 42,28 0))
794 119 POLYGON((0 0,3 0,3 3,0 3,0 0))
795 120 POLYGON((0 0,10 0,10 10,0 10,0 0))
796 121 POLYGON((3 6,44 6,44 9,3 9,3 6))
797 explain select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry;
798 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
799 1 SIMPLE gis_geometry p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 21 100.00 NULL
800 Warnings:
801 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`
802 SELECT fid, ST_X(g) FROM gis_point ORDER by fid;
803 fid ST_X(g)
804 101 10
805 102 20
806 103 20
807 104 10
808 SELECT fid, ST_Y(g) FROM gis_point ORDER by fid;
809 fid ST_Y(g)
810 101 10
811 102 10
812 103 20
813 104 20
814 explain select ST_X(g),ST_Y(g) FROM gis_point;
815 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
816 1 SIMPLE gis_point p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 4 100.00 NULL
817 Warnings:
818 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`
819 SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line ORDER by fid;
820 fid ST_AsText(ST_StartPoint(g))
821 105 POINT(0 0)
822 106 POINT(10 10)
823 107 POINT(10 10)
824 SELECT fid, ST_AsText(ST_EndPoint(g)) FROM gis_line ORDER by fid;
825 fid ST_AsText(ST_EndPoint(g))
826 105 POINT(10 0)
827 106 POINT(10 10)
828 107 POINT(40 10)
829 SELECT fid, ST_Length(g) FROM gis_line ORDER by fid;
830 fid ST_Length(g)
831 105 24.14213562373095
832 106 40
833 107 30
834 SELECT fid, ST_NumPoints(g) FROM gis_line ORDER by fid;
835 fid ST_NumPoints(g)
836 105 3
837 106 5
838 107 2
839 SELECT fid, ST_AsText(ST_PointN(g, 2)) FROM gis_line ORDER by fid;
840 fid ST_AsText(ST_PointN(g, 2))
841 105 POINT(0 10)
842 106 POINT(20 10)
843 107 POINT(40 10)
844 SELECT fid, ST_IsClosed(g) FROM gis_line ORDER by fid;
845 fid ST_IsClosed(g)
846 105 0
847 106 1
848 107 0
849 explain select ST_AsText(ST_StartPoint(g)),ST_AsText(ST_EndPoint(g)),ST_Length(g),ST_NumPoints(g),ST_AsText(ST_PointN(g, 2)),ST_IsClosed(g) FROM gis_line;
850 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
851 1 SIMPLE gis_line p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 100.00 NULL
852 Warnings:
853 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`
854 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon ORDER by fid;
855 fid ST_AsText(ST_Centroid(g))
856 108 POINT(15 15)
857 109 POINT(25.416666666666668 25.416666666666668)
858 110 POINT(20 10)
859 SELECT fid, ST_Area(g) FROM gis_polygon ORDER by fid;
860 fid ST_Area(g)
861 108 100
862 109 2400
863 110 450
864 SELECT fid, ST_AsText(ST_ExteriorRing(g)) FROM gis_polygon ORDER by fid;
865 fid ST_AsText(ST_ExteriorRing(g))
866 108 LINESTRING(10 10,20 10,20 20,10 20,10 10)
867 109 LINESTRING(0 0,50 0,50 50,0 50,0 0)
868 110 LINESTRING(0 0,30 0,30 30,0 0)
869 SELECT fid, ST_NumInteriorRings(g) FROM gis_polygon ORDER by fid;
870 fid ST_NumInteriorRings(g)
871 108 0
872 109 1
873 110 0
874 SELECT fid, ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid;
875 fid ST_AsText(ST_InteriorRingN(g, 1))
876 108 NULL
877 109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
878 110 NULL
879 explain select ST_AsText(ST_Centroid(g)),ST_Area(g),ST_AsText(ST_ExteriorRing(g)),ST_NumInteriorRings(g),ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
880 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
881 1 SIMPLE gis_polygon p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 100.00 NULL
882 Warnings:
883 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`
884 SELECT fid, ST_IsClosed(g) FROM gis_multi_line ORDER by fid;
885 fid ST_IsClosed(g)
886 114 0
887 115 0
888 116 0
889 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_multi_polygon ORDER by fid;
890 fid ST_AsText(ST_Centroid(g))
891 117 POINT(57.98031067576927 17.854754130800437)
892 118 POINT(57.98031067576927 17.854754130800437)
893 119 POINT(2 2)
894 SELECT fid, ST_Area(g) FROM gis_multi_polygon ORDER by fid;
895 fid ST_Area(g)
896 117 1684.5
897 118 1684.5
898 119 4.5
899 SELECT fid, ST_NumGeometries(g) from gis_multi_point ORDER by fid;
900 fid ST_NumGeometries(g)
901 111 4
902 112 4
903 113 2
904 SELECT fid, ST_NumGeometries(g) from gis_multi_line ORDER by fid;
905 fid ST_NumGeometries(g)
906 114 2
907 115 1
908 116 2
909 SELECT fid, ST_NumGeometries(g) from gis_multi_polygon ORDER by fid;
910 fid ST_NumGeometries(g)
911 117 2
912 118 2
913 119 1
914 SELECT fid, ST_NumGeometries(g) from gis_geometrycollection ORDER by fid;
915 fid ST_NumGeometries(g)
916 120 2
917 121 2
918 explain SELECT fid, ST_NumGeometries(g) from gis_multi_point;
919 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
920 1 SIMPLE gis_multi_point p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 100.00 NULL
921 Warnings:
922 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`
923 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point ORDER by fid;
924 fid ST_AsText(ST_GeometryN(g, 2))
925 111 POINT(10 10)
926 112 POINT(11 11)
927 113 POINT(4 10)
928 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_line ORDER by fid;
929 fid ST_AsText(ST_GeometryN(g, 2))
930 114 LINESTRING(16 0,16 23,16 48)
931 115 NULL
932 116 LINESTRING(2 5,5 8,21 7)
933 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_polygon ORDER by fid;
934 fid ST_AsText(ST_GeometryN(g, 2))
935 117 POLYGON((59 18,67 18,67 13,59 13,59 18))
936 118 POLYGON((59 18,67 18,67 13,59 13,59 18))
937 119 NULL
938 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_geometrycollection ORDER by fid;
939 fid ST_AsText(ST_GeometryN(g, 2))
940 120 LINESTRING(0 0,10 10)
941 121 LINESTRING(3 6,7 9)
942 SELECT fid, ST_AsText(ST_GeometryN(g, 1)) from gis_geometrycollection ORDER by fid;
943 fid ST_AsText(ST_GeometryN(g, 1))
944 120 POINT(0 0)
945 121 POINT(44 6)
946 explain SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
947 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
948 1 SIMPLE gis_multi_point p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 100.00 NULL
949 Warnings:
950 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`
951 SELECT g1.fid as first, g2.fid as second,
952 MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
953 MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
954 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
955 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
956 first second w c o e d t i r
957 120 120 1 1 0 1 0 0 1 0
958 120 121 0 0 1 0 0 0 1 0
959 121 120 0 0 1 0 0 0 1 0
960 121 121 1 1 0 1 0 0 1 0
961 explain SELECT g1.fid as first, g2.fid as second,
962 MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
963 MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
964 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
965 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
966 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
967 1 SIMPLE g1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
968 1 SIMPLE g2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (hash join)
969 Warnings:
970 Note 1003 Table 'g1' is not pushable: select list can't contain BLOB columns
971 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`
972 DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
973 CREATE TABLE t1 (
974 a INTEGER PRIMARY KEY AUTO_INCREMENT,
975 gp point,
976 ln linestring,
977 pg polygon,
978 mp multipoint,
979 mln multilinestring,
980 mpg multipolygon,
981 gc geometrycollection,
982 gm geometry
983 );
984 SHOW FIELDS FROM t1;
985 Field Type Null Key Default Extra
986 a int NO PRI NULL auto_increment
987 gp point YES NULL
988 ln linestring YES NULL
989 pg polygon YES NULL
990 mp multipoint YES NULL
991 mln multilinestring YES NULL
992 mpg multipolygon YES NULL
993 gc geomcollection YES NULL
994 gm geometry YES NULL
995 ALTER TABLE t1 ADD fid INT;
996 Warnings:
997 Warning 1478 Converted FIXED field 'fid' to DYNAMIC to enable online ADD COLUMN
998 SHOW FIELDS FROM t1;
999 Field Type Null Key Default Extra
1000 a int NO PRI NULL auto_increment
1001 gp point YES NULL
1002 ln linestring YES NULL
1003 pg polygon YES NULL
1004 mp multipoint YES NULL
1005 mln multilinestring YES NULL
1006 mpg multipolygon YES NULL
1007 gc geomcollection YES NULL
1008 gm geometry YES NULL
1009 fid int YES NULL
1010 DROP TABLE t1;
1011 create table t1 (pk integer primary key auto_increment, a geometry not null);
1012 insert into t1 (a) values (ST_GeomFromText('Point(1 2)'));
1013 insert into t1 (a) values ('Garbage');
1014 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
1015 insert IGNORE into t1 (a) values ('Garbage');
1016 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
1017 drop table t1;
1018 create table t1 (pk integer primary key auto_increment, fl geometry not null);
1019 insert into t1 (fl) values (1);
1020 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
1021 insert into t1 (fl) values (1.11);
1022 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
1023 insert into t1 (fl) values ("qwerty");
1024 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
1025 insert into t1 (fl) values (ST_pointfromtext('point(1,1)'));
1026 ERROR 22023: Invalid GIS data provided to function st_pointfromtext.
1027 drop table t1;
1028 End of 4.1 tests
1029 CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY);
1030 Warnings:
1031 Warning 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK'
1032 INSERT INTO t1 VALUES("center", ST_GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));
1033 INSERT INTO t1 VALUES("small", ST_GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
1034 INSERT INTO t1 VALUES("big", ST_GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
1035 INSERT INTO t1 VALUES("up", ST_GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));
1036 INSERT INTO t1 VALUES("up2", ST_GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));
1037 INSERT INTO t1 VALUES("up3", ST_GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));
1038 INSERT INTO t1 VALUES("down", ST_GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));
1039 INSERT INTO t1 VALUES("down2", ST_GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));
1040 INSERT INTO t1 VALUES("down3", ST_GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
1041 INSERT INTO t1 VALUES("right", ST_GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
1042 INSERT INTO t1 VALUES("right2", ST_GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
1043 INSERT INTO t1 VALUES("right3", ST_GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
1044 INSERT INTO t1 VALUES("left", ST_GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));
1045 INSERT INTO t1 VALUES("left2", ST_GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));
1046 INSERT INTO t1 VALUES("left3", ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))'));
1047 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrcontains FROM t1 a1 JOIN t1 a2 ON MBRContains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1048 mbrcontains
1049 center,small
1050 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrdisjoint FROM t1 a1 JOIN t1 a2 ON MBRDisjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1051 mbrdisjoint
1052 down3,left3,right3,up3
1053 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrequals FROM t1 a1 JOIN t1 a2 ON MBREquals( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1054 mbrequals
1055 center
1056 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrintersect FROM t1 a1 JOIN t1 a2 ON MBRIntersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1057 mbrintersect
1058 big,center,down,down2,left,left2,right,right2,small,up,up2
1059 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbroverlaps FROM t1 a1 JOIN t1 a2 ON MBROverlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1060 mbroverlaps
1061 down,left,right,up
1062 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrtouches FROM t1 a1 JOIN t1 a2 ON MBRTouches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1063 mbrtouches
1064 down2,left2,right2,up2
1065 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrwithin FROM t1 a1 JOIN t1 a2 ON MBRWithin( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1066 mbrwithin
1067 big,center
1068 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRcontains FROM t1 a1 JOIN t1 a2 ON MBRContains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1069 MBRcontains
1070 center,small
1071 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRdisjoint FROM t1 a1 JOIN t1 a2 ON MBRDisjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1072 MBRdisjoint
1073 down3,left3,right3,up3
1074 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRequals FROM t1 a1 JOIN t1 a2 ON MBREquals( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1075 MBRequals
1076 center
1077 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS `intersect` FROM t1 a1 JOIN t1 a2 ON MBRIntersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1078 intersect
1079 big,center,down,down2,left,left2,right,right2,small,up,up2
1080 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRoverlaps FROM t1 a1 JOIN t1 a2 ON MBROverlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1081 MBRoverlaps
1082 down,left,right,up
1083 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS ST_touches FROM t1 a1 JOIN t1 a2 ON ST_Touches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1084 ST_touches
1085 down2,left2,right2,up2
1086 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRwithin FROM t1 a1 JOIN t1 a2 ON MBRWithin( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1087 MBRwithin
1088 big,center
1089 SET @vert1 = ST_GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');
1090 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
1091 SET @horiz1 = ST_GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');
1092 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
1093 SET @horiz2 = ST_GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');
1094 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
1095 SET @horiz3 = ST_GeomFromText('POLYGON ((2 0, 3 0, 2 0))');
1096 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
1097 SET @point1 = ST_GeomFromText('POLYGON ((0 0))');
1098 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
1099 SET @point2 = ST_GeomFromText('POLYGON ((-2 0))');
1100 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
1101 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @vert1) GROUP BY a1.name;
1102 MBRoverlaps
1103 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @horiz1) GROUP BY a1.name;
1104 MBRoverlaps
1105 SELECT MBROverlaps(@horiz1, @vert1) FROM DUAL;
1106 MBROverlaps(@horiz1, @vert1)
1107 NULL
1108 SELECT MBROverlaps(@horiz1, @horiz2) FROM DUAL;
1109 MBROverlaps(@horiz1, @horiz2)
1110 NULL
1111 SELECT MBROverlaps(@horiz1, @horiz3) FROM DUAL;
1112 MBROverlaps(@horiz1, @horiz3)
1113 NULL
1114 SELECT MBROverlaps(@horiz1, @point1) FROM DUAL;
1115 MBROverlaps(@horiz1, @point1)
1116 NULL
1117 SELECT MBROverlaps(@horiz1, @point2) FROM DUAL;
1118 MBROverlaps(@horiz1, @point2)
1119 NULL
1120 DROP TABLE t1;
1121 End of 5.0 tests
1122 set @@global.ndb_read_backup = @save_ndb_read_backup;