1
SET storage_engine=innodb;
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, g POINT);
4
CREATE TABLE gis_line (fid INTEGER, g LINESTRING);
5
CREATE TABLE gis_polygon (fid INTEGER, g POLYGON);
6
CREATE TABLE gis_multi_point (fid INTEGER, g MULTIPOINT);
7
CREATE TABLE gis_multi_line (fid INTEGER, g MULTILINESTRING);
8
CREATE TABLE gis_multi_polygon (fid INTEGER, g MULTIPOLYGON);
9
CREATE TABLE gis_geometrycollection (fid INTEGER, g GEOMETRYCOLLECTION);
10
CREATE TABLE gis_geometry (fid INTEGER, g GEOMETRY);
11
SHOW CREATE TABLE gis_point;
13
gis_point CREATE TABLE `gis_point` (
14
`fid` int(11) default NULL,
15
`g` point default NULL
16
) ENGINE=InnoDB DEFAULT CHARSET=latin1
17
SHOW FIELDS FROM gis_point;
18
Field Type Null Key Default Extra
21
SHOW FIELDS FROM gis_line;
22
Field Type Null Key Default Extra
25
SHOW FIELDS FROM gis_polygon;
26
Field Type Null Key Default Extra
29
SHOW FIELDS FROM gis_multi_point;
30
Field Type Null Key Default Extra
33
SHOW FIELDS FROM gis_multi_line;
34
Field Type Null Key Default Extra
36
g multilinestring YES NULL
37
SHOW FIELDS FROM gis_multi_polygon;
38
Field Type Null Key Default Extra
40
g multipolygon YES NULL
41
SHOW FIELDS FROM gis_geometrycollection;
42
Field Type Null Key Default Extra
44
g geometrycollection YES NULL
45
SHOW FIELDS FROM gis_geometry;
46
Field Type Null Key Default Extra
49
INSERT INTO gis_point VALUES
50
(101, PointFromText('POINT(10 10)')),
51
(102, PointFromText('POINT(20 10)')),
52
(103, PointFromText('POINT(20 20)')),
53
(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
54
INSERT INTO gis_line VALUES
55
(105, LineFromText('LINESTRING(0 0,0 10,10 0)')),
56
(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
57
(107, LineStringFromWKB(LineString(Point(10, 10), Point(40, 10))));
58
INSERT INTO gis_polygon VALUES
59
(108, PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
60
(109, PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
61
(110, PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))));
62
INSERT INTO gis_multi_point VALUES
63
(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
64
(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
65
(113, MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10))));
66
INSERT INTO gis_multi_line VALUES
67
(114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
68
(115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
69
(116, MLineFromWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))));
70
INSERT INTO gis_multi_polygon VALUES
71
(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)))')),
72
(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)))')),
73
(119, MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))));
74
INSERT INTO gis_geometrycollection VALUES
75
(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
76
(121, GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))));
77
INSERT into gis_geometry SELECT * FROM gis_point;
78
INSERT into gis_geometry SELECT * FROM gis_line;
79
INSERT into gis_geometry SELECT * FROM gis_polygon;
80
INSERT into gis_geometry SELECT * FROM gis_multi_point;
81
INSERT into gis_geometry SELECT * FROM gis_multi_line;
82
INSERT into gis_geometry SELECT * FROM gis_multi_polygon;
83
INSERT into gis_geometry SELECT * FROM gis_geometrycollection;
84
SELECT fid, AsText(g) FROM gis_point ORDER by fid;
90
SELECT fid, AsText(g) FROM gis_line ORDER by fid;
92
105 LINESTRING(0 0,0 10,10 0)
93
106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
94
107 LINESTRING(10 10,40 10)
95
SELECT fid, AsText(g) FROM gis_polygon ORDER by fid;
97
108 POLYGON((10 10,20 10,20 20,10 20,10 10))
98
109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
99
110 POLYGON((0 0,30 0,30 30,0 0))
100
SELECT fid, AsText(g) FROM gis_multi_point ORDER by fid;
102
111 MULTIPOINT(0 0,10 10,10 20,20 20)
103
112 MULTIPOINT(1 1,11 11,11 21,21 21)
104
113 MULTIPOINT(3 6,4 10)
105
SELECT fid, AsText(g) FROM gis_multi_line ORDER by fid;
107
114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
108
115 MULTILINESTRING((10 48,10 21,10 0))
109
116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
110
SELECT fid, AsText(g) FROM gis_multi_polygon ORDER by fid;
112
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)))
113
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)))
114
119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
115
SELECT fid, AsText(g) FROM gis_geometrycollection ORDER by fid;
117
120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
118
121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
119
SELECT fid, AsText(g) FROM gis_geometry ORDER by fid;
125
105 LINESTRING(0 0,0 10,10 0)
126
106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
127
107 LINESTRING(10 10,40 10)
128
108 POLYGON((10 10,20 10,20 20,10 20,10 10))
129
109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
130
110 POLYGON((0 0,30 0,30 30,0 0))
131
111 MULTIPOINT(0 0,10 10,10 20,20 20)
132
112 MULTIPOINT(1 1,11 11,11 21,21 21)
133
113 MULTIPOINT(3 6,4 10)
134
114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
135
115 MULTILINESTRING((10 48,10 21,10 0))
136
116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
137
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)))
138
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)))
139
119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
140
120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
141
121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
142
SELECT fid, Dimension(g) FROM gis_geometry ORDER by fid;
165
SELECT fid, GeometryType(g) FROM gis_geometry ORDER by fid;
186
120 GEOMETRYCOLLECTION
187
121 GEOMETRYCOLLECTION
188
SELECT fid, IsEmpty(g) FROM gis_geometry ORDER by fid;
211
SELECT fid, AsText(Envelope(g)) FROM gis_geometry ORDER by fid;
212
fid AsText(Envelope(g))
213
101 POLYGON((10 10,10 10,10 10,10 10,10 10))
214
102 POLYGON((20 10,20 10,20 10,20 10,20 10))
215
103 POLYGON((20 20,20 20,20 20,20 20,20 20))
216
104 POLYGON((10 20,10 20,10 20,10 20,10 20))
217
105 POLYGON((0 0,10 0,10 10,0 10,0 0))
218
106 POLYGON((10 10,20 10,20 20,10 20,10 10))
219
107 POLYGON((10 10,40 10,40 10,10 10,10 10))
220
108 POLYGON((10 10,20 10,20 20,10 20,10 10))
221
109 POLYGON((0 0,50 0,50 50,0 50,0 0))
222
110 POLYGON((0 0,30 0,30 30,0 30,0 0))
223
111 POLYGON((0 0,20 0,20 20,0 20,0 0))
224
112 POLYGON((1 1,21 1,21 21,1 21,1 1))
225
113 POLYGON((3 6,4 6,4 10,3 10,3 6))
226
114 POLYGON((10 0,16 0,16 48,10 48,10 0))
227
115 POLYGON((10 0,10 0,10 48,10 48,10 0))
228
116 POLYGON((1 2,21 2,21 8,1 8,1 2))
229
117 POLYGON((28 0,84 0,84 42,28 42,28 0))
230
118 POLYGON((28 0,84 0,84 42,28 42,28 0))
231
119 POLYGON((0 0,3 0,3 3,0 3,0 0))
232
120 POLYGON((0 0,10 0,10 10,0 10,0 0))
233
121 POLYGON((3 6,44 6,44 9,3 9,3 6))
234
explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from gis_geometry;
235
id select_type table type possible_keys key key_len ref rows Extra
236
1 SIMPLE gis_geometry ALL NULL NULL NULL NULL 21
238
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`
239
SELECT fid, X(g) FROM gis_point ORDER by fid;
245
SELECT fid, Y(g) FROM gis_point ORDER by fid;
251
explain extended select X(g),Y(g) FROM gis_point;
252
id select_type table type possible_keys key key_len ref rows Extra
253
1 SIMPLE gis_point ALL NULL NULL NULL NULL 4
255
Note 1003 select x(`test`.`gis_point`.`g`) AS `X(g)`,y(`test`.`gis_point`.`g`) AS `Y(g)` from `test`.`gis_point`
256
SELECT fid, AsText(StartPoint(g)) FROM gis_line ORDER by fid;
257
fid AsText(StartPoint(g))
261
SELECT fid, AsText(EndPoint(g)) FROM gis_line ORDER by fid;
262
fid AsText(EndPoint(g))
266
SELECT fid, GLength(g) FROM gis_line ORDER by fid;
271
SELECT fid, NumPoints(g) FROM gis_line ORDER by fid;
276
SELECT fid, AsText(PointN(g, 2)) FROM gis_line ORDER by fid;
277
fid AsText(PointN(g, 2))
281
SELECT fid, IsClosed(g) FROM gis_line ORDER by fid;
286
explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM gis_line;
287
id select_type table type possible_keys key key_len ref rows Extra
288
1 SIMPLE gis_line ALL NULL NULL NULL NULL 3
290
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`
291
SELECT fid, AsText(Centroid(g)) FROM gis_polygon ORDER by fid;
292
fid AsText(Centroid(g))
294
109 POINT(25.416666666667 25.416666666667)
296
SELECT fid, Area(g) FROM gis_polygon ORDER by fid;
301
SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon ORDER by fid;
302
fid AsText(ExteriorRing(g))
303
108 LINESTRING(10 10,20 10,20 20,10 20,10 10)
304
109 LINESTRING(0 0,50 0,50 50,0 50,0 0)
305
110 LINESTRING(0 0,30 0,30 30,0 0)
306
SELECT fid, NumInteriorRings(g) FROM gis_polygon ORDER by fid;
307
fid NumInteriorRings(g)
311
SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid;
312
fid AsText(InteriorRingN(g, 1))
314
109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
316
explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM gis_polygon;
317
id select_type table type possible_keys key key_len ref rows Extra
318
1 SIMPLE gis_polygon ALL NULL NULL NULL NULL 3
320
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`
321
SELECT fid, IsClosed(g) FROM gis_multi_line ORDER by fid;
326
SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon ORDER by fid;
327
fid AsText(Centroid(g))
328
117 POINT(55.588527753042 17.426536064114)
329
118 POINT(55.588527753042 17.426536064114)
331
SELECT fid, Area(g) FROM gis_multi_polygon ORDER by fid;
336
SELECT fid, NumGeometries(g) from gis_multi_point ORDER by fid;
341
SELECT fid, NumGeometries(g) from gis_multi_line ORDER by fid;
346
SELECT fid, NumGeometries(g) from gis_multi_polygon ORDER by fid;
351
SELECT fid, NumGeometries(g) from gis_geometrycollection ORDER by fid;
355
explain extended SELECT fid, NumGeometries(g) from gis_multi_point;
356
id select_type table type possible_keys key key_len ref rows Extra
357
1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3
359
Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from `test`.`gis_multi_point`
360
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point ORDER by fid;
361
fid AsText(GeometryN(g, 2))
365
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line ORDER by fid;
366
fid AsText(GeometryN(g, 2))
367
114 LINESTRING(16 0,16 23,16 48)
369
116 LINESTRING(2 5,5 8,21 7)
370
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon ORDER by fid;
371
fid AsText(GeometryN(g, 2))
372
117 POLYGON((59 18,67 18,67 13,59 13,59 18))
373
118 POLYGON((59 18,67 18,67 13,59 13,59 18))
375
SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection ORDER by fid;
376
fid AsText(GeometryN(g, 2))
377
120 LINESTRING(0 0,10 10)
378
121 LINESTRING(3 6,7 9)
379
SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection ORDER by fid;
380
fid AsText(GeometryN(g, 1))
383
explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
384
id select_type table type possible_keys key key_len ref rows Extra
385
1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3
387
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`
388
SELECT g1.fid as first, g2.fid as second,
389
Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
390
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
391
Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
392
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
393
first second w c o e d t i r
394
120 120 1 1 0 1 0 0 1 0
395
120 121 0 0 0 0 0 0 1 0
396
121 120 0 0 1 0 0 0 1 0
397
121 121 1 1 0 1 0 0 1 0
398
explain extended SELECT g1.fid as first, g2.fid as second,
399
Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
400
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
401
Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
402
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
403
id select_type table type possible_keys key key_len ref rows Extra
404
1 SIMPLE g1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
405
1 SIMPLE g2 ALL NULL NULL NULL NULL 2
407
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`
408
DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
416
gc geometrycollection,
420
Field Type Null Key Default Extra
422
ln linestring YES NULL
424
mp multipoint YES NULL
425
mln multilinestring YES NULL
426
mpg multipolygon YES NULL
427
gc geometrycollection YES NULL
429
ALTER TABLE t1 ADD fid INT;
431
Field Type Null Key Default Extra
433
ln linestring YES NULL
435
mp multipoint YES NULL
436
mln multilinestring YES NULL
437
mpg multipolygon YES NULL
438
gc geometrycollection YES NULL
442
create table t1 (a geometry not null);
443
insert into t1 values (GeomFromText('Point(1 2)'));
444
insert into t1 values ('Garbage');
445
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
446
insert IGNORE into t1 values ('Garbage');
447
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
449
create table t1 (fl geometry);
450
insert into t1 values (1);
451
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
452
insert into t1 values (1.11);
453
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
454
insert into t1 values ("qwerty");
455
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
456
insert into t1 values (pointfromtext('point(1,1)'));
457
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field