Debian Codegraph @

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

raw
1 ###############################################################################
2 # #
3 # This test is aimed to focus on Operators creating Geometry objects. #
4 # #
5 # These operators take 2 geometries as input parameters and return #
6 # a new geometry object. #
7 # #
8 # Creation Date: 2014-02-20 #
9 # Author : Horst Hunger #
10 # #
11 ###############################################################################
12
13
14 ############################################################################################
15 # Creating the spatial objects #
16 ############################################################################################
17
18 --echo #Creating the spatial Geometry object
19 USE test;
20
21 CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
22
23 ############################################################################################
24 # Inserting the values specific to the spatial objects #
25 ############################################################################################
26
27 # Geometric elements constructing a star and some of its elememts as base
28 SET @star_elem_vertical= 'POLYGON((5 0,15 25,25 0,15 5,5 0))';
29 SET @star_elem_horizontal= 'POLYGON((25 0,0 15,30 15,22 10,25 0))';
30 SET @star_center= 'POINT(15 10)';
31 SET @star_top= 'POINT(15 25)';
32 SET @star_bottom_left= 'POINT(5 0)';
33 SET @star_bottom_right= 'POINT(25 0)';
34 SET @star_bottom_points= 'MULTIPOINT(5 0,25 0)';
35 SET @star_all_points= 'MULTIPOINT(5 0,25 0,15 10,15 25)';
36 SET @star_line_horizontal= 'LINESTRING(10 15,20 15)';
37 SET @star_line_vertical= 'LINESTRING(15 5,15 25)';
38 SET @star_top_to_center= 'LINESTRING(15 25,15 10)';
39 SET @star_lines_near_horizontal= 'MULTILINESTRING((25 0,0 15,15 30,0 5))';
40 SET @star_lines_near_vertical= 'MULTILINESTRING((0 5,15 25,0 25))';
41 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))';
42 SET @star_elem_vertical_val= '((5 0,15 25,25 0,15 5,5 0))';
43 SET @star_elem_horizontal_val= '((25 0,0 15,30 15,22 10,25 0))';
44 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)))';
45 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))';
46 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))';
47
48 # Star identical to the base star
49 SET @star_elem_vertical_1= 'POLYGON((5 0,15 25,25 0,15 5,5 0))';
50 SET @star_elem_horizontal_1= 'POLYGON((25 0,0 15,30 15,22 10,25 0))';
51 SET @star_center_1= 'POINT(15 10)';
52 SET @star_top_1= 'POINT(15 25)';
53 SET @star_bottom_left_1= 'POINT(5 0)';
54 SET @star_bottom_right_1= 'POINT(25 0)';
55 SET @star_bottom_points_1= 'MULTIPOINT(5 0,25 0)';
56 SET @star_all_points_1= 'MULTIPOINT(5 0,25 0,15 10,15 25)';
57 SET @star_line_horizontal_1= 'LINESTRING(10 15,20 15)';
58 SET @star_line_vertical_1= 'LINESTRING(15 5,15 25)';
59 SET @star_top_to_center_1= 'LINESTRING(15 25,15 10)';
60 SET @star_lines_near_horizontal_1= 'MULTILINESTRING((25 0,0 15,15 30,0 5))';
61 SET @star_lines_near_vertical_1= 'MULTILINESTRING((0 5,15 25,0 25))';
62 SET @star_1= '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_1= '((5 0,15 25,25 0,15 5,5 0))';
64 SET @star_elem_horizontal_val_1= '((25 0,0 15,30 15,22 10,25 0))';
65 SET @star_of_elems_1='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_1='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
68 # Star like the base star, but shifted to right by 10 on X to overlap base star
69 SET @star_elem_vertical_2= 'POLYGON((15 0,25 25,35 0,25 5,15 0))';
70 SET @star_elem_horizontal_2= 'POLYGON((35 0,10 15,40 15,32 10,35 0))';
71 SET @star_center_2= 'POINT(25 10)';
72 SET @star_top_2= 'POINT(25 25)';
73 SET @star_bottom_left_2= 'POINT(15 0)';
74 SET @star_bottom_right_2= 'POINT(35 0)';
75 SET @star_bottom_points_2= 'MULTIPOINT(15 0,35 0)';
76 SET @star_all_points_2= 'MULTIPOINT(15 0,35 0,35 10,25 25)';
77 SET @star_line_horizontal_2= 'LINESTRING(20 15,30 15)';
78 SET @star_line_vertical_2= 'LINESTRING(25 5,25 25)';
79 SET @star_top_to_center_2= 'LINESTRING(25 25,25 10)';
80 SET @star_lines_near_horizontal_2= 'MULTILINESTRING((35 0,10 15,25 30,10 5))';
81 SET @star_lines_near_vertical_2= 'MULTILINESTRING((10 5,25 25,10 25))';
82 SET @star_2= 'POLYGON((15 0,17 10,10 15,20 15,25 25,30 15,40 15,32 10,35 0,25 5,15 0))';
83 SET @star_elem_vertical_val_2= '((15 0,25 25,35 0,25 5,15 0))';
84 SET @star_elem_horizontal_val_2= '((35 0,10 15,40 15,32 10,35 0))';
85 SET @star_of_elems_2='MULTIPOLYGON(((15 0,25 25,35 0,25 5,15 0)),((35 0,10 15,40 15,32 10,35 0)))';
86 SET @star_collection_elems_2='GEOMETRYCOLLECTION(MULTIPOLYGON(((15 0,25 25,35 0,25 5,15 0)),((35 0,10 15,40 15,32 10,35 0))),POLYGON((15 0,17 10,10 15,20 15,25 25,30 15,40 15,32 10,35 0,25 5,15 0)),LINESTRING(25 25,25 10),MULTIPOINT(15 0,35 0),POINT(25 25))';
87
88 # Star like the base star, but shifted to right by 60 on X to be completly separate
89 SET @star_elem_vertical_3= 'POLYGON((65 0,75 25,85 0,75 5,65 0))';
90 SET @star_elem_horizontal_3= 'POLYGON((85 0,60 15,90 15,82 10,85 0))';
91 SET @star_center_3= 'POINT(75 10)';
92 SET @star_top_3= 'POINT(75 25)';
93 SET @star_bottom_left_3= 'POINT(65 0)';
94 SET @star_bottom_right_3= 'POINT(85 0)';
95 SET @star_bottom_points_3= 'MULTIPOINT(65 0,85 0)';
96 SET @star_all_points_3= 'MULTIPOINT(65 0,85 0,75 10,75 25)';
97 SET @star_line_horizontal_3= 'LINESTRING(70 15,80 15)';
98 SET @star_line_vertical_3= 'LINESTRING(75 5,75 25)';
99 SET @star_top_to_center_3= 'LINESTRING(75 25,75 10)';
100 SET @star_lines_near_horizontal_3= 'MULTILINESTRING((85 0,60 15,85 30,60 5))';
101 SET @star_lines_near_vertical_3= 'MULTILINESTRING((60 5,75 25,60 25))';
102 SET @star_3= 'POLYGON((65 0,67 10,60 15,70 15,75 25,80 15,90 15,82 10,85 0,75 5,65 0))';
103 SET @star_elem_vertical_val_3= '((65 0,75 25,85 0,75 5,65 0))';
104 SET @star_elem_horizontal_val_3= '((85 0,60 15,90 15,82 10,85 0))';
105 SET @star_of_elems_3='MULTIPOLYGON(((65 0,75 25,85 0,75 5,65 0)),((85 0,60 15,90 15,82 10,85 0)))';
106 SET @star_collection_elems_3='GEOMETRYCOLLECTION(MULTIPOLYGON(((65 0,75 25,85 0,75 5,65 0)),((85 0,60 15,90 15,82 10,85 0))),POLYGON((65 0,67 10,60 15,70 15,75 25,80 15,90 15,82 10,85 0,75 5,65 0)),LINESTRING(75 25,75 10),MULTIPOINT(65 0,85 0),POINT(75 25))';
107
108 --echo #INSERT base star
109 --disable_warnings
110 INSERT INTO gis_geometrycollection VALUES
111 (100,ST_GEOMFROMTEXT(@star)),
112 (101,ST_GEOMFROMTEXT(@star_elem_vertical)),
113 (102,ST_GEOMFROMTEXT(@star_elem_horizontal)),
114 (103,ST_GEOMFROMTEXT(@star_of_elems)),
115 (104,ST_GEOMFROMTEXT(@star_top)),
116 (105,ST_GEOMFROMTEXT(@star_center)),
117 (106,ST_GEOMFROMTEXT(@star_bottom_left)),
118 (107,ST_GEOMFROMTEXT(@star_bottom_right)),
119 (108,ST_GEOMFROMTEXT(@star_bottom_points)),
120 (109,ST_GEOMFROMTEXT(@star_all_points)),
121 (110,ST_GEOMFROMTEXT(@star_line_horizontal)),
122 (111,ST_GEOMFROMTEXT(@star_line_vertical)),
123 (112,ST_GEOMFROMTEXT(@star_top_to_center)),
124 (113,ST_GEOMFROMTEXT(@star_lines_near_horizontal)),
125 (114,ST_GEOMFROMTEXT(@star_lines_near_vertical)),
126 (115,ST_GEOMFROMTEXT(@star_collection_elems));
127 --enable_warnings
128
129 --echo #INSERT identical (to base) star
130 --disable_warnings
131 INSERT INTO gis_geometrycollection VALUES
132 (200,ST_GEOMFROMTEXT(@star_1)),
133 (201,ST_GEOMFROMTEXT(@star_elem_vertical_1)),
134 (202,ST_GEOMFROMTEXT(@star_elem_horizontal_1)),
135 (203,ST_GEOMFROMTEXT(@star_of_elems_1)),
136 (204,ST_GEOMFROMTEXT(@star_top_1)),
137 (205,ST_GEOMFROMTEXT(@star_center_1)),
138 (206,ST_GEOMFROMTEXT(@star_bottom_left_1)),
139 (207,ST_GEOMFROMTEXT(@star_bottom_right_1)),
140 (208,ST_GEOMFROMTEXT(@star_bottom_points_1)),
141 (209,ST_GEOMFROMTEXT(@star_all_points_1)),
142 (210,ST_GEOMFROMTEXT(@star_line_horizontal_1)),
143 (211,ST_GEOMFROMTEXT(@star_line_vertical_1)),
144 (212,ST_GEOMFROMTEXT(@star_top_to_center_1)),
145 (213,ST_GEOMFROMTEXT(@star_lines_near_horizontal_1)),
146 (214,ST_GEOMFROMTEXT(@star_lines_near_vertical_1)),
147 (215,ST_GEOMFROMTEXT(@star_collection_elems_1));
148 --enable_warnings
149
150 --echo #INSERT overlapping star
151 --disable_warnings
152 INSERT INTO gis_geometrycollection VALUES
153 (300,ST_GEOMFROMTEXT(@star_2)),
154 (301,ST_GEOMFROMTEXT(@star_elem_vertical_2)),
155 (302,ST_GEOMFROMTEXT(@star_elem_horizontal_2)),
156 (303,ST_GEOMFROMTEXT(@star_of_elems_2)),
157 (304,ST_GEOMFROMTEXT(@star_top_2)),
158 (305,ST_GEOMFROMTEXT(@star_center_2)),
159 (306,ST_GEOMFROMTEXT(@star_bottom_left_2)),
160 (307,ST_GEOMFROMTEXT(@star_bottom_right_2)),
161 (308,ST_GEOMFROMTEXT(@star_bottom_points_2)),
162 (309,ST_GEOMFROMTEXT(@star_all_points_2)),
163 (310,ST_GEOMFROMTEXT(@star_line_horizontal_2)),
164 (311,ST_GEOMFROMTEXT(@star_line_vertical_2)),
165 (312,ST_GEOMFROMTEXT(@star_top_to_center_2)),
166 (313,ST_GEOMFROMTEXT(@star_lines_near_horizontal_2)),
167 (314,ST_GEOMFROMTEXT(@star_lines_near_vertical_2)),
168 (315,ST_GEOMFROMTEXT(@star_collection_elems_2));
169 --enable_warnings
170
171 --echo #INSERT separate star
172 --disable_warnings
173 INSERT INTO gis_geometrycollection VALUES
174 (400,ST_GEOMFROMTEXT(@star_3)),
175 (401,ST_GEOMFROMTEXT(@star_elem_vertical_3)),
176 (402,ST_GEOMFROMTEXT(@star_elem_horizontal_3)),
177 (403,ST_GEOMFROMTEXT(@star_of_elems_3)),
178 (404,ST_GEOMFROMTEXT(@star_top_3)),
179 (405,ST_GEOMFROMTEXT(@star_center_3)),
180 (406,ST_GEOMFROMTEXT(@star_bottom_left_3)),
181 (407,ST_GEOMFROMTEXT(@star_bottom_right_3)),
182 (408,ST_GEOMFROMTEXT(@star_bottom_points_3)),
183 (409,ST_GEOMFROMTEXT(@star_all_points_3)),
184 (410,ST_GEOMFROMTEXT(@star_line_horizontal_3)),
185 (411,ST_GEOMFROMTEXT(@star_line_vertical_3)),
186 (412,ST_GEOMFROMTEXT(@star_top_to_center_3)),
187 (413,ST_GEOMFROMTEXT(@star_lines_near_horizontal_3)),
188 (414,ST_GEOMFROMTEXT(@star_lines_near_vertical_3)),
189 (415,ST_GEOMFROMTEXT(@star_collection_elems_3));
190 --enable_warnings
191
192 CREATE TABLE gis_geometrycollection_2 SELECT fid as fid2,g as g2 FROM gis_geometrycollection;
193
194 --echo #Checking the integrity of the above create/insert statements
195
196 --echo #64 rows.
197 SELECT count(ST_ASTEXT(g) != 'NULL') FROM gis_geometrycollection;
198 --echo #64 rows.
199 SELECT count(ST_ASTEXT(g2) != 'NULL') FROM gis_geometrycollection_2;
200
201 ############################################################################################
202 # Checking the spatial operator functions #
203 ############################################################################################
204
205 --echo #####################################################################################
206 --echo # ST_DIFFERENCE(g1,g2)
207 --echo #####################################################################################
208
209 --echo #====================================================================================
210 --echo # point,point
211 --echo #====================================================================================
212 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_center),ST_GEOMFROMTEXT(@star_center)));
213
214 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top),ST_GEOMFROMTEXT(@star_center)));
215
216 SELECT ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_center))) FROM gis_geometrycollection WHERE fid=105;
217
218 --echo #====================================================================================
219 --echo # point,multipoint
220 --echo #====================================================================================
221 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_bottom_left),ST_GEOMFROMTEXT(@star_bottom_points)));
222
223 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_center),ST_GEOMFROMTEXT(@star_bottom_points)));
224
225 SELECT ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_bottom_points))) FROM gis_geometrycollection WHERE fid=106;
226
227 --echo #====================================================================================
228 --echo # point,linestring
229 --echo #====================================================================================
230 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top),ST_GEOMFROMTEXT(@star_top_to_center)));
231
232 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_bottom_left),ST_GEOMFROMTEXT(@star_top_to_center)));
233
234 SELECT ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_top_to_center))) FROM gis_geometrycollection WHERE fid=104;
235
236 --echo #====================================================================================
237 --echo # point,multilinestring
238 --echo #====================================================================================
239 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top),ST_GEOMFROMTEXT(@star_lines_near_vertical)));
240
241 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_center),ST_GEOMFROMTEXT(@star_lines_near_vertical)));
242
243 SELECT ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_lines_near_vertical))) FROM gis_geometrycollection WHERE fid=104;
244
245 --echo #====================================================================================
246 --echo # point,polygon
247 --echo #====================================================================================
248 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top),ST_GEOMFROMTEXT(@star)));
249
250 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top_3),ST_GEOMFROMTEXT(@star)));
251
252 SELECT ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star))) FROM gis_geometrycollection WHERE fid=104;
253
254 --echo #====================================================================================
255 --echo # point,multipolygon
256 --echo #====================================================================================
257 --echo #====================================================================================
258 --echo # point,geometrycollection
259 --echo #====================================================================================
260 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()')));
261
262 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))')));
263
264 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(4 4)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(3 3),LINESTRING(0 0,1 1))')));
265
266 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(4 4)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(3 3),POLYGON((0 0,0 5,5 5,5 0,0 0)))')));
267
268 --echo #====================================================================================
269 --echo # multipoint,point
270 --echo #====================================================================================
271 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_top)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(15 10),(25 0))'));
272 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_bottom_points),ST_GEOMFROMTEXT(@star_center)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(25 0))'));
273 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_bottom_left)),ST_GEOMFROMTEXT('POINT(25 0)')) FROM gis_geometrycollection WHERE fid=108;
274 --echo #====================================================================================
275 --echo # multipoint,multipoint
276 --echo #====================================================================================
277 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_bottom_points)),ST_GEOMFROMTEXT('MULTIPOINT((15 10),(15 25))'));
278 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_bottom_points),ST_GEOMFROMTEXT(@star_all_points)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
279 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_all_points_1)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
280 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_all_points)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY')) FROM gis_geometrycollection WHERE fid=108;
281 --echo #====================================================================================
282 --echo # multipoint,linestring
283 --echo #====================================================================================
284 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_top_to_center)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(25 0))'));
285 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_top_to_center)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(25 0))'));
286 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_top_to_center)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(25 0))')) FROM gis_geometrycollection WHERE fid=109;
287 --echo #====================================================================================
288 --echo # multipoint,multilinestring
289 --echo #====================================================================================
290 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_lines_near_vertical)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(15 10),(25 0))'));
291 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_lines_near_horizontal)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(15 10),(15 25))'));
292 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_lines_near_horizontal)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(15 10),(15 25))')) FROM gis_geometrycollection WHERE fid=109;
293 --echo #====================================================================================
294 --echo # multipoint,polygon
295 --echo #====================================================================================
296 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_elem_vertical)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
297 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_elem_horizontal)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(15 25))'));
298 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_elem_horizontal)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(15 25))')) FROM gis_geometrycollection WHERE fid=109;
299 --echo #====================================================================================
300 --echo # multipoint,multipolygon
301 --echo #====================================================================================
302 --echo #====================================================================================
303 --echo # multipoint,geometrycollection
304 --echo #====================================================================================
305 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTIPOINT(0 0,10 10)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()')),ST_GEOMFROMTEXT('MULTIPOINT((0 0),(10 10))'));
306 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTIPOINT(0 0,1 1,2 2)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))')),ST_GEOMFROMTEXT('MULTIPOINT((0 0),(1 1),(2 2))'));
307 --echo #====================================================================================
308 --echo # linestring,point
309 --echo #====================================================================================
310 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top_to_center),ST_GEOMFROMTEXT(@star_center)),ST_GEOMFROMTEXT('LINESTRING(15 25,15 10)'));
311 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('LINESTRING(15 25,15 10)'),ST_GEOMFROMTEXT('POINT(15 10)')),ST_GEOMFROMTEXT('LINESTRING(15 25,15 10)'));
312 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top_to_center),ST_GEOMFROMTEXT(@star_bottom_left)),ST_GEOMFROMTEXT('LINESTRING(15 25,15 10)'));
313 SELECT ST_EQUALS(ST_DIFFERENCE(g,g2),ST_GEOMFROMTEXT('LINESTRING(10 15,20 15)')) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=110 and fid2=105;
314 --echo #====================================================================================
315 --echo # linestring,multipoint
316 --echo #====================================================================================
317 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_vertical),ST_GEOMFROMTEXT('MULTIPOINT(15 10,15 25)')),ST_GEOMFROMTEXT('LINESTRING(15 5,15 25)'));
318 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_vertical),ST_GEOMFROMTEXT(@star_all_points)),ST_GEOMFROMTEXT('LINESTRING(15 5,15 25)'));
319 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT('MULTIPOINT(15 10,15 25)')),ST_GEOMFROMTEXT('LINESTRING(15 5,15 25)')) FROM gis_geometrycollection WHERE fid=111;
320 --echo #====================================================================================
321 --echo # linestring,linestring
322 --echo #====================================================================================
323 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_vertical),ST_GEOMFROMTEXT(@star_top_to_center)),ST_GEOMFROMTEXT('LINESTRING(15 5,15 10)'));
324 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top_to_center),ST_GEOMFROMTEXT(@star_line_vertical)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
325 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top_to_center),ST_GEOMFROMTEXT(@star_top_to_center_1)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
326 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_top_to_center)),ST_GEOMFROMTEXT('LINESTRING(15 5,15 10)')) FROM gis_geometrycollection WHERE fid=111;
327 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)'),ST_GEOMFROMTEXT('LINESTRING(3 3,4 4,5 5)')),ST_GEOMFROMTEXT('MULTILINESTRING((0 0,3 3),(5 5,10 10))'));
328 --echo #====================================================================================
329 --echo # linestring,multilinestring
330 --echo #====================================================================================
331 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_horizontal),ST_GEOMFROMTEXT(@star_lines_near_horizontal)),ST_GEOMFROMTEXT('LINESTRING(10 15,20 15)'));
332 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_horizontal),ST_GEOMFROMTEXT(@star_lines_near_vertical)),ST_GEOMFROMTEXT('LINESTRING(10 15,20 15)'));
333 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_lines_near_horizontal)),ST_GEOMFROMTEXT('LINESTRING(10 15,20 15)')) FROM gis_geometrycollection WHERE fid=110;
334 --echo #====================================================================================
335 --echo # linestring,polygon
336 --echo #====================================================================================
337 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_horizontal),ST_GEOMFROMTEXT('POLYGON((11 15,19 15,19 10,11 15))')),ST_GEOMFROMTEXT('MULTILINESTRING((10 15,11 15),(19 15,20 15))'));
338 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_horizontal),ST_GEOMFROMTEXT(@star_elem_horizontal)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
339 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT('POLYGON((11 15,19 15,19 10,11 15))')),ST_GEOMFROMTEXT('MULTILINESTRING((10 15,11 15),(19 15,20 15))')) FROM gis_geometrycollection WHERE fid=110;
340 --echo #====================================================================================
341 --echo # linestring,multipolygon
342 --echo #====================================================================================
343 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_horizontal),ST_GEOMFROMTEXT('MULTIPOLYGON(((11 15,19 15,19 25,11 15)))')),ST_GEOMFROMTEXT('MULTILINESTRING((10 15,11 15),(19 15,20 15))'));
344 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT('MULTIPOLYGON(((11 15,19 15,19 25,11 15)),((25 0,0 15,25 10,25 0)))')),ST_GEOMFROMTEXT('MULTILINESTRING((10 15,11 15),(19 15,20 15))')) FROM gis_geometrycollection WHERE fid=110;
345 --echo #====================================================================================
346 --echo # linestring,geometrycollection
347 --echo #====================================================================================
348 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_horizontal),ST_GEOMFROMTEXT(@star_collection_elems_3)),ST_GEOMFROMTEXT('LINESTRING(10 15,20 15)'));
349 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_collection_elems_3)),ST_GEOMFROMTEXT('LINESTRING(10 15,20 15)')) FROM gis_geometrycollection WHERE fid=110;
350 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()')),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)'));
351 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('LINESTRING(0 0,1 1,2 2)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))')),ST_GEOMFROMTEXT('LINESTRING(0 0,1 1,2 2)'));
352 --echo #====================================================================================
353 --echo # multilinestring,point
354 --echo #====================================================================================
355 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_top)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'));
356 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_center)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'));
357 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_top)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)')) FROM gis_geometrycollection WHERE fid=114;
358 --echo #====================================================================================
359 --echo # multilinestring,multipoint
360 --echo #====================================================================================
361 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_bottom_points)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'));
362 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_all_points)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'));
363 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_bottom_points)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)')) FROM gis_geometrycollection WHERE fid=114;
364 SELECT ST_EQUALS(ST_DIFFERENCE(g,g2),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)')) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=114 and fid2=108;
365 --echo #====================================================================================
366 --echo # multilinestring,linestring
367 --echo #====================================================================================
368 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_line_vertical)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'));
369 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_line_horizontal)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'));
370 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_line_vertical)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)')) FROM gis_geometrycollection WHERE fid=114;
371 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))'),ST_GEOMFROMTEXT('LINESTRING(0 0, 1 1)')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
372 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTILINESTRING((0 0,10 10))'),ST_GEOMFROMTEXT('LINESTRING(3 3,4 4,5 5)')),ST_GEOMFROMTEXT('MULTILINESTRING((0 0,3 3),(5 5,10 10))'));
373 --echo #====================================================================================
374 --echo # multilinestring,multilinestring
375 --echo #====================================================================================
376 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT('MULTILINESTRING((0 5,15 25,0 15))')),ST_GEOMFROMTEXT('LINESTRING(15 25,0 25)'));
377 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_lines_near_horizontal)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'));
378 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT('MULTILINESTRING((0 5,15 25,0 15))')),ST_GEOMFROMTEXT('LINESTRING(15 25,0 25)')) FROM gis_geometrycollection WHERE fid=114;
379 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_line_vertical)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)')) FROM gis_geometrycollection WHERE fid=114;
380 --echo #====================================================================================
381 --echo # multilinestring,polygon
382 --echo #====================================================================================
383 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_elem_vertical)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'));
384 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_elem_horizontal)),ST_GEOMFROMTEXT('MULTILINESTRING((0 5,5.172413793103448 11.89655172413793),(7.5 15,15 25,0 25))'));
385 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_elem_vertical)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)')) FROM gis_geometrycollection WHERE fid=114;
386 --echo #====================================================================================
387 --echo # multilinestring,multipolygon
388 --echo #====================================================================================
389 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTILINESTRING((0 25,25 0,25 30))'),ST_GEOMFROMTEXT(@star_elem_vertical)),ST_GEOMFROMTEXT('MULTILINESTRING((0 25,10.714285714285715 14.285714285714286),(25 0,25 30))'));
390 --echo #====================================================================================
391 --echo # multilinestring,geometrycollection
392 --echo #====================================================================================
393 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTILINESTRING((0 0,10 10),(2 2,7 7))'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()')),ST_GEOMFROMTEXT('MULTILINESTRING((0 0,10 10),(2 2,7 7))'));
394 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTILINESTRING((0 0,1 1,2 2))'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION('
395 'GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))')),ST_GEOMFROMTEXT('LINESTRING(0 0,1 1,2 2)'));
396 --echo #====================================================================================
397 --echo # polygon,point
398 --echo #====================================================================================
399 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star),ST_GEOMFROMTEXT(@star_center)),ST_GEOMFROMTEXT('POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0))'));
400 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_elem_vertical),ST_GEOMFROMTEXT('POINT(0 0)')),ST_GEOMFROMTEXT('POLYGON((5 0,15 25,25 0,15 5,5 0))'));
401 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_center)),ST_GEOMFROMTEXT('POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0))')) FROM gis_geometrycollection WHERE fid=100;
402 --echo #====================================================================================
403 --echo # polygon,multipoint
404 --echo #====================================================================================
405 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star),ST_GEOMFROMTEXT(@star_all_points)),ST_GEOMFROMTEXT('POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0))'));
406 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_elem_vertical),ST_GEOMFROMTEXT('MULTIPOINT(0 0,30 25)')),ST_GEOMFROMTEXT('POLYGON((5 0,15 25,25 0,15 5,5 0))'));
407 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_all_points)),ST_GEOMFROMTEXT('POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0))')) FROM gis_geometrycollection WHERE fid=100;
408 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star),ST_GEOMFROMTEXT(@star_all_points)),ST_GEOMFROMTEXT('POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0))'));
409 --echo #====================================================================================
410 --echo # polygon,linestring
411 --echo #====================================================================================
412 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star),ST_GEOMFROMTEXT(@star_line_vertical)),ST_GEOMFROMTEXT('POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0))'));
413 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_elem_vertical),ST_GEOMFROMTEXT(@star_line_horizontal)),ST_GEOMFROMTEXT('POLYGON((5 0,15 25,25 0,15 5,5 0))'));
414 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_line_vertical)),ST_GEOMFROMTEXT('POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0))')) FROM gis_geometrycollection WHERE fid=100;
415 SELECT ST_EQUALS(ST_DIFFERENCE(g,g2),ST_GEOMFROMTEXT('POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0))')) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=100 and fid2=111;
416 --echo #====================================================================================
417 --echo # polygon,multilinestring
418 --echo #====================================================================================
419 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star),ST_GEOMFROMTEXT(@star_lines_near_vertical)),ST_GEOMFROMTEXT('POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0))'));
420 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_elem_vertical),ST_GEOMFROMTEXT(@star_lines_near_horizontal)),ST_GEOMFROMTEXT('POLYGON((5 0,15 25,25 0,15 5,5 0))'));
421 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_lines_near_vertical)),ST_GEOMFROMTEXT('POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0))')) FROM gis_geometrycollection WHERE fid=100;
422 --echo #====================================================================================
423 --echo # polygon,polygon
424 --echo #====================================================================================
425 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star),ST_GEOMFROMTEXT(@star_elem_vertical)),ST_GEOMFROMTEXT('MULTIPOLYGON(((15 25,25 0,22 10,30 15,20 15,15 25)),((15 25,10 15,0 15,7 10,5 0,15 25)))'));
426 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_elem_vertical),ST_GEOMFROMTEXT(@star)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
427 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('POLYGON((0 0, 1 0, 0 1, 0 0))'),ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
428 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_elem_vertical)),ST_GEOMFROMTEXT('MULTIPOLYGON(((15 25,25 0,22 10,30 15,20 15,15 25)),((15 25,10 15,0 15,7 10,5 0,15 25)))')) FROM gis_geometrycollection WHERE fid=100;
429 --echo #====================================================================================
430 --echo # polygon,multipolygon
431 --echo #====================================================================================
432 --echo #====================================================================================
433 --echo # polygon,geometrycollection
434 --echo #====================================================================================
435 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star),ST_GEOMFROMTEXT(@star_collection_elems_3)),ST_GEOMFROMTEXT('POLYGON((5 0,15 5,25 0,22 10,30 15,20 15,15 25,10 15,0 15,7 10,5 0))'));
436 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0))'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()')),ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0))'));
437 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))'),
438 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))')),ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))'));
439 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('POLYGON((4 4,4 6,6 6,6 4,4 4),(0 0,0 10,10 10,10 0,0 0))'),
440 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))')),ST_GEOMFROMTEXT('POLYGON((4 4,4 6,6 6,6 4,4 4),(0 0,0 10,10 10,10 0,0 0))'));
441 --echo #====================================================================================
442 --echo # multipolygon,point
443 --echo #====================================================================================
444 --echo #====================================================================================
445 --echo # multipolygon,multipoint
446 --echo #====================================================================================
447 --echo #====================================================================================
448 --echo # multipolygon,linestring
449 --echo #====================================================================================
450 --echo #====================================================================================
451 --echo # multipolygon,multilinestring
452 --echo #====================================================================================
453 --echo #====================================================================================
454 --echo # multipolygon,polygon
455 --echo #====================================================================================
456 --echo #====================================================================================
457 --echo # multipolygon,multipolygon
458 --echo #====================================================================================
459 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)))'),
460 ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5)))')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
461 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)))'),
462 ST_GEOMFROMTEXT('MULTIPOLYGOn(((5 5,5 10,10 10,10 5,5 5)))')),ST_GEOMFROMTEXT('POLYGON((5 5,0 5,0 0,5 0,5 5))'));
463 --echo #====================================================================================
464 --echo # multipolygon,geometrycollectionn
465 --echo #====================================================================================
466 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0)))'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()')),ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0))'));
467 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4)))'),
468 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))')),
469 ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))'));
470 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTIPOLYGON(((4 4,4 6,6 6,6 4,4 4)),((0 0,0 10,10 10,10 0,0 0)))'),
471 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))')),ST_GEOMFROMTEXT('MULTIPOLYGON(((4 4,4 6,6 6,6 4,4 4)),((0 0,0 10,10 10,10 0,0 0)))'));
472 --echo #====================================================================================
473 --echo # geometrycollection,point
474 --echo #====================================================================================
475 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_top)),ST_GEOMFROMTEXT('POLYGON((5 0,15 5,25 0,22 10,30 15,20 15,15 25,10 15,0 15,7 10,5 0))')) FROM gis_geometrycollection WHERE fid=115;
476 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(3 3),LINESTRING(0 0,1 1))'),ST_GEOMFROMTEXT('POINT(4 4)')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(3 3),LINESTRING(0 0,1 1))'));
477 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(3 3),POLYGON((0 0,0 5,5 5,5 0,0 0)))'),ST_GEOMFROMTEXT('POINT(4 4)')),ST_GEOMFROMTEXT('POLYGON((0 0,5 0,5 5,0 5,0 0))'));
478 --echo #====================================================================================
479 --echo # geometrycollection,multipoint
480 --echo #====================================================================================
481 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_bottom_points)),ST_GEOMFROMTEXT('POLYGON((5 0,15 5,25 0,22 10,30 15,20 15,15 25,10 15,0 15,7 10,5 0))')) FROM gis_geometrycollection WHERE fid=115;
482 --echo #====================================================================================
483 --echo # geometrycollection,linestring
484 --echo #====================================================================================
485 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_top_to_center)),ST_GEOMFROMTEXT('POLYGON((5 0,15 5,25 0,22 10,30 15,20 15,15 25,10 15,0 15,7 10,5 0))')) FROM gis_geometrycollection WHERE fid=115;
486 --echo #====================================================================================
487 --echo # geometrycollection,multilinestring
488 --echo #====================================================================================
489 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_lines_near_vertical)),ST_GEOMFROMTEXT('POLYGON((5 0,15 5,25 0,22 10,30 15,20 15,15 25,10 15,0 15,7 10,5 0))')) FROM gis_geometrycollection WHERE fid=115;
490 --echo #====================================================================================
491 --echo # geometrycollection,polygon
492 --echo #====================================================================================
493 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_elem_vertical)),ST_GEOMFROMTEXT('MULTIPOLYGON(((15 25,25 0,22 10,30 15,20 15,15 25)),((15 25,10 15,0 15,7 10,5 0,15 25)))')) FROM gis_geometrycollection WHERE fid=115;
494 --echo #====================================================================================
495 --echo # geometrycollection,multipolygon
496 --echo #====================================================================================
497 SELECT ST_EQUALS(ST_DIFFERENCE(g,g2),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY')) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=115 and fid2=215;
498 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),POINT(1 1),POINT(2 2))'),ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)))')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
499 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(LINESTRING(0 0,2 2),LINESTRING(4 4,6 6))'),ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5)))')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
500 --echo #====================================================================================
501 --echo # geometrycollection,geometrycollectionn
502 --echo #====================================================================================
503 SELECT ST_EQUALS(ST_DIFFERENCE(g,g2),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY')) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=115 and fid2=215;
504 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(1 1,2 2))'),
505 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),POINT(5 5))')),ST_GEOMFROMTEXT('LINESTRING(1 1,2 2)'));
506 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(2 2),POLYGON((0 0,0 5,5 5,5 0,0 0)))'),
507 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),POINT(5 5))')),ST_GEOMFROMTEXT('POLYGON((0 0,5 0,5 5,0 5,0 0))'));
508 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())'),
509 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())))')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
510 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())'),
511 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(1 1), GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())))')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
512 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(1 1)))'),
513 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())))')),ST_GEOMFROMTEXT('POINT(1 1)'));
514 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(1 1)))'),
515 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(1 1), GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())))')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
516
517 --echo #####################################################################################
518 --echo # Testing ST_DIFFERENCE() with 2 geometries of same SRID
519 --echo #####################################################################################
520 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,0),ST_GEOMFROMTEXT(@star_center,0)));
521
522 DO ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,4145),ST_GEOMFROMTEXT(@star_center,4145)));
523
524 DO ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,4326),ST_GEOMFROMTEXT(@star_center,4326)));
525
526 DO ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,4135),ST_GEOMFROMTEXT(@star_center,4135)));
527
528 --error ER_DATA_OUT_OF_RANGE
529 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,-1),ST_GEOMFROMTEXT(@star_center,-1)));
530
531 --error ER_DATA_OUT_OF_RANGE
532 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,-1024),ST_GEOMFROMTEXT(@star_center,-1024)));
533
534 --error ER_DATA_OUT_OF_RANGE
535 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,4294967295000),ST_GEOMFROMTEXT(@star_center,4294967295000)));
536
537 --echo #####################################################################################
538 --echo # Invalid function calls
539 --echo #####################################################################################
540 --error ER_GIS_DIFFERENT_SRIDS
541 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top),ST_GEOMFROMTEXT(@star_center,4326)));
542
543 --error ER_GIS_DIFFERENT_SRIDS
544 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,4145),ST_GEOMFROMTEXT(@star_center,4326)));
545
546 --error ER_SRS_NOT_FOUND
547 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,1048576),ST_GEOMFROMTEXT(@star_center,1048576)));
548
549 --error ER_SRS_NOT_FOUND
550 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,1048575),ST_GEOMFROMTEXT(@star_center,1048575)));
551
552 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,0),ST_GEOMFROMTEXT(@star_center,0)));
553
554 --error ER_DATA_OUT_OF_RANGE
555 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,-1),ST_GEOMFROMTEXT(@star_center,-1)));
556
557 SELECT ST_ASTEXT(ST_DIFFERENCE(NULL,ST_GEOMFROMTEXT(@star_top)));
558
559 --echo # Invalid geometry in input
560 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),NULL));
561
562 --echo # Invalid geometry in input
563 SELECT ST_ASTEXT(ST_DIFFERENCE(g,NULL)) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103;
564
565 SELECT ST_ASTEXT(ST_DIFFERENCE(NULL,NULL));
566
567 --echo # Invalid geometry in input
568 SELECT ST_ASTEXT(ST_DIFFERENCE(fid,NULL)) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103;
569
570 --echo # Invalid geometry in input
571 --error ER_GIS_INVALID_DATA
572 SELECT ST_ASTEXT(ST_DIFFERENCE(fid,ST_GEOMFROMTEXT(@star_top))) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103;
573
574 --echo # Invalid geometry in input
575 --error ER_GIS_INVALID_DATA
576 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),fid)) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103;
577
578 --error ER_GIS_INVALID_DATA
579 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(a 0)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
580
581 --error ER_GIS_INVALID_DATA
582 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(! 0)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
583
584 --error ER_PARSE_ERROR
585 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT('!' 0)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
586
587 --error ER_GIS_INVALID_DATA
588 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(12,34 0)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
589
590 --error ER_GIS_INVALID_DATA
591 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('LINESTRING(a 0,10 10)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
592
593 --error ER_GIS_INVALID_DATA
594 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('LINESTRING(0 0,! 10)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
595
596 --error ER_PARSE_ERROR
597 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('LINESTRING('!' 0,10 10)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
598
599 --error ER_GIS_INVALID_DATA
600 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('LINESTRING(12,34 0,10 10)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
601
602 --error ER_GIS_INVALID_DATA
603 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POLYGON((a 0,10 10,10 0,0 0))'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
604
605 --error ER_GIS_INVALID_DATA
606 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POLYGON((0 0,! 10,10 0,0 0))'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
607
608 --error ER_PARSE_ERROR
609 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POLYGON(('!' 0,10 10,10 0,0 0))'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
610
611 --error ER_GIS_INVALID_DATA
612 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POLYGON((12,34 0,10 10,10 0,0 0))'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
613
614 --error ER_GIS_INVALID_DATA
615 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POLYGON((12,34 0,10 10,10 0,0 0)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
616
617 --error ER_PARSE_ERROR
618 SELECT ST_ASTEXT(ST_DIFFERENCE(,));
619
620 --echo # Invalid geometry in input
621 --error ER_PARSE_ERROR
622 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),));
623
624 --error ER_PARSE_ERROR
625 SELECT ST_ASTEXT(ST_DIFFERENCE(,ST_GEOMFROMTEXT(@star_top)));
626
627 --echo # Invalid geometry in input
628 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_elem_vertical),ST_GEOMFROMTEXT(@star_of_elems)));
629
630 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_elem_vertical),ST_GEOMFROMTEXT(@star_collection_elems)));
631
632 --echo # Invalid geometry in input
633 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT(@star_of_elems)));
634
635 --echo # Invalid geometry in input
636 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),
637 ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,15 25,35 0,0 0)),((5 0,15 25,25 0,15 5,5 0)))')));
638
639 --echo # Invalid geometry in input
640 SELECT ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_of_elems))) FROM gis_geometrycollection WHERE fid=103;
641
642 --echo # Invalid geometry in input
643 SELECT ST_ASTEXT(ST_DIFFERENCE(g,g2)) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103;
644
645 --echo #clean up
646 DROP TABLE gis_geometrycollection;
647 DROP TABLE gis_geometrycollection_2;
648
649 --echo #
650 --echo # Bug#21977775 ST_DIFFERENCE(POLYGON, POLYGON) RETURNS INVALID GEOMETRY
651 --echo #
652 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POLYGON((8 6,5 7,-1
653 4,-8 -7,0 -17,8 6),(3 6,5 5,0 -2,3 6))'), ST_GEOMFROMTEXT('POLYGON((3 5,-17
654 11,-8 -3,3 5))'))) AS result;
655
656 --echo #
657 --echo # WL#8579 Spatial Reference Systems
658 --echo #
659
660 --echo # SRID 0 (should pass)
661 SELECT ST_ASTEXT(ST_DIFFERENCE(
662 ST_GEOMFROMTEXT('POINT(0 0)', 0),
663 ST_GEOMFROMTEXT('POINT(0 0)', 0)
664 ));
665
666 --echo # Projected SRS (should pass)
667 SELECT ST_ASTEXT(ST_DIFFERENCE(
668 ST_GEOMFROMTEXT('POINT(0 0)', 2000),
669 ST_GEOMFROMTEXT('POINT(0 0)', 2000)
670 ));
671
672 SELECT ST_ASTEXT(ST_DIFFERENCE(
673 ST_GEOMFROMTEXT('POINT(0 0)', 4326),
674 ST_GEOMFROMTEXT('POINT(0 0)', 4326)
675 ));
676
677 --echo #
678 --echo # WL#11096 Don't do Cartesian computations on geographic geometries
679 --echo #
680
681 --echo # Assume SRID 10 is not defined.
682 --error ER_SRS_NOT_FOUND
683 DO ST_DIFFERENCE(
684 x'0A000000010100000000000000000000000000000000000000',
685 x'0A000000010100000000000000000000000000000000000000'
686 );