Debian Codegraph @

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

raw
1 ###############################################################################
2 # #
3 # This test is aimed to focus on the properties and attributes of the #
4 # Mutator function ST_SWAPXY. #
5 # #
6 # #
7 # Creation Date: 2016-08-16 #
8 # Author : Jens Even Berg Blomsoy #
9 # #
10 ###############################################################################
11
12
13 ###############################################################################
14 # Creating the spatial objects #
15 ###############################################################################
16
17 --echo # Creating the spatial objects
18
19 CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT);
20 CREATE TABLE gis_linestring (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING);
21 CREATE TABLE gis_polygon (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON);
22 CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT);
23 CREATE TABLE gis_multi_linestring (fid INTEGER NOT NULL PRIMARY KEY,
24 g MULTILINESTRING);
25 CREATE TABLE gis_multi_polygon (fid INTEGER NOT NULL PRIMARY KEY,
26 g MULTIPOLYGON);
27 CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY,
28 g GEOMETRYCOLLECTION);
29
30
31 ###############################################################################
32 # Inserting POINT Geometry values #
33 ###############################################################################
34
35 --echo # Inserting POINT Geometry Values
36
37 INSERT INTO gis_point VALUES
38 (101, POINT(1.1111,-2.2222)),
39 (102, POINT(10.0000,20.0000));
40
41 INSERT INTO gis_point VALUES
42 (103, POINT(1e308, 1e202));
43
44 --echo # Displaying the inserted POINT Geometry Data
45 SELECT fid, ST_ASTEXT(g) FROM gis_point;
46
47 ###############################################################################
48 # Inserting LINESTRING Geometry values #
49 ###############################################################################
50
51 --echo # Inserting LINESTRING Geometry Values
52
53 INSERT INTO gis_linestring VALUES
54 (201, ST_LINEFROMTEXT('LINESTRING(0 2,2 4,4 8)'));
55
56 INSERT INTO gis_linestring VALUES
57 (202, ST_LINESTRINGFROMTEXT('LINESTRING(0 12,12 24,24 48)'));
58
59 INSERT INTO gis_linestring VALUES
60 (203, LINESTRING(POINT(0,0), POINT(-5,7),
61 POINT(-10,70)));
62
63 --echo # Displaying the inserted LINESTRING Geometry Data
64 SELECT fid, ST_ASTEXT(g) FROM gis_linestring;
65
66 ###############################################################################
67 # Inserting POLYGON Geometry values #
68 ###############################################################################
69
70 --echo # Inserting POLYGON Geometry Values
71
72 INSERT INTO gis_polygon VALUES
73 (301, ST_POLYFROMTEXT('POLYGON((0 0,5 0,5 5, 0 0))'));
74
75 INSERT INTO gis_polygon VALUES
76 (302, ST_POLYGONFROMTEXT('POLYGON((0 0,10 0,10 10,0 10,0 0),
77 (2 2,2 4,4 4,4 2,2 2),(6 6,6 8,8 8,8 6,6 6))'));
78
79 INSERT INTO gis_polygon VALUES
80 (303, POLYGON(LINESTRING(POINT(0,0), POINT(10,0),
81 POINT(10,10), POINT(0,10), POINT(0,0)), LINESTRING(POINT(4,4), POINT(4,6),
82 POINT(6,6), POINT(6,4), POINT(4,4))));
83
84 --echo # Displaying the inserted POLYGON Geometry Data
85 SELECT fid, ST_ASTEXT(g) FROM gis_polygon;
86
87 ###############################################################################
88 # Inserting MULTIPOINT Geometry values #
89 ###############################################################################
90
91 --echo # Inserting MULTIPOINT Geometry Values
92
93 INSERT INTO gis_multi_point VALUES
94 (401, ST_MPOINTFROMTEXT('MULTIPOINT(0 0,5 5,10 10)'));
95
96 INSERT INTO gis_multi_point VALUES
97 (402, ST_MULTIPOINTFROMTEXT('MULTIPOINT(1e308 1e308,1e308 -1e308,-1e308
98 1e308,-1e308 -1e308)'));
99
100 INSERT INTO gis_multi_point VALUES
101 (403, ST_MPOINTFROMTEXT('MULTIPOINT(1e308
102 1e308, 1e308 -1e308, -1e308 1e308, -1e308 -1e308, 1e308 1e308)'));
103
104 --echo # Displaying the inserted MULTIPOINT Geometry Data
105 SELECT fid, ST_ASTEXT(g) FROM gis_multi_point;
106
107 ###############################################################################
108 # Inserting MULTILINESTRING Geometry values #
109 ###############################################################################
110
111 --echo # Inserting MULTILINESTRING Geometry Values
112
113 INSERT INTO gis_multi_linestring VALUES
114 (501, ST_MLINEFROMTEXT('MULTILINESTRING((0 0,2 2,4 4),(6 6,8 8,10 10))'));
115
116 INSERT INTO gis_multi_linestring VALUES
117 (502, MULTILINESTRING(LINESTRING(POINT(0,0),
118 POINT(12,12), POINT(24,24)),
119 LINESTRING(POINT(36,36), POINT(48,48), POINT(50,50))));
120
121 INSERT INTO gis_multi_linestring VALUES
122 (503, ST_MULTILINESTRINGFROMTEXT
123 ('MULTILINESTRING((0 0,0 100),(0 0,100 0),(0 0,0 -100),(0 0,-100 0))'));
124
125 --echo # Displaying the inserted MULTILINESTRING Geometry Data
126 SELECT fid, ST_ASTEXT(g) FROM gis_multi_linestring;
127
128 ###############################################################################
129 # Inserting MULTIPOLYGON Geometry values #
130 ###############################################################################
131
132 --echo # Inserting MULTIPOLGYON Geometry Values
133
134 INSERT INTO gis_multi_polygon VALUES
135 (601, MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0),
136 POINT(5,0), POINT(5,5), POINT(0,5), POINT(0,0))))),
137 (602, MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0),
138 POINT(10, 0), POINT(10,10), POINT(10, 0), POINT(0,0)),
139 LINESTRING(POINT(4,4),
140 POINT(4,6), POINT(6,6),
141 POINT(6,4), POINT(4,4))))),
142 (603, MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0),
143 POINT(5,0), POINT(5,5), POINT(0,5), POINT(0,0))),
144 POLYGON(LINESTRING(POINT(0,0),
145 POINT(-5, 0), POINT(-5,-5), POINT(0 ,-5),
146 POINT(0,0)))));
147
148 --echo # Displaying the inserted MULTIPOLYGON Geometry Data
149 SELECT fid, ST_ASTEXT(g) FROM gis_multi_polygon;
150
151 ###############################################################################
152 # Inserting GEOMETRYCOLLECTION Geometry values #
153 ###############################################################################
154
155 INSERT INTO gis_geometrycollection VALUES
156 (701, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))')),
157 (702, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),
158 LINESTRING(0 0,10 10))')),
159 (703, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(5 5),
160 POLYGON((0 0,10 0,10 10,0 10,0 0)))'));
161
162 --echo # Displaying the inserted GEOMETRYCOLLECTION Geometry Data
163 SELECT fid, ST_ASTEXT(g) FROM gis_geometrycollection;
164
165 ###############################################################################
166 # Geometry call to ST_SWAPXY() #
167 ###############################################################################
168
169 --echo #=======================================================================
170 --echo # ST_SWAPXY(point)
171 --echo #=======================================================================
172
173 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_point WHERE fid = 101;
174 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_point WHERE fid = 102;
175 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_point WHERE fid = 103;
176
177 SELECT ST_ASTEXT(ST_SWAPXY(ST_POINTFROMTEXT(
178 'POINT(1.1111 -2.2222)')));
179 SELECT ST_ASTEXT(ST_SWAPXY(ST_POINTFROMTEXT(
180 'POINT(10.0000 20.0000)')));
181 SELECT ST_ASTEXT(ST_SWAPXY(ST_POINTFROMTEXT(
182 'POINT(1e308 1e202)')));
183
184
185 --echo #=======================================================================
186 --echo # ST_SWAPXY(linestring)
187 --echo #=======================================================================
188
189 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_linestring WHERE fid = 201;
190 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_linestring WHERE fid = 202;
191 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_linestring WHERE fid = 203;
192
193 SELECT ST_ASTEXT(ST_SWAPXY(ST_LINEFROMTEXT('LINESTRING(0 2,2 4,4 8)')));
194 SELECT ST_ASTEXT(ST_SWAPXY(ST_LINESTRINGFROMTEXT('LINESTRING(0 12,12 24,24 48)')));
195 SELECT ST_ASTEXT(ST_SWAPXY(LINESTRING(POINT(0,2), POINT(-5,7),
196 POINT(-10,70))));
197
198
199 --echo #=======================================================================
200 --echo # ST_SWAPXY(polygon)
201 --echo #=======================================================================
202
203 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_polygon WHERE fid = 301;
204 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_polygon WHERE fid = 302;
205 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_polygon WHERE fid = 303;
206
207 SELECT ST_ASTEXT(ST_SWAPXY(ST_POLYFROMTEXT('POLYGON((0 0,5 0,5 5, 0 0))')));
208 SELECT ST_ASTEXT(ST_SWAPXY(ST_POLYGONFROMTEXT('POLYGON((0 0,10 0,10 10,0 10,0 0),
209 (2 2,2 4,4 4,4 2,2 2),(6 6,6 8,8 8,8 6,6 6))')));
210 SELECT ST_ASTEXT(ST_SWAPXY(POLYGON(LINESTRING(POINT(0,0), POINT(10,0),
211 POINT(10,10), POINT(0,10), POINT(0,0)), LINESTRING(POINT(4,4), POINT(4,6),
212 POINT(6,6), POINT(6,4), POINT(4,4)))));
213
214 --echo #=======================================================================
215 --echo # ST_SWAPXY(multipoint)
216 --echo #=======================================================================
217
218 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_point WHERE fid = 401;
219 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_point WHERE fid = 402;
220 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_point WHERE fid = 403;
221
222 SELECT ST_ASTEXT(ST_SWAPXY(ST_MPOINTFROMTEXT('MULTIPOINT(0 5,5 10,10 20)')));
223 SELECT ST_ASTEXT(ST_SWAPXY(ST_MPOINTFROMTEXT('MULTIPOINT(
224 1e308 1e208,1e308 -1e208,-1e308 1e208,-1e308 -1e208)')));
225 SELECT ST_ASTEXT(ST_SWAPXY(ST_MPOINTFROMTEXT(
226 'MULTIPOINT(1e308 1e308,1e308 -1e308,-1e308 1e308,
227 -1e308 -1e308,1e308 1e308)')));
228
229
230 --echo #=======================================================================
231 --echo # ST_SWAPXY(multilinestring)
232 --echo #=======================================================================
233
234 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_linestring WHERE fid = 501;
235 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_linestring WHERE fid = 502;
236 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_linestring WHERE fid = 503;
237
238 SELECT ST_ASTEXT(ST_SWAPXY(ST_MLINEFROMTEXT('MULTILINESTRING((0 0,2 2,4 4),
239 (6 6,8 8,10 10))')));
240
241 SELECT ST_ASTEXT(ST_SWAPXY(MULTILINESTRING(
242 LINESTRING(POINT(0,0), POINT(12,12), POINT(24,24)), LINESTRING(POINT(36,36),
243 POINT(48,48), POINT(50,50)))));
244
245 SELECT ST_ASTEXT(ST_SWAPXY(
246 ST_MULTILINESTRINGFROMTEXT('MULTILINESTRING((0 0,0 100),(0 0,100 0),
247 (0 0,0 -100),(0 0,-100 0))')));
248
249
250 --echo #=======================================================================
251 --echo # ST_SWAPXY(multipolygon)
252 --echo #=======================================================================
253
254 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_polygon WHERE fid = 601;
255 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_polygon WHERE fid = 602;
256 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_polygon WHERE fid = 603;
257
258 SELECT ST_ASTEXT(ST_SWAPXY(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0),
259 POINT(5,0), POINT(5,5), POINT(0,5), POINT(0,0))))));
260
261 SELECT ST_ASTEXT(ST_SWAPXY(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0),
262 POINT(10,0), POINT(10,10), POINT(0,10), POINT(0,0)), LINESTRING(POINT(4,4),
263 POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))))));
264
265 SELECT ST_ASTEXT(ST_SWAPXY(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0),
266 POINT(5,0), POINT(5,5), POINT(0,5), POINT(0,0))),
267 POLYGON(LINESTRING(POINT(0,0), POINT(-5, 0), POINT(-5,-5), POINT(0, -5),
268 POINT(0,0))))));
269
270
271 --echo #=======================================================================
272 --echo # ST_SWAPXY(geometrycollection)
273 --echo #=======================================================================
274
275 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_geometrycollection WHERE fid = 701;
276 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_geometrycollection WHERE fid = 702;
277 SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_geometrycollection WHERE fid = 703;
278
279 SELECT ST_ASTEXT(ST_SWAPXY(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))')));
280 SELECT ST_ASTEXT(ST_SWAPXY(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),
281 LINESTRING(0 0,10 10))')));
282 SELECT ST_ASTEXT(ST_SWAPXY(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(5 5),
283 POLYGON((0 0,10 0,10 10,0 10,0 0)))')));
284
285 ########################################
286 # Null value testing #
287 ########################################
288
289 SELECT ST_ASTEXT(ST_SWAPXY(NULL));
290
291 ###############################################################################
292 # Invalid function calls #
293 ###############################################################################
294
295 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
296 SELECT ST_ASTEXT(ST_SWAPXY());
297
298 --error ER_GIS_INVALID_DATA
299 SELECT ST_SWAPXY('foobar');
300
301 # Point without coordinates
302 --error ER_GIS_INVALID_DATA
303 SELECT ST_SWAPXY(x'0101000000');
304
305 # Linestring with zero points
306 --error ER_GIS_INVALID_DATA
307 SELECT ST_SWAPXY(x'010200000000000000');
308
309 --echo # Final cleanup
310 DROP TABLE gis_point;
311 DROP TABLE gis_linestring;
312 DROP TABLE gis_polygon;
313 DROP TABLE gis_multi_point;
314 DROP TABLE gis_multi_linestring;
315 DROP TABLE gis_multi_polygon;
316 DROP TABLE gis_geometrycollection;
317
318 --echo #
319 --echo # WL#11096 Don't do Cartesian computations on geographic geometries
320 --echo #
321
322 --error ER_SRS_NOT_FOUND
323 DO ST_SWAPXY(x'0A000000010100000000000000000000000000000000000000');