Debian Codegraph @

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

raw
1 ###############################################################################
2 # #
3 # ST_MakeEnvelope makes a rectangle polygon, i.e. an envelope using #
4 # two diagonal points. #
5 # #
6 # Creation Date: 2014-12-03 #
7 # Author : Pavan Naik #
8 # #
9 ###############################################################################
10
11
12 ############################################################################################
13 # Creating the spatial objects #
14 ############################################################################################
15
16 --echo # Creating the spatial Geometry object
17 USE test;
18
19 CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
20
21 ############################################################################################
22 # Inserting the values specific to the spatial objects #
23 ############################################################################################
24
25 # Geometric elements constructing a star and some of its elememts as base
26 SET @star_elem_vertical= 'POLYGON((5 0,15 25,25 0,15 5,5 0))';
27 SET @star_elem_horizontal= 'POLYGON((25 0,0 15,30 15,22 10,25 0))';
28 SET @star_center= 'POINT(15 10)';
29 SET @star_top= 'POINT(15 25)';
30 SET @star_bottom_left= 'POINT(5 0)';
31 SET @star_bottom_right= 'POINT(25 0)';
32 SET @star_bottom_points= 'MULTIPOINT(5 0,25 0)';
33 SET @star_all_points= 'MULTIPOINT(5 0,25 0,15 10,15 25)';
34 SET @star_line_horizontal= 'LINESTRING(10 15,20 15)';
35 SET @star_line_vertical= 'LINESTRING(15 5,15 25)';
36 SET @star_top_to_center= 'LINESTRING(15 25,15 10)';
37 SET @star_lines_near_horizontal= 'MULTILINESTRING((25 0,0 15,15 30,0 5))';
38 SET @star_lines_near_vertical= 'MULTILINESTRING((0 5,15 25,0 25))';
39 SET @star= 'POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0))';
40 SET @star_of_elems='MULTIPOLYGON(((5 0,15 25,25 0,15 5,5 0)),((25 0,0 15,30 15,22 10,25 0)))';
41 SET @star_collection_elems='GEOMETRYCOLLECTION(MULTIPOLYGON(((5 0,15 25,25 0,15 5,5 0)),((25 0,0 15,30 15,22 10,25 0))),POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0)),LINESTRING(15 25,15 10),MULTIPOINT(5 0,25 0),POINT(15 25))';
42 SET @star_collection_multilinestr='GEOMETRYCOLLECTION(MULTIPOLYGON(((5 0,15 25,25 0,15 5,5 0)),((25 0,0 15,30 15,22 10,25 0))),POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0)),MULTILINESTRING((25 0,0 15,15 30,0 5)),LINESTRING(15 25,15 10),MULTIPOINT(5 0,25 0),POINT(15 25))';
43
44 --echo # INSERT base star
45 --disable_warnings
46 INSERT INTO gis_geometrycollection VALUES
47 (100,ST_GEOMFROMTEXT(@star)),
48 (101,ST_GEOMFROMTEXT(@star_elem_vertical)),
49 (102,ST_GEOMFROMTEXT(@star_elem_horizontal)),
50 (103,ST_GEOMFROMTEXT(@star_of_elems)),
51 (104,ST_GEOMFROMTEXT(@star_top)),
52 (105,ST_GEOMFROMTEXT(@star_center)),
53 (106,ST_GEOMFROMTEXT(@star_bottom_left)),
54 (107,ST_GEOMFROMTEXT(@star_bottom_right)),
55 (108,ST_GEOMFROMTEXT(@star_bottom_points)),
56 (109,ST_GEOMFROMTEXT(@star_all_points)),
57 (110,ST_GEOMFROMTEXT(@star_line_horizontal)),
58 (111,ST_GEOMFROMTEXT(@star_line_vertical)),
59 (112,ST_GEOMFROMTEXT(@star_top_to_center)),
60 (113,ST_GEOMFROMTEXT(@star_lines_near_horizontal)),
61 (114,ST_GEOMFROMTEXT(@star_lines_near_vertical)),
62 (115,ST_GEOMFROMTEXT(@star_collection_elems)),
63 (116,ST_GEOMFROMTEXT(@star_collection_multilinestr));
64 --enable_warnings
65
66 --echo # Checking the integrity of the above create/insert statements
67
68 --echo # 17 rows.
69 SELECT count(ST_ASTEXT(g) != 'NULL') FROM gis_geometrycollection;
70
71 --echo #####################################################################################
72 --echo # ST_MAKEENVELOPE(point, point)
73 --echo #####################################################################################
74
75 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)')));
76
77 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 -1)')));
78
79 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(-1 1)')));
80
81 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(-1 -1)')));
82
83 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 0)')));
84
85 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(1 -1)'), ST_GEOMFROMTEXT('POINT(0 0)')));
86
87 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(-1 1)'), ST_GEOMFROMTEXT('POINT(0 0)')));
88
89 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(-1 -1)'), ST_GEOMFROMTEXT('POINT(0 0)')));
90
91 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 10)'), ST_GEOMFROMTEXT('POINT(10 0)')));
92
93 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(10 0)'), ST_GEOMFROMTEXT('POINT(0 10)')));
94
95 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(10 0)'), ST_GEOMFROMTEXT('POINT(10 0)')));
96
97 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(100 -100)'), ST_GEOMFROMTEXT('POINT(100.00 -100.000)')));
98
99 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(0 0)')));
100
101 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 1)')));
102
103 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(1 0)'), ST_GEOMFROMTEXT('POINT(0 0)')));
104
105 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(1 1)'), ST_GEOMFROMTEXT('POINT(-1 -1)')));
106
107 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0.0 -0.0)'), ST_GEOMFROMTEXT('POINT(0 0)')));
108
109 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(1 1)'), ST_GEOMFROMTEXT('POINT(1 -100)')));
110
111 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(1e100 -1e100)'), ST_GEOMFROMTEXT('POINT(0 0)')));
112
113 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(1e100 -1e100)'), ST_GEOMFROMTEXT('POINT(1e100 -1e100)')));
114
115 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(1e100 1e100)'), ST_GEOMFROMTEXT('POINT(-1e100 -1e100)')));
116
117 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(-1e100 -1e100)'), ST_GEOMFROMTEXT('POINT(1e100 -1e100)')));
118
119 --echo #####################################################################################
120 --echo # Invalid function calls
121 --echo #####################################################################################
122
123 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
124 SELECT ST_ASTEXT(ST_MAKEENVELOPE());
125
126 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
127 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(10 10)')));
128
129 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(10 10)'),NULL));
130
131 SELECT ST_ASTEXT(ST_MAKEENVELOPE(NULL,ST_GEOMFROMTEXT('POINT(10 10)')));
132
133 SELECT ST_ASTEXT(ST_MAKEENVELOPE(NULL,ST_GEOMFROMTEXT(@star_top)));
134
135 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT(@star_of_elems),NULL));
136
137 SELECT ST_ASTEXT(ST_MAKEENVELOPE(NULL,NULL));
138
139 --error ER_GIS_INVALID_DATA
140 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(a 0)'),ST_GEOMFROMTEXT('POINT(0 0)')));
141
142 --error ER_GIS_INVALID_DATA
143 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(! 0)'),ST_GEOMFROMTEXT('POINT(0 0)')));
144
145 --error ER_PARSE_ERROR
146 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT('!' 0)'),ST_GEOMFROMTEXT('POINT(0 0)')));
147
148 --error ER_GIS_INVALID_DATA
149 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(12,34 0)'),ST_GEOMFROMTEXT('POINT(0 0)')));
150
151 --error ER_PARSE_ERROR
152 SELECT ST_ASTEXT(ST_MAKEENVELOPE(,));
153
154 --error ER_PARSE_ERROR
155 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT(@star_of_elems),));
156
157 --error ER_PARSE_ERROR
158 SELECT ST_ASTEXT(ST_MAKEENVELOPE(,ST_GEOMFROMTEXT(@star_top)));
159
160 --error ER_DATA_OUT_OF_RANGE
161 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 0)', -1024), ST_GEOMFROMTEXT('POINT(-1 1)', -1024)));
162
163 --error ER_DATA_OUT_OF_RANGE
164 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 0)', -1), ST_GEOMFROMTEXT('POINT(-1 1)', -1)));
165
166 --error ER_GIS_DIFFERENT_SRIDS
167 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 0)', 0), ST_GEOMFROMTEXT('POINT(-1 1)', 4145)));
168
169 --error ER_GIS_DIFFERENT_SRIDS
170 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 1)', 0), ST_GEOMFROMTEXT('POINT(0 0)', 4236)));
171
172 --error ER_WRONG_ARGUMENTS
173 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('MULTIPOINT(0 0)')));
174
175 --error ER_WRONG_ARGUMENTS
176 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('MULTIPOINT(0 1)'), ST_GEOMFROMTEXT('POINT(0 0)')));
177
178 --error ER_WRONG_ARGUMENTS
179 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('MULTIPOINT(0 1)'), ST_GEOMFROMTEXT('MULTIPOINT(0 0)')));
180
181 --error ER_WRONG_ARGUMENTS
182 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
183
184 --error ER_WRONG_ARGUMENTS
185 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('POLYGON((0 0,0 5,5 5,5 0,0 0))')));
186
187 --error ER_WRONG_ARGUMENTS
188 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))')));
189
190 --error ER_WRONG_ARGUMENTS
191 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1))'), ST_GEOMFROMTEXT('POINT(0 0)')));
192
193 --error ER_WRONG_ARGUMENTS
194 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()')));
195
196 --error ER_WRONG_ARGUMENTS
197 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(0 0)))')));
198
199 --error ER_WRONG_ARGUMENTS
200 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),GEOMETRYCOLLECTION())')));
201
202 --error ER_WRONG_ARGUMENTS
203 SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()')));
204
205 --error ER_GIS_DIFFERENT_SRIDS
206 SELECT ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 0)', 4326), ST_GEOMFROMTEXT('POINT(1 1)'));
207
208 --error ER_GIS_INVALID_DATA
209 SELECT ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1e1000)'));
210
211 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
212 SELECT ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0 0)'));
213
214 --echo # Clean up
215 DROP TABLE gis_geometrycollection;
216
217 --echo #
218 --echo # WL#8579 Spatial Reference Systems
219 --echo #
220
221 --echo # SRID 0 (should pass)
222 DO ST_MAKEENVELOPE(
223 ST_GEOMFROMTEXT('POINT(0 0)', 0),
224 ST_GEOMFROMTEXT('POINT(0 0)', 0)
225 );
226
227 --echo # Projected SRS (should pass)
228 DO ST_MAKEENVELOPE(
229 ST_GEOMFROMTEXT('POINT(0 0)', 2000),
230 ST_GEOMFROMTEXT('POINT(0 0)', 2000)
231 );
232
233 --echo # Geographic SRS (should fail)
234 --error ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS
235 DO ST_MAKEENVELOPE(
236 ST_GEOMFROMTEXT('POINT(0 0)', 4326),
237 ST_GEOMFROMTEXT('POINT(0 0)', 4326)
238 );
239
240 --echo #
241 --echo # WL#11096 Don't do Cartesian computations on geographic geometries
242 --echo #
243
244 --echo # Assume SRID 10 is not defined.
245 --error ER_SRS_NOT_FOUND
246 DO ST_MAKEENVELOPE(
247 x'0A000000010100000000000000000000000000000000000000',
248 x'0A000000010100000000000000000000000000000000000000'
249 );