Debian Codegraph @

mysql-test/suite/gis/r/spatial_analysis_functions_area.result

raw
1 SELECT ST_AREA(NULL);
2 ST_AREA(NULL)
3 NULL
4 DO ST_AREA('');
5 ERROR 22023: Invalid GIS data provided to function st_area.
6 DO ST_AREA(ST_GEOMFROMTEXT('POINT(0 0)'));
7 ERROR 22S01: POLYGON/MULTIPOLYGON value is a geometry of unexpected type POINT in st_area.
8 DO ST_AREA(ST_GEOMFROMTEXT('MULTIPOINT(0 0)'));
9 ERROR 22S01: POLYGON/MULTIPOLYGON value is a geometry of unexpected type MULTIPOINT in st_area.
10 DO ST_AREA(ST_GEOMFROMTEXT('LINESTRING(0 0, 1 0)'));
11 ERROR 22S01: POLYGON/MULTIPOLYGON value is a geometry of unexpected type LINESTRING in st_area.
12 DO ST_AREA(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 0))'));
13 ERROR 22S01: POLYGON/MULTIPOLYGON value is a geometry of unexpected type MULTILINESTRING in st_area.
14 DO ST_AREA(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()'));
15 ERROR 22S01: POLYGON/MULTIPOLYGON value is a geometry of unexpected type GEOMCOLLECTION in st_area.
16 DO ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1e308, 1e308 1e308, 0 0))'));
17 ERROR 22003: Result value is out of range in 'st_area'
18 SELECT
19 ROUND(
20 ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 0))', 0)),
21 2
22 ) AS area;
23 area
24 0.5
25 SELECT
26 ROUND(
27 ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 1 0, 1 1, 0 0)))', 0)),
28 2
29 ) AS area;
30 area
31 0.5
32 SELECT
33 ROUND(
34 ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 4326)),
35 2
36 ) AS area;
37 area
38 6154855102.02
39 SELECT
40 ROUND(
41 ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 4326)),
42 2
43 ) AS area;
44 area
45 6154855102.02
46 # Creating the spatial Geometry object
47 USE test;
48 CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
49 SET @star_elem_vertical= 'POLYGON((5 0,15 25,25 0,15 5,5 0))';
50 SET @star_elem_horizontal= 'POLYGON((25 0,0 15,30 15,22 10,25 0))';
51 SET @star_center= 'POINT(15 10)';
52 SET @star_top= 'POINT(15 25)';
53 SET @star_bottom_left= 'POINT(5 0)';
54 SET @star_bottom_right= 'POINT(25 0)';
55 SET @star_bottom_points= 'MULTIPOINT(5 0,25 0)';
56 SET @star_all_points= 'MULTIPOINT(5 0,25 0,15 10,15 25)';
57 SET @star_line_horizontal= 'LINESTRING(10 15,20 15)';
58 SET @star_line_vertical= 'LINESTRING(15 5,15 25)';
59 SET @star_top_to_center= 'LINESTRING(15 25,15 10)';
60 SET @star_lines_near_horizontal= 'MULTILINESTRING((25 0,0 15,15 30,0 5))';
61 SET @star_lines_near_vertical= 'MULTILINESTRING((0 5,15 25,0 25))';
62 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))';
63 SET @star_elem_vertical_val= '((5 0,15 25,25 0,15 5,5 0))';
64 SET @star_elem_horizontal_val= '((25 0,0 15,30 15,22 10,25 0))';
65 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)))';
66 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))';
67 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))';
68 # INSERT base star
69 INSERT INTO gis_geometrycollection VALUES
70 (100,ST_GEOMFROMTEXT(@star)),
71 (101,ST_GEOMFROMTEXT(@star_elem_vertical)),
72 (102,ST_GEOMFROMTEXT(@star_elem_horizontal)),
73 (103,ST_GEOMFROMTEXT(@star_of_elems)),
74 (104,ST_GEOMFROMTEXT(@star_top)),
75 (105,ST_GEOMFROMTEXT(@star_center)),
76 (106,ST_GEOMFROMTEXT(@star_bottom_left)),
77 (107,ST_GEOMFROMTEXT(@star_bottom_right)),
78 (108,ST_GEOMFROMTEXT(@star_bottom_points)),
79 (109,ST_GEOMFROMTEXT(@star_all_points)),
80 (110,ST_GEOMFROMTEXT(@star_line_horizontal)),
81 (111,ST_GEOMFROMTEXT(@star_line_vertical)),
82 (112,ST_GEOMFROMTEXT(@star_top_to_center)),
83 (113,ST_GEOMFROMTEXT(@star_lines_near_horizontal)),
84 (114,ST_GEOMFROMTEXT(@star_lines_near_vertical)),
85 (115,ST_GEOMFROMTEXT(@star_collection_elems)),
86 (116,ST_GEOMFROMTEXT(@star_collection_multilinestr));
87 # Checking the integrity of the above create/insert statements
88 # 17 rows.
89 SELECT count(ST_ASTEXT(g) != 'NULL') FROM gis_geometrycollection;
90 count(ST_ASTEXT(g) != 'NULL')
91 17
92 #####################################################################################
93 # ST_AREA(polygon)
94 #####################################################################################
95 SELECT ST_AREA(ST_GEOMFROMTEXT(@star));
96 ST_AREA(ST_GEOMFROMTEXT(@star))
97 287.5
98 SELECT ST_AREA(ST_GEOMFROMTEXT(@star_elem_vertical));
99 ST_AREA(ST_GEOMFROMTEXT(@star_elem_vertical))
100 200
101 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,0 5,5 5, 0 0))'));
102 ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,0 5,5 5, 0 0))'))
103 12.5
104 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0))'));
105 ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0))'))
106 100
107 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0,-10 0,-10 -10,0 -10, 0 0))'));
108 ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0,-10 0,-10 -10,0 -10, 0 0))'))
109 0
110 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))'));
111 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))'))
112 96
113 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))'));
114 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))'))
115 0
116 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))'));
117 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))'))
118 -96
119 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))'));
120 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))'))
121 0
122 SELECT ST_AREA(g) FROM gis_geometrycollection WHERE fid=100;
123 ST_AREA(g)
124 287.5
125 #####################################################################################
126 # ST_AREA(multipolygon)
127 #####################################################################################
128 SELECT ST_AREA(ST_GEOMFROMTEXT(@star_of_elems));
129 ST_AREA(ST_GEOMFROMTEXT(@star_of_elems))
130 377.5
131 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5, 0 0)))'));
132 ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5, 0 0)))'))
133 12.5
134 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 10,10 0,0 0, 0 10)),((0 0,10 10,10 0, 0 0)))'));
135 ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 10,10 0,0 0, 0 10)),((0 0,10 10,10 0, 0 0)))'))
136 100
137 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10, 0 0)),((10 10,10 15,15 15,10 10)))'));
138 ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10, 0 0)),((10 10,10 15,15 15,10 10)))'))
139 62.5
140 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)))'));
141 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)))'))
142 104
143 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)))'));
144 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)))'))
145 96
146 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)))'));
147 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)))'))
148 0
149 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)))'));
150 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)))'))
151 -96
152 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)))'));
153 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)))'))
154 0
155 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((10 10,10 0,5 10,10 10)),((0 10,0 20,5 10,0 10)))'));
156 ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((10 10,10 0,5 10,10 10)),((0 10,0 20,5 10,0 10)))'))
157 50
158 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)))'));
159 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)))'))
160 100
161 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)))'));
162 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)))'))
163 98
164 SELECT ST_AREA(g) FROM gis_geometrycollection WHERE fid=103;
165 ST_AREA(g)
166 377.5
167 #####################################################################################
168 # ST_AREA(geometry) with different SRID values
169 #####################################################################################
170 SELECT ST_AREA(ST_GEOMFROMTEXT(@star_center,-1024));
171 ERROR 22003: SRID value is out of range in 'st_geomfromtext'
172 SELECT ST_AREA(ST_GEOMFROMTEXT(@star_all_points,-1));
173 ERROR 22003: SRID value is out of range in 'st_geomfromtext'
174 SELECT ST_AREA(ST_GEOMFROMTEXT(@star_of_elems,4294967296));
175 ERROR 22003: SRID value is out of range in 'st_geomfromtext'
176 SELECT ST_AREA(ST_GEOMFROMTEXT(@star_collection_elems,4294967295000));
177 ERROR 22003: SRID value is out of range in 'st_geomfromtext'
178 #####################################################################################
179 # Invalid function calls
180 #####################################################################################
181 SELECT ST_AREA();
182 ERROR 42000: Incorrect parameter count in the call to native function 'ST_AREA'
183 SELECT ST_AREA(NULL);
184 ST_AREA(NULL)
185 NULL
186 SELECT ST_AREA(ST_GEOMFROMTEXT('POINT()'));
187 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
188 SELECT ST_AREA(ST_GEOMFROMTEXT('LINESTRING()'));
189 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
190 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON(())'));
191 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
192 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOINT()'));
193 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
194 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTILINESTRING(())'));
195 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
196 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON((()))'));
197 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
198 SELECT ST_AREA(ST_GEOMFROMTEXT('POINT(a 0)'));
199 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
200 SELECT ST_AREA(ST_GEOMFROMTEXT('POINT(! 0)'));
201 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
202 SELECT ST_AREA(ST_GEOMFROMTEXT('POINT('!' 0)'));
203 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '!' 0)'))' at line 1
204 SELECT ST_AREA(ST_GEOMFROMTEXT('POINT(12,34 0)'));
205 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
206 SELECT ST_AREA(ST_GEOMFROMTEXT('LINESTRING(a 0,10 10)'));
207 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
208 SELECT ST_AREA(ST_GEOMFROMTEXT('LINESTRING(0 0,! 10)'));
209 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
210 SELECT ST_AREA(ST_GEOMFROMTEXT('LINESTRING('!' 0,10 10)'));
211 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '!' 0,10 10)'))' at line 1
212 SELECT ST_AREA(ST_GEOMFROMTEXT('LINESTRING(12,34 0,10 10)'));
213 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
214 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((a 0,10 10,10 0,0 0))'));
215 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
216 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,! 10,10 0,0 0))'));
217 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
218 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON(('!' 0,10 10,10 0,0 0))'));
219 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '!' 0,10 10,10 0,0 0))'))' at line 1
220 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((12,34 0,10 10,10 0,0 0))'));
221 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
222 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0))'));
223 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
224 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,5 5))'));
225 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
226 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,5 5,10 10))'));
227 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
228 SELECT ST_AREA(ST_GEOMFROMTEXT('POLYGON((0 0,0 5,10 10),(0 0))'));
229 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
230 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,5 5,10 10)),((10 10)))'));
231 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
232 SELECT ST_AREA(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,5 5,10 10)),((10 10))'));
233 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
234 # Clean up
235 DROP TABLE gis_geometrycollection;
236 #
237 # WL#11096 Don't do Cartesian computations on geographic geometries
238 #
239 # Assume SRID 10 is not defined.
240 DO ST_AREA(x'0A000000010100000000000000000000000000000000000000');
241 ERROR SR001: There's no spatial reference system with SRID 10.
242 #
243 # Bug#28301552 ASSERTION FAILED: STD::ISFINITE(*RESULT)
244 #
245 DO ST_Area(
246 POLYGON(
247 LINESTRING(
248 POINT(4.271317e+307,-18656),
249 POINT(-29177,-15475),
250 POINT(8554,3430),
251 POINT(4.271317e+307,-18656)
252 )
253 )
254 );
255 ERROR 22003: Result value is out of range in 'st_area'
256 DO ST_Area(POLYGON(
257 LINESTRING(
258 POINT(3006,-15963),
259 POINT(-17919,-15105),
260 POINT(9.466488e+307,1.280303e+308),
261 POINT(3006,-15963)
262 )
263 )
264 );
265 ERROR 22003: Result value is out of range in 'st_area'