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.416666666667 25.416666666667)
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.588527753042 17.426536064114)
330
118 POINT(55.588527753042 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 0 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
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);
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 22003: Cannot get geometry object from data you send to the GEOMETRY field
463
set engine_condition_pushdown = on;
464
DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
465
CREATE TABLE gis_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT);
466
CREATE TABLE gis_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g LINESTRING);
467
CREATE TABLE gis_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POLYGON);
468
CREATE TABLE gis_multi_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOINT);
469
CREATE TABLE gis_multi_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTILINESTRING);
470
CREATE TABLE gis_multi_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOLYGON);
471
CREATE TABLE gis_geometrycollection (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRYCOLLECTION);
472
CREATE TABLE gis_geometry (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRY);
473
SHOW CREATE TABLE gis_point;
475
gis_point CREATE TABLE `gis_point` (
476
`fid` int(11) NOT NULL AUTO_INCREMENT,
477
`g` point DEFAULT NULL,
479
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
480
SHOW FIELDS FROM gis_point;
481
Field Type Null Key Default Extra
482
fid int(11) NO PRI NULL auto_increment
484
SHOW FIELDS FROM gis_line;
485
Field Type Null Key Default Extra
486
fid int(11) NO PRI NULL auto_increment
487
g linestring YES NULL
488
SHOW FIELDS FROM gis_polygon;
489
Field Type Null Key Default Extra
490
fid int(11) NO PRI NULL auto_increment
492
SHOW FIELDS FROM gis_multi_point;
493
Field Type Null Key Default Extra
494
fid int(11) NO PRI NULL auto_increment
495
g multipoint YES NULL
496
SHOW FIELDS FROM gis_multi_line;
497
Field Type Null Key Default Extra
498
fid int(11) NO PRI NULL auto_increment
499
g multilinestring YES NULL
500
SHOW FIELDS FROM gis_multi_polygon;
501
Field Type Null Key Default Extra
502
fid int(11) NO PRI NULL auto_increment
503
g multipolygon YES NULL
504
SHOW FIELDS FROM gis_geometrycollection;
505
Field Type Null Key Default Extra
506
fid int(11) NO PRI NULL auto_increment
507
g geometrycollection YES NULL
508
SHOW FIELDS FROM gis_geometry;
509
Field Type Null Key Default Extra
510
fid int(11) NO PRI NULL auto_increment
512
INSERT INTO gis_point VALUES
513
(101, PointFromText('POINT(10 10)')),
514
(102, PointFromText('POINT(20 10)')),
515
(103, PointFromText('POINT(20 20)')),
516
(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
517
INSERT INTO gis_line VALUES
518
(105, LineFromText('LINESTRING(0 0,0 10,10 0)')),
519
(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
520
(107, LineStringFromWKB(LineString(Point(10, 10), Point(40, 10))));
521
INSERT INTO gis_polygon VALUES
522
(108, PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
523
(109, PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
524
(110, PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))));
525
INSERT INTO gis_multi_point VALUES
526
(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
527
(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
528
(113, MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10))));
529
INSERT INTO gis_multi_line VALUES
530
(114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
531
(115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
532
(116, MLineFromWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))));
533
INSERT INTO gis_multi_polygon VALUES
534
(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)))')),
535
(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)))')),
536
(119, MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))));
537
INSERT INTO gis_geometrycollection VALUES
538
(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
539
(121, GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))));
540
INSERT into gis_geometry SELECT * FROM gis_point;
541
INSERT into gis_geometry SELECT * FROM gis_line;
542
INSERT into gis_geometry SELECT * FROM gis_polygon;
543
INSERT into gis_geometry SELECT * FROM gis_multi_point;
544
INSERT into gis_geometry SELECT * FROM gis_multi_line;
545
INSERT into gis_geometry SELECT * FROM gis_multi_polygon;
546
INSERT into gis_geometry SELECT * FROM gis_geometrycollection;
547
SELECT fid, AsText(g) FROM gis_point ORDER by fid;
553
SELECT fid, AsText(g) FROM gis_line ORDER by fid;
555
105 LINESTRING(0 0,0 10,10 0)
556
106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
557
107 LINESTRING(10 10,40 10)
558
SELECT fid, AsText(g) FROM gis_polygon ORDER by fid;
560
108 POLYGON((10 10,20 10,20 20,10 20,10 10))
561
109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
562
110 POLYGON((0 0,30 0,30 30,0 0))
563
SELECT fid, AsText(g) FROM gis_multi_point ORDER by fid;
565
111 MULTIPOINT(0 0,10 10,10 20,20 20)
566
112 MULTIPOINT(1 1,11 11,11 21,21 21)
567
113 MULTIPOINT(3 6,4 10)
568
SELECT fid, AsText(g) FROM gis_multi_line ORDER by fid;
570
114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
571
115 MULTILINESTRING((10 48,10 21,10 0))
572
116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
573
SELECT fid, AsText(g) FROM gis_multi_polygon ORDER by fid;
575
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)))
576
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)))
577
119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
578
SELECT fid, AsText(g) FROM gis_geometrycollection ORDER by fid;
580
120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
581
121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
582
SELECT fid, AsText(g) FROM gis_geometry ORDER by fid;
588
105 LINESTRING(0 0,0 10,10 0)
589
106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
590
107 LINESTRING(10 10,40 10)
591
108 POLYGON((10 10,20 10,20 20,10 20,10 10))
592
109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
593
110 POLYGON((0 0,30 0,30 30,0 0))
594
111 MULTIPOINT(0 0,10 10,10 20,20 20)
595
112 MULTIPOINT(1 1,11 11,11 21,21 21)
596
113 MULTIPOINT(3 6,4 10)
597
114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
598
115 MULTILINESTRING((10 48,10 21,10 0))
599
116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
600
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)))
601
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)))
602
119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
603
120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
604
121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
605
SELECT fid, Dimension(g) FROM gis_geometry ORDER by fid;
628
SELECT fid, GeometryType(g) FROM gis_geometry ORDER by fid;
649
120 GEOMETRYCOLLECTION
650
121 GEOMETRYCOLLECTION
651
SELECT fid, IsEmpty(g) FROM gis_geometry ORDER by fid;
674
SELECT fid, AsText(Envelope(g)) FROM gis_geometry ORDER by fid;
675
fid AsText(Envelope(g))
676
101 POLYGON((10 10,10 10,10 10,10 10,10 10))
677
102 POLYGON((20 10,20 10,20 10,20 10,20 10))
678
103 POLYGON((20 20,20 20,20 20,20 20,20 20))
679
104 POLYGON((10 20,10 20,10 20,10 20,10 20))
680
105 POLYGON((0 0,10 0,10 10,0 10,0 0))
681
106 POLYGON((10 10,20 10,20 20,10 20,10 10))
682
107 POLYGON((10 10,40 10,40 10,10 10,10 10))
683
108 POLYGON((10 10,20 10,20 20,10 20,10 10))
684
109 POLYGON((0 0,50 0,50 50,0 50,0 0))
685
110 POLYGON((0 0,30 0,30 30,0 30,0 0))
686
111 POLYGON((0 0,20 0,20 20,0 20,0 0))
687
112 POLYGON((1 1,21 1,21 21,1 21,1 1))
688
113 POLYGON((3 6,4 6,4 10,3 10,3 6))
689
114 POLYGON((10 0,16 0,16 48,10 48,10 0))
690
115 POLYGON((10 0,10 0,10 48,10 48,10 0))
691
116 POLYGON((1 2,21 2,21 8,1 8,1 2))
692
117 POLYGON((28 0,84 0,84 42,28 42,28 0))
693
118 POLYGON((28 0,84 0,84 42,28 42,28 0))
694
119 POLYGON((0 0,3 0,3 3,0 3,0 0))
695
120 POLYGON((0 0,10 0,10 10,0 10,0 0))
696
121 POLYGON((3 6,44 6,44 9,3 9,3 6))
697
explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from gis_geometry;
698
id select_type table type possible_keys key key_len ref rows filtered Extra
699
1 SIMPLE gis_geometry ALL NULL NULL NULL NULL 21 100.00
701
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`
702
SELECT fid, X(g) FROM gis_point ORDER by fid;
708
SELECT fid, Y(g) FROM gis_point ORDER by fid;
714
explain extended select X(g),Y(g) FROM gis_point;
715
id select_type table type possible_keys key key_len ref rows filtered Extra
716
1 SIMPLE gis_point ALL NULL NULL NULL NULL 4 100.00
718
Note 1003 select x(`test`.`gis_point`.`g`) AS `X(g)`,y(`test`.`gis_point`.`g`) AS `Y(g)` from `test`.`gis_point`
719
SELECT fid, AsText(StartPoint(g)) FROM gis_line ORDER by fid;
720
fid AsText(StartPoint(g))
724
SELECT fid, AsText(EndPoint(g)) FROM gis_line ORDER by fid;
725
fid AsText(EndPoint(g))
729
SELECT fid, GLength(g) FROM gis_line ORDER by fid;
734
SELECT fid, NumPoints(g) FROM gis_line ORDER by fid;
739
SELECT fid, AsText(PointN(g, 2)) FROM gis_line ORDER by fid;
740
fid AsText(PointN(g, 2))
744
SELECT fid, IsClosed(g) FROM gis_line ORDER by fid;
749
explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM gis_line;
750
id select_type table type possible_keys key key_len ref rows filtered Extra
751
1 SIMPLE gis_line ALL NULL NULL NULL NULL 3 100.00
753
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`
754
SELECT fid, AsText(Centroid(g)) FROM gis_polygon ORDER by fid;
755
fid AsText(Centroid(g))
757
109 POINT(25.416666666667 25.416666666667)
759
SELECT fid, Area(g) FROM gis_polygon ORDER by fid;
764
SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon ORDER by fid;
765
fid AsText(ExteriorRing(g))
766
108 LINESTRING(10 10,20 10,20 20,10 20,10 10)
767
109 LINESTRING(0 0,50 0,50 50,0 50,0 0)
768
110 LINESTRING(0 0,30 0,30 30,0 0)
769
SELECT fid, NumInteriorRings(g) FROM gis_polygon ORDER by fid;
770
fid NumInteriorRings(g)
774
SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid;
775
fid AsText(InteriorRingN(g, 1))
777
109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
779
explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM gis_polygon;
780
id select_type table type possible_keys key key_len ref rows filtered Extra
781
1 SIMPLE gis_polygon ALL NULL NULL NULL NULL 3 100.00
783
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`
784
SELECT fid, IsClosed(g) FROM gis_multi_line ORDER by fid;
789
SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon ORDER by fid;
790
fid AsText(Centroid(g))
791
117 POINT(55.588527753042 17.426536064114)
792
118 POINT(55.588527753042 17.426536064114)
794
SELECT fid, Area(g) FROM gis_multi_polygon ORDER by fid;
799
SELECT fid, NumGeometries(g) from gis_multi_point ORDER by fid;
804
SELECT fid, NumGeometries(g) from gis_multi_line ORDER by fid;
809
SELECT fid, NumGeometries(g) from gis_multi_polygon ORDER by fid;
814
SELECT fid, NumGeometries(g) from gis_geometrycollection ORDER by fid;
818
explain extended SELECT fid, NumGeometries(g) from gis_multi_point;
819
id select_type table type possible_keys key key_len ref rows filtered Extra
820
1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00
822
Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from `test`.`gis_multi_point`
823
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point ORDER by fid;
824
fid AsText(GeometryN(g, 2))
828
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line ORDER by fid;
829
fid AsText(GeometryN(g, 2))
830
114 LINESTRING(16 0,16 23,16 48)
832
116 LINESTRING(2 5,5 8,21 7)
833
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon ORDER by fid;
834
fid AsText(GeometryN(g, 2))
835
117 POLYGON((59 18,67 18,67 13,59 13,59 18))
836
118 POLYGON((59 18,67 18,67 13,59 13,59 18))
838
SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection ORDER by fid;
839
fid AsText(GeometryN(g, 2))
840
120 LINESTRING(0 0,10 10)
841
121 LINESTRING(3 6,7 9)
842
SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection ORDER by fid;
843
fid AsText(GeometryN(g, 1))
846
explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
847
id select_type table type possible_keys key key_len ref rows filtered Extra
848
1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00
850
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`
851
SELECT g1.fid as first, g2.fid as second,
852
Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
853
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
854
Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
855
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
856
first second w c o e d t i r
857
120 120 1 1 0 1 0 0 1 0
858
120 121 0 0 0 0 0 0 1 0
859
121 120 0 0 1 0 0 0 1 0
860
121 121 1 1 0 1 0 0 1 0
861
explain extended SELECT g1.fid as first, g2.fid as second,
862
Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
863
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
864
Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
865
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
866
id select_type table type possible_keys key key_len ref rows filtered Extra
867
1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
868
1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00
870
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`
871
DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
873
a INTEGER PRIMARY KEY AUTO_INCREMENT,
880
gc geometrycollection,
884
Field Type Null Key Default Extra
885
a int(11) NO PRI NULL auto_increment
887
ln linestring YES NULL
889
mp multipoint YES NULL
890
mln multilinestring YES NULL
891
mpg multipolygon YES NULL
892
gc geometrycollection YES NULL
894
ALTER TABLE t1 ADD fid INT;
896
Field Type Null Key Default Extra
897
a int(11) NO PRI NULL auto_increment
899
ln linestring YES NULL
901
mp multipoint YES NULL
902
mln multilinestring YES NULL
903
mpg multipolygon YES NULL
904
gc geometrycollection YES NULL
908
create table t1 (pk integer primary key auto_increment, a geometry not null);
909
insert into t1 (a) values (GeomFromText('Point(1 2)'));
910
insert into t1 (a) values ('Garbage');
911
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
912
insert IGNORE into t1 (a) values ('Garbage');
913
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
915
create table t1 (pk integer primary key auto_increment, fl geometry);
916
insert into t1 (fl) values (1);
917
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
918
insert into t1 (fl) values (1.11);
919
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
920
insert into t1 (fl) values ("qwerty");
921
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
922
insert into t1 (fl) values (pointfromtext('point(1,1)'));
923
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field