Debian Codegraph @

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

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