Debian Codegraph @

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

raw
1 ###############################################################################
2 # #
3 # This test is aimed at testing the modified ST_ASWKB/ST_ASBINARY #
4 # functions where they have been extended to accept two parameters: #
5 # a geometry and an optional argument to specify the axis ordering of #
6 # the returned WKB string. #
7 # #
8 # Creation Date: 2016-29-07 #
9 # Author : Hans H. Melby #
10 # #
11 ###############################################################################
12
13 --echo ##########################################################
14 --echo # Inserting testing SRS values in spatial reference table
15 --echo ##########################################################
16
17 --echo # Inserting geographical srs with long-lat ordering
18 CREATE SPATIAL REFERENCE SYSTEM 30000000 NAME 'TEST30000000' DEFINITION 'GEOGCS["SIRGAS 1995",DATUM["Sistema de Referencia Geocentrico para America del Sur 1995",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6170"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["LONG",EAST],AXIS["LAT",NORTH],AUTHORITY["EPSG","30000000"]]';
19
20 --echo # Inserting geographical srs with lat-long ordering
21 CREATE SPATIAL REFERENCE SYSTEM 30100000 NAME 'TEST30100000' DEFINITION 'GEOGCS["SIRGAS 1995",DATUM["Sistema de Referencia Geocentrico para America del Sur 1995",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6170"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["LAT",NORTH],AXIS["LONG",EAST],AUTHORITY["EPSG","30100000"]]';
22
23 --echo # Inserting projected srs with northing-easting ordering
24 CREATE SPATIAL REFERENCE SYSTEM 30200000 NAME 'TEST30200000' DEFINITION 'PROJCS["NAD83(NSRS2007) / Virginia Lambert",GEOGCS["NAD83(NSRS2007)",DATUM["NAD83 (National Spatial Reference System 2007)",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6759"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Long",EAST],AUTHORITY["EPSG","4759"]],PROJECTION["Lambert Conic Conformal (2SP)",AUTHORITY["EPSG","9802"]],PARAMETER["Latitude of false origin",36,AUTHORITY["EPSG","8821"]],PARAMETER["Longitude of false origin",-79.5,AUTHORITY["EPSG","8822"]],PARAMETER["Latitude of 1st standard parallel",37,AUTHORITY["EPSG","8823"]],PARAMETER["Latitude of 2nd standard parallel",39.5,AUTHORITY["EPSG","8824"]],PARAMETER["Easting at false origin",0,AUTHORITY["EPSG","8826"]],PARAMETER["Northing at false origin",0,AUTHORITY["EPSG","8827"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["X",NORTH],AXIS["Y",EAST],AUTHORITY["EPSG","30200000"]]';
25
26 --echo # Inserting projected srs with easting-northing ordering
27 CREATE SPATIAL REFERENCE SYSTEM 30300000 NAME 'TEST30300000' DEFINITION 'PROJCS["NAD83(NSRS2007) / Virginia Lambert",GEOGCS["NAD83(NSRS2007)",DATUM["NAD83 (National Spatial Reference System 2007)",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6759"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Long",EAST],AUTHORITY["EPSG","4759"]],PROJECTION["Lambert Conic Conformal (2SP)",AUTHORITY["EPSG","9802"]],PARAMETER["Latitude of false origin",36,AUTHORITY["EPSG","8821"]],PARAMETER["Longitude of false origin",-79.5,AUTHORITY["EPSG","8822"]],PARAMETER["Latitude of 1st standard parallel",37,AUTHORITY["EPSG","8823"]],PARAMETER["Latitude of 2nd standard parallel",39.5,AUTHORITY["EPSG","8824"]],PARAMETER["Easting at false origin",0,AUTHORITY["EPSG","8826"]],PARAMETER["Northing at false origin",0,AUTHORITY["EPSG","8827"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["X",EAST],AXIS["Y",NORTH],AUTHORITY["EPSG","30300000"]]';
28
29
30 ####################################
31 # Creating the spatial objects
32 ####################################
33
34 --echo # Creating the spatial objects
35
36 CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT);
37 CREATE TABLE gis_linestring (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING);
38 CREATE TABLE gis_polygon (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON);
39 CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT);
40 CREATE TABLE gis_multi_linestring (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING);
41 CREATE TABLE gis_multi_polygon (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON);
42 CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION);
43
44 ######################################
45 # Inserting POINT Geometry values
46 ######################################
47
48 --echo # Inserting POINT Geometry Values with different SRID types
49
50 INSERT INTO gis_point VALUES
51 (101, ST_SRID(ST_POINTFROMTEXT('POINT(5 10)',0), 30000000)),
52 (102, ST_SRID(ST_POINTFROMTEXT('POINT(5 10)',0), 30100000)),
53 (103, ST_SRID(ST_POINTFROMTEXT('POINT(5 10)',0), 30200000)),
54 (104, ST_SRID(ST_POINTFROMTEXT('POINT(5 10)',0), 30300000)),
55 (105, ST_SRID(ST_POINTFROMTEXT('POINT(5 10)',0), 0));
56
57 ###########################################
58 # Inserting LINESTRING Geometry values
59 ###########################################
60
61 --echo # Inserting LINESTRING Geometry Values with different SRID types
62
63 INSERT INTO gis_linestring VALUES
64 (201, ST_SRID(ST_LINEFROMTEXT('LINESTRING(0 5,5 10,10 15)',0), 30000000)),
65 (202, ST_SRID(ST_LINEFROMTEXT('LINESTRING(0 5,5 10,10 15)',0), 30100000)),
66 (203, ST_SRID(ST_LINEFROMTEXT('LINESTRING(0 5,5 10,10 15)',0), 30200000)),
67 (204, ST_SRID(ST_LINEFROMTEXT('LINESTRING(0 5,5 10,10 15)',0), 30300000)),
68 (205, ST_SRID(ST_LINEFROMTEXT('LINESTRING(0 5,5 10,10 15)',0), 0));
69
70 ############################################
71 # Inserting POLYGON Geometry values
72 ############################################
73
74 --echo # Inserting POLYGON Geometry Values with different SRID types
75
76 INSERT INTO gis_polygon VALUES
77 (301, ST_SRID(ST_POLYFROMTEXT('POLYGON((0 0,5 0,5 5,0 5,0 0))',0), 30000000)),
78 (302, ST_SRID(ST_POLYFROMTEXT('POLYGON((0 0,5 0,5 5,0 5,0 0))',0), 30100000)),
79 (303, ST_SRID(ST_POLYFROMTEXT('POLYGON((0 0,5 0,5 5,0 5,0 0))',0), 30200000)),
80 (304, ST_SRID(ST_POLYFROMTEXT('POLYGON((0 0,5 0,5 5,0 5,0 0))',0), 30300000)),
81 (305, ST_SRID(ST_POLYFROMTEXT('POLYGON((0 0,5 0,5 5,0 5,0 0))',0), 0));
82
83 ############################################
84 # Inserting MULTIPOINT Geometry values
85 ############################################
86
87 --echo # Inserting MULTIPOINT Geometry Values with different SRID types
88
89 INSERT INTO gis_multi_point VALUES
90 (401, ST_SRID(ST_MPOINTFROMTEXT('MULTIPOINT((0 2), (5 7), (8 2), (4 9))',0), 30000000)),
91 (402, ST_SRID(ST_MPOINTFROMTEXT('MULTIPOINT((0 2), (5 7), (8 2), (4 9))',0), 30100000)),
92 (403, ST_SRID(ST_MPOINTFROMTEXT('MULTIPOINT((0 2), (5 7), (8 2), (4 9))',0), 30200000)),
93 (404, ST_SRID(ST_MPOINTFROMTEXT('MULTIPOINT((0 2), (5 7), (8 2), (4 9))',0), 30300000)),
94 (405, ST_SRID(ST_MPOINTFROMTEXT('MULTIPOINT((0 2), (5 7), (8 2), (4 9))',0), 0));
95
96 ################################################
97 # Inserting MULTILINESTRING Geometry values
98 ################################################
99
100 --echo # Inserting MULTILINESTRING Geometry Values with different SRID types
101
102 INSERT INTO gis_multi_linestring VALUES
103 (501, ST_SRID(ST_MLINEFROMTEXT('MULTILINESTRING((0 2,2 4,4 6),(8 11,11 14,14 17))',0), 30000000)),
104 (502, ST_SRID(ST_MLINEFROMTEXT('MULTILINESTRING((0 2,2 4,4 6),(8 11,11 14,14 17))',0), 30100000)),
105 (503, ST_SRID(ST_MLINEFROMTEXT('MULTILINESTRING((0 2,2 4,4 6),(8 11,11 14,14 17))',0), 30200000)),
106 (504, ST_SRID(ST_MLINEFROMTEXT('MULTILINESTRING((0 2,2 4,4 6),(8 11,11 14,14 17))',0), 30300000)),
107 (505, ST_SRID(ST_MLINEFROMTEXT('MULTILINESTRING((0 2,2 4,4 6),(8 11,11 14,14 17))',0), 0));
108
109 #############################################
110 # Inserting MULTIPOLYGON Geometry values
111 #############################################
112
113 --echo # Inserting MULTIPOLYGON Geometry Values with different SRID types
114
115 INSERT INTO gis_multi_polygon VALUES
116 (601, ST_SRID(ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0),(4 4,4 6,6 6,6 4, 4 4)),((0 0,-2 -2,0 -2, 0 0)))',0), 30000000)),
117 (602, ST_SRID(ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0),(4 4,4 6,6 6,6 4, 4 4)),((0 0,-2 -2,0 -2, 0 0)))',0), 30100000)),
118 (603, ST_SRID(ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0),(4 4,4 6,6 6,6 4, 4 4)),((0 0,-2 -2,0 -2, 0 0)))',0), 30200000)),
119 (604, ST_SRID(ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0),(4 4,4 6,6 6,6 4, 4 4)),((0 0,-2 -2,0 -2, 0 0)))',0), 30300000)),
120 (605, ST_SRID(ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0),(4 4,4 6,6 6,6 4, 4 4)),((0 0,-2 -2,0 -2, 0 0)))',0), 0));
121
122 ##################################################
123 # Inserting GEOMETRYCOLLECTION Geometry values
124 ##################################################
125
126 --echo # Inserting GEOMETRYCOLLECTION Geometry Values with different SRID types
127
128 INSERT INTO gis_geometrycollection VALUES
129 (701, ST_SRID(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(3 5), POLYGON((0 0,10 0,10 10,0 10,0 0)), MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0),(4 4,4 6,6 6,6 4, 4 4)),((0 0,-2 -2,0 -2, 0 0))))',0), 30000000)),
130 (702, ST_SRID(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(3 5), POLYGON((0 0,10 0,10 10,0 10,0 0)), MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0),(4 4,4 6,6 6,6 4, 4 4)),((0 0,-2 -2,0 -2, 0 0))))',0), 30100000)),
131 (703, ST_SRID(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(3 5), POLYGON((0 0,10 0,10 10,0 10,0 0)), MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0),(4 4,4 6,6 6,6 4, 4 4)),((0 0,-2 -2,0 -2, 0 0))))',0), 30200000)),
132 (704, ST_SRID(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(3 5), POLYGON((0 0,10 0,10 10,0 10,0 0)), MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0),(4 4,4 6,6 6,6 4, 4 4)),((0 0,-2 -2,0 -2, 0 0))))',0), 30300000)),
133 (705, ST_SRID(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(3 5), POLYGON((0 0,10 0,10 10,0 10,0 0)), MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0),(4 4,4 6,6 6,6 4, 4 4)),((0 0,-2 -2,0 -2, 0 0))))',0), 0));
134
135 --echo #####################################################################
136 --echo # Retrieving geometries with ST_ASWKB with no parameter
137 --echo # (Should thus be retrieved with the SRID default axis ordering)
138 --echo #####################################################################
139
140 --echo # Retrieve all values from gis_point
141 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g))) FROM gis_point
142 ORDER BY fid;
143
144 --echo # Retrieve all values from gis_linestring
145 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g))) FROM gis_linestring
146 ORDER BY fid;
147
148 --echo # Retrieve all values from gis_polygon
149 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g))) FROM gis_polygon
150 ORDER BY fid;
151
152 --echo # Retrieve all values from gis_multi_point
153 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g))) FROM gis_multi_point
154 ORDER BY fid;
155
156 --echo # Retrieve all values from gis_multi_linestring
157 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g))) FROM gis_multi_linestring
158 ORDER BY fid;
159
160 --echo # Retrieve all values from gis_multi_polygon
161 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g))) FROM gis_multi_polygon
162 ORDER BY fid;
163
164 --echo # Retrieve all values from gis_geometrycollection
165 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g))) FROM gis_geometrycollection
166 ORDER BY fid;
167
168 --echo #####################################################################
169 --echo # Retrieving geometries with ST_ASWKB with lat-long axis ordering
170 --echo # (Default SRID coordination should thus be overridden)
171 --echo #####################################################################
172
173 --echo # Retrieve all values from gis_point
174 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=lat-long'))) FROM gis_point
175 ORDER BY fid;
176
177 --echo # Retrieve all values from gis_linestring
178 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=lat-long'))) FROM gis_linestring ORDER BY fid;
179
180 --echo # Retrieve all values from gis_polygon
181 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=lat-long'))) FROM gis_polygon
182 ORDER BY fid;
183
184 --echo # Retrieve all values from gis_multi_point
185 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=lat-long'))) FROM gis_multi_point ORDER BY fid;
186
187 --echo # Retrieve all values from gis_multi_linestring
188 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=lat-long'))) FROM gis_multi_linestring ORDER BY fid;
189
190 --echo # Retrieve all values from gis_multi_polygon
191 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=lat-long'))) FROM gis_multi_polygon ORDER BY fid;
192
193 --echo # Retrieve all values from gis_geometrycollection
194 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=lat-long'))) FROM gis_geometrycollection ORDER BY fid;
195
196 --echo #####################################################################
197 --echo # Retrieving geometries with ST_ASWKB with long-lat axis ordering
198 --echo # (Default SRID coordination should thus be overridden)
199 --echo #####################################################################
200
201 --echo # Retrieve all values from gis_point
202 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=long-lat'))) FROM gis_point
203 ORDER BY fid;
204
205 --echo # Retrieve all values from gis_linestring
206 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=long-lat'))) FROM gis_linestring ORDER BY fid;
207
208 --echo # Retrieve all values from gis_polygon
209 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=long-lat'))) FROM gis_polygon
210 ORDER BY fid;
211
212 --echo # Retrieve all values from gis_multi_point
213 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=long-lat'))) FROM gis_multi_point ORDER BY fid;
214
215 --echo # Retrieve all values from gis_multi_linestring
216 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=long-lat'))) FROM gis_multi_linestring ORDER BY fid;
217
218 --echo # Retrieve all values from gis_multi_polygon
219 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=long-lat'))) FROM gis_multi_polygon ORDER BY fid;
220
221 --echo # Retrieve all values from gis_geometrycollection
222 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=long-lat'))) FROM gis_geometrycollection ORDER BY fid;
223
224 --echo #####################################################################
225 --echo # Retrieving geometries with ST_ASWKB with srid-default axis ordering
226 --echo # (Default SRID coordination should thus be explicitly used)
227 --echo #####################################################################
228
229 --echo # Retrieve all values from gis_point
230 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=srid-defined'))) FROM gis_point
231 ORDER BY fid;
232
233 --echo # Retrieve all values from gis_linestring
234 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=srid-defined'))) FROM gis_linestring ORDER BY fid;
235
236 --echo # Retrieve all values from gis_polygon
237 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=srid-defined'))) FROM gis_polygon ORDER BY fid;
238
239 --echo # Retrieve all values from gis_multi_point
240 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=srid-defined'))) FROM gis_multi_point ORDER BY fid;
241
242 --echo # Retrieve all values from gis_multi_linestring
243 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=srid-defined'))) FROM gis_multi_linestring ORDER BY fid;
244
245 --echo # Retrieve all values from gis_multi_polygon
246 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=srid-defined'))) FROM gis_multi_polygon ORDER BY fid;
247
248 --echo # Retrieve all values from gis_geometrycollection
249 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=srid-defined'))) FROM gis_geometrycollection ORDER BY fid;
250
251 --echo ##########################################################################
252 --echo # Retrieving geometries with ST_ASWKB with empty axis ordering parameter
253 --echo # (Default SRID coordination should thus be used)
254 --echo ##########################################################################
255
256 --echo # Retrieve all values from gis_point
257 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, ''))) FROM gis_point
258 ORDER BY fid;
259
260 --echo # Retrieve all values from gis_linestring
261 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, ''))) FROM gis_linestring
262 ORDER BY fid;
263
264 --echo # Retrieve all values from gis_polygon
265 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, ''))) FROM gis_polygon
266 ORDER BY fid;
267
268 --echo # Retrieve all values from gis_multi_point
269 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, ''))) FROM gis_multi_point
270 ORDER BY fid;
271
272 --echo # Retrieve all values from gis_multi_linestring
273 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, ''))) FROM gis_multi_linestring
274 ORDER BY fid;
275
276 --echo # Retrieve all values from gis_multi_polygon
277 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, ''))) FROM gis_multi_polygon
278 ORDER BY fid;
279
280 --echo # Retrieve all values from gis_geometrycollection
281 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, ''))) FROM gis_geometrycollection
282 ORDER BY fid;
283
284
285 --echo ################################
286 --echo # Specific cases testing
287 --echo # ##############################
288
289 --echo # Test with combined lowercase and uppercase characters in optional parameter
290 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'aXIs-oRDer=sriD-dEFineD'))) FROM gis_geometrycollection ORDER BY fid;
291 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=LaT-LOng'))) FROM gis_geometrycollection ORDER BY fid;
292 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, 'axis-order=LOnG-lAT'))) FROM gis_geometrycollection ORDER BY fid;
293
294 --echo # Test with combined lowercase, uppercase and whitespaces in optional parameter
295 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, ' aXIs-oRDer=sriD-dEFineD '))) FROM gis_geometrycollection ORDER BY fid;
296 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, ' axis-order=LaT-LOng '))) FROM gis_geometrycollection ORDER BY fid;
297 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, ' axis-order=LOnG-lAT '))) FROM gis_geometrycollection ORDER BY fid;
298
299 --echo # Test with only whitespaces in optional parameter (Should return same as empty string)
300 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASWKB(g, ' '))) FROM gis_geometrycollection
301 ORDER BY fid;
302
303 --echo ##########################
304 --echo # Error testing
305 --echo # ########################
306
307 --echo # Test with badly formed options string
308
309 --error ER_INVALID_OPTION_KEY_VALUE_PAIR
310 SELECT ST_ASWKB(g, 'axis-order srid-defined') FROM gis_geometrycollection;
311 --error ER_INVALID_OPTION_START_CHARACTER
312 SELECT ST_ASWKB(g, '=axis-order srid-defined') FROM gis_geometrycollection;
313 --error ER_INVALID_OPTION_END_CHARACTER
314 SELECT ST_ASWKB(g, 'axis-order=srid-defined=') FROM gis_geometrycollection;
315 --error ER_INVALID_OPTION_CHARACTERS
316 SELECT ST_ASWKB(g, 'axis-order== srid-defined') FROM gis_geometrycollection;
317 --error ER_INVALID_OPTION_KEY_VALUE_PAIR
318 SELECT ST_ASWKB(g, 'axis-=order srid-def=ined') FROM gis_geometrycollection;
319 --error ER_INVALID_OPTION_END_CHARACTER
320 SELECT ST_ASWKB(g, 'axis-order srid-defined,') FROM gis_geometrycollection;
321 --error ER_INVALID_OPTION_START_CHARACTER
322 SELECT ST_ASWKB(g, ',axis-order srid-defined') FROM gis_geometrycollection;
323 --error ER_INVALID_OPTION_CHARACTERS
324 SELECT ST_ASWKB(g, 'axis-order,,srid-defined') FROM gis_geometrycollection;
325 --error ER_DUPLICATE_OPTION_KEY
326 SELECT ST_ASWKB(g, 'axis-order=srid-defined, axis-order=lat-long') FROM gis_geometrycollection;
327
328
329
330 --echo # Test with too many options
331
332 --error ER_INVALID_OPTION_KEY
333 SELECT ST_ASWKB(g, 'axis-order=srid-defined, axis-order2=lat-long') FROM gis_geometrycollection;
334
335 --echo # Test with invalid options key
336
337 --error ER_INVALID_OPTION_KEY
338 SELECT ST_ASWKB(g, 'axix-order=srid-defined') FROM gis_geometrycollection;
339 --error ER_INVALID_OPTION_KEY
340 SELECT ST_ASWKB(g, 'axix-order=lat-long') FROM gis_geometrycollection;
341 --error ER_INVALID_OPTION_KEY
342 SELECT ST_ASWKB(g, 'axix-order=long-lat') FROM gis_geometrycollection;
343
344 --echo # Test with invalid options value
345
346 --error ER_INVALID_OPTION_VALUE
347 SELECT ST_ASWKB(g, 'axis-order=invalid-option') FROM gis_geometrycollection;
348
349 --echo # Test with too many function parameters
350
351 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
352 SELECT ST_ASWKB(g, 'axis-order=long-lat', POINT(2, 4)) FROM gis_geometrycollection;
353
354 --echo # Test with too invalid start and end characters in substring
355
356 --error ER_INVALID_OPTION_START_CHARACTER
357 SELECT ST_ASWKB(POINT(4, 2), 'axis-order=test,=long-lathello,=test');
358
359 --error ER_INVALID_OPTION_END_CHARACTER
360 SELECT ST_ASWKB(POINT(4, 2), 'axis-order=long-lat=, axis-order=lat-long');
361
362 --echo # Test with both invalid option and invalid value
363
364 --error ER_INVALID_OPTION_KEY
365 SELECT ST_ASWKB(g, 'invalid_option=invalid-value') FROM gis_geometrycollection;
366
367 --echo # NULL value testing: Should return NULL if either or both parameters of
368 --echo # ST_ASWKB/ST_ABINARY is NULL
369
370 SELECT ST_AsWKB(NULL, 'axis-order=srid-defined');
371 SELECT ST_AsWKB(POINT(0, 0), NULL);
372 SELECT ST_AsWKB(NULL, NULL);
373
374 --echo # Bug#24488316 Test case
375
376 SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(ST_GeomFromText('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(MULTILINESTRING((1 9,18 2,0 -17,5 -3),(19 19,-4 -14),(-16 5,19 6),(-6 0,6 12,12 19,-19 -12),(5 -19,8 -19),(14 0,-16 -1,-1 -4,-1 20)),MULTILINESTRING((-14 -4,6 -4),(1 -4,-13 -1),(-17 5,-10 -8),(9 -17,-17 0,4 -19,12 2)))))),LINESTRING(-5 -12,-5 -9))', 4016))));
377
378 --echo # Testing with null character in options string
379
380 --error ER_INVALID_OPTION_KEY_VALUE_PAIR
381 SELECT HEX(ST_ASWKB(ST_GEOMFROMTEXT('POINT(0 0)'), x'00c0ffee'));
382
383 --echo # Test with options strings that previously gave wrong error message
384
385 --error ER_INVALID_OPTION_KEY
386 SELECT ST_AsWKB(POINT(1, 1), 'axis-order=lat-long,foo=bar');
387 --error ER_INVALID_OPTION_KEY
388 SELECT ST_AsWKB(POINT(1, 1), 'axis-order=lat-long , foo=bar');
389
390 --echo # Test with invalid options value and invalid SRID (should give same
391 --echo # error as with a valid SRID)
392
393 --error ER_INVALID_OPTION_VALUE
394 DO ST_AsWKB(ST_GeomFromText('POINT(1 1)', 4326), 'axis-order = foo');
395
396
397 --echo #####################################################################
398 --echo # ST_ASBINARY
399 --echo #####################################################################
400
401 # ST_ASBINARY has the exact same implementation as ST_ASWKB. We still need
402 # minimal testing of the interface to detect changes that affect only the one
403 # function name.
404
405 # These queries are a selection of those done for ST_ASWKB.
406
407 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASBINARY(g)))
408 FROM gis_geometrycollection ORDER BY fid;
409
410 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASBINARY(g, 'axis-order=srid-defined')))
411 FROM gis_geometrycollection ORDER BY fid;
412
413 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASBINARY(g, '')))
414 FROM gis_geometrycollection ORDER BY fid;
415
416 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASBINARY(g, 'axis-order=lat-long')))
417 FROM gis_geometrycollection ORDER BY fid;
418
419 SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASBINARY(g, 'axis-order=long-lat')))
420 FROM gis_geometrycollection ORDER BY fid;
421
422 --error ER_INVALID_OPTION_KEY
423 SELECT ST_ASBINARY(g, 'invalid_option=invalid-value')
424 FROM gis_geometrycollection;
425
426 --error ER_INVALID_OPTION_KEY
427 SELECT ST_ASBINARY(g, 'axis-order=srid-defined, axis-order2=lat-long')
428 FROM gis_geometrycollection;
429
430 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
431 SELECT ST_ASBINARY(g, 'axis-order=long-lat', POINT(2, 4))
432 FROM gis_geometrycollection;
433
434 SELECT ST_ASBINARY(NULL, 'axis-order=srid-defined');
435 SELECT ST_ASBINARY(POINT(0, 0), NULL);
436 SELECT ST_ASBINARY(NULL, NULL);
437
438 DROP TABLE gis_point;
439 DROP TABLE gis_linestring;
440 DROP TABLE gis_polygon;
441 DROP TABLE gis_multi_point;
442 DROP TABLE gis_multi_linestring;
443 DROP TABLE gis_multi_polygon;
444 DROP TABLE gis_geometrycollection;
445
446 # Cleanup.
447 DROP SPATIAL REFERENCE SYSTEM 30000000;
448 DROP SPATIAL REFERENCE SYSTEM 30100000;
449 DROP SPATIAL REFERENCE SYSTEM 30200000;
450 DROP SPATIAL REFERENCE SYSTEM 30300000;