Debian Codegraph @

mysql-test/suite/innodb_gis/t/gis.test

raw
1 # This is a testcase copied from mysql-test/t/gis.test
2
3 --source include/not_have_innodb_4k.inc
4
5 SET default_storage_engine=InnoDB;
6
7 #
8 # Spatial objects
9 #
10
11 --disable_warnings
12 DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
13 --enable_warnings
14
15 CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT);
16 CREATE TABLE gis_line (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING);
17 CREATE TABLE gis_polygon (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON);
18 CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT);
19 CREATE TABLE gis_multi_line (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING);
20 CREATE TABLE gis_multi_polygon (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON);
21 CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION);
22 CREATE TABLE gis_geometry (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
23
24 SHOW FIELDS FROM gis_point;
25 SHOW FIELDS FROM gis_line;
26 SHOW FIELDS FROM gis_polygon;
27 SHOW FIELDS FROM gis_multi_point;
28 SHOW FIELDS FROM gis_multi_line;
29 SHOW FIELDS FROM gis_multi_polygon;
30 SHOW FIELDS FROM gis_geometrycollection;
31 SHOW FIELDS FROM gis_geometry;
32
33
34 INSERT INTO gis_point VALUES
35 (101, ST_PointFromText('POINT(10 10)')),
36 (102, ST_PointFromText('POINT(20 10)')),
37 (103, ST_PointFromText('POINT(20 20)')),
38 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
39
40 INSERT INTO gis_line VALUES
41 (105, ST_LineFromText('LINESTRING(0 0,0 10,10 0)')),
42 (106, ST_LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
43 (107, ST_LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(40, 10)))));
44
45 INSERT INTO gis_polygon VALUES
46 (108, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
47 (109, ST_PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
48 (110, ST_PolyFromWKB(ST_AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))));
49
50 INSERT INTO gis_multi_point VALUES
51 (111, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
52 (112, ST_MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
53 (113, ST_MPointFromWKB(ST_AsWKB(MultiPoint(Point(3, 6), Point(4, 10)))));
54
55 INSERT INTO gis_multi_line VALUES
56 (114, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
57 (115, ST_MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
58 (116, ST_MLineFromWKB(ST_AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7))))));
59
60
61 INSERT INTO gis_multi_polygon VALUES
62 (117, ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')),
63 (118, ST_MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')),
64 (119, ST_MPolyFromWKB(ST_AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))));
65
66 INSERT INTO gis_geometrycollection VALUES
67 (120, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
68 (121, ST_GeometryFromWKB(ST_AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))))));
69
70 INSERT into gis_geometry SELECT * FROM gis_point;
71 INSERT into gis_geometry SELECT * FROM gis_line;
72 INSERT into gis_geometry SELECT * FROM gis_polygon;
73 INSERT into gis_geometry SELECT * FROM gis_multi_point;
74 INSERT into gis_geometry SELECT * FROM gis_multi_line;
75 INSERT into gis_geometry SELECT * FROM gis_multi_polygon;
76 INSERT into gis_geometry SELECT * FROM gis_geometrycollection;
77
78 SELECT fid, ST_AsText(g) FROM gis_point;
79 SELECT fid, ST_AsText(g) FROM gis_line;
80 SELECT fid, ST_AsText(g) FROM gis_polygon;
81 SELECT fid, ST_AsText(g) FROM gis_multi_point;
82 SELECT fid, ST_AsText(g) FROM gis_multi_line;
83 SELECT fid, ST_AsText(g) FROM gis_multi_polygon;
84 SELECT fid, ST_AsText(g) FROM gis_geometrycollection;
85 SELECT fid, ST_AsText(g) FROM gis_geometry;
86
87 SELECT fid, ST_Dimension(g) FROM gis_geometry;
88 SELECT fid, ST_GeometryType(g) FROM gis_geometry;
89 SELECT fid, ST_IsEmpty(g) FROM gis_geometry;
90 SELECT fid, ST_AsText(ST_Envelope(g)) FROM gis_geometry;
91 --replace_column 10 #
92 explain select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry;
93
94 SELECT fid, ST_X(g) FROM gis_point;
95 SELECT fid, ST_Y(g) FROM gis_point;
96 --replace_column 10 #
97 explain select ST_X(g),ST_Y(g) FROM gis_point;
98
99 SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line;
100 SELECT fid, ST_AsText(ST_EndPoint(g)) FROM gis_line;
101 SELECT fid, ST_Length(g) FROM gis_line;
102 SELECT fid, ST_NumPoints(g) FROM gis_line;
103 SELECT fid, ST_AsText(ST_PointN(g, 2)) FROM gis_line;
104 SELECT fid, ST_IsClosed(g) FROM gis_line;
105 --replace_column 10 #
106 explain select ST_AsText(ST_StartPoint(g)),ST_AsText(ST_EndPoint(g)),ST_Length(g),ST_NumPoints(g),ST_AsText(ST_PointN(g, 2)),ST_IsClosed(g) FROM gis_line;
107
108 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon;
109 SELECT fid, ST_Area(g) FROM gis_polygon;
110 SELECT fid, ST_AsText(ST_ExteriorRing(g)) FROM gis_polygon;
111 SELECT fid, ST_NumInteriorRings(g) FROM gis_polygon;
112 SELECT fid, ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
113 --replace_column 10 #
114 explain select ST_AsText(ST_Centroid(g)),ST_Area(g),ST_AsText(ST_ExteriorRing(g)),ST_NumInteriorRings(g),ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
115
116 SELECT fid, ST_IsClosed(g) FROM gis_multi_line;
117
118 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_multi_polygon;
119 SELECT fid, ST_Area(g) FROM gis_multi_polygon;
120
121 SELECT fid, ST_NumGeometries(g) from gis_multi_point;
122 SELECT fid, ST_NumGeometries(g) from gis_multi_line;
123 SELECT fid, ST_NumGeometries(g) from gis_multi_polygon;
124 SELECT fid, ST_NumGeometries(g) from gis_geometrycollection;
125 --replace_column 10 #
126 explain SELECT fid, ST_NumGeometries(g) from gis_multi_point;
127
128 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
129 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_line;
130 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_polygon;
131 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_geometrycollection;
132 SELECT fid, ST_AsText(ST_GeometryN(g, 1)) from gis_geometrycollection;
133 --replace_column 10 #
134 explain SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
135
136 SELECT g1.fid as first, g2.fid as second,
137 MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
138 MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
139 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
140 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
141 --replace_column 10 #
142 explain SELECT g1.fid as first, g2.fid as second,
143 MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
144 MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
145 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
146 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
147
148 DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
149
150 #
151 # Check that ALTER TABLE doesn't loose geometry type
152 #
153 CREATE TABLE t1 (
154 gp point,
155 ln linestring,
156 pg polygon,
157 mp multipoint,
158 mln multilinestring,
159 mpg multipolygon,
160 gc geometrycollection,
161 gm geometry
162 );
163
164 SHOW FIELDS FROM t1;
165 ALTER TABLE t1 ADD fid INT NOT NULL;
166 SHOW FIELDS FROM t1;
167 DROP TABLE t1;
168
169 SELECT ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_GeometryFromText('POINT(1 4)'))));
170 explain SELECT ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_GeometryFromText('POINT(1 4)'))));
171 explain SELECT ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_PointFromText('POINT(1 4)'))));
172 SELECT ST_SRID(ST_GeomFromText('LineString(1 1,2 2)'));
173 explain SELECT ST_SRID(ST_GeomFromText('LineString(1 1,2 2)'));
174 #select ST_issimple(MultiPoint(Point(3, 6), Point(4, 10))), ST_issimple(Point(3, 6)),ST_issimple(ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),ST_issimple(ST_GeometryFromText('POINT(1 4)')), ST_issimple(ST_AsWKB(ST_GeometryFromText('POINT(1 4)')));
175 explain select ST_issimple(MultiPoint(Point(3, 6), Point(4, 10))), ST_issimple(Point(3, 6));
176
177 create table t1 (a geometry not null SRID 0);
178 insert into t1 values (ST_GeomFromText('Point(1 2)'));
179 -- error 1416
180 insert into t1 values ('Garbage');
181 -- error 1416
182 insert IGNORE into t1 values ('Garbage');
183 alter table t1 add spatial index(a);
184
185 drop table t1;
186
187 #
188 # Bug #5219: problem with range optimizer
189 #
190
191 create table t1(a geometry not null SRID 0, spatial index(a));
192 insert into t1 values
193 (ST_GeomFromText('POINT(1 1)')), (ST_GeomFromText('POINT(3 3)')),
194 (ST_GeomFromText('POINT(4 4)')), (ST_GeomFromText('POINT(6 6)'));
195 select ST_AsText(a) from t1 where
196 MBRContains(ST_GeomFromText('Polygon((0 0, 0 2, 2 2, 2 0, 0 0))'), a)
197 or
198 MBRContains(ST_GeomFromText('Polygon((2 2, 2 5, 5 5, 5 2, 2 2))'), a);
199 select ST_AsText(a) from t1 where
200 MBRContains(ST_GeomFromText('Polygon((0 0, 0 2, 2 2, 2 0, 0 0))'), a)
201 and
202 MBRContains(ST_GeomFromText('Polygon((0 0, 0 7, 7 7, 7 0, 0 0))'), a);
203 drop table t1;
204
205 CREATE TABLE t1 (Coordinates POINT NOT NULL SRID 0, SPATIAL INDEX(Coordinates));
206 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(383293632 1754448)'));
207 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(564952612 157516260)'));
208 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(903994614 180726515)'));
209 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(98128178 141127631)'));
210 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(862547902 799334546)'));
211 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(341989013 850270906)'));
212 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(803302376 93039099)'));
213 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(857439153 817431356)'));
214 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(319757546 343162742)'));
215 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(826341972 717484432)'));
216 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(305066789 201736238)'));
217 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(626068992 616241497)'));
218 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(55789424 755830108)'));
219 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(802874458 312435220)'));
220 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(153795660 551723671)'));
221 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(242207428 537089292)'));
222 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(553478119 807160039)'));
223 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(694605552 457472733)'));
224 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(987886554 792733729)'));
225 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(598600363 850434457)'));
226 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(592068275 940589376)'));
227 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(700705362 395370650)'));
228 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(33628474 558144514)'));
229 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(212802006 353386020)'));
230 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(901307256 39143977)'));
231 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(70870451 206374045)'));
232 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(240880214 696939443)'));
233 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(822615542 296669638)'));
234 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(452769551 625489999)'));
235 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(609104858 606565210)'));
236 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(177213669 851312285)'));
237 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(143654501 730691787)'));
238 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(658472325 838260052)'));
239 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(188164520 646358878)'));
240 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(630993781 786764883)'));
241 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(496793334 223062055)'));
242 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(727354258 197498696)'));
243 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(618432704 760982731)'));
244 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(755643210 831234710)'));
245 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(114368751 656950466)'));
246 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(870378686 185239202)'));
247 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(863324511 111258900)'));
248 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(882178645 685940052)'));
249 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(407928538 334948195)'));
250 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(311430051 17033395)'));
251 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(941513405 488643719)'));
252 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(868345680 85167906)'));
253 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(219335507 526818004)'));
254 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(923427958 407500026)'));
255 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(173176882 554421738)'));
256 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(194264908 669970217)'));
257 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(777483793 921619165)'));
258 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(867468912 395916497)'));
259 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(682601897 623112122)'));
260 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(227151206 796970647)'));
261 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(280062588 97529892)'));
262 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(982209849 143387099)'));
263 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(208788792 864388493)'));
264 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(829327151 616717329)'));
265 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(199336688 140757201)'));
266 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(633750724 140850093)'));
267 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(629400920 502096404)'));
268 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(226017998 848736426)'));
269 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(28914408 149445955)'));
270 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(256236452 202091290)'));
271 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(703867693 450501360)'));
272 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(872061506 481351486)'));
273 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(372120524 739530418)'));
274 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(877267982 54722420)'));
275 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(362642540 104419188)'));
276 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(851693067 642705127)'));
277 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(201949080 833902916)'));
278 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(786092225 410737872)'));
279 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(698291409 615419376)'));
280 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(27455201 897628096)'));
281 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(756176576 661205925)'));
282 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(38478189 385577496)'));
283 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(163302328 264496186)'));
284 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(234313922 192216735)'));
285 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(413942141 490550373)'));
286 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(394308025 117809834)'));
287 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(941051732 266369530)'));
288 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(599161319 313172256)'));
289 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(5899948 476429301)'));
290 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(367894677 368542487)'));
291 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(580848489 219587743)'));
292 INSERT INTO t1 VALUES(ST_GeomFromText('POINT(11247614 782797569)'));
293 drop table t1;
294
295 create table t1 select ST_GeomFromWKB(ST_AsWKB(POINT(1,3)));
296 show create table t1;
297 drop table t1;
298
299 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
300 CREATE TABLE `t1` (`object_id` bigint(20) unsigned NOT NULL default '0', `geo`
301 geometry NOT NULL);
302 SET sql_mode = default;
303
304 insert into t1 values ('85984',ST_GeomFromText('MULTIPOLYGON(((-115.006363
305 36.305435,-114.992394 36.305202,-114.991219 36.305975,-114.991163
306 36.306845,-114.989432 36.309452,-114.978275 36.312642,-114.977363
307 36.311978,-114.975327 36.312344,-114.96502 36.31597,-114.963364
308 36.313629,-114.961723 36.313721,-114.956398 36.316057,-114.951882
309 36.320979,-114.947073 36.323475,-114.945207 36.326451,-114.945207
310 36.326451,-114.944132 36.326061,-114.94003 36.326588,-114.924017
311 36.334484,-114.923281 36.334146,-114.92564 36.331504,-114.94072
312 36.319282,-114.945348 36.314812,-114.948091 36.314762,-114.951755
313 36.316211,-114.952446 36.313883,-114.952644 36.309488,-114.944725
314 36.313083,-114.93706 36.32043,-114.932478 36.323497,-114.924556
315 36.327708,-114.922608 36.329715,-114.92009 36.328695,-114.912105
316 36.323566,-114.901647 36.317952,-114.897436 36.313968,-114.895344
317 36.309573,-114.891699 36.304398,-114.890569 36.303551,-114.886356
318 36.302702,-114.885141 36.301351,-114.885709 36.297391,-114.892499
319 36.290893,-114.902142 36.288974,-114.904941 36.288838,-114.905308
320 36.289845,-114.906325 36.290395,-114.909916 36.289549,-114.914527
321 36.287535,-114.918797 36.284423,-114.922982 36.279731,-114.924113
322 36.277282,-114.924057 36.275817,-114.927733 36.27053,-114.929354
323 36.269029,-114.929354 36.269029,-114.950856 36.268715,-114.950768
324 36.264324,-114.960206 36.264293,-114.960301 36.268943,-115.006662
325 36.268929,-115.008583 36.265619,-115.00665 36.264247,-115.006659
326 36.246873,-115.006659 36.246873,-115.006838 36.247697,-115.010764
327 36.247774,-115.015609 36.25113,-115.015765 36.254505,-115.029517
328 36.254619,-115.038573 36.249317,-115.038573 36.249317,-115.023403
329 36.25841,-115.023873 36.258994,-115.031845 36.259829,-115.03183
330 36.261053,-115.025561 36.261095,-115.036417 36.274632,-115.033729
331 36.276041,-115.032217 36.274851,-115.029845 36.273959,-115.029934
332 36.274966,-115.025763 36.274896,-115.025406 36.281044,-115.028731
333 36.284471,-115.036497 36.290377,-115.042071 36.291039,-115.026759
334 36.298478,-115.008995 36.301966,-115.006363 36.305435),(-115.079835
335 36.244369,-115.079735 36.260186,-115.076435 36.262369,-115.069758
336 36.265,-115.070235 36.268757,-115.064542 36.268655,-115.061843
337 36.269857,-115.062676 36.270693,-115.06305 36.272344,-115.059051
338 36.281023,-115.05918 36.283008,-115.060591 36.285246,-115.061913
339 36.290022,-115.062499 36.306353,-115.062499 36.306353,-115.060918
340 36.30642,-115.06112 36.289779,-115.05713 36.2825,-115.057314
341 36.279446,-115.060779 36.274659,-115.061366 36.27209,-115.057858
342 36.26557,-115.055805 36.262883,-115.054688 36.262874,-115.047335
343 36.25037,-115.044234 36.24637,-115.052434 36.24047,-115.061734
344 36.23507,-115.061934 36.22677,-115.061934 36.22677,-115.061491
345 36.225267,-115.062024 36.218194,-115.060134 36.218278,-115.060133
346 36.210771,-115.057833 36.210771,-115.057433 36.196271,-115.062233
347 36.196271,-115.062233 36.190371,-115.062233 36.190371,-115.065533
348 36.190371,-115.071333 36.188571,-115.098331 36.188275,-115.098331
349 36.188275,-115.098435 36.237569,-115.097535 36.240369,-115.097535
350 36.240369,-115.093235 36.240369,-115.089135 36.240469,-115.083135
351 36.240569,-115.083135 36.240569,-115.079835
352 36.244369)))')),('85998',ST_GeomFromText('MULTIPOLYGON(((-115.333107
353 36.264587,-115.333168 36.280638,-115.333168 36.280638,-115.32226
354 36.280643,-115.322538 36.274311,-115.327222 36.274258,-115.32733
355 36.263026,-115.330675 36.262984,-115.332132 36.264673,-115.333107
356 36.264587),(-115.247239 36.247066,-115.247438 36.218267,-115.247438
357 36.218267,-115.278525 36.219263,-115.278525 36.219263,-115.301545
358 36.219559,-115.332748 36.219197,-115.332757 36.220041,-115.332757
359 36.220041,-115.332895 36.233514,-115.349023 36.233479,-115.351489
360 36.234475,-115.353681 36.237021,-115.357106 36.239789,-115.36519
361 36.243331,-115.368156 36.243487,-115.367389 36.244902,-115.364553
362 36.246014,-115.359219 36.24616,-115.356186 36.248025,-115.353347
363 36.248004,-115.350813 36.249507,-115.339673 36.25387,-115.333069
364 36.255018,-115.333069 36.255018,-115.333042 36.247767,-115.279039
365 36.248666,-115.263639 36.247466,-115.263839 36.252766,-115.261439
366 36.252666,-115.261439 36.247366,-115.247239 36.247066)))'));
367
368 select object_id, ST_geometrytype(geo), ST_ISSIMPLE(GEO), ST_ASTEXT(ST_centroid(geo)) from
369 t1 where object_id=85998;
370
371 select object_id, ST_geometrytype(geo), ST_ISSIMPLE(GEO), ST_ASTEXT(ST_centroid(geo)) from
372 t1 where object_id=85984;
373
374 drop table t1;
375
376 create table t1 (fl geometry not null);
377 --error 1416
378 insert into t1 values (1);
379 --error 1416
380 insert into t1 values (1.11);
381 --error 1416
382 insert into t1 values ("qwerty");
383 --error 1048
384 --error ER_GIS_INVALID_DATA
385 insert into t1 values (ST_pointfromtext('point(1,1)'));
386
387 drop table t1;
388
389 select (ST_asWKT(ST_geomfromwkb((0x000000000140240000000000004024000000000000))));
390 select (ST_asWKT(ST_geomfromwkb((0x010100000000000000000024400000000000002440))));
391
392 --enable_metadata
393 create table t1 (g GEOMETRY);
394 select * from t1;
395 select ST_asbinary(g) from t1;
396 --disable_metadata
397 drop table t1;
398
399 create table t1 (a TEXT, b GEOMETRY NOT NULL SRID 0, SPATIAL KEY(b));
400 alter table t1 disable keys;
401 --error 1263
402 load data infile '../../std_data/bad_gis_data.dat' into table t1;
403 alter table t1 enable keys;
404 drop table t1;
405
406 #
407 # Bug #26038: is null and bad data
408 #
409
410 create table t1 (a int, b blob);
411 insert into t1 values (1, ''), (2, NULL), (3, '1');
412 select * from t1;
413
414 --error ER_ILLEGAL_VALUE_FOR_TYPE
415 select
416 ST_geometryfromtext(b) IS NULL, ST_geometryfromwkb(ST_AsWKB(b)) IS NULL, ST_astext(b) IS NULL,
417 ST_aswkb(b) IS NULL, ST_geometrytype(b) IS NULL, ST_centroid(b) IS NULL,
418 ST_envelope(b) IS NULL, ST_startpoint(b) IS NULL, ST_endpoint(b) IS NULL,
419 ST_exteriorring(b) IS NULL, ST_pointn(b, 1) IS NULL, ST_geometryn(b, 1) IS NULL,
420 ST_interiorringn(b, 1) IS NULL, multipoint(b) IS NULL, ST_isempty(b) IS NULL,
421 ST_issimple(b) IS NULL, ST_isclosed(b) IS NULL, ST_dimension(b) IS NULL,
422 ST_numgeometries(b) IS NULL, ST_numinteriorrings(b) IS NULL, ST_numpoints(b) IS NULL,
423 ST_area(b) IS NULL, ST_length(b) IS NULL, ST_srid(b) IS NULL, ST_x(b) IS NULL,
424 ST_y(b) IS NULL
425 from t1;
426
427 --error ER_GIS_INVALID_DATA
428 select
429 MBRwithin(b, b) IS NULL, MBRcontains(b, b) IS NULL, MBRoverlaps(b, b) IS NULL,
430 MBRequals(b, b) IS NULL, MBRdisjoint(b, b) IS NULL, ST_touches(b, b) IS NULL,
431 MBRintersects(b, b) IS NULL, ST_crosses(b, b) IS NULL
432 from t1;
433
434 --error ER_ILLEGAL_VALUE_FOR_TYPE
435 select
436 point(b, b) IS NULL, linestring(b) IS NULL, polygon(b) IS NULL, multipoint(b) IS NULL,
437 multilinestring(b) IS NULL, multipolygon(b) IS NULL,
438 geometrycollection(b) IS NULL
439 from t1;
440
441 drop table t1;
442
443 #
444 # Bug #27164: Crash when mixing InnoDB and MyISAM Geospatial tables
445 #
446 CREATE TABLE t1(a POINT) ENGINE=MyISAM;
447 INSERT INTO t1 VALUES (NULL);
448 SELECT * FROM t1;
449 DROP TABLE t1;
450
451 #
452 # Bug #30955 ST_geomfromtext() crasher
453 #
454 CREATE TABLE `t1` ( `col9` set('a'), `col89` date);
455 INSERT IGNORE INTO `t1` VALUES ('','0000-00-00');
456 --error ER_GIS_INVALID_DATA
457 select ST_geomfromtext(col9,col89) as a from t1;
458 DROP TABLE t1;
459
460 #
461 # Bug #31158 Spatial, Union, LONGBLOB vs BLOB bug (crops data)
462 #
463
464 CREATE TABLE t1 (
465 geomdata polygon NOT NULL SRID 0,
466 SPATIAL KEY index_geom (geomdata)
467 ) ENGINE=MyISAM DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED;
468
469 CREATE TABLE t2 (
470 geomdata polygon NOT NULL SRID 0,
471 SPATIAL KEY index_geom (geomdata)
472 ) ENGINE=MyISAM DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED;
473
474 CREATE TABLE t3
475 select
476 ST_aswkb(ws.geomdata) AS geomdatawkb
477 from
478 t1 ws
479 union
480 select
481 ST_aswkb(ws.geomdata) AS geomdatawkb
482 from
483 t2 ws;
484
485 describe t3;
486
487 drop table t1;
488 drop table t2;
489 drop table t3;
490
491 #
492 # Bug #30284 spatial key corruption
493 #
494
495 create table t1(col1 geometry default null,col15 geometrycollection not
496 null SRID 0,spatial index(col15))engine=innodb;
497 --error ER_CANT_CREATE_GEOMETRY_OBJECT
498 insert into t1 set col15 = ST_GeomFromText('POINT(6 5)');
499 --error ER_CANT_CREATE_GEOMETRY_OBJECT
500 insert into t1 set col15 = ST_GeomFromText('POINT(6 5)');
501 check table t1 extended;
502 drop table t1;
503
504 --echo End of 4.1 tests
505
506 #
507 # Bug #12281 (Geometry: crash in trigger)
508 #
509
510 create table t1 (s1 geometry not null,s2 char(100));
511 create trigger t1_bu before update on t1 for each row set new.s1 = null;
512 --error ER_BAD_NULL_ERROR_NOT_IGNORED
513 insert into t1 values (null,null);
514 drop table t1;
515
516 #
517 # Bug #10499 (function creation with GEOMETRY datatype)
518 #
519 --disable_warnings
520 drop procedure if exists fn3;
521 --enable_warnings
522 create function fn3 () returns point deterministic return ST_GeomFromText("point(1 1)");
523 show create function fn3;
524 select ST_astext(fn3());
525 drop function fn3;
526
527 #
528 # Bug #12267 (primary key over GIS)
529 #
530 create table t1(pt POINT);
531 --error ER_SPATIAL_UNIQUE_INDEX
532 alter table t1 add primary key pti(pt);
533 drop table t1;
534 create table t1(pt GEOMETRY);
535 --error ER_SPATIAL_UNIQUE_INDEX
536 alter table t1 add primary key pti(pt);
537 --error ER_WRONG_SUB_KEY
538 alter table t1 add primary key pti(pt(20));
539 drop table t1;
540
541
542 create table t1 select ST_GeomFromText('point(1 1)');
543 desc t1;
544 drop table t1;
545
546 #
547 # Bug #20691 (DEFAULT over NOT NULL field)
548 #
549 create table t1 (g geometry not null);
550 --error ER_CANT_CREATE_GEOMETRY_OBJECT
551 insert into t1 values(default);
552 drop table t1;
553
554 #
555 # Bug #27300: create view with geometry functions lost columns types
556 #
557 CREATE TABLE t1 (a GEOMETRY);
558 CREATE VIEW v1 AS SELECT ST_GeomFromwkb(ST_ASBINARY(a)) FROM t1;
559 CREATE VIEW v2 AS SELECT a FROM t1;
560 DESCRIBE v1;
561 DESCRIBE v2;
562
563 DROP VIEW v1,v2;
564 DROP TABLE t1;
565
566 #
567 # Bug#24563: MBROverlaps does not seem to function propertly
568 # Bug#54888: MBROverlaps missing in 5.1?
569 #
570
571 # Test all MBR* functions and their non-MBR-prefixed aliases,
572 # using shifted squares to verify the spatial relations.
573
574 create table t1 (name VARCHAR(100), square GEOMETRY);
575
576 INSERT INTO t1 VALUES("center", ST_GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));
577
578 INSERT INTO t1 VALUES("small", ST_GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
579 INSERT INTO t1 VALUES("big", ST_GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
580
581 INSERT INTO t1 VALUES("up", ST_GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));
582 INSERT INTO t1 VALUES("up2", ST_GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));
583 INSERT INTO t1 VALUES("up3", ST_GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));
584
585 INSERT INTO t1 VALUES("down", ST_GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));
586 INSERT INTO t1 VALUES("down2", ST_GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));
587 INSERT INTO t1 VALUES("down3", ST_GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
588
589 INSERT INTO t1 VALUES("right", ST_GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
590 INSERT INTO t1 VALUES("right2", ST_GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
591 INSERT INTO t1 VALUES("right3", ST_GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
592
593 INSERT INTO t1 VALUES("left", ST_GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));
594 INSERT INTO t1 VALUES("left2", ST_GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));
595 INSERT INTO t1 VALUES("left3", ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))'));
596
597 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;
598 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;
599 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrequals FROM t1 a1 JOIN t1 a2 ON MBREquals( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
600 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;
601 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;
602 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;
603 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;
604
605 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;
606 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;
607 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRequals FROM t1 a1 JOIN t1 a2 ON MBREquals( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
608 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS `intersect` FROM t1 a1 JOIN t1 a2 ON MBRIntersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
609 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;
610 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS ST_touches FROM t1 a1 JOIN t1 a2 ON ST_Touches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
611 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;
612
613 # MBROverlaps needs a few more tests, with point and line dimensions
614
615 --error ER_GIS_INVALID_DATA
616 SET @vert1 = ST_GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');
617 --error ER_GIS_INVALID_DATA
618 SET @horiz1 = ST_GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');
619 --error ER_GIS_INVALID_DATA
620 SET @horiz2 = ST_GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');
621 --error ER_GIS_INVALID_DATA
622 SET @horiz3 = ST_GeomFromText('POLYGON ((2 0, 3 0, 2 0))');
623 --error ER_GIS_INVALID_DATA
624 SET @point1 = ST_GeomFromText('POLYGON ((0 0))');
625 --error ER_GIS_INVALID_DATA
626 SET @point2 = ST_GeomFromText('POLYGON ((-2 0))');
627
628 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @vert1) GROUP BY a1.name;
629 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @horiz1) GROUP BY a1.name;
630 SELECT MBROverlaps(@horiz1, @vert1) FROM DUAL;
631 SELECT MBROverlaps(@horiz1, @horiz2) FROM DUAL;
632 SELECT MBROverlaps(@horiz1, @horiz3) FROM DUAL;
633 SELECT MBROverlaps(@horiz1, @point1) FROM DUAL;
634 SELECT MBROverlaps(@horiz1, @point2) FROM DUAL;
635
636 DROP TABLE t1;
637
638 #
639 # Bug#28763: Selecting geometry fields in UNION caused server crash.
640 #
641 create table t1(f1 geometry, f2 polygon, f3 linestring);
642 select f1 from t1 union select f1 from t1;
643 insert into t1 (f2,f3) values (ST_GeomFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))'), ST_GeomFromText('LINESTRING(0 0,1 1,2 2)'));
644 select ST_AsText(f2),ST_AsText(f3) from t1;
645 select ST_AsText(a) from (select f2 as a from t1 union select f3 from t1) t;
646 create table t2 as select f2 as a from t1 union select f3 from t1;
647 desc t2;
648 select ST_AsText(a) from t2;
649 drop table t1, t2;
650
651 #
652 # Bug #29166: MYsql crash when query is run
653 #
654
655 # The test query itself is not logged : too large output.
656 # The real test is the second query : see if the first hasn't crashed the
657 # server
658 --disable_query_log
659 --disable_result_log
660 SELECT ST_AsText(ST_GeometryFromText(CONCAT(
661 'MULTIPOLYGON(((',
662 REPEAT ('-0.00000000001234567890123456789012 -0.123456789012345678,', 1000),
663 '-0.00000000001234567890123456789012 -0.123456789012345678',
664 ')))'
665 ))) AS a;
666 --enable_result_log
667 --enable_query_log
668 SELECT 1;
669
670 -- source include/gis_keys.inc
671
672 #
673 # Bug #31155 gis types in union'd select cause crash
674 #
675
676 create table `t1` (`col002` point)engine=innodb;
677 insert into t1 values (),(),();
678 --error ER_WRONG_ARGUMENTS
679 select min(`col002`) from t1 union select `col002` from t1;
680 drop table t1;
681
682 --echo #
683 --echo # Bug #47780: crash when comparing GIS items from subquery
684 --echo #
685
686 CREATE TABLE t1(a INT, b MULTIPOLYGON);
687 INSERT INTO t1 VALUES
688 (0,
689 ST_GEOMFROMTEXT(
690 'multipolygon(((1 2,3 4,5 6,7 8,9 8,1 2,1 2),(7 6,5 4,3 2,1 2,3 4,7 6)))'));
691
692 --echo # must not crash
693 SELECT 1 FROM t1 WHERE a <> (SELECT ST_GEOMETRYCOLLECTIONFROMWKB(ST_AsWKB(b)) FROM t1);
694
695 DROP TABLE t1;
696
697 --echo #
698 --echo # Bug #49250 : spatial btree index corruption and crash
699 --echo # Part one : spatial syntax check
700 --echo #
701
702 --error ER_PARSE_ERROR
703 CREATE TABLE t1(col1 MULTIPOLYGON NOT NULL,
704 SPATIAL INDEX USING BTREE (col1));
705 CREATE TABLE t2(col1 MULTIPOLYGON NOT NULL);
706 --error ER_PARSE_ERROR
707 CREATE SPATIAL INDEX USING BTREE ON t2(col);
708 --error ER_PARSE_ERROR
709 ALTER TABLE t2 ADD SPATIAL INDEX USING BTREE (col1);
710
711 DROP TABLE t2;
712
713 --echo End of 5.0 tests
714
715
716 #
717 # Bug #11335 View redefines column types
718 #
719 create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime);
720 create view v1 as select * from t1;
721 desc v1;
722 drop view v1;
723 drop table t1;
724
725 #
726 # Bug#44684: valgrind reports invalid reads in
727 # Item_func_spatial_collection::val_str
728 #
729 --error ER_ILLEGAL_VALUE_FOR_TYPE
730 SELECT MultiPoint(12345,'');
731 #SELECT MultiPoint(123451,'');
732 #SELECT MultiPoint(1234512,'');
733 #SELECT MultiPoint(12345123,'');
734
735 --error ER_ILLEGAL_VALUE_FOR_TYPE
736 #SELECT MultiLineString(12345,'');
737 #SELECT MultiLineString(123451,'');
738 #SELECT MultiLineString(1234512,'');
739 #SELECT MultiLineString(12345123,'');
740
741 --error ER_ILLEGAL_VALUE_FOR_TYPE
742 #SELECT LineString(12345,'');
743 #SELECT LineString(123451,'');
744 #SELECT LineString(1234512,'');
745 #SELECT LineString(12345123,'');
746
747 --error ER_ILLEGAL_VALUE_FOR_TYPE
748 #SELECT Polygon(12345,'');
749 #SELECT Polygon(123451,'');
750 #SELECT Polygon(1234512,'');
751 #SELECT Polygon(12345123,'');
752
753 #
754 # Bug55531 crash with conversions of geometry types / strings
755 #
756 --error ER_ILLEGAL_VALUE_FOR_TYPE
757 SELECT 1 FROM (SELECT GREATEST(1,GEOMETRYCOLLECTION('00000','00000')) b FROM DUAL) AS d WHERE (LINESTRING(d.b));
758
759
760 --echo #
761 --echo # BUG#51875: crash when loading data into geometry function ST_polyfromwkb
762 --echo #
763 SET @a=0x00000000030000000100000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440;
764 --error ER_GIS_INVALID_DATA
765 SET @a=ST_POLYFROMWKB(@a);
766 SET @a=0x00000000030000000000000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440;
767 --error ER_GIS_INVALID_DATA
768 SET @a=ST_POLYFROMWKB(@a);
769
770
771 #
772 # Bug #57321 crashes and valgrind errors from spatial types
773 #
774
775 create table t1(a polygon NOT NULL)engine=innodb;
776 --error ER_CANT_CREATE_GEOMETRY_OBJECT
777 insert into t1 values (ST_geomfromtext("point(0 1)"));
778 --error ER_CANT_CREATE_GEOMETRY_OBJECT
779 insert into t1 values (ST_geomfromtext("point(1 0)"));
780 select * from (select polygon(t1.a) as p from t1 order by t1.a) d;
781 drop table t1;
782
783
784 --echo #
785 --echo # Test for bug #59888 "debug assertion when attempt to create spatial index
786 --echo # on char > 31 bytes".
787 --echo #
788 create table t1(a char(32) not null) engine=innodb;
789 --error ER_SPATIAL_MUST_HAVE_GEOM_COL
790 create spatial index i on t1 (a);
791 drop table t1;
792
793
794 --echo End of 5.1 tests
795
796 #
797 # Bug #50574 5.5.ST_x allows spatial indexes on non-spatial
798 # columns, causing crashes!
799 # Bug#11767480 SPATIAL INDEXES ON NON-SPATIAL COLUMNS
800 # CAUSE CRASHES.
801 #
802 CREATE TABLE t0 (a BINARY(32) NOT NULL);
803 --error ER_SPATIAL_MUST_HAVE_GEOM_COL
804 CREATE SPATIAL INDEX i on t0 (a);
805 INSERT INTO t0 VALUES (1);
806
807 --error ER_SPATIAL_MUST_HAVE_GEOM_COL
808 CREATE TABLE t1(
809 col0 BINARY NOT NULL,
810 col2 TIMESTAMP,
811 SPATIAL INDEX i1 (col0)
812 ) ENGINE=MyISAM;
813
814 # Test other ways to add indices
815 CREATE TABLE t1 (
816 col0 BINARY NOT NULL,
817 col2 TIMESTAMP
818 ) ENGINE=MyISAM;
819
820 --error ER_SPATIAL_MUST_HAVE_GEOM_COL
821 CREATE SPATIAL INDEX idx0 ON t1(col0);
822
823 --error ER_SPATIAL_MUST_HAVE_GEOM_COL
824 ALTER TABLE t1 ADD SPATIAL INDEX i1 (col0);
825
826 CREATE TABLE t2 (
827 col0 INTEGER NOT NULL,
828 col1 POINT,
829 col2 POINT
830 );
831
832 --error ER_TOO_MANY_KEY_PARTS
833 CREATE SPATIAL INDEX idx0 ON t2 (col1, col2);
834
835 --error ER_TOO_MANY_KEY_PARTS
836 CREATE TABLE t3 (
837 col0 INTEGER NOT NULL,
838 col1 POINT,
839 col2 LINESTRING,
840 SPATIAL INDEX i1 (col1, col2)
841 );
842
843 # cleanup
844 DROP TABLE t0, t1, t2;
845
846
847 --echo #
848 --echo # BUG#12414917 - ST_ISCLOSED() CRASHES ON 64-BIT BUILDS
849 --echo #
850 --error ER_GIS_DATA_WRONG_ENDIANESS
851 SELECT ST_ISCLOSED(CONVERT(CONCAT(' ', 0x2), BINARY(20)));
852
853 --echo #
854 --echo # BUG#12537203 - CRASH WHEN SUBSELECTING GLOBAL VARIABLES IN
855 --echo # GEOMETRY FUNCTION ARGUMENTS
856 --echo #
857 --replace_regex /non geometric .* value/non geometric '' value/
858 --error ER_ILLEGAL_VALUE_FOR_TYPE
859 SELECT GEOMETRYCOLLECTION((SELECT @@OLD));
860
861
862 --echo End of 5.1 tests
863
864
865 --echo #
866 --echo # Bug#11908153: CRASH AND/OR VALGRIND ERRORS IN FIELD_BLOB::GET_KEY_IMAGE
867 --echo #
868
869 CREATE TABLE g1
870 (a geometry NOT NULL) engine=innodb;
871
872 INSERT INTO g1 VALUES (ST_geomfromtext('point(1 1)'));
873 INSERT INTO g1 VALUES (ST_geomfromtext('point(1 2)'));
874
875 FLUSH TABLES;
876
877 --skip_if_hypergraph # Differing warnings.
878 SELECT 1 FROM g1 WHERE a = date_sub(now(), interval 2808.4 year_month);
879
880 DROP TABLE g1;
881
882 --echo #
883 --echo # Bug#13013970 MORE CRASHES IN FIELD_BLOB::GET_KEY_IMAGE
884 --echo #
885
886 CREATE TABLE g1(a TEXT NOT NULL, KEY(a(255)));
887
888 INSERT INTO g1 VALUES ('a'),('a');
889 --error ER_GIS_INVALID_DATA
890 SELECT 1 FROM g1 WHERE a >= ANY
891 (SELECT 1 FROM g1 WHERE a = ST_geomfromtext('') OR a) ;
892
893 DROP TABLE g1;
894
895 --echo End of 5.5 tests
896
897
898 # Conformance tests
899 #
900 # C.3.3 Geometry types and functions
901 #
902
903 --disable_warnings
904 DROP DATABASE IF EXISTS gis_ogs;
905 --enable_warnings
906
907 CREATE DATABASE gis_ogs;
908 USE gis_ogs;
909
910 --echo #
911 --echo # C.3.3.1 Geometry types and functions schema construction
912 --echo #
913
914
915 CREATE TABLE lakes (
916 fid INTEGER NOT NULL PRIMARY KEY,
917 name CHARACTER VARYING(64),
918 shore POLYGON);
919
920 CREATE TABLE road_segments (
921 fid INTEGER NOT NULL PRIMARY KEY,
922 name CHARACTER VARYING(64),
923 aliases CHARACTER VARYING(64),
924 num_lanes INTEGER,
925 centerline LINESTRING);
926
927 CREATE TABLE divided_routes (
928 fid INTEGER NOT NULL PRIMARY KEY,
929 name CHARACTER VARYING(64),
930 num_lanes INTEGER,
931 centerlines MULTILINESTRING);
932
933 CREATE TABLE forests (
934 fid INTEGER NOT NULL PRIMARY KEY,
935 name CHARACTER VARYING(64),
936 boundary MULTIPOLYGON);
937
938 CREATE TABLE bridges (
939 fid INTEGER NOT NULL PRIMARY KEY,
940 name CHARACTER VARYING(64),
941 position POINT);
942
943 CREATE TABLE streams (
944 fid INTEGER NOT NULL PRIMARY KEY,
945 name CHARACTER VARYING(64),
946 centerline LINESTRING);
947
948 CREATE TABLE buildings (
949 fid INTEGER NOT NULL PRIMARY KEY,
950 address CHARACTER VARYING(64),
951 position POINT,
952 footprint POLYGON);
953
954 CREATE TABLE ponds (
955 fid INTEGER NOT NULL PRIMARY KEY,
956 name CHARACTER VARYING(64),
957 type CHARACTER VARYING(64),
958 shores MULTIPOLYGON);
959
960 CREATE TABLE named_places (
961 fid INTEGER NOT NULL PRIMARY KEY,
962 name CHARACTER VARYING(64),
963 boundary POLYGON);
964
965 CREATE TABLE map_neatlines (
966 fid INTEGER NOT NULL PRIMARY KEY,
967 neatline POLYGON);
968
969 --echo #
970 --echo # C.3.3.2 Geometry types and functions schema data loading
971 --echo #
972
973 # TODO: WL#2377
974 #-- Spatial Reference System
975 #INSERT INTO mysql.st_spatial_reference_systems( id, catalog_id, name, organization, organization_coordsys_id, definition, description)
976 #VALUES (101, 1, 'TEST101', 'POSC', 32214,
977 #'PROJCS[
978 # "UTM_ZONE_14N",
979 # GEOGCS[
980 # "World Geodetic System 72",
981 # DATUM[
982 # "WGS_72",
983 # SPHEROID["NWL_10D", 6378135, 298.26]
984 # ],
985 # PRIMEM["Greenwich", 0],
986 # UNIT["Degree", 0.017453292519943278]
987 # ],
988 # PROJECTION["Transverse_Mercator"],
989 # PARAMETER["False_Easting", 500000.0],
990 # PARAMETER["False_Northing", 0.0],
991 # PARAMETER["Central_Meridian", -99.0],
992 # PARAMETER["Scale_Factor", 0.9996],
993 # PARAMETER["Latitude_of_origin", 0.0],
994 # UNIT["Meter", 1.0]
995 #]', '');
996
997 --echo # Lakes
998 INSERT INTO lakes VALUES (
999 101, 'BLUE LAKE',
1000 ST_PolyFromText(
1001 'POLYGON(
1002 (52 18,66 23,73 9,48 6,52 18),
1003 (59 18,67 18,67 13,59 13,59 18)
1004 )'));
1005
1006
1007 --echo # Road Segments
1008
1009 INSERT INTO road_segments VALUES(102, 'Route 5', NULL, 2,
1010 ST_LineFromText(
1011 'LINESTRING( 0 18, 10 21, 16 23, 28 26, 44 31 )'));
1012
1013 INSERT INTO road_segments VALUES(103, 'Route 5', 'Main Street', 4,
1014 ST_LineFromText(
1015 'LINESTRING( 44 31, 56 34, 70 38 )'));
1016
1017 INSERT INTO road_segments VALUES(104, 'Route 5', NULL, 2,
1018 ST_LineFromText(
1019 'LINESTRING( 70 38, 72 48 )'));
1020
1021 INSERT INTO road_segments VALUES(105, 'Main Street', NULL, 4,
1022 ST_LineFromText(
1023 'LINESTRING( 70 38, 84 42 )'));
1024
1025 INSERT INTO road_segments VALUES(106, 'Dirt Road by Green Forest', NULL,
1026 1,
1027 ST_LineFromText(
1028 'LINESTRING( 28 26, 28 0 )'));
1029
1030 --echo # DividedRoutes
1031
1032 INSERT INTO divided_routes VALUES(119, 'Route 75', 4,
1033 ST_MLineFromText(
1034 'MULTILINESTRING((10 48,10 21,10 0),
1035 (16 0,16 23,16 48))'));
1036
1037 --echo # Forests
1038
1039 INSERT INTO forests VALUES(109, 'Green Forest',
1040 ST_MPolyFromText(
1041 'MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),
1042 (52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))'));
1043
1044 --echo # Bridges
1045
1046 INSERT INTO bridges VALUES(110, 'Cam Bridge', ST_PointFromText(
1047 'POINT( 44 31 )'));
1048
1049 --echo # Streams
1050
1051 INSERT INTO streams VALUES(111, 'Cam Stream',
1052 ST_LineFromText(
1053 'LINESTRING( 38 48, 44 41, 41 36, 44 31, 52 18 )'));
1054
1055 INSERT INTO streams VALUES(112, NULL,
1056 ST_LineFromText(
1057 'LINESTRING( 76 0, 78 4, 73 9 )'));
1058
1059 --echo # Buildings
1060
1061 INSERT INTO buildings VALUES(113, '123 Main Street',
1062 ST_PointFromText(
1063 'POINT( 52 30 )'),
1064 ST_PolyFromText(
1065 'POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )'));
1066
1067 INSERT INTO buildings VALUES(114, '215 Main Street',
1068 ST_PointFromText(
1069 'POINT( 64 33 )'),
1070 ST_PolyFromText(
1071 'POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) )'));
1072
1073
1074 --echo # Ponds
1075
1076 INSERT INTO ponds VALUES(120, NULL, 'Stock Pond',
1077 ST_MPolyFromText(
1078 'MULTIPOLYGON( ( ( 24 44, 22 42, 24 40, 24 44) ),
1079 ( ( 26 44, 26 40, 28 42, 26 44) ) )'));
1080
1081 --echo # Named Places
1082
1083 INSERT INTO named_places VALUES(117, 'Ashton',
1084 ST_PolyFromText(
1085 'POLYGON( ( 62 48, 84 48, 84 30, 56 30, 56 34, 62 48) )'));
1086
1087 INSERT INTO named_places VALUES(118, 'Goose Island',
1088 ST_PolyFromText(
1089 'POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )'));
1090
1091 --echo # Map Neatlines
1092
1093 INSERT INTO map_neatlines VALUES(115,
1094 ST_PolyFromText(
1095 'POLYGON( ( 0 0, 0 48, 84 48, 84 0, 0 0 ) )'));
1096
1097 --echo #
1098 --echo # C.3.3.3 Geometry types and functions schema test queries
1099 --echo
1100
1101 # TODO: WL#2377
1102 #--echo # Conformance Item T1
1103 #SELECT f_table_name
1104 #FROM geometry_columns;
1105 #
1106 #--echo # Conformance Item T2
1107 #SELECT f_geometry_column
1108 #FROM geometry_columns
1109 #WHERE f_table_name = 'streams';
1110 #
1111 #--echo # Conformance Item T3
1112 #SELECT coord_dimension
1113 #FROM geometry_columns
1114 #WHERE f_table_name = 'streams';
1115 #
1116 #--echo # Conformance Item T4
1117 #
1118 #SELECT ST_srid
1119 #FROM geometry_columns
1120 #WHERE f_table_name = 'streams';
1121 #
1122 #--echo # Conformance Item T5
1123 #
1124 #SELECT srtext
1125 #FROM SPATIAL_REF_SYS
1126 #WHERE ST_SRID = 101;
1127 #
1128
1129
1130 --echo # Conformance Item T6
1131 # TODO: ST_Dimension() alias
1132 SELECT ST_Dimension(shore)
1133 FROM lakes
1134 WHERE name = 'Blue Lake';
1135
1136 --echo # Conformance Item T7
1137 # TODO: ST_GeometryType() alias
1138 SELECT ST_GeometryType(centerlines)
1139 FROM divided_routes
1140 WHERE name = 'Route 75';
1141
1142 --echo # Conformance Item T8
1143 # TODO: ST_AsText() alias
1144 SELECT ST_AsText(boundary)
1145 FROM named_places
1146 WHERE name = 'Goose Island';
1147
1148 --echo # Conformance Item T9
1149 # TODO: ST_AsBinary(), ST_PolyFromWKB() aliases
1150 SELECT ST_AsText(ST_PolyFromWKB(ST_AsBinary(boundary)))
1151 FROM named_places
1152 WHERE name = 'Goose Island';
1153
1154 --echo # Conformance Item T10
1155 # TODO: ST_SRID() alias
1156 SELECT ST_SRID(boundary)
1157 FROM named_places
1158 WHERE name = 'Goose Island';
1159
1160 --echo # Conformance Item T11
1161 # TODO: ST_IsEmpty() alias
1162 SELECT ST_IsEmpty(centerline)
1163 FROM road_segments
1164 WHERE name = 'Route 5'
1165 AND aliases = 'Main Street';
1166
1167 # FIXME: get wrong result:0, expected 1.
1168 #--echo # Conformance Item T12
1169 # TODO: ST_IsSimple() alias
1170 #SELECT ST_IsSimple(shore)
1171 #FROM lakes
1172 #WHERE name = 'Blue Lake';
1173
1174 # TODO: WL#2377
1175 #--echo # Conformance Item T13
1176 #SELECT ST_AsText(Boundary((boundary),101)
1177 #FROM named_places
1178 #WHERE name = 'Goose Island';
1179
1180 --echo # Conformance Item T14
1181 # TODO: ST_Envelope( ) alias
1182 # FIXME: we get anticlockwise, GIS suggests clockwise
1183 SELECT ST_AsText(ST_Envelope(boundary))
1184 FROM named_places
1185 WHERE name = 'Goose Island';
1186
1187 --echo # Conformance Item T15
1188 # TODO: ST_X() alias
1189 SELECT ST_X(position)
1190 FROM bridges
1191 WHERE name = 'Cam Bridge';
1192
1193 --echo # Conformance Item T16
1194 # TODO: ST_Y() alias
1195 SELECT ST_Y(position)
1196 FROM bridges
1197 WHERE name = 'Cam Bridge';
1198
1199 --echo # Conformance Item T17
1200 # TODO: ST_StartPoint() alias
1201 SELECT ST_AsText(ST_StartPoint(centerline))
1202 FROM road_segments
1203 WHERE fid = 102;
1204
1205 --echo # Conformance Item T18
1206 # TODO: ST_EndPoint
1207 SELECT ST_AsText(ST_EndPoint(centerline))
1208 FROM road_segments
1209 WHERE fid = 102;
1210
1211 # TODO: WL#2377
1212 #--echo # Conformance Item T19
1213 # TODO: ST_LineFromWKB() alias
1214 #SELECT ST_IsClosed(LineFromWKB(ST_AsBinary(Boundary(boundary)),ST_SRID(boundary)))
1215 #FROM named_places
1216 #WHERE name = 'Goose Island';
1217
1218 # TODO: WL#2377
1219 #--echo # Conformance Item T20
1220 #SELECT IsRing(LineFromWKB(ST_AsBinary(Boundary(boundary)),ST_SRID(boundary)))
1221 #FROM named_places
1222 #WHERE name = 'Goose Island';
1223
1224 --echo # Conformance Item T21
1225 # TODO: ST_Length() alias
1226 SELECT ST_Length(centerline)
1227 FROM road_segments
1228 WHERE fid = 106;
1229
1230 --echo # Conformance Item T22
1231 # TODO: ST_NumPoints() alias
1232 SELECT ST_NumPoints(centerline)
1233 FROM road_segments
1234 WHERE fid = 102;
1235
1236 --echo # Conformance Item T23
1237 # TODO: ST_PointN() alias
1238 SELECT ST_AsText(ST_PointN(centerline, 1))
1239 FROM road_segments
1240 WHERE fid = 102;
1241
1242 --echo # Conformance Item T24
1243 # TODO: ST_Centroid() alias
1244 SELECT ST_AsText(ST_Centroid(boundary))
1245 FROM named_places
1246 WHERE name = 'Goose Island';
1247
1248 # TODO: WL#2377
1249 #--echo # Conformance Item T25
1250 #SELECT MBRContains(boundary, PointOnSurface(boundary))
1251 #FROM named_places
1252 #WHERE name = 'Goose Island';
1253
1254 --echo # Conformance Item T26
1255 # TODO: ST_Area() alias
1256 SELECT ST_Area(boundary)
1257 FROM named_places
1258 WHERE name = 'Goose Island';
1259
1260 --echo # Conformance Item T27
1261 # TODO: ST_ExteriorRing() alias
1262 SELECT ST_AsText(ST_ExteriorRing(shore))
1263 FROM lakes
1264 WHERE name = 'Blue Lake';
1265
1266 --echo # Conformance Item T28
1267 # TODO: ST_NumInteriorRings() alias
1268 SELECT ST_NumInteriorRings(shore)
1269 FROM lakes
1270 WHERE name = 'Blue Lake';
1271
1272 --echo # Conformance Item T29
1273 # TODO: ST_InteriorRingN() alias
1274 SELECT ST_AsText(ST_InteriorRingN(shore, 1))
1275 FROM lakes
1276 WHERE name = 'Blue Lake';
1277
1278 --echo # Conformance Item T30
1279 # TODO: ST_NumGeometries() alias
1280 SELECT ST_NumGeometries(centerlines)
1281 FROM divided_routes
1282 WHERE name = 'Route 75';
1283
1284 --echo # Conformance Item T31
1285 # TODO: ST_GeometryN() alias
1286 SELECT ST_AsText(ST_GeometryN(centerlines, 2))
1287 FROM divided_routes
1288 WHERE name = 'Route 75';
1289
1290 --echo # Conformance Item T32
1291 # TODO: ST_IsClosed() alias
1292 SELECT ST_IsClosed(centerlines)
1293 FROM divided_routes
1294 WHERE name = 'Route 75';
1295
1296 --echo # Conformance Item T33
1297 # TODO: ST_Length() alias
1298 SELECT ST_Length(centerlines)
1299 FROM divided_routes
1300 WHERE name = 'Route 75';
1301
1302 --echo # Conformance Item T34
1303 # TODO: ST_Centroid() alias
1304 SELECT ST_AsText(ST_Centroid(shores))
1305 FROM ponds
1306 WHERE fid = 120;
1307
1308 # TODO: WL#2377
1309 #--echo # Conformance Item T35
1310 #SELECT MBRContains(shores, PointOnSurface(shores))
1311 #FROM ponds
1312 #WHERE fid = 120;
1313
1314 --echo # Conformance Item T36
1315 # TODO: ST_Area() alias
1316 SELECT ST_Area(shores)
1317 FROM ponds
1318 WHERE fid = 120;
1319
1320 --echo # Conformance Item T37
1321 # TODO: ST_PolyFromText() alias
1322 --error ER_SRS_NOT_FOUND
1323 SELECT ST_Equals(boundary,
1324 ST_PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',101))
1325 FROM named_places
1326 WHERE name = 'Goose Island';
1327
1328 --echo # Conformance Item T38
1329 SELECT ST_Disjoint(centerlines, boundary)
1330 FROM divided_routes, named_places
1331 WHERE divided_routes.name = 'Route 75'
1332 AND named_places.name = 'Ashton';
1333
1334 --echo # Conformance Item T39
1335 SELECT ST_Touches(centerline, shore)
1336 FROM streams, lakes
1337 WHERE streams.name = 'Cam Stream'
1338 AND lakes.name = 'Blue Lake';
1339
1340 # FIXME: wrong result: get 0, expected 1
1341 #--echo # Conformance Item T40
1342 #SELECT ST_Within(boundary, footprint)
1343 #FROM named_places, buildings
1344 #WHERE named_places.name = 'Ashton'
1345 #AND buildings.address = '215 Main Street';
1346
1347 # FIXME: wrong result: get 0, expected 1
1348 #--echo # Conformance Item T41
1349 #SELECT ST_Overlaps(forests.boundary, named_places.boundary)
1350 #FROM forests, named_places
1351 #WHERE forests.name = 'Green Forest'
1352 #AND named_places.name = 'Ashton';
1353
1354 --echo # Conformance Item T42
1355 # FIXME: TODO: ST_Crosses() alias
1356 SELECT ST_Crosses(road_segments.centerline, divided_routes.centerlines)
1357 FROM road_segments, divided_routes
1358 WHERE road_segments.fid = 102
1359 AND divided_routes.name = 'Route 75';
1360
1361 --echo # Conformance Item T43
1362 SELECT ST_Intersects(road_segments.centerline, divided_routes.centerlines)
1363 FROM road_segments, divided_routes
1364 WHERE road_segments.fid = 102
1365 AND divided_routes.name = 'Route 75';
1366
1367 --echo # Conformance Item T44
1368 SELECT ST_Contains(forests.boundary, named_places.boundary)
1369 FROM forests, named_places
1370 WHERE forests.name = 'Green Forest'
1371 AND named_places.name = 'Ashton';
1372
1373 # TODO: WL#2377
1374 #--echo # Conformance Item T45
1375 #SELECT Relate(forests.boundary, named_places.boundary, 'TTTTTTTTT')
1376 #FROM forests, named_places
1377 #WHERE forests.name = 'Green Forest'
1378 #AND named_places.name = 'Ashton';
1379
1380 --echo # Conformance Item T46
1381 SELECT ST_Distance(position, boundary)
1382 FROM bridges, named_places
1383 WHERE bridges.name = 'Cam Bridge'
1384 AND named_places.name = 'Ashton';
1385
1386 # FIXME: wrong result: NULL, expected 12
1387 #--echo # Conformance Item T47
1388 #SELECT ST_AsText(ST_Intersection(centerline, shore))
1389 #FROM streams, lakes
1390 #WHERE streams.name = 'Cam Stream'
1391 #AND lakes.name = 'Blue Lake';
1392
1393 --echo # Conformance Item T48
1394 SELECT ST_AsText(ST_Difference(named_places.boundary, forests.boundary))
1395 FROM named_places, forests
1396 WHERE named_places.name = 'Ashton'
1397 AND forests.name = 'Green Forest';
1398
1399 #--echo # Conformance Item T49
1400 SELECT ST_AsText(ST_Union(shore, boundary))
1401 FROM lakes, named_places
1402 WHERE lakes.name = 'Blue Lake'
1403 AND named_places.name = 'Goose Island';
1404
1405 --echo # Conformance Item T50
1406 SELECT ST_AsText(ST_SymDifference(shore, boundary))
1407 FROM lakes, named_places
1408 WHERE lakes.name = 'Blue Lake'
1409 AND named_places.name = 'Ashton';
1410
1411 --echo # Conformance Item T51
1412 SELECT count(*)
1413 FROM buildings, bridges
1414 WHERE ST_Contains(ST_Buffer(bridges.position, 15.0), buildings.footprint) = 1;
1415
1416 # TODO: WL#2377
1417 #--echo # Conformance Item T52
1418 #SELECT ST_AsText(ConvexHull(shore))
1419 #FROM lakes
1420 #WHERE lakes.name = 'Blue Lake';
1421
1422 DROP DATABASE gis_ogs;
1423
1424 --echo #
1425 --echo # Bug#13362660 ASSERTION `FIELD_POS < FIELD_COUNT' FAILED. IN PROTOCOL_TEXT::STORE
1426 --echo #
1427
1428 --error ER_GIS_INVALID_DATA
1429 SELECT ST_Union('', ''), md5(1);