1
SET storage_engine=ndbcluster;
2
DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
3
CREATE TABLE gis_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT);
4
CREATE TABLE gis_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g LINESTRING);
5
CREATE TABLE gis_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POLYGON);
6
CREATE TABLE gis_multi_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOINT);
7
CREATE TABLE gis_multi_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTILINESTRING);
8
CREATE TABLE gis_multi_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOLYGON);
9
CREATE TABLE gis_geometrycollection (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRYCOLLECTION);
10
CREATE TABLE gis_geometry (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRY);
11
SHOW CREATE TABLE gis_point;
13
gis_point CREATE TABLE `gis_point` (
14
`fid` int(11) NOT NULL AUTO_INCREMENT,
15
`g` point DEFAULT NULL,
17
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
18
SHOW FIELDS FROM gis_point;
19
Field Type Null Key Default Extra
20
fid int(11) NO PRI NULL auto_increment
22
SHOW FIELDS FROM gis_line;
23
Field Type Null Key Default Extra
24
fid int(11) NO PRI NULL auto_increment
26
SHOW FIELDS FROM gis_polygon;
27
Field Type Null Key Default Extra
28
fid int(11) NO PRI NULL auto_increment
30
SHOW FIELDS FROM gis_multi_point;
31
Field Type Null Key Default Extra
32
fid int(11) NO PRI NULL auto_increment
34
SHOW FIELDS FROM gis_multi_line;
35
Field Type Null Key Default Extra
36
fid int(11) NO PRI NULL auto_increment
37
g multilinestring YES NULL
38
SHOW FIELDS FROM gis_multi_polygon;
39
Field Type Null Key Default Extra
40
fid int(11) NO PRI NULL auto_increment
41
g multipolygon YES NULL
42
SHOW FIELDS FROM gis_geometrycollection;
43
Field Type Null Key Default Extra
44
fid int(11) NO PRI NULL auto_increment
45
g geometrycollection YES NULL
46
SHOW FIELDS FROM gis_geometry;
47
Field Type Null Key Default Extra
48
fid int(11) NO PRI NULL auto_increment
50
INSERT INTO gis_point VALUES
51
(101, PointFromText('POINT(10 10)')),
52
(102, PointFromText('POINT(20 10)')),
53
(103, PointFromText('POINT(20 20)')),
54
(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
55
INSERT INTO gis_line VALUES
56
(105, LineFromText('LINESTRING(0 0,0 10,10 0)')),
57
(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
58
(107, LineStringFromWKB(LineString(Point(10, 10), Point(40, 10))));
59
INSERT INTO gis_polygon VALUES
60
(108, PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
61
(109, PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
62
(110, PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))));
63
INSERT INTO gis_multi_point VALUES
64
(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
65
(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
66
(113, MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10))));
67
INSERT INTO gis_multi_line VALUES
68
(114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
69
(115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
70
(116, MLineFromWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))));
71
INSERT INTO gis_multi_polygon VALUES
72
(117, 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)))')),
73
(118, 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)))')),
74
(119, MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))));
75
INSERT INTO gis_geometrycollection VALUES
76
(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
77
(121, GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))));
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
SELECT fid, AsText(g) FROM gis_point ORDER by fid;
91
SELECT fid, AsText(g) FROM gis_line ORDER by fid;
93
105 LINESTRING(0 0,0 10,10 0)
94
106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
95
107 LINESTRING(10 10,40 10)
96
SELECT fid, AsText(g) FROM gis_polygon ORDER by fid;
98
108 POLYGON((10 10,20 10,20 20,10 20,10 10))
99
109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
100
110 POLYGON((0 0,30 0,30 30,0 0))
101
SELECT fid, AsText(g) FROM gis_multi_point ORDER by fid;
103
111 MULTIPOINT(0 0,10 10,10 20,20 20)
104
112 MULTIPOINT(1 1,11 11,11 21,21 21)
105
113 MULTIPOINT(3 6,4 10)
106
SELECT fid, AsText(g) FROM gis_multi_line ORDER by fid;
108
114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
109
115 MULTILINESTRING((10 48,10 21,10 0))
110
116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
111
SELECT fid, AsText(g) FROM gis_multi_polygon ORDER by fid;
113
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)))
114
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)))
115
119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
116
SELECT fid, AsText(g) FROM gis_geometrycollection ORDER by fid;
118
120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
119
121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
120
SELECT fid, AsText(g) FROM gis_geometry ORDER by fid;
126
105 LINESTRING(0 0,0 10,10 0)
127
106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
128
107 LINESTRING(10 10,40 10)
129
108 POLYGON((10 10,20 10,20 20,10 20,10 10))
130
109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
131
110 POLYGON((0 0,30 0,30 30,0 0))
132
111 MULTIPOINT(0 0,10 10,10 20,20 20)
133
112 MULTIPOINT(1 1,11 11,11 21,21 21)
134
113 MULTIPOINT(3 6,4 10)
135
114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
136
115 MULTILINESTRING((10 48,10 21,10 0))
137
116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
138
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)))
139
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)))
140
119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
141
120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
142
121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
143
SELECT fid, Dimension(g) FROM gis_geometry ORDER by fid;
166
SELECT fid, GeometryType(g) FROM gis_geometry ORDER by fid;
187
120 GEOMETRYCOLLECTION
188
121 GEOMETRYCOLLECTION
189
SELECT fid, IsEmpty(g) FROM gis_geometry ORDER by fid;
212
SELECT fid, AsText(Envelope(g)) FROM gis_geometry ORDER by fid;
213
fid AsText(Envelope(g))
214
101 POLYGON((10 10,10 10,10 10,10 10,10 10))
215
102 POLYGON((20 10,20 10,20 10,20 10,20 10))
216
103 POLYGON((20 20,20 20,20 20,20 20,20 20))
217
104 POLYGON((10 20,10 20,10 20,10 20,10 20))
218
105 POLYGON((0 0,10 0,10 10,0 10,0 0))
219
106 POLYGON((10 10,20 10,20 20,10 20,10 10))
220
107 POLYGON((10 10,40 10,40 10,10 10,10 10))
221
108 POLYGON((10 10,20 10,20 20,10 20,10 10))
222
109 POLYGON((0 0,50 0,50 50,0 50,0 0))
223
110 POLYGON((0 0,30 0,30 30,0 30,0 0))
224
111 POLYGON((0 0,20 0,20 20,0 20,0 0))
225
112 POLYGON((1 1,21 1,21 21,1 21,1 1))
226
113 POLYGON((3 6,4 6,4 10,3 10,3 6))
227
114 POLYGON((10 0,16 0,16 48,10 48,10 0))
228
115 POLYGON((10 0,10 0,10 48,10 48,10 0))
229
116 POLYGON((1 2,21 2,21 8,1 8,1 2))
230
117 POLYGON((28 0,84 0,84 42,28 42,28 0))
231
118 POLYGON((28 0,84 0,84 42,28 42,28 0))
232
119 POLYGON((0 0,3 0,3 3,0 3,0 0))
233
120 POLYGON((0 0,10 0,10 10,0 10,0 0))
234
121 POLYGON((3 6,44 6,44 9,3 9,3 6))
235
explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from gis_geometry;
236
id select_type table type possible_keys key key_len ref rows filtered Extra
237
1 SIMPLE gis_geometry ALL NULL NULL NULL NULL 21 100.00
239
Note 1003 select dimension(`test`.`gis_geometry`.`g`) AS `Dimension(g)`,geometrytype(`test`.`gis_geometry`.`g`) AS `GeometryType(g)`,isempty(`test`.`gis_geometry`.`g`) AS `IsEmpty(g)`,astext(envelope(`test`.`gis_geometry`.`g`)) AS `AsText(Envelope(g))` from `test`.`gis_geometry`
240
SELECT fid, X(g) FROM gis_point ORDER by fid;
246
SELECT fid, Y(g) FROM gis_point ORDER by fid;
252
explain extended select X(g),Y(g) FROM gis_point;
253
id select_type table type possible_keys key key_len ref rows filtered Extra
254
1 SIMPLE gis_point ALL NULL NULL NULL NULL 4 100.00
256
Note 1003 select x(`test`.`gis_point`.`g`) AS `X(g)`,y(`test`.`gis_point`.`g`) AS `Y(g)` from `test`.`gis_point`
257
SELECT fid, AsText(StartPoint(g)) FROM gis_line ORDER by fid;
258
fid AsText(StartPoint(g))
262
SELECT fid, AsText(EndPoint(g)) FROM gis_line ORDER by fid;
263
fid AsText(EndPoint(g))
267
SELECT fid, GLength(g) FROM gis_line ORDER by fid;
272
SELECT fid, NumPoints(g) FROM gis_line ORDER by fid;
277
SELECT fid, AsText(PointN(g, 2)) FROM gis_line ORDER by fid;
278
fid AsText(PointN(g, 2))
282
SELECT fid, IsClosed(g) FROM gis_line ORDER by fid;
287
explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM gis_line;
288
id select_type table type possible_keys key key_len ref rows filtered Extra
289
1 SIMPLE gis_line ALL NULL NULL NULL NULL 3 100.00
291
Note 1003 select astext(startpoint(`test`.`gis_line`.`g`)) AS `AsText(StartPoint(g))`,astext(endpoint(`test`.`gis_line`.`g`)) AS `AsText(EndPoint(g))`,glength(`test`.`gis_line`.`g`) AS `GLength(g)`,numpoints(`test`.`gis_line`.`g`) AS `NumPoints(g)`,astext(pointn(`test`.`gis_line`.`g`,2)) AS `AsText(PointN(g, 2))`,isclosed(`test`.`gis_line`.`g`) AS `IsClosed(g)` from `test`.`gis_line`
292
SELECT fid, AsText(Centroid(g)) FROM gis_polygon ORDER by fid;
293
fid AsText(Centroid(g))
295
109 POINT(25.4166666666667 25.4166666666667)
297
SELECT fid, Area(g) FROM gis_polygon ORDER by fid;
302
SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon ORDER by fid;
303
fid AsText(ExteriorRing(g))
304
108 LINESTRING(10 10,20 10,20 20,10 20,10 10)
305
109 LINESTRING(0 0,50 0,50 50,0 50,0 0)
306
110 LINESTRING(0 0,30 0,30 30,0 0)
307
SELECT fid, NumInteriorRings(g) FROM gis_polygon ORDER by fid;
308
fid NumInteriorRings(g)
312
SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid;
313
fid AsText(InteriorRingN(g, 1))
315
109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
317
explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM gis_polygon;
318
id select_type table type possible_keys key key_len ref rows filtered Extra
319
1 SIMPLE gis_polygon ALL NULL NULL NULL NULL 3 100.00
321
Note 1003 select astext(centroid(`test`.`gis_polygon`.`g`)) AS `AsText(Centroid(g))`,area(`test`.`gis_polygon`.`g`) AS `Area(g)`,astext(exteriorring(`test`.`gis_polygon`.`g`)) AS `AsText(ExteriorRing(g))`,numinteriorrings(`test`.`gis_polygon`.`g`) AS `NumInteriorRings(g)`,astext(interiorringn(`test`.`gis_polygon`.`g`,1)) AS `AsText(InteriorRingN(g, 1))` from `test`.`gis_polygon`
322
SELECT fid, IsClosed(g) FROM gis_multi_line ORDER by fid;
327
SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon ORDER by fid;
328
fid AsText(Centroid(g))
329
117 POINT(55.5885277530424 17.426536064114)
330
118 POINT(55.5885277530424 17.426536064114)
332
SELECT fid, Area(g) FROM gis_multi_polygon ORDER by fid;
337
SELECT fid, NumGeometries(g) from gis_multi_point ORDER by fid;
342
SELECT fid, NumGeometries(g) from gis_multi_line ORDER by fid;
347
SELECT fid, NumGeometries(g) from gis_multi_polygon ORDER by fid;
352
SELECT fid, NumGeometries(g) from gis_geometrycollection ORDER by fid;
356
explain extended SELECT fid, NumGeometries(g) from gis_multi_point;
357
id select_type table type possible_keys key key_len ref rows filtered Extra
358
1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00
360
Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from `test`.`gis_multi_point`
361
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point ORDER by fid;
362
fid AsText(GeometryN(g, 2))
366
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line ORDER by fid;
367
fid AsText(GeometryN(g, 2))
368
114 LINESTRING(16 0,16 23,16 48)
370
116 LINESTRING(2 5,5 8,21 7)
371
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon ORDER by fid;
372
fid AsText(GeometryN(g, 2))
373
117 POLYGON((59 18,67 18,67 13,59 13,59 18))
374
118 POLYGON((59 18,67 18,67 13,59 13,59 18))
376
SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection ORDER by fid;
377
fid AsText(GeometryN(g, 2))
378
120 LINESTRING(0 0,10 10)
379
121 LINESTRING(3 6,7 9)
380
SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection ORDER by fid;
381
fid AsText(GeometryN(g, 1))
384
explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
385
id select_type table type possible_keys key key_len ref rows filtered Extra
386
1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00
388
Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,astext(geometryn(`test`.`gis_multi_point`.`g`,2)) AS `AsText(GeometryN(g, 2))` from `test`.`gis_multi_point`
389
SELECT g1.fid as first, g2.fid as second,
390
Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
391
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
392
Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
393
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
394
first second w c o e d t i r
395
120 120 1 1 0 1 0 0 1 0
396
120 121 0 0 1 0 0 0 1 0
397
121 120 0 0 1 0 0 0 1 0
398
121 121 1 1 0 1 0 0 1 0
399
explain extended SELECT g1.fid as first, g2.fid as second,
400
Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
401
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
402
Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
403
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
404
id select_type table type possible_keys key key_len ref rows filtered Extra
405
1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
406
1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer
408
Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,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`
409
DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
411
a INTEGER PRIMARY KEY AUTO_INCREMENT,
418
gc geometrycollection,
422
Field Type Null Key Default Extra
423
a int(11) NO PRI NULL auto_increment
425
ln linestring YES NULL
427
mp multipoint YES NULL
428
mln multilinestring YES NULL
429
mpg multipolygon YES NULL
430
gc geometrycollection YES NULL
432
ALTER TABLE t1 ADD fid INT;
434
Field Type Null Key Default Extra
435
a int(11) NO PRI NULL auto_increment
437
ln linestring YES NULL
439
mp multipoint YES NULL
440
mln multilinestring YES NULL
441
mpg multipolygon YES NULL
442
gc geometrycollection YES NULL
446
create table t1 (pk integer primary key auto_increment, a geometry not null);
447
insert into t1 (a) values (GeomFromText('Point(1 2)'));
448
insert into t1 (a) values ('Garbage');
449
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
450
insert IGNORE into t1 (a) values ('Garbage');
451
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
453
create table t1 (pk integer primary key auto_increment, fl geometry not null);
454
insert into t1 (fl) values (1);
455
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
456
insert into t1 (fl) values (1.11);
457
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
458
insert into t1 (fl) values ("qwerty");
459
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
460
insert into t1 (fl) values (pointfromtext('point(1,1)'));
461
ERROR 23000: Column 'fl' cannot be null
464
CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY);
466
Error 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK'
467
INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));
468
INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
469
INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
470
INSERT INTO t1 VALUES("up", GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));
471
INSERT INTO t1 VALUES("up2", GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));
472
INSERT INTO t1 VALUES("up3", GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));
473
INSERT INTO t1 VALUES("down", GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));
474
INSERT INTO t1 VALUES("down2", GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));
475
INSERT INTO t1 VALUES("down3", GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
476
INSERT INTO t1 VALUES("right", GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
477
INSERT INTO t1 VALUES("right2", GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
478
INSERT INTO t1 VALUES("right3", GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
479
INSERT INTO t1 VALUES("left", GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));
480
INSERT INTO t1 VALUES("left2", GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));
481
INSERT INTO t1 VALUES("left3", GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))'));
482
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;
485
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;
487
down3,left3,right3,up3
488
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrequal FROM t1 a1 JOIN t1 a2 ON MBREqual( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
491
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;
493
big,center,down,down2,left,left2,right,right2,small,up,up2
494
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;
497
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;
499
down2,left2,right2,up2
500
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;
503
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS contains FROM t1 a1 JOIN t1 a2 ON Contains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
506
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS disjoint FROM t1 a1 JOIN t1 a2 ON Disjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
508
down3,left3,right3,up3
509
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS equals FROM t1 a1 JOIN t1 a2 ON Equals( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
512
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS intersect FROM t1 a1 JOIN t1 a2 ON Intersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
514
big,center,down,down2,left,left2,right,right2,small,up,up2
515
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS overlaps FROM t1 a1 JOIN t1 a2 ON Overlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
518
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS touches FROM t1 a1 JOIN t1 a2 ON Touches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
520
down2,left2,right2,up2
521
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS within FROM t1 a1 JOIN t1 a2 ON Within( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
524
SET @vert1 = GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');
525
SET @horiz1 = GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');
526
SET @horiz2 = GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');
527
SET @horiz3 = GeomFromText('POLYGON ((2 0, 3 0, 2 0))');
528
SET @point1 = GeomFromText('POLYGON ((0 0))');
529
SET @point2 = GeomFromText('POLYGON ((-2 0))');
530
SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @vert1) GROUP BY a1.name;
532
SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @horiz1) GROUP BY a1.name;
534
SELECT Overlaps(@horiz1, @vert1) FROM DUAL;
535
Overlaps(@horiz1, @vert1)
537
SELECT Overlaps(@horiz1, @horiz2) FROM DUAL;
538
Overlaps(@horiz1, @horiz2)
540
SELECT Overlaps(@horiz1, @horiz3) FROM DUAL;
541
Overlaps(@horiz1, @horiz3)
543
SELECT Overlaps(@horiz1, @point1) FROM DUAL;
544
Overlaps(@horiz1, @point1)
546
SELECT Overlaps(@horiz1, @point2) FROM DUAL;
547
Overlaps(@horiz1, @point2)
551
set engine_condition_pushdown = on;
552
DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
553
CREATE TABLE gis_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT);
554
CREATE TABLE gis_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g LINESTRING);
555
CREATE TABLE gis_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POLYGON);
556
CREATE TABLE gis_multi_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOINT);
557
CREATE TABLE gis_multi_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTILINESTRING);
558
CREATE TABLE gis_multi_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOLYGON);
559
CREATE TABLE gis_geometrycollection (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRYCOLLECTION);
560
CREATE TABLE gis_geometry (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRY);
561
SHOW CREATE TABLE gis_point;
563
gis_point CREATE TABLE `gis_point` (
564
`fid` int(11) NOT NULL AUTO_INCREMENT,
565
`g` point DEFAULT NULL,
567
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
568
SHOW FIELDS FROM gis_point;
569
Field Type Null Key Default Extra
570
fid int(11) NO PRI NULL auto_increment
572
SHOW FIELDS FROM gis_line;
573
Field Type Null Key Default Extra
574
fid int(11) NO PRI NULL auto_increment
575
g linestring YES NULL
576
SHOW FIELDS FROM gis_polygon;
577
Field Type Null Key Default Extra
578
fid int(11) NO PRI NULL auto_increment
580
SHOW FIELDS FROM gis_multi_point;
581
Field Type Null Key Default Extra
582
fid int(11) NO PRI NULL auto_increment
583
g multipoint YES NULL
584
SHOW FIELDS FROM gis_multi_line;
585
Field Type Null Key Default Extra
586
fid int(11) NO PRI NULL auto_increment
587
g multilinestring YES NULL
588
SHOW FIELDS FROM gis_multi_polygon;
589
Field Type Null Key Default Extra
590
fid int(11) NO PRI NULL auto_increment
591
g multipolygon YES NULL
592
SHOW FIELDS FROM gis_geometrycollection;
593
Field Type Null Key Default Extra
594
fid int(11) NO PRI NULL auto_increment
595
g geometrycollection YES NULL
596
SHOW FIELDS FROM gis_geometry;
597
Field Type Null Key Default Extra
598
fid int(11) NO PRI NULL auto_increment
600
INSERT INTO gis_point VALUES
601
(101, PointFromText('POINT(10 10)')),
602
(102, PointFromText('POINT(20 10)')),
603
(103, PointFromText('POINT(20 20)')),
604
(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
605
INSERT INTO gis_line VALUES
606
(105, LineFromText('LINESTRING(0 0,0 10,10 0)')),
607
(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
608
(107, LineStringFromWKB(LineString(Point(10, 10), Point(40, 10))));
609
INSERT INTO gis_polygon VALUES
610
(108, PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
611
(109, PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
612
(110, PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))));
613
INSERT INTO gis_multi_point VALUES
614
(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
615
(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
616
(113, MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10))));
617
INSERT INTO gis_multi_line VALUES
618
(114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
619
(115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
620
(116, MLineFromWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))));
621
INSERT INTO gis_multi_polygon VALUES
622
(117, 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)))')),
623
(118, 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)))')),
624
(119, MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))));
625
INSERT INTO gis_geometrycollection VALUES
626
(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
627
(121, GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))));
628
INSERT into gis_geometry SELECT * FROM gis_point;
629
INSERT into gis_geometry SELECT * FROM gis_line;
630
INSERT into gis_geometry SELECT * FROM gis_polygon;
631
INSERT into gis_geometry SELECT * FROM gis_multi_point;
632
INSERT into gis_geometry SELECT * FROM gis_multi_line;
633
INSERT into gis_geometry SELECT * FROM gis_multi_polygon;
634
INSERT into gis_geometry SELECT * FROM gis_geometrycollection;
635
SELECT fid, AsText(g) FROM gis_point ORDER by fid;
641
SELECT fid, AsText(g) FROM gis_line ORDER by fid;
643
105 LINESTRING(0 0,0 10,10 0)
644
106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
645
107 LINESTRING(10 10,40 10)
646
SELECT fid, AsText(g) FROM gis_polygon ORDER by fid;
648
108 POLYGON((10 10,20 10,20 20,10 20,10 10))
649
109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
650
110 POLYGON((0 0,30 0,30 30,0 0))
651
SELECT fid, AsText(g) FROM gis_multi_point ORDER by fid;
653
111 MULTIPOINT(0 0,10 10,10 20,20 20)
654
112 MULTIPOINT(1 1,11 11,11 21,21 21)
655
113 MULTIPOINT(3 6,4 10)
656
SELECT fid, AsText(g) FROM gis_multi_line ORDER by fid;
658
114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
659
115 MULTILINESTRING((10 48,10 21,10 0))
660
116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
661
SELECT fid, AsText(g) FROM gis_multi_polygon ORDER by fid;
663
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)))
664
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)))
665
119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
666
SELECT fid, AsText(g) FROM gis_geometrycollection ORDER by fid;
668
120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
669
121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
670
SELECT fid, AsText(g) FROM gis_geometry ORDER by fid;
676
105 LINESTRING(0 0,0 10,10 0)
677
106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
678
107 LINESTRING(10 10,40 10)
679
108 POLYGON((10 10,20 10,20 20,10 20,10 10))
680
109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
681
110 POLYGON((0 0,30 0,30 30,0 0))
682
111 MULTIPOINT(0 0,10 10,10 20,20 20)
683
112 MULTIPOINT(1 1,11 11,11 21,21 21)
684
113 MULTIPOINT(3 6,4 10)
685
114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
686
115 MULTILINESTRING((10 48,10 21,10 0))
687
116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
688
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)))
689
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)))
690
119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
691
120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
692
121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
693
SELECT fid, Dimension(g) FROM gis_geometry ORDER by fid;
716
SELECT fid, GeometryType(g) FROM gis_geometry ORDER by fid;
737
120 GEOMETRYCOLLECTION
738
121 GEOMETRYCOLLECTION
739
SELECT fid, IsEmpty(g) FROM gis_geometry ORDER by fid;
762
SELECT fid, AsText(Envelope(g)) FROM gis_geometry ORDER by fid;
763
fid AsText(Envelope(g))
764
101 POLYGON((10 10,10 10,10 10,10 10,10 10))
765
102 POLYGON((20 10,20 10,20 10,20 10,20 10))
766
103 POLYGON((20 20,20 20,20 20,20 20,20 20))
767
104 POLYGON((10 20,10 20,10 20,10 20,10 20))
768
105 POLYGON((0 0,10 0,10 10,0 10,0 0))
769
106 POLYGON((10 10,20 10,20 20,10 20,10 10))
770
107 POLYGON((10 10,40 10,40 10,10 10,10 10))
771
108 POLYGON((10 10,20 10,20 20,10 20,10 10))
772
109 POLYGON((0 0,50 0,50 50,0 50,0 0))
773
110 POLYGON((0 0,30 0,30 30,0 30,0 0))
774
111 POLYGON((0 0,20 0,20 20,0 20,0 0))
775
112 POLYGON((1 1,21 1,21 21,1 21,1 1))
776
113 POLYGON((3 6,4 6,4 10,3 10,3 6))
777
114 POLYGON((10 0,16 0,16 48,10 48,10 0))
778
115 POLYGON((10 0,10 0,10 48,10 48,10 0))
779
116 POLYGON((1 2,21 2,21 8,1 8,1 2))
780
117 POLYGON((28 0,84 0,84 42,28 42,28 0))
781
118 POLYGON((28 0,84 0,84 42,28 42,28 0))
782
119 POLYGON((0 0,3 0,3 3,0 3,0 0))
783
120 POLYGON((0 0,10 0,10 10,0 10,0 0))
784
121 POLYGON((3 6,44 6,44 9,3 9,3 6))
785
explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from gis_geometry;
786
id select_type table type possible_keys key key_len ref rows filtered Extra
787
1 SIMPLE gis_geometry ALL NULL NULL NULL NULL 21 100.00
789
Note 1003 select dimension(`test`.`gis_geometry`.`g`) AS `Dimension(g)`,geometrytype(`test`.`gis_geometry`.`g`) AS `GeometryType(g)`,isempty(`test`.`gis_geometry`.`g`) AS `IsEmpty(g)`,astext(envelope(`test`.`gis_geometry`.`g`)) AS `AsText(Envelope(g))` from `test`.`gis_geometry`
790
SELECT fid, X(g) FROM gis_point ORDER by fid;
796
SELECT fid, Y(g) FROM gis_point ORDER by fid;
802
explain extended select X(g),Y(g) FROM gis_point;
803
id select_type table type possible_keys key key_len ref rows filtered Extra
804
1 SIMPLE gis_point ALL NULL NULL NULL NULL 4 100.00
806
Note 1003 select x(`test`.`gis_point`.`g`) AS `X(g)`,y(`test`.`gis_point`.`g`) AS `Y(g)` from `test`.`gis_point`
807
SELECT fid, AsText(StartPoint(g)) FROM gis_line ORDER by fid;
808
fid AsText(StartPoint(g))
812
SELECT fid, AsText(EndPoint(g)) FROM gis_line ORDER by fid;
813
fid AsText(EndPoint(g))
817
SELECT fid, GLength(g) FROM gis_line ORDER by fid;
822
SELECT fid, NumPoints(g) FROM gis_line ORDER by fid;
827
SELECT fid, AsText(PointN(g, 2)) FROM gis_line ORDER by fid;
828
fid AsText(PointN(g, 2))
832
SELECT fid, IsClosed(g) FROM gis_line ORDER by fid;
837
explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM gis_line;
838
id select_type table type possible_keys key key_len ref rows filtered Extra
839
1 SIMPLE gis_line ALL NULL NULL NULL NULL 3 100.00
841
Note 1003 select astext(startpoint(`test`.`gis_line`.`g`)) AS `AsText(StartPoint(g))`,astext(endpoint(`test`.`gis_line`.`g`)) AS `AsText(EndPoint(g))`,glength(`test`.`gis_line`.`g`) AS `GLength(g)`,numpoints(`test`.`gis_line`.`g`) AS `NumPoints(g)`,astext(pointn(`test`.`gis_line`.`g`,2)) AS `AsText(PointN(g, 2))`,isclosed(`test`.`gis_line`.`g`) AS `IsClosed(g)` from `test`.`gis_line`
842
SELECT fid, AsText(Centroid(g)) FROM gis_polygon ORDER by fid;
843
fid AsText(Centroid(g))
845
109 POINT(25.4166666666667 25.4166666666667)
847
SELECT fid, Area(g) FROM gis_polygon ORDER by fid;
852
SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon ORDER by fid;
853
fid AsText(ExteriorRing(g))
854
108 LINESTRING(10 10,20 10,20 20,10 20,10 10)
855
109 LINESTRING(0 0,50 0,50 50,0 50,0 0)
856
110 LINESTRING(0 0,30 0,30 30,0 0)
857
SELECT fid, NumInteriorRings(g) FROM gis_polygon ORDER by fid;
858
fid NumInteriorRings(g)
862
SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid;
863
fid AsText(InteriorRingN(g, 1))
865
109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
867
explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM gis_polygon;
868
id select_type table type possible_keys key key_len ref rows filtered Extra
869
1 SIMPLE gis_polygon ALL NULL NULL NULL NULL 3 100.00
871
Note 1003 select astext(centroid(`test`.`gis_polygon`.`g`)) AS `AsText(Centroid(g))`,area(`test`.`gis_polygon`.`g`) AS `Area(g)`,astext(exteriorring(`test`.`gis_polygon`.`g`)) AS `AsText(ExteriorRing(g))`,numinteriorrings(`test`.`gis_polygon`.`g`) AS `NumInteriorRings(g)`,astext(interiorringn(`test`.`gis_polygon`.`g`,1)) AS `AsText(InteriorRingN(g, 1))` from `test`.`gis_polygon`
872
SELECT fid, IsClosed(g) FROM gis_multi_line ORDER by fid;
877
SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon ORDER by fid;
878
fid AsText(Centroid(g))
879
117 POINT(55.5885277530424 17.426536064114)
880
118 POINT(55.5885277530424 17.426536064114)
882
SELECT fid, Area(g) FROM gis_multi_polygon ORDER by fid;
887
SELECT fid, NumGeometries(g) from gis_multi_point ORDER by fid;
892
SELECT fid, NumGeometries(g) from gis_multi_line ORDER by fid;
897
SELECT fid, NumGeometries(g) from gis_multi_polygon ORDER by fid;
902
SELECT fid, NumGeometries(g) from gis_geometrycollection ORDER by fid;
906
explain extended SELECT fid, NumGeometries(g) from gis_multi_point;
907
id select_type table type possible_keys key key_len ref rows filtered Extra
908
1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00
910
Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from `test`.`gis_multi_point`
911
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point ORDER by fid;
912
fid AsText(GeometryN(g, 2))
916
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line ORDER by fid;
917
fid AsText(GeometryN(g, 2))
918
114 LINESTRING(16 0,16 23,16 48)
920
116 LINESTRING(2 5,5 8,21 7)
921
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon ORDER by fid;
922
fid AsText(GeometryN(g, 2))
923
117 POLYGON((59 18,67 18,67 13,59 13,59 18))
924
118 POLYGON((59 18,67 18,67 13,59 13,59 18))
926
SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection ORDER by fid;
927
fid AsText(GeometryN(g, 2))
928
120 LINESTRING(0 0,10 10)
929
121 LINESTRING(3 6,7 9)
930
SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection ORDER by fid;
931
fid AsText(GeometryN(g, 1))
934
explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
935
id select_type table type possible_keys key key_len ref rows filtered Extra
936
1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00
938
Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,astext(geometryn(`test`.`gis_multi_point`.`g`,2)) AS `AsText(GeometryN(g, 2))` from `test`.`gis_multi_point`
939
SELECT g1.fid as first, g2.fid as second,
940
Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
941
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
942
Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
943
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
944
first second w c o e d t i r
945
120 120 1 1 0 1 0 0 1 0
946
120 121 0 0 1 0 0 0 1 0
947
121 120 0 0 1 0 0 0 1 0
948
121 121 1 1 0 1 0 0 1 0
949
explain extended SELECT g1.fid as first, g2.fid as second,
950
Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
951
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
952
Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
953
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
954
id select_type table type possible_keys key key_len ref rows filtered Extra
955
1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
956
1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer
958
Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,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`
959
DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
961
a INTEGER PRIMARY KEY AUTO_INCREMENT,
968
gc geometrycollection,
972
Field Type Null Key Default Extra
973
a int(11) NO PRI NULL auto_increment
975
ln linestring YES NULL
977
mp multipoint YES NULL
978
mln multilinestring YES NULL
979
mpg multipolygon YES NULL
980
gc geometrycollection YES NULL
982
ALTER TABLE t1 ADD fid INT;
984
Field Type Null Key Default Extra
985
a int(11) NO PRI NULL auto_increment
987
ln linestring YES NULL
989
mp multipoint YES NULL
990
mln multilinestring YES NULL
991
mpg multipolygon YES NULL
992
gc geometrycollection YES NULL
996
create table t1 (pk integer primary key auto_increment, a geometry not null);
997
insert into t1 (a) values (GeomFromText('Point(1 2)'));
998
insert into t1 (a) values ('Garbage');
999
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
1000
insert IGNORE into t1 (a) values ('Garbage');
1001
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
1003
create table t1 (pk integer primary key auto_increment, fl geometry not null);
1004
insert into t1 (fl) values (1);
1005
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
1006
insert into t1 (fl) values (1.11);
1007
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
1008
insert into t1 (fl) values ("qwerty");
1009
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
1010
insert into t1 (fl) values (pointfromtext('point(1,1)'));
1011
ERROR 23000: Column 'fl' cannot be null
1014
CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY);
1016
Error 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK'
1017
INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));
1018
INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
1019
INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
1020
INSERT INTO t1 VALUES("up", GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));
1021
INSERT INTO t1 VALUES("up2", GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));
1022
INSERT INTO t1 VALUES("up3", GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));
1023
INSERT INTO t1 VALUES("down", GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));
1024
INSERT INTO t1 VALUES("down2", GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));
1025
INSERT INTO t1 VALUES("down3", GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
1026
INSERT INTO t1 VALUES("right", GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
1027
INSERT INTO t1 VALUES("right2", GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
1028
INSERT INTO t1 VALUES("right3", GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
1029
INSERT INTO t1 VALUES("left", GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));
1030
INSERT INTO t1 VALUES("left2", GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));
1031
INSERT INTO t1 VALUES("left3", GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))'));
1032
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;
1035
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;
1037
down3,left3,right3,up3
1038
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrequal FROM t1 a1 JOIN t1 a2 ON MBREqual( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1041
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;
1043
big,center,down,down2,left,left2,right,right2,small,up,up2
1044
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;
1047
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;
1049
down2,left2,right2,up2
1050
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;
1053
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS contains FROM t1 a1 JOIN t1 a2 ON Contains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1056
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS disjoint FROM t1 a1 JOIN t1 a2 ON Disjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1058
down3,left3,right3,up3
1059
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS equals FROM t1 a1 JOIN t1 a2 ON Equals( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1062
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS intersect FROM t1 a1 JOIN t1 a2 ON Intersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1064
big,center,down,down2,left,left2,right,right2,small,up,up2
1065
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS overlaps FROM t1 a1 JOIN t1 a2 ON Overlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1068
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS touches FROM t1 a1 JOIN t1 a2 ON Touches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1070
down2,left2,right2,up2
1071
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS within FROM t1 a1 JOIN t1 a2 ON Within( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
1074
SET @vert1 = GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');
1075
SET @horiz1 = GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');
1076
SET @horiz2 = GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');
1077
SET @horiz3 = GeomFromText('POLYGON ((2 0, 3 0, 2 0))');
1078
SET @point1 = GeomFromText('POLYGON ((0 0))');
1079
SET @point2 = GeomFromText('POLYGON ((-2 0))');
1080
SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @vert1) GROUP BY a1.name;
1082
SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @horiz1) GROUP BY a1.name;
1084
SELECT Overlaps(@horiz1, @vert1) FROM DUAL;
1085
Overlaps(@horiz1, @vert1)
1087
SELECT Overlaps(@horiz1, @horiz2) FROM DUAL;
1088
Overlaps(@horiz1, @horiz2)
1090
SELECT Overlaps(@horiz1, @horiz3) FROM DUAL;
1091
Overlaps(@horiz1, @horiz3)
1093
SELECT Overlaps(@horiz1, @point1) FROM DUAL;
1094
Overlaps(@horiz1, @point1)
1096
SELECT Overlaps(@horiz1, @point2) FROM DUAL;
1097
Overlaps(@horiz1, @point2)