Debian Codegraph @

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

raw
1 # Top-level coverage tests for ST_AREA function.
2
3 # NULL handling
4 SELECT ST_AREA(NULL);
5
6 # Test error forwarding from geometry parsing.
7 --error ER_GIS_INVALID_DATA
8 DO ST_AREA('');
9
10 # Test error on unsupported types.
11 --error ER_UNEXPECTED_GEOMETRY_TYPE
12 DO ST_AREA(ST_GEOMFROMTEXT('POINT(0 0)'));
13 --error ER_UNEXPECTED_GEOMETRY_TYPE
14 DO ST_AREA(ST_GEOMFROMTEXT('MULTIPOINT(0 0)'));
15 --error ER_UNEXPECTED_GEOMETRY_TYPE
16 DO ST_AREA(ST_GEOMFROMTEXT('LINESTRING(0 0, 1 0)'));
17 --error ER_UNEXPECTED_GEOMETRY_TYPE
18 DO ST_AREA(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 0))'));
19 --error ER_UNEXPECTED_GEOMETRY_TYPE
20 DO ST_AREA(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()'));
21
22
23 # Test error due to numeric overflow.
24 --error ER_DATA_OUT_OF_RANGE
25 DO ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1e308, 1e308 1e308, 0 0))'));
26
27 # Excercise code on supported types.
28 # Cartesian
29 SELECT
30 ROUND(
31 ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 0))', 0)),
32 2
33 ) AS area;
34 SELECT
35 ROUND(
36 ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 1 0, 1 1, 0 0)))', 0)),
37 2
38 ) AS area;
39 # Geographic
40 SELECT
41 ROUND(
42 ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 4326)),
43 2
44 ) AS area;
45 SELECT
46 ROUND(
47 ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 4326)),
48 2
49 ) AS area;
50
51 # End of top-level coverage test.
52
53 #########################################################################################
54 # #
55 # This test is aimed to focus on Spatial Analysis Functions. #
56 # #
57 # ST_Area function returns the area of the geometry if it is a polygon or #
58 # multi-polygon or geometry-collection containing polygon or multi-polygon or both. #
59 # #
60 # Creation Date: 2014-06-03 #
61 # Author : Pavan Naik #
62 # #
63 #########################################################################################
64
65
66 ############################################################################################
67 # Creating the spatial objects #
68 ############################################################################################
69
70 --echo # Creating the spatial Geometry object
71 USE test;
72
73 CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
74
75 ############################################################################################
76 # Inserting the values specific to the spatial objects #
77 ############################################################################################
78
79 # Geometric elements constructing a star and some of its elememts as base
80 SET @star_elem_vertical= 'POLYGON((5 0,15 25,25 0,15 5,5 0))';
81 SET @star_elem_horizontal= 'POLYGON((25 0,0 15,30 15,22 10,25 0))';
82 SET @star_center= 'POINT(15 10)';
83 SET @star_top= 'POINT(15 25)';
84 SET @star_bottom_left= 'POINT(5 0)';
85 SET @star_bottom_right= 'POINT(25 0)';
86 SET @star_bottom_points= 'MULTIPOINT(5 0,25 0)';
87 SET @star_all_points= 'MULTIPOINT(5 0,25 0,15 10,15 25)';
88 SET @star_line_horizontal= 'LINESTRING(10 15,20 15)';
89 SET @star_line_vertical= 'LINESTRING(15 5,15 25)';
90 SET @star_top_to_center= 'LINESTRING(15 25,15 10)';
91 SET @star_lines_near_horizontal= 'MULTILINESTRING((25 0,0 15,15 30,0 5))';
92 SET @star_lines_near_vertical= 'MULTILINESTRING((0 5,15 25,0 25))';
93 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))';
94 SET @star_elem_vertical_val= '((5 0,15 25,25 0,15 5,5 0))';
95 SET @star_elem_horizontal_val= '((25 0,0 15,30 15,22 10,25 0))';
96 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)))';
97 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))';
98 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))';
99
100 --echo # INSERT base star
101 --disable_warnings
102 INSERT INTO gis_geometrycollection VALUES
103 (100,ST_GEOMFROMTEXT(@star)),
104 (101,ST_GEOMFROMTEXT(@star_elem_vertical)),
105 (102,ST_GEOMFROMTEXT(@star_elem_horizontal)),
106 (103,ST_GEOMFROMTEXT(@star_of_elems)),
107 (104,ST_GEOMFROMTEXT(@star_top)),
108 (105,ST_GEOMFROMTEXT(@star_center)),
109 (106,ST_GEOMFROMTEXT(@star_bottom_left)),
110 (107,ST_GEOMFROMTEXT(@star_bottom_right)),
111 (108,ST_GEOMFROMTEXT(@star_bottom_points)),
112 (109,ST_GEOMFROMTEXT(@star_all_points)),
113 (110,ST_GEOMFROMTEXT(@star_line_horizontal)),
114 (111,ST_GEOMFROMTEXT(@star_line_vertical)),
115 (112,ST_GEOMFROMTEXT(@star_top_to_center)),
116 (113,ST_GEOMFROMTEXT(@star_lines_near_horizontal)),
117 (114,ST_GEOMFROMTEXT(@star_lines_near_vertical)),
118 (115,ST_GEOMFROMTEXT(@star_collection_elems)),
119 (116,ST_GEOMFROMTEXT(@star_collection_multilinestr));
120 --enable_warnings
121
122 --echo # Checking the integrity of the above create/insert statements
123
124 --echo # 17 rows.
125 SELECT count(ST_ASTEXT(g) != 'NULL') FROM gis_geometrycollection;
126
127 --echo #####################################################################################
128 --echo # ST_AREA(polygon)
129 --echo #####################################################################################
130
131 SELECT ST_AREA(ST_GEOMFROMTEXT(@star));
132
133 SELECT ST_AREA(ST_GEOMFROMTEXT(@star_elem_vertical));
134
135 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,0 5,5 5, 0 0))'));
136
137 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0))'));
138
139 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0,-10 0,-10 -10,0 -10, 0 0))'));
140
141 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))'));
142
143 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(0 0,0 10,10 10,10 0,0 0))'));
144
145 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((4 4,4 6,6 6,6 4,4 4),(0 0,0 10,10 10,10 0,0 0))'));
146
147 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((4 4,4 6,6 6,6 4,4 4),(8 8,8 10,10 10,10 8,8 8))'));
148
149 SELECT ST_AREA(g) FROM gis_geometrycollection WHERE fid=100;
150
151 --echo #####################################################################################
152 --echo # ST_AREA(multipolygon)
153 --echo #####################################################################################
154
155 SELECT ST_AREA(ST_GEOMFROMTEXT(@star_of_elems));
156
157 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5, 0 0)))'));
158
159 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 10,10 0,0 0, 0 10)),((0 0,10 10,10 0, 0 0)))'));
160
161 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10, 0 0)),((10 10,10 15,15 15,10 10)))'));
162
163 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0)),((4 4,4 6,6 6,6 4, 4 4)))'));
164
165 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10,10 0, 0 0),(4 4,4 6,6 6,6 4, 4 4)))'));
166
167 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0),(0 0,0 10,10 10,10 0,0 0)))'));
168
169 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((4 4,4 6,6 6,6 4,4 4),(0 0,0 10,10 10,10 0,0 0)))'));
170
171 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((4 4,4 6,6 6,6 4,4 4),(8 8,8 10,10 10,10 8,8 8)))'));
172
173 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((10 10,10 0,5 10,10 10)),((0 10,0 20,5 10,0 10)))'));
174
175 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0),(0 0,0 10,10 10,10 0,0 0)),((0 0,0 10,10 10,10 0,0 0)))'));
176
177 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0),(0 0,0 10,10 10,10 0,0 0)),((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6, 4 4)))'));
178
179 SELECT ST_AREA(g) FROM gis_geometrycollection WHERE fid=103;
180
181 --echo #####################################################################################
182 --echo # ST_AREA(geometry) with different SRID values
183 --echo #####################################################################################
184
185 --error ER_DATA_OUT_OF_RANGE
186 SELECT ST_AREA(ST_GEOMFROMTEXT(@star_center,-1024));
187
188 --error ER_DATA_OUT_OF_RANGE
189 SELECT ST_AREA(ST_GEOMFROMTEXT(@star_all_points,-1));
190
191 --error ER_DATA_OUT_OF_RANGE
192 SELECT ST_AREA(ST_GEOMFROMTEXT(@star_of_elems,4294967296));
193
194 --error ER_DATA_OUT_OF_RANGE
195 SELECT ST_AREA(ST_GEOMFROMTEXT(@star_collection_elems,4294967295000));
196
197 --echo #####################################################################################
198 --echo # Invalid function calls
199 --echo #####################################################################################
200
201 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
202 SELECT ST_AREA();
203
204 SELECT ST_AREA(NULL);
205
206 --error ER_GIS_INVALID_DATA
207 SELECT ST_AREA(ST_GEOMFROMTEXT('POINT()'));
208
209 --error ER_GIS_INVALID_DATA
210 SELECT ST_AREA(ST_GEOMFROMTEXT('LINESTRING()'));
211
212 --error ER_GIS_INVALID_DATA
213 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON(())'));
214
215 --error ER_GIS_INVALID_DATA
216 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOINT()'));
217
218 --error ER_GIS_INVALID_DATA
219 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTILINESTRING(())'));
220
221 --error ER_GIS_INVALID_DATA
222 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON((()))'));
223
224 --error ER_GIS_INVALID_DATA
225 SELECT ST_AREA(ST_GEOMFROMTEXT('POINT(a 0)'));
226
227 --error ER_GIS_INVALID_DATA
228 SELECT ST_AREA(ST_GEOMFROMTEXT('POINT(! 0)'));
229
230 --error ER_PARSE_ERROR
231 SELECT ST_AREA(ST_GEOMFROMTEXT('POINT('!' 0)'));
232
233 --error ER_GIS_INVALID_DATA
234 SELECT ST_AREA(ST_GEOMFROMTEXT('POINT(12,34 0)'));
235
236 --error ER_GIS_INVALID_DATA
237 SELECT ST_AREA(ST_GEOMFROMTEXT('LINESTRING(a 0,10 10)'));
238
239 --error ER_GIS_INVALID_DATA
240 SELECT ST_AREA(ST_GEOMFROMTEXT('LINESTRING(0 0,! 10)'));
241
242 --error ER_PARSE_ERROR
243 SELECT ST_AREA(ST_GEOMFROMTEXT('LINESTRING('!' 0,10 10)'));
244
245 --error ER_GIS_INVALID_DATA
246 SELECT ST_AREA(ST_GEOMFROMTEXT('LINESTRING(12,34 0,10 10)'));
247
248 --error ER_GIS_INVALID_DATA
249 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((a 0,10 10,10 0,0 0))'));
250
251 --error ER_GIS_INVALID_DATA
252 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,! 10,10 0,0 0))'));
253
254 --error ER_PARSE_ERROR
255 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON(('!' 0,10 10,10 0,0 0))'));
256
257 --error ER_GIS_INVALID_DATA
258 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((12,34 0,10 10,10 0,0 0))'));
259
260 --error ER_GIS_INVALID_DATA
261 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0))'));
262
263 --error ER_GIS_INVALID_DATA
264 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,5 5))'));
265
266 --error ER_GIS_INVALID_DATA
267 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,5 5,10 10))'));
268
269 --error ER_GIS_INVALID_DATA
270 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,0 5,10 10),(0 0))'));
271
272 --error ER_GIS_INVALID_DATA
273 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,5 5,10 10)),((10 10)))'));
274
275 --error ER_GIS_INVALID_DATA
276 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,5 5,10 10)),((10 10))'));
277
278 --echo # Clean up
279 DROP TABLE gis_geometrycollection;
280
281 --echo #
282 --echo # WL#11096 Don't do Cartesian computations on geographic geometries
283 --echo #
284
285 --echo # Assume SRID 10 is not defined.
286 --error ER_SRS_NOT_FOUND
287 DO ST_AREA(x'0A000000010100000000000000000000000000000000000000');
288
289 --echo #
290 --echo # Bug#28301552 ASSERTION FAILED: STD::ISFINITE(*RESULT)
291 --echo #
292 --error ER_DATA_OUT_OF_RANGE
293 DO ST_Area(
294 POLYGON(
295 LINESTRING(
296 POINT(4.271317e+307,-18656),
297 POINT(-29177,-15475),
298 POINT(8554,3430),
299 POINT(4.271317e+307,-18656)
300 )
301 )
302 );
303
304 --error ER_DATA_OUT_OF_RANGE
305 DO ST_Area(POLYGON(
306 LINESTRING(
307 POINT(3006,-15963),
308 POINT(-17919,-15105),
309 POINT(9.466488e+307,1.280303e+308),
310 POINT(3006,-15963)
311 )
312 )
313 );