Debian Codegraph @

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

raw
1 ###############################################################################
2 # #
3 # This test is aimed to focus on the properties and attributes of the #
4 # Mutator ST_X and ST_Y #
5 # point Geometry classes, and the functionality of ST_X() and ST_Y(). #
6 # #
7 # #
8 # Creation Date: 2016-07-25 #
9 # Author : Jens Even Berg Blomsøy #
10 # #
11 ###############################################################################
12
13
14 ###############################################################################
15 # Creating the spatial objects #
16 ###############################################################################
17
18 --echo # Creating the spatial objects
19
20 CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT);
21
22
23 ###############################################################################
24 # Inserting POINT Geometry values #
25 ###############################################################################
26
27 --echo # Inserting POINT Geometry Values
28
29 INSERT INTO gis_point 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 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 VALUES
44 (111, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(1 1)')))),
45 (112, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(1e308 1e308)')))),
46 (113, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(1e308 -1e308)')))),
47 (114, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(-1e308 1e308)')))),
48 (115, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(-1e308 -1e308)'))));
49
50 --echo # Displaying the inserted POINT Geometry Data
51 SELECT fid, ST_ASTEXT(g) FROM gis_point;
52
53 ###############################################################################
54 # POINT geometry property functions #
55 ###############################################################################
56
57 --echo #=======================================================================
58 --echo # ST_X(point)
59 --echo #=======================================================================
60
61 SELECT ST_ASTEXT(ST_X(ST_POINTFROMTEXT('POINT(0 0)'),5));
62
63 SELECT ST_ASTEXT(ST_X(ST_POINTFROMTEXT('POINT(10 10)'), 5));
64
65 SELECT ST_ASTEXT(ST_X(ST_POINTFROMTEXT('POINT(1e308 1e308)'), 1e205));
66
67 SELECT ST_ASTEXT(ST_X(ST_POINTFROMWKB(ST_ASWKB(POINT(1.1111,-1.1111))), 5.5555));
68
69 SELECT ST_ASTEXT(ST_X(ST_POINTFROMWKB(ST_ASWKB(POINT(10.0000,10.0000))), 50.0000));
70
71 SELECT ST_ASTEXT(ST_X(ST_POINTFROMWKB(ST_ASWKB(POINT(-1e308,-1e308))), -1e205));
72
73 --echo #=======================================================================
74 --echo # ST_Y(point)
75 --echo #=======================================================================
76
77 SELECT ST_ASTEXT(ST_Y(ST_POINTFROMTEXT('POINT(0 0)'),5));
78
79 SELECT ST_ASTEXT(ST_Y(ST_POINTFROMTEXT('POINT(10 10)'), 5));
80
81 SELECT ST_ASTEXT(ST_Y(ST_POINTFROMTEXT('POINT(1e308 1e308)'), 1e205));
82
83 SELECT ST_ASTEXT(ST_Y(ST_POINTFROMWKB(ST_ASWKB(POINT(1.1111,-1.1111))), 5.5555));
84
85 SELECT ST_ASTEXT(ST_Y(ST_POINTFROMWKB(ST_ASWKB(POINT(10.0000,10.0000))), 50.0000));
86
87 SELECT ST_ASTEXT(ST_Y(ST_POINTFROMWKB(ST_ASWKB(POINT(-1e308,-1e308))), -1e205));
88
89 --echo #=======================================================================
90 --echo # ST_X(point)
91 --echo #=======================================================================
92
93 ########################################
94 # Null value testing #
95 ########################################
96
97 SELECT ST_ASTEXT(ST_X(NULL, 5));
98 SELECT ST_ASTEXT(ST_X(POINT(4, 5), NULL));
99 SELECT ST_ASTEXT(ST_X(NULL, NULL));
100
101 SELECT ST_ASTEXT(ST_X(ST_POINTFROMWKB(NULL), NULL));
102
103 --error ER_PARSE_ERROR
104 SELECT ST_ASTEXT(ST_X(ST_POINTFROMWKB(ST_ASWKB(POINT(1)), 5)));
105
106 --error ER_PARSE_ERROR
107 SELECT ST_ASTEXT(ST_X(ST_POINTFROMWKB(ST_ASWKB(POINT(1)), )));
108
109 --error ER_BAD_FIELD_ERROR
110 SELECT ST_ASTEXT(ST_X(ST_POINTFROMWKB(ST_ASWKB(POINT(1, 2)), a)));
111
112 --error ER_BAD_FIELD_ERROR
113 SELECT ST_ASTEXT(ST_X(ST_POINTFROMWKB(ST_ASWKB(POINT(a,b)), c)));
114
115 --error ER_GIS_INVALID_DATA
116 SELECT ST_ASTEXT(ST_X(ST_POINTFROMTEXT('NULL'), NULL));
117
118 --error ER_GIS_INVALID_DATA
119 SELECT ST_ASTEXT(ST_X(ST_POINTFROMTEXT('POINT()'), 5));
120
121 --error ER_GIS_INVALID_DATA
122 SELECT ST_ASTEXT(ST_X(ST_POINTFROMTEXT('POINT(1)'), 5));
123
124 --echo #=======================================================================
125 --echo # ST_Y(point)
126 --echo #=======================================================================
127
128 ########################################
129 # Null value testing #
130 ########################################
131
132 SELECT ST_ASTEXT(ST_Y(NULL, 5));
133 SELECT ST_ASTEXT(ST_Y(POINT(4, 5), NULL));
134 SELECT ST_ASTEXT(ST_Y(NULL, NULL));
135
136 SELECT ST_ASTEXT(ST_Y(ST_POINTFROMWKB(NULL), NULL));
137
138 --error ER_PARSE_ERROR
139 SELECT ST_ASTEXT(ST_Y(ST_POINTFROMWKB(ST_ASWKB(POINT(1)), 5)));
140
141 --error ER_PARSE_ERROR
142 SELECT ST_ASTEXT(ST_Y(ST_POINTFROMWKB(ST_ASWKB(POINT(1)), )));
143
144 --error ER_BAD_FIELD_ERROR
145 SELECT ST_ASTEXT(ST_Y(ST_POINTFROMWKB(ST_ASWKB(POINT(1, 2)), a)));
146
147 --error ER_BAD_FIELD_ERROR
148 SELECT ST_ASTEXT(ST_Y(ST_POINTFROMWKB(ST_ASWKB(POINT(a,b)), c)));
149
150 --error ER_GIS_INVALID_DATA
151 SELECT ST_ASTEXT(ST_Y(ST_POINTFROMTEXT('NULL'), NULL));
152
153 --error ER_GIS_INVALID_DATA
154 SELECT ST_ASTEXT(ST_Y(ST_POINTFROMTEXT('POINT()'), 5));
155
156 --error ER_GIS_INVALID_DATA
157 SELECT ST_ASTEXT(ST_Y(ST_POINTFROMTEXT('POINT(1)'), 5));
158
159
160 ###############################################################################
161 # Invalid range testing #
162 ###############################################################################
163 --echo # Try to use ST_X and ST_Y with out of range point values.
164
165 --error ER_DATA_OUT_OF_RANGE
166 SELECT ST_X(POINT(pow(2, 2048), 4), 5);
167 --error ER_DATA_OUT_OF_RANGE
168 SELECT ST_Y(POINT(pow(2, 2048), 4), 5);
169
170 --error ER_DATA_OUT_OF_RANGE
171 SELECT ST_Y(POINT(4, pow(2, 2048)), 5);
172 --error ER_DATA_OUT_OF_RANGE
173 SELECT ST_X(POINT(4, pow(2, 2048)), 5);
174
175 --error ER_DATA_OUT_OF_RANGE
176 SELECT ST_X(POINT(4, 5), pow(2, 2048));
177 --error ER_DATA_OUT_OF_RANGE
178 SELECT ST_Y(POINT(4, 5), pow(2, 2048));
179
180 ###############################################################################
181 # Invalid geometry testing #
182 ###############################################################################
183
184 --error ER_UNEXPECTED_GEOMETRY_TYPE
185 SELECT ST_X(ST_GeomFromText('LINESTRING(0 0, 1 1)'), 5);
186
187 --error ER_UNEXPECTED_GEOMETRY_TYPE
188 SELECT ST_Y(ST_GeomFromText('LINESTRING(0 0, 1 1)'), 5);
189
190 --error ER_UNEXPECTED_GEOMETRY_TYPE
191 SELECT ST_X(ST_GEOMFROMTEXT('POLYGON((0 0,2 2,0 2,0 0))'), -1024);
192
193 --error ER_UNEXPECTED_GEOMETRY_TYPE
194 SELECT ST_Y(ST_GEOMFROMTEXT('POLYGON((0 0,2 2,0 2,0 0))'), -1024);
195
196 --error ER_UNEXPECTED_GEOMETRY_TYPE
197 SELECT ST_X(ST_GEOMFROMTEXT('MULTIPOINT(10 40, 40 30, 20 20, 30 10)'), 1024);
198
199 --error ER_UNEXPECTED_GEOMETRY_TYPE
200 SELECT ST_Y(ST_GEOMFROMTEXT('MULTIPOINT(10 40, 40 30, 20 20, 30 10)'), 1024);
201
202 --error ER_UNEXPECTED_GEOMETRY_TYPE
203 SELECT ST_X(ST_GEOMFROMTEXT("MULTILINESTRING((10 10, 20 20, 10 40),"
204 "(40 40, 30 30, 40 20, 30 10))"), 1024);
205
206 --error ER_UNEXPECTED_GEOMETRY_TYPE
207 SELECT ST_Y(ST_GEOMFROMTEXT("MULTILINESTRING((10 10, 20 20, 10 40),"
208 "(40 40, 30 30, 40 20, 30 10))"), 1024);
209
210 --error ER_UNEXPECTED_GEOMETRY_TYPE
211 SELECT ST_X(ST_GEOMFROMTEXT("MULTIPOLYGON(((40 40, 20 45, 45 30, 40 40)),"
212 "((20 35, 10 30, 10 10, 30 5, 45 20, 20 35)))"), -1024);
213
214 --error ER_UNEXPECTED_GEOMETRY_TYPE
215 SELECT ST_Y(ST_GEOMFROMTEXT("MULTIPOLYGON(((40 40, 20 45, 45 30, 40 40)),"
216 "((20 35, 10 30, 10 10, 30 5, 45 20, 20 35)))"), -1024);
217
218 --error ER_UNEXPECTED_GEOMETRY_TYPE
219 SELECT ST_X(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION("
220 "POINT(4 6),"
221 "LINESTRING(4 6,7 10),"
222 "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10)),"
223 "MULTIPOINT (10 40, 40 30, 20 20, 30 10),"
224 "MULTILINESTRING ((10 10, 20 20, 10 40),"
225 "(40 40, 30 30, 40 20, 30 10)),"
226 "MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)),"
227 "((15 5, 40 10, 10 20, 5 10, 15 5)))"
228 ")"
229 ), 1024);
230
231 --error ER_UNEXPECTED_GEOMETRY_TYPE
232 SELECT ST_Y(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION("
233 "POINT(4 6),"
234 "LINESTRING(4 6,7 10),"
235 "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10)),"
236 "MULTIPOINT (10 40, 40 30, 20 20, 30 10),"
237 "MULTILINESTRING ((10 10, 20 20, 10 40),"
238 "(40 40, 30 30, 40 20, 30 10)),"
239 "MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)),"
240 "((15 5, 40 10, 10 20, 5 10, 15 5)))"
241 ")"
242 ), 1024);
243
244 --echo # Final cleanup
245 DROP TABLE gis_point;