Debian Codegraph @

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

raw
1 ###############################################################################
2 # #
3 # This test is aimed at testing the geometry mutator functionality #
4 # following Geometry classes. #
5 # #
6 # Creation Date: 2016-05-07 #
7 # Author : Hans Hellzen Melby #
8 # (Although much was taken from Pavan's geometry_property_functions test file #
9 # and thus altered to fit the use case) #
10 # #
11 ###############################################################################
12
13 --echo # Creating the spatial objects
14
15 CREATE TABLE gis_point_srid (fid INTEGER NOT NULL PRIMARY KEY, g POINT);
16 CREATE TABLE gis_linestring_srid (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING);
17 CREATE TABLE gis_polygon_srid (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON);
18 CREATE TABLE gis_multi_point_srid (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT);
19 CREATE TABLE gis_multi_linestring_srid (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING);
20 CREATE TABLE gis_multi_polygon_srid (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON);
21 CREATE TABLE gis_geometrycollection_srid (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION);
22
23 #######################################
24 # Inserting POINT Geometry values
25 #######################################
26
27 --echo # Inserting POINT Geometry Values
28
29 INSERT INTO gis_point_srid VALUES
30 (101, ST_POINTFROMTEXT('POINT(0 0)')),
31 (102, ST_POINTFROMTEXT('POINT(1 0)')),
32 (103, ST_POINTFROMTEXT('POINT(0 1)')),
33 (104, ST_POINTFROMTEXT('POINT(1 1)')),
34 (105, ST_POINTFROMTEXT('POINT(-1 1)'));
35
36 INSERT INTO gis_point_srid VALUES
37 (106, ST_POINTFROMWKB(ST_ASWKB(POINT(0,0)))),
38 (107, ST_POINTFROMWKB(ST_ASWKB(POINT(10,0)))),
39 (108, ST_POINTFROMWKB(ST_ASWKB(POINT(0,10)))),
40 (109, ST_POINTFROMWKB(ST_ASWKB(POINT(-10,0)))),
41 (110, ST_POINTFROMWKB(ST_ASWKB(POINT(0,-10))));
42
43 INSERT INTO gis_point_srid VALUES
44 (111, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(1 1)'))));
45
46
47 --echo # Retrieving table of POINT geometries and SRID values
48 --echo # (the latter should be 0 by default)
49 SELECT ST_ASTEXT(g), ST_SRID(g) FROM gis_point_srid;
50 --echo # Update all fields of the table to have the same SRID
51 UPDATE gis_point_srid SET g=ST_SRID(g, 4326);
52 --echo # Check that SRIDs are now updated, and check that ST_SRID(g)
53 --echo # returns a geometry in the second column
54 SELECT ST_ASTEXT(g), ST_SRID(g) FROM gis_point_srid;
55
56 ###############################################
57 # Inserting LINESTRING Geometry values
58 ###############################################
59
60 --echo # Inserting LINESTRING Geometry Values
61 INSERT INTO gis_linestring_srid VALUES
62 (201, ST_LINEFROMTEXT('LINESTRING(0 0,5 5)')),
63 (202, ST_LINEFROMTEXT('LINESTRING(0 0,2 2,4 4)')),
64 (203, ST_LINEFROMTEXT('LINESTRING(0 0,5 5,10 10)'));
65
66 INSERT INTO gis_linestring_srid VALUES
67 (204, ST_LINESTRINGFROMTEXT('LINESTRING(10 10,5 5)')),
68 (205, ST_LINESTRINGFROMTEXT('LINESTRING(0 0,12 12,24 24)')),
69 (206, ST_LINESTRINGFROMTEXT('LINESTRING(0 0,50 50,90 90)'));
70
71 INSERT INTO gis_linestring_srid VALUES
72 (207, ST_LINEFROMWKB(ST_ASWKB(LINESTRING(POINT(0,0), POINT(5,5))))),
73 (208, ST_LINEFROMWKB(ST_ASWKB(LINESTRING(POINT(0,0), POINT(-5,-5), POINT(-10,10))))),
74 (209, ST_LINEFROMWKB(ST_ASWKB(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6), POINT(8,8)))));
75
76 INSERT INTO gis_linestring_srid VALUES
77 (210, ST_LINESTRINGFROMWKB(ST_ASWKB(LINESTRING(POINT(0,0), POINT(5,5))))),
78 (211, ST_LINESTRINGFROMWKB(ST_ASWKB(LINESTRING(POINT(0,0), POINT(-50,-50), POINT(10,-10)))));
79
80 --echo # Retrieving table of LINESTRING geometries and SRID values (the latter should be 0 by default)
81 SELECT ST_ASTEXT(g), ST_SRID(g) FROM gis_linestring_srid;
82 --echo # Update all fields of the table to have the same SRID
83 UPDATE gis_linestring_srid SET g=ST_SRID(g, 4326);
84 --echo # Check that SRIDs are now updated, and check that ST_SRID(g)
85 --echo # returns a geometry in the second column
86 SELECT ST_ASTEXT(g), ST_SRID(g) FROM gis_linestring_srid;
87
88 ##############################################
89 # Inserting POLYGON Geometry values
90 ##############################################
91
92 --echo # Inserting POLYGON Geometry Values
93
94 INSERT INTO gis_polygon_srid VALUES
95 (301, ST_POLYFROMTEXT('POLYGON((0 0,5 5,0 5, 0 0))')),
96 (302, ST_POLYFROMTEXT('POLYGON((0 0,5 0,5 5,0 5,0 0))')),
97 (303, ST_POLYFROMTEXT('POLYGON((0 0,10 0,10 10,0 10,0 0))'));
98
99 INSERT INTO gis_polygon_srid VALUES
100 (304, ST_POLYGONFROMTEXT('POLYGON((0 0,50 0,50 50,0 50,0 0))')),
101 (305, ST_POLYGONFROMTEXT('POLYGON((0 0,10 0,10 10,0 10,0 0),(4 4,4 6,6 6,6 4,4 4))')),
102 (306, ST_POLYGONFROMTEXT('POLYGON((0 0,10 0,10 10,0 10,0 0),(2 2,2 4,4 4,4 2,2 2),(6 6,6 8,8 8,8 6,6 6))'));
103
104 INSERT INTO gis_polygon_srid VALUES
105 (307, ST_POLYFROMWKB(ST_ASWKB(POLYGON(LINESTRING(POINT(0,0), POINT(5,5), POINT(0,5), POINT(0,0)))))),
106 (308, ST_POLYFROMWKB(ST_ASWKB(POLYGON(LINESTRING(POINT(0,0), POINT(15,0), POINT(15,15), POINT(0,15), POINT(0,0)))))),
107 (309, ST_POLYFROMWKB(ST_ASWKB(POLYGON(LINESTRING(POINT(0,0), POINT(10,0), POINT(10,10), POINT(0,10), POINT(0,0)),
108 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))))));
109
110 INSERT INTO gis_polygon_srid VALUES
111 (310, ST_POLYGONFROMWKB(ST_ASWKB(POLYGON(LINESTRING(POINT(0,0), POINT(5,5), POINT(0,5), POINT(0,0)))))),
112 (311, ST_POLYGONFROMWKB(ST_ASWKB(POLYGON(LINESTRING(POINT(10,10), POINT(15,10), POINT(15,15), POINT(10,15), POINT(10,10)))))),
113 (312, ST_POLYGONFROMWKB(ST_ASWKB(POLYGON(LINESTRING(POINT(10,10), POINT(20,10), POINT(20,20), POINT(10,20), POINT(10,10)),
114 LINESTRING(POINT(14,14), POINT(14,16), POINT(16,16), POINT(16,14), POINT(14,14))))));
115
116 INSERT INTO gis_polygon_srid VALUES
117 (313, ST_POLYFROMWKB(ST_ASWKB(ST_POLYFROMTEXT('POLYGON((10 0,10 10,0 10,-10 10,-10 0,-10 -10,0 9,10 -10,10 0))')))),
118 (314, ST_POLYFROMWKB(ST_ASWKB(ST_POLYGONFROMTEXT('POLYGON((10 0,10 10,0 10,-10 10,-10 0,-10 -10,0 10,10 -10,10 0))')))),
119 (315, ST_POLYGONFROMWKB(ST_ASWKB(ST_POLYGONFROMTEXT('POLYGON((0 0,10 0,10 10,0 10,0 0),(2 2,4 2,4 4,2 4,2 2),(6 6,8 6,8 8,6 8,6 6))'))));
120
121 --echo # Retrieving table of POLYGON geometries and SRID values
122 --echo # (the latter should be 0 by default)
123 SELECT ST_ASTEXT(g), ST_SRID(g) FROM gis_polygon_srid;
124 --echo # Update all fields of the table to have the same SRID
125 UPDATE gis_polygon_srid SET g=ST_SRID(g, 4326);
126 --echo # Check that SRIDs are now updated, and check that ST_SRID(g)
127 --echo # returns a geometry in the second column
128 SELECT ST_ASTEXT(g), ST_SRID(g) FROM gis_polygon_srid;
129
130 #####################################################################
131 # Inserting MULTIPOINT Geometry values
132 #####################################################################
133
134 --echo # Inserting MULTIPOINT Geometry Values
135
136 INSERT INTO gis_multi_point_srid VALUES
137 (401, ST_MPOINTFROMTEXT('MULTIPOINT((0 0))')),
138 (402, ST_MPOINTFROMTEXT('MULTIPOINT((0 0),(2 2),(4 4))')),
139 (403, ST_MPOINTFROMTEXT('MULTIPOINT((0 0),(5 5),(10 10))'));
140
141 INSERT INTO gis_multi_point_srid VALUES
142 (407, ST_MPOINTFROMWKB(ST_ASWKB(MULTIPOINT(POINT(0,0))))),
143 (408, ST_MPOINTFROMWKB(ST_ASWKB(MULTIPOINT(POINT(0,0), POINT(10,10))))),
144 (409, ST_MPOINTFROMWKB(ST_ASWKB(MULTIPOINT(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6)))));
145
146 --echo # Retrieving table of MULTIPOINT geometries and SRID values
147 --echo # (the latter should be 0 by default)
148 SELECT ST_ASTEXT(g), ST_SRID(g) FROM gis_multi_point_srid;
149 --echo # Update all fields of the table to have the same SRID
150 UPDATE gis_multi_point_srid SET g=ST_SRID(g, 4326);
151 --echo # Check that SRIDs are now updated, and check that ST_SRID(g) returns
152 --echo # a geometry in the second column
153 SELECT ST_ASTEXT(g), ST_SRID(g) FROM gis_multi_point_srid;
154
155 #################################################
156 # Inserting MULTILINESTRING Geometry values
157 #################################################
158
159 --echo # Inserting MULTILINESTRING Geometry Values
160 INSERT INTO gis_multi_linestring_srid VALUES
161 (501, ST_MLINEFROMTEXT('MULTILINESTRING((0 0,2 2))')),
162 (502, ST_MLINEFROMTEXT('MULTILINESTRING((0 0,2 2,4 4))')),
163 (503, ST_MLINEFROMTEXT('MULTILINESTRING((0 0,2 2,4 4),(6 6,8 8,10 10))'));
164
165 INSERT INTO gis_multi_linestring_srid VALUES
166 (504, ST_MULTILINESTRINGFROMTEXT('MULTILINESTRING((0 0,90 90,-90 -90))'));
167
168 INSERT INTO gis_multi_linestring_srid VALUES
169 (507, ST_MLINEFROMWKB(ST_ASWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2)))))),
170 (508, ST_MLINEFROMWKB(ST_ASWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(12,12), POINT(24,24)))))),
171 (509, ST_MLINEFROMWKB(ST_ASWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4)),
172 LINESTRING(POINT(6,6), POINT(8,8), POINT(10,10))))));
173
174 INSERT INTO gis_multi_linestring_srid VALUES
175 (510, ST_MULTILINESTRINGFROMWKB(ST_ASWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4)))))),
176 (511, ST_MULTILINESTRINGFROMWKB(ST_ASWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(1,1), POINT(2,2)))))),
177 (512, ST_MULTILINESTRINGFROMWKB(ST_ASWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(12,12), POINT(24,24)),
178 LINESTRING(POINT(36,36), POINT(48,48), POINT(50,50))))));
179
180 INSERT INTO gis_multi_linestring_srid VALUES
181 (513, ST_MLINEFROMWKB(ST_ASWKB(ST_MLINEFROMTEXT('MULTILINESTRING((0 0,10 10),(0 10,10 0))')))),
182 (514, ST_MLINEFROMWKB(ST_ASWKB(ST_MULTILINESTRINGFROMTEXT('MULTILINESTRING((0 0,10 10,-10 10,0 0),(0 0,-10 -10,10 -10,0 0))')))),
183 (515, ST_MULTILINESTRINGFROMWKB(ST_ASWKB(ST_MULTILINESTRINGFROMTEXT('MULTILINESTRING((0 0,0 90),(0 0,90 0),(0 0,0 -90),(0 0,-90 0))'))));
184
185 --echo # Retrieving table of MULTILINESTRING geometries and SRID values
186 --echo # (the latter should be 0 by default)
187 SELECT ST_ASTEXT(g), ST_SRID(g) FROM gis_multi_linestring_srid;
188 --echo # Update all fields of the table to have the same SRID
189 UPDATE gis_multi_linestring_srid SET g=ST_SRID(g, 4326);
190 --echo # Check that SRIDs are now updated
191 SELECT ST_ASTEXT(g), ST_SRID(g) FROM gis_multi_linestring_srid;
192
193
194 ###############################################
195 # Inserting MULTIPOLYGON Geometry values
196 ###############################################
197
198 --echo # Inserting MULTIPOLGYON Geometry Values
199
200 INSERT INTO gis_multi_polygon_srid VALUES
201 (601, ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,5 5,0 5,0 0)))')),
202 (602, ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,0 0)),((5 5,5 10,10 10,5 5)))')),
203 (603, ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0),(4 4,6 4,6 6,4 6,4 4)))'));
204
205 INSERT INTO gis_multi_polygon_srid VALUES
206 (604, ST_MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((0 0,5 5,0 5, 0 0)))')),
207 (605, ST_MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0),(4 4,6 4,6 6,4 6, 4 4)),((0 0,-2 -2,0 -2, 0 0)))')),
208 (606, ST_MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((0 0,5 5,-5 5,0 0)),((0 0,-5 -5,5 -5,0 0)))'));
209
210 INSERT INTO gis_multi_polygon_srid VALUES
211 (607, ST_MPOLYFROMWKB(ST_ASWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(0,5), POINT(5,5), POINT(5,0), POINT(0,0))))))),
212 (608, ST_MPOLYFROMWKB(ST_ASWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(10,0), POINT(10,10), POINT(0,10), POINT(0,0)),
213 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))))))),
214 (609, ST_MPOLYFROMWKB(ST_ASWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(5,0), POINT(5,5), POINT(0,5), POINT(0,0))),
215 POLYGON(LINESTRING(POINT(0,0), POINT(-5,0), POINT(-5,-5), POINT(0,-5), POINT(0,0)))))));
216
217 INSERT INTO gis_multi_polygon_srid VALUES
218 (610, ST_MULTIPOLYGONFROMWKB(ST_ASWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(-5,0), POINT(-5,-5), POINT(0,-5), POINT(0,0))))))),
219 (611, ST_MULTIPOLYGONFROMWKB(ST_ASWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(10,10), POINT(10,20), POINT(20,20), POINT(20,10), POINT(10,10)),
220 LINESTRING(POINT(14,14), POINT(14,16), POINT(16,16), POINT(16,14), POINT(14,14))))))),
221 (612, ST_MULTIPOLYGONFROMWKB(ST_ASWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(10,0), POINT(10,10), POINT(0,10), POINT(0,0)),
222 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))),
223 POLYGON(LINESTRING(POINT(0,0), POINT(-5,0), POINT(-5,-5), POINT(0,-5), POINT(0,0)))))));
224
225 INSERT INTO gis_multi_polygon_srid VALUES
226 (613, ST_MPOLYFROMWKB(ST_ASWKB(ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,-5 5,5 5,0 0)),((0 0,-5 5,-5 -5,0 0)))')))),
227 (614, ST_MPOLYFROMWKB(ST_ASWKB(ST_MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((0 0,10 10,-10 10,0 0)),((0 0,-10 -10,10 -10,0 0)))')))),
228 (615, ST_MULTIPOLYGONFROMWKB(ST_ASWKB(ST_MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((0 0,5 -5,10 0,5 5,0 0)))'))));
229
230
231 --echo # Retrieving table of MULTIPOLYGON geometries and SRID values
232 --echo # (the latter should be 0 by default)
233 SELECT ST_ASTEXT(g), ST_SRID(g) FROM gis_multi_polygon_srid;
234 --echo # Update all fields of the table to have the same SRID
235 UPDATE gis_multi_polygon_srid SET g=ST_SRID(g, 4326);
236 --echo # Check that SRIDs are now updated, and check that ST_SRID(g)
237 --echo # returns a geometry in the second column
238 SELECT ST_ASTEXT(g), ST_SRID(g) FROM gis_multi_polygon_srid;
239
240 ########################################################
241 # Inserting GEOMETRYCOLLECTION Geometry values
242 ########################################################
243
244 --echo # Inserting GEOMETRYCOLLECTION Geometry Values
245
246 INSERT INTO gis_geometrycollection_srid VALUES
247 (701, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))')),
248 (702, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
249 (703, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(5 5), POLYGON((0 0,10 0,10 10,0 10,0 0)))'));
250
251 INSERT INTO gis_geometrycollection_srid VALUES
252 (704, ST_GEOMETRYCOLLECTIONFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
253 (705, ST_GEOMETRYCOLLECTIONFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),'
254 'LINESTRING(0 0,10 10),'
255 'POLYGON((0 0,10 0,10 10,0 10, 0 0)))')),
256 (706, ST_GEOMETRYCOLLECTIONFROMTEXT('GEOMETRYCOLLECTION(MULTIPOINT((0 0),(5 5),(10 10)),'
257 'MULTILINESTRING((0 0,10 10),(0 10,10 0)),'
258 'MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),'
259 '((0 0,-10 0,-10 -10,0 -10,0 0))))'));
260
261 INSERT INTO gis_geometrycollection_srid VALUES
262 (707, ST_GEOMCOLLFROMWKB(ST_ASWKB(GEOMETRYCOLLECTION(POINT(0,0))))),
263 (708, ST_GEOMCOLLFROMWKB(ST_ASWKB(GEOMETRYCOLLECTION(POINT(0,0),
264 LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6), POINT(8,8)),
265 POLYGON(LINESTRING(POINT(0,0), POINT(10,0), POINT(10,10), POINT(0,10), POINT(0,0)),
266 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))))))),
267 (709, ST_GEOMCOLLFROMWKB(ST_ASWKB(GEOMETRYCOLLECTION(MULTIPOINT(POINT(0,0), POINT(5,5), POINT(10,10)),
268 MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6), POINT(8,8)),
269 LINESTRING(POINT(10,10), POINT(5,5), POINT(0,10))),
270 MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(10,0), POINT(10,10), POINT(0,10), POINT(0,0)),
271 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))),
272 POLYGON(LINESTRING(POINT(0,0), POINT(-10,0), POINT(-10,-10), POINT(0,0))))))));
273
274 INSERT INTO gis_geometrycollection_srid VALUES
275 (710, ST_GEOMETRYCOLLECTIONFROMWKB(ST_ASWKB(GEOMETRYCOLLECTION(POINT(0,0),
276 LINESTRING(POINT(0,0), POINT(90,90)))))),
277 (711, ST_GEOMETRYCOLLECTIONFROMWKB(ST_ASWKB(GEOMETRYCOLLECTION(POINT(10,10),
278 LINESTRING(POINT(10,10), POINT(12,12), POINT(14,14), POINT(16,16), POINT(18,18)),
279 POLYGON(LINESTRING(POINT(0,0), POINT(10,0), POINT(10,10), POINT(0,10), POINT(0,0)),
280 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))))))),
281 (712, ST_GEOMETRYCOLLECTIONFROMWKB(ST_ASWKB(GEOMETRYCOLLECTION(MULTIPOINT(POINT(10,10), POINT(15,15), POINT(20,20)),
282 MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6), POINT(8,8)),
283 LINESTRING(POINT(10,10), POINT(5,5), POINT(0,10))),
284 MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(10,0), POINT(10,10), POINT(0,10), POINT(0,0)),
285 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))),
286 POLYGON(LINESTRING(POINT(0,0), POINT(-10,0), POINT(-10,-10), POINT(0,0))))))));
287
288 INSERT INTO gis_geometrycollection_srid VALUES
289 (713, ST_GEOMCOLLFROMWKB(ST_ASWKB(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION('
290 'POINT(0 0),'
291 'LINESTRING(0 0,10 10),'
292 'POLYGON((0 0,10 0,10 10,0 10, 0 0)),'
293 'MULTIPOINT((0 0),(2 2),(4 4),(6 6),(8 8),(10 10)),'
294 'MULTILINESTRING((0 0,10 10),(0 10,10 0)),'
295 'MULTIPOLYGON(((0 0,5 0,5 5,0 5,0 0)),((5 5,10 5,10 10,5 10,5 5))))')))),
296 (714, ST_GEOMCOLLFROMWKB(ST_ASWKB(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION('
297 'GEOMETRYCOLLECTION('
298 'POINT(0 0)),'
299 'GEOMETRYCOLLECTION('
300 'LINESTRING(0 0,10 10)),'
301 'GEOMETRYCOLLECTION('
302 'POLYGON((0 0,10 0,10 10,0 10, 0 0))),'
303 'GEOMETRYCOLLECTION('
304 'MULTIPOINT((0 0),(2 2),(4 4),(6 6),(8 8),(10 10))),'
305 'GEOMETRYCOLLECTION('
306 'MULTILINESTRING((0 0,10 10),(0 10,10 0))),'
307 'GEOMETRYCOLLECTION('
308 'MULTIPOLYGON(((0 0,5 0,5 5,0 5,0 0)),((5 5,10 5,10 10,5 10,5 5)))))')))),
309 (715, ST_GEOMCOLLFROMWKB(ST_ASWKB(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION('
310 'GEOMETRYCOLLECTION(),'
311 'POINT(0 0),'
312 'GEOMETRYCOLLECTION('
313 'LINESTRING(0 0,10 10),'
314 'GEOMETRYCOLLECTION('
315 'GEOMETRYCOLLECTION())),'
316 'GEOMETRYCOLLECTION(),'
317 'GEOMETRYCOLLECTION('
318 'GEOMETRYCOLLECTION()),'
319 'POLYGON((0 0,10 0,10 10,0 10, 0 0)),'
320 'MULTIPOINT((0 0),(2 2),(4 4),(6 6),(8 8),(10 10)),'
321 'MULTILINESTRING((0 0,10 10),(0 10,10 0)),'
322 'MULTIPOLYGON(((0 0,5 0,5 5,0 5,0 0)),((5 5,10 5,10 10,5 10,5 5))))'))));
323
324 --echo # Retrieving table of MULTIPOLYGON geometries and SRID values
325 --echo # (the latter should be 0 by default)
326
327 SELECT ST_ASTEXT(g), ST_SRID(g) FROM gis_geometrycollection_srid;
328
329 --echo # Update all fields of the table to have the same SRID
330 UPDATE gis_geometrycollection_srid SET g=ST_SRID(g, 4326);
331
332 --echo # Check that SRIDs are now updated, and check that ST_SRID(g) returns
333 --echo # a geometry in the second column
334 SELECT ST_ASTEXT(g), ST_SRID(g) FROM gis_geometrycollection_srid;
335
336
337 ####################################
338 # Invalid SRID range testing
339 ####################################
340
341 --echo # Try to use ST_SRID with out of range SRID values (Should give error message)
342
343 --error ER_DATA_OUT_OF_RANGE
344 UPDATE gis_point_srid SET g=ST_SRID(g, -100); # Less than 0
345 --error ER_DATA_OUT_OF_RANGE
346 UPDATE gis_point_srid SET g=ST_SRID(g, 5094967295); # More than (2^32)-1
347
348 --error ER_DATA_OUT_OF_RANGE
349 UPDATE gis_linestring_srid SET g=ST_SRID(g, -100); # Less than 0
350 --error ER_DATA_OUT_OF_RANGE
351 UPDATE gis_linestring_srid SET g=ST_SRID(g, 5094967295); # More than (2^32)-1
352
353 --error ER_DATA_OUT_OF_RANGE
354 UPDATE gis_polygon_srid SET g=ST_SRID(g, -100); # Less than 0
355 --error ER_DATA_OUT_OF_RANGE
356 UPDATE gis_polygon_srid SET g=ST_SRID(g, 5094967295); # More than (2^32)-1
357
358 --error ER_DATA_OUT_OF_RANGE
359 UPDATE gis_multi_point_srid SET g=ST_SRID(g, -100); # Less than 0
360 --error ER_DATA_OUT_OF_RANGE
361 UPDATE gis_multi_point_srid SET g=ST_SRID(g, 5094967295); # More than (2^32)-1
362
363 --error ER_DATA_OUT_OF_RANGE
364 UPDATE gis_multi_linestring_srid SET g=ST_SRID(g, -100); # Less than 0
365 --error ER_DATA_OUT_OF_RANGE
366 UPDATE gis_multi_linestring_srid SET g=ST_SRID(g, 5094967295); # More than (2^32)-1
367
368 --error ER_DATA_OUT_OF_RANGE
369 UPDATE gis_multi_polygon_srid SET g=ST_SRID(g, -100); # Less than 0
370 --error ER_DATA_OUT_OF_RANGE
371 UPDATE gis_multi_polygon_srid SET g=ST_SRID(g, 5094967295); # More than (2^32)-1
372
373 --error ER_DATA_OUT_OF_RANGE
374 UPDATE gis_geometrycollection_srid SET g=ST_SRID(g, -100); # Less than 0
375 --error ER_DATA_OUT_OF_RANGE
376 UPDATE gis_geometrycollection_srid SET g=ST_SRID(g, 5094967295); # More than (2^32)-1
377
378
379 ########################################
380 # Invalid parameter count testing
381 ########################################
382
383 --echo # Try to use ST_SRID with incorrect parameter count (>2 or <1)
384
385 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
386 UPDATE gis_point_srid SET g=ST_SRID();
387 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
388 UPDATE gis_point_srid SET g=ST_SRID(g, 4145, 0);
389
390 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
391 UPDATE gis_linestring_srid SET g=ST_SRID();
392 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
393 UPDATE gis_linestring_srid SET g=ST_SRID(g, 4145, 0);
394
395 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
396 UPDATE gis_polygon_srid SET g=ST_SRID();
397 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
398 UPDATE gis_polygon_srid SET g=ST_SRID(g, 4145, 0);
399
400 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
401 UPDATE gis_multi_point_srid SET g=ST_SRID();
402 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
403 UPDATE gis_multi_point_srid SET g=ST_SRID(g, 4145, 0);
404
405 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
406 UPDATE gis_multi_linestring_srid SET g=ST_SRID();
407 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
408 UPDATE gis_multi_linestring_srid SET g=ST_SRID(g, 4145, 0);
409
410 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
411 UPDATE gis_multi_polygon_srid SET g=ST_SRID();
412 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
413 UPDATE gis_multi_polygon_srid SET g=ST_SRID(g, 4145, 0);
414
415 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
416 UPDATE gis_geometrycollection_srid SET g=ST_SRID();
417 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
418 UPDATE gis_geometrycollection_srid SET g=ST_SRID(g, 4145, 0);
419
420 ###############################
421 # Undefined SRID testing
422 ###############################
423
424 --echo # Try to use the mutator SRID function with an undefined SRID
425 --error ER_SRS_NOT_FOUND
426 UPDATE gis_point_srid SET g=ST_SRID(g, 10101);
427
428 --error ER_SRS_NOT_FOUND
429 UPDATE gis_linestring_srid SET g=ST_SRID(g, 10101);
430
431 --error ER_SRS_NOT_FOUND
432 UPDATE gis_polygon_srid SET g=ST_SRID(g, 10101);
433
434 --error ER_SRS_NOT_FOUND
435 UPDATE gis_multi_point_srid SET g=ST_SRID(g, 10101);
436
437 --error ER_SRS_NOT_FOUND
438 UPDATE gis_multi_linestring_srid SET g=ST_SRID(g, 10101);
439
440 --error ER_SRS_NOT_FOUND
441 UPDATE gis_multi_polygon_srid SET g=ST_SRID(g, 10101);
442
443 --error ER_SRS_NOT_FOUND
444 UPDATE gis_geometrycollection_srid SET g=ST_SRID(g, 10101);
445
446 ##################################
447 # Invalid geometry testing
448 ##################################
449
450 --echo # Testing ST_SRID mutator function with badly formed wkb strings
451 --echo # Note: Here the binary representation is applied directly to the
452 --echo # mutator ST_SRID function because if other helper functions were
453 --echo # to be used, such as ST_GEOMFROMTEXT or similar, then the error
454 --echo # would be caught in those functions instead of in the ST_SRID function.
455
456 --echo # Testing with wkb of size less than 13 bytes
457 SET @geom_obj_too_short=x'311000000101';
458 --echo # Testing with wkb that does not correspond to a valid geometry
459 SET @geom_obj_badly_formed=x'311000000101FFFFFF1221111100000011';
460
461 --error ER_GIS_INVALID_DATA
462 SELECT ST_SRID(@geom_obj_too_short, 4145);
463 --error ER_GIS_INVALID_DATA
464 SELECT ST_SRID(@geom_obj_badly_formed, 4145);
465
466
467 #############################
468 # Null value testing
469 #############################
470
471 --echo # Test for geometry = NULL:
472 SELECT ST_SRID(g, NULL) FROM gis_point_srid;
473 SELECT ST_SRID(g, NULL) FROM gis_linestring_srid;
474 SELECT ST_SRID(g, NULL) FROM gis_polygon_srid;
475 SELECT ST_SRID(g, NULL) FROM gis_multi_point_srid;
476 SELECT ST_SRID(g, NULL) FROM gis_multi_linestring_srid;
477 SELECT ST_SRID(g, NULL) FROM gis_multi_polygon_srid;
478 SELECT ST_SRID(g, NULL) FROM gis_geometrycollection_srid;
479
480 --echo # Test for SRID = NULL:
481 SELECT ST_SRID(NULL, 4145) FROM gis_point_srid;
482 SELECT ST_SRID(NULL, 4145) FROM gis_linestring_srid;
483 SELECT ST_SRID(NULL, 4145) FROM gis_polygon_srid;
484 SELECT ST_SRID(NULL, 4145) FROM gis_multi_point_srid;
485 SELECT ST_SRID(NULL, 4145) FROM gis_multi_linestring_srid;
486 SELECT ST_SRID(NULL, 4145) FROM gis_multi_polygon_srid;
487 SELECT ST_SRID(NULL, 4145) FROM gis_geometrycollection_srid;
488
489 --echo # Test for geometry = NULL and SRID = NULL:
490 SELECT ST_SRID(NULL, NULL) FROM gis_point_srid;
491 SELECT ST_SRID(NULL, NULL) FROM gis_linestring_srid;
492 SELECT ST_SRID(NULL, NULL) FROM gis_polygon_srid;
493 SELECT ST_SRID(NULL, NULL) FROM gis_multi_point_srid;
494 SELECT ST_SRID(NULL, NULL) FROM gis_multi_linestring_srid;
495 SELECT ST_SRID(NULL, NULL) FROM gis_multi_polygon_srid;
496 SELECT ST_SRID(NULL, NULL) FROM gis_geometrycollection_srid;
497
498 ##################################
499 # Boundary case testing
500 ##################################
501
502 --echo # Setting SRID to minimum value of zero (should be allowed)
503 UPDATE gis_point_srid SET g=ST_SRID(g, 0);
504 UPDATE gis_linestring_srid SET g=ST_SRID(g, 0);
505 UPDATE gis_polygon_srid SET g=ST_SRID(g, 0);
506 UPDATE gis_multi_point_srid SET g=ST_SRID(g, 0);
507 UPDATE gis_multi_linestring_srid SET g=ST_SRID(g, 0);
508 UPDATE gis_multi_polygon_srid SET g=ST_SRID(g, 0);
509 UPDATE gis_geometrycollection_srid SET g=ST_SRID(g, 0);
510
511 --echo # Using non-numeric value as SRID (should set SRID as zero)
512 SELECT ST_SRID(ST_SRID(g, 'a')) from gis_point_srid;
513 SELECT ST_SRID(ST_SRID(g, 'a')) from gis_linestring_srid;
514 SELECT ST_SRID(ST_SRID(g, 'a')) from gis_polygon_srid;
515 SELECT ST_SRID(ST_SRID(g, 'a')) from gis_multi_point_srid;
516 SELECT ST_SRID(ST_SRID(g, 'a')) from gis_multi_linestring_srid;
517 SELECT ST_SRID(ST_SRID(g, 'a')) from gis_multi_polygon_srid;
518 SELECT ST_SRID(ST_SRID(g, 'a')) from gis_geometrycollection_srid;
519
520 --echo # Setting SRID to maximum allowed value of 2^32-1=4294967295
521 --echo # (Should return ER_SRS_NOT_FOUND)
522 --error ER_SRS_NOT_FOUND
523 UPDATE gis_point_srid SET g=ST_SRID(g, 4294967295);
524 --error ER_SRS_NOT_FOUND
525 UPDATE gis_linestring_srid SET g=ST_SRID(g, 4294967295);
526 --error ER_SRS_NOT_FOUND
527 UPDATE gis_polygon_srid SET g=ST_SRID(g, 4294967295);
528 --error ER_SRS_NOT_FOUND
529 UPDATE gis_multi_point_srid SET g=ST_SRID(g, 4294967295);
530 --error ER_SRS_NOT_FOUND
531 UPDATE gis_multi_linestring_srid SET g=ST_SRID(g, 4294967295);
532 --error ER_SRS_NOT_FOUND
533 UPDATE gis_multi_polygon_srid SET g=ST_SRID(g, 4294967295);
534 --error ER_SRS_NOT_FOUND
535 UPDATE gis_geometrycollection_srid SET g=ST_SRID(g, 4294967295);
536
537 --echo # Setting SRID to one less than minimum value (-1)
538 --error ER_DATA_OUT_OF_RANGE
539 UPDATE gis_point_srid SET g=ST_SRID(g, -1);
540 --error ER_DATA_OUT_OF_RANGE
541 UPDATE gis_linestring_srid SET g=ST_SRID(g, -1);
542 --error ER_DATA_OUT_OF_RANGE
543 UPDATE gis_polygon_srid SET g=ST_SRID(g, -1);
544 --error ER_DATA_OUT_OF_RANGE
545 UPDATE gis_multi_point_srid SET g=ST_SRID(g, -1);
546 --error ER_DATA_OUT_OF_RANGE
547 UPDATE gis_multi_linestring_srid SET g=ST_SRID(g, -1);
548 --error ER_DATA_OUT_OF_RANGE
549 UPDATE gis_multi_polygon_srid SET g=ST_SRID(g, -1);
550 --error ER_DATA_OUT_OF_RANGE
551 UPDATE gis_geometrycollection_srid SET g=ST_SRID(g, -1);
552
553 --echo # Setting SRID to one more than maximum value (4294967296)
554 --error ER_DATA_OUT_OF_RANGE
555 UPDATE gis_point_srid SET g=ST_SRID(g, 4294967296);
556 --error ER_DATA_OUT_OF_RANGE
557 UPDATE gis_linestring_srid SET g=ST_SRID(g, 4294967296);
558 --error ER_DATA_OUT_OF_RANGE
559 UPDATE gis_polygon_srid SET g=ST_SRID(g, 4294967296);
560 --error ER_DATA_OUT_OF_RANGE
561 UPDATE gis_multi_point_srid SET g=ST_SRID(g, 4294967296);
562 --error ER_DATA_OUT_OF_RANGE
563 UPDATE gis_multi_linestring_srid SET g=ST_SRID(g, 4294967296);
564 --error ER_DATA_OUT_OF_RANGE
565 UPDATE gis_multi_polygon_srid SET g=ST_SRID(g, 4294967296);
566 --error ER_DATA_OUT_OF_RANGE
567 UPDATE gis_geometrycollection_srid SET g=ST_SRID(g, 4294967296);
568
569 --echo # Check that SRID values are set to 0 (and thus haven't been
570 --echo # altered by the other boundary case tests
571 SELECT ST_SRID(g) FROM gis_point_srid;
572 SELECT ST_SRID(g) FROM gis_linestring_srid;
573 SELECT ST_SRID(g) FROM gis_polygon_srid;
574 SELECT ST_SRID(g) FROM gis_multi_point_srid;
575 SELECT ST_SRID(g) FROM gis_multi_linestring_srid;
576 SELECT ST_SRID(g) FROM gis_multi_polygon_srid;
577 SELECT ST_SRID(g) FROM gis_geometrycollection_srid;
578
579 --echo # Dropping tables to leave test database in original state
580 DROP TABLE gis_point_srid;
581 DROP TABLE gis_linestring_srid;
582 DROP TABLE gis_polygon_srid;
583 DROP TABLE gis_multi_point_srid;
584 DROP TABLE gis_multi_linestring_srid;
585 DROP TABLE gis_multi_polygon_srid;
586 DROP TABLE gis_geometrycollection_srid;
587
588 --echo # Bug 23857035 Test case
589
590 SET @@sql_mode='';
591 CREATE TABLE t1 (g GEOMETRY);
592 INSERT INTO t1 VALUES (POINT(10, 10)), (NULL);
593 SELECT ST_AsText(ST_SRID(g, 4326)) FROM t1;
594
595 DROP TABLE t1;
596
597 --echo #
598 --echo # Bug #27892138 ST_LONGITUD() RETURNS LATITUDE OUT OF RANGE ERROR
599 --echo #
600
601 # The Item::null_value for literal NULLs is hardcoded in the item. This
602 # test reads the values from a table.
603
604 CREATE TABLE t1 (srid INT);
605 INSERT INTO t1 VALUES (NULL), (4326);
606 SELECT srid, HEX(ST_SRID(POINT(0, 0), srid)) FROM t1;
607 DROP TABLE t1;
608
609 CREATE TABLE t1 (pk INT PRIMARY KEY, g GEOMETRY);
610 INSERT INTO t1 VALUES (0, NULL), (1, ST_GEOMFROMTEXT('POINT(0 0)'));
611 SELECT pk, ST_SRID(g) FROM t1;
612 SELECT pk, HEX(ST_SRID(g, 4326)) FROM t1;
613 DROP TABLE t1;