Debian Codegraph @

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

raw
1 #Creating the spatial Geometry object
2 USE test;
3 CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
4 SET @star_elem_vertical= 'POLYGON((5 0,15 25,25 0,15 5,5 0))';
5 SET @star_elem_horizontal= 'POLYGON((25 0,0 15,30 15,22 10,25 0))';
6 SET @star_center= 'POINT(15 10)';
7 SET @star_top= 'POINT(15 25)';
8 SET @star_bottom_left= 'POINT(5 0)';
9 SET @star_bottom_right= 'POINT(25 0)';
10 SET @star_bottom_points= 'MULTIPOINT(5 0,25 0)';
11 SET @star_all_points= 'MULTIPOINT(5 0,25 0,15 10,15 25)';
12 SET @star_line_horizontal= 'LINESTRING(10 15,20 15)';
13 SET @star_line_vertical= 'LINESTRING(15 5,15 25)';
14 SET @star_top_to_center= 'LINESTRING(15 25,15 10)';
15 SET @star_lines_near_horizontal= 'MULTILINESTRING((25 0,0 15,15 30,0 5))';
16 SET @star_lines_near_vertical= 'MULTILINESTRING((0 5,15 25,0 25))';
17 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))';
18 SET @star_elem_vertical_val= '((5 0,15 25,25 0,15 5,5 0))';
19 SET @star_elem_horizontal_val= '((25 0,0 15,30 15,22 10,25 0))';
20 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)))';
21 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))';
22 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))';
23 SET @star_elem_vertical_1= 'POLYGON((5 0,15 25,25 0,15 5,5 0))';
24 SET @star_elem_horizontal_1= 'POLYGON((25 0,0 15,30 15,22 10,25 0))';
25 SET @star_center_1= 'POINT(15 10)';
26 SET @star_top_1= 'POINT(15 25)';
27 SET @star_bottom_left_1= 'POINT(5 0)';
28 SET @star_bottom_right_1= 'POINT(25 0)';
29 SET @star_bottom_points_1= 'MULTIPOINT(5 0,25 0)';
30 SET @star_all_points_1= 'MULTIPOINT(5 0,25 0,15 10,15 25)';
31 SET @star_line_horizontal_1= 'LINESTRING(10 15,20 15)';
32 SET @star_line_vertical_1= 'LINESTRING(15 5,15 25)';
33 SET @star_top_to_center_1= 'LINESTRING(15 25,15 10)';
34 SET @star_lines_near_horizontal_1= 'MULTILINESTRING((25 0,0 15,15 30,0 5))';
35 SET @star_lines_near_vertical_1= 'MULTILINESTRING((0 5,15 25,0 25))';
36 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))';
37 SET @star_elem_vertical_val_1= '((5 0,15 25,25 0,15 5,5 0))';
38 SET @star_elem_horizontal_val_1= '((25 0,0 15,30 15,22 10,25 0))';
39 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)))';
40 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))';
41 SET @star_elem_vertical_2= 'POLYGON((15 0,25 25,35 0,25 5,15 0))';
42 SET @star_elem_horizontal_2= 'POLYGON((35 0,10 15,40 15,32 10,35 0))';
43 SET @star_center_2= 'POINT(25 10)';
44 SET @star_top_2= 'POINT(25 25)';
45 SET @star_bottom_left_2= 'POINT(15 0)';
46 SET @star_bottom_right_2= 'POINT(35 0)';
47 SET @star_bottom_points_2= 'MULTIPOINT(15 0,35 0)';
48 SET @star_all_points_2= 'MULTIPOINT(15 0,35 0,35 10,25 25)';
49 SET @star_line_horizontal_2= 'LINESTRING(20 15,30 15)';
50 SET @star_line_vertical_2= 'LINESTRING(25 5,25 25)';
51 SET @star_top_to_center_2= 'LINESTRING(25 25,25 10)';
52 SET @star_lines_near_horizontal_2= 'MULTILINESTRING((35 0,10 15,25 30,10 5))';
53 SET @star_lines_near_vertical_2= 'MULTILINESTRING((10 5,25 25,10 25))';
54 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))';
55 SET @star_elem_vertical_val_2= '((15 0,25 25,35 0,25 5,15 0))';
56 SET @star_elem_horizontal_val_2= '((35 0,10 15,40 15,32 10,35 0))';
57 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)))';
58 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))';
59 SET @star_elem_vertical_3= 'POLYGON((65 0,75 25,85 0,75 5,65 0))';
60 SET @star_elem_horizontal_3= 'POLYGON((85 0,60 15,90 15,82 10,85 0))';
61 SET @star_center_3= 'POINT(75 10)';
62 SET @star_top_3= 'POINT(75 25)';
63 SET @star_bottom_left_3= 'POINT(65 0)';
64 SET @star_bottom_right_3= 'POINT(85 0)';
65 SET @star_bottom_points_3= 'MULTIPOINT(65 0,85 0)';
66 SET @star_all_points_3= 'MULTIPOINT(65 0,85 0,75 10,75 25)';
67 SET @star_line_horizontal_3= 'LINESTRING(70 15,80 15)';
68 SET @star_line_vertical_3= 'LINESTRING(75 5,75 25)';
69 SET @star_top_to_center_3= 'LINESTRING(75 25,75 10)';
70 SET @star_lines_near_horizontal_3= 'MULTILINESTRING((85 0,60 15,85 30,60 5))';
71 SET @star_lines_near_vertical_3= 'MULTILINESTRING((60 5,75 25,60 25))';
72 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))';
73 SET @star_elem_vertical_val_3= '((65 0,75 25,85 0,75 5,65 0))';
74 SET @star_elem_horizontal_val_3= '((85 0,60 15,90 15,82 10,85 0))';
75 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)))';
76 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))';
77 #INSERT base star
78 INSERT INTO gis_geometrycollection VALUES
79 (100,ST_GEOMFROMTEXT(@star)),
80 (101,ST_GEOMFROMTEXT(@star_elem_vertical)),
81 (102,ST_GEOMFROMTEXT(@star_elem_horizontal)),
82 (103,ST_GEOMFROMTEXT(@star_of_elems)),
83 (104,ST_GEOMFROMTEXT(@star_top)),
84 (105,ST_GEOMFROMTEXT(@star_center)),
85 (106,ST_GEOMFROMTEXT(@star_bottom_left)),
86 (107,ST_GEOMFROMTEXT(@star_bottom_right)),
87 (108,ST_GEOMFROMTEXT(@star_bottom_points)),
88 (109,ST_GEOMFROMTEXT(@star_all_points)),
89 (110,ST_GEOMFROMTEXT(@star_line_horizontal)),
90 (111,ST_GEOMFROMTEXT(@star_line_vertical)),
91 (112,ST_GEOMFROMTEXT(@star_top_to_center)),
92 (113,ST_GEOMFROMTEXT(@star_lines_near_horizontal)),
93 (114,ST_GEOMFROMTEXT(@star_lines_near_vertical)),
94 (115,ST_GEOMFROMTEXT(@star_collection_elems));
95 #INSERT identical (to base) star
96 INSERT INTO gis_geometrycollection VALUES
97 (200,ST_GEOMFROMTEXT(@star_1)),
98 (201,ST_GEOMFROMTEXT(@star_elem_vertical_1)),
99 (202,ST_GEOMFROMTEXT(@star_elem_horizontal_1)),
100 (203,ST_GEOMFROMTEXT(@star_of_elems_1)),
101 (204,ST_GEOMFROMTEXT(@star_top_1)),
102 (205,ST_GEOMFROMTEXT(@star_center_1)),
103 (206,ST_GEOMFROMTEXT(@star_bottom_left_1)),
104 (207,ST_GEOMFROMTEXT(@star_bottom_right_1)),
105 (208,ST_GEOMFROMTEXT(@star_bottom_points_1)),
106 (209,ST_GEOMFROMTEXT(@star_all_points_1)),
107 (210,ST_GEOMFROMTEXT(@star_line_horizontal_1)),
108 (211,ST_GEOMFROMTEXT(@star_line_vertical_1)),
109 (212,ST_GEOMFROMTEXT(@star_top_to_center_1)),
110 (213,ST_GEOMFROMTEXT(@star_lines_near_horizontal_1)),
111 (214,ST_GEOMFROMTEXT(@star_lines_near_vertical_1)),
112 (215,ST_GEOMFROMTEXT(@star_collection_elems_1));
113 #INSERT overlapping star
114 INSERT INTO gis_geometrycollection VALUES
115 (300,ST_GEOMFROMTEXT(@star_2)),
116 (301,ST_GEOMFROMTEXT(@star_elem_vertical_2)),
117 (302,ST_GEOMFROMTEXT(@star_elem_horizontal_2)),
118 (303,ST_GEOMFROMTEXT(@star_of_elems_2)),
119 (304,ST_GEOMFROMTEXT(@star_top_2)),
120 (305,ST_GEOMFROMTEXT(@star_center_2)),
121 (306,ST_GEOMFROMTEXT(@star_bottom_left_2)),
122 (307,ST_GEOMFROMTEXT(@star_bottom_right_2)),
123 (308,ST_GEOMFROMTEXT(@star_bottom_points_2)),
124 (309,ST_GEOMFROMTEXT(@star_all_points_2)),
125 (310,ST_GEOMFROMTEXT(@star_line_horizontal_2)),
126 (311,ST_GEOMFROMTEXT(@star_line_vertical_2)),
127 (312,ST_GEOMFROMTEXT(@star_top_to_center_2)),
128 (313,ST_GEOMFROMTEXT(@star_lines_near_horizontal_2)),
129 (314,ST_GEOMFROMTEXT(@star_lines_near_vertical_2)),
130 (315,ST_GEOMFROMTEXT(@star_collection_elems_2));
131 #INSERT separate star
132 INSERT INTO gis_geometrycollection VALUES
133 (400,ST_GEOMFROMTEXT(@star_3)),
134 (401,ST_GEOMFROMTEXT(@star_elem_vertical_3)),
135 (402,ST_GEOMFROMTEXT(@star_elem_horizontal_3)),
136 (403,ST_GEOMFROMTEXT(@star_of_elems_3)),
137 (404,ST_GEOMFROMTEXT(@star_top_3)),
138 (405,ST_GEOMFROMTEXT(@star_center_3)),
139 (406,ST_GEOMFROMTEXT(@star_bottom_left_3)),
140 (407,ST_GEOMFROMTEXT(@star_bottom_right_3)),
141 (408,ST_GEOMFROMTEXT(@star_bottom_points_3)),
142 (409,ST_GEOMFROMTEXT(@star_all_points_3)),
143 (410,ST_GEOMFROMTEXT(@star_line_horizontal_3)),
144 (411,ST_GEOMFROMTEXT(@star_line_vertical_3)),
145 (412,ST_GEOMFROMTEXT(@star_top_to_center_3)),
146 (413,ST_GEOMFROMTEXT(@star_lines_near_horizontal_3)),
147 (414,ST_GEOMFROMTEXT(@star_lines_near_vertical_3)),
148 (415,ST_GEOMFROMTEXT(@star_collection_elems_3));
149 CREATE TABLE gis_geometrycollection_2 SELECT fid as fid2,g as g2 FROM gis_geometrycollection;
150 #Checking the integrity of the above create/insert statements
151 #64 rows.
152 SELECT count(ST_ASTEXT(g) != 'NULL') FROM gis_geometrycollection;
153 count(ST_ASTEXT(g) != 'NULL')
154 64
155 #64 rows.
156 SELECT count(ST_ASTEXT(g2) != 'NULL') FROM gis_geometrycollection_2;
157 count(ST_ASTEXT(g2) != 'NULL')
158 64
159 #####################################################################################
160 # ST_DIFFERENCE(g1,g2)
161 #####################################################################################
162 #====================================================================================
163 # point,point
164 #====================================================================================
165 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_center),ST_GEOMFROMTEXT(@star_center)));
166 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_center),ST_GEOMFROMTEXT(@star_center)))
167 GEOMETRYCOLLECTION EMPTY
168 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top),ST_GEOMFROMTEXT(@star_center)));
169 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top),ST_GEOMFROMTEXT(@star_center)))
170 POINT(15 25)
171 SELECT ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_center))) FROM gis_geometrycollection WHERE fid=105;
172 ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_center)))
173 GEOMETRYCOLLECTION EMPTY
174 #====================================================================================
175 # point,multipoint
176 #====================================================================================
177 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_bottom_left),ST_GEOMFROMTEXT(@star_bottom_points)));
178 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_bottom_left),ST_GEOMFROMTEXT(@star_bottom_points)))
179 GEOMETRYCOLLECTION EMPTY
180 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_center),ST_GEOMFROMTEXT(@star_bottom_points)));
181 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_center),ST_GEOMFROMTEXT(@star_bottom_points)))
182 POINT(15 10)
183 SELECT ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_bottom_points))) FROM gis_geometrycollection WHERE fid=106;
184 ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_bottom_points)))
185 GEOMETRYCOLLECTION EMPTY
186 #====================================================================================
187 # point,linestring
188 #====================================================================================
189 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top),ST_GEOMFROMTEXT(@star_top_to_center)));
190 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top),ST_GEOMFROMTEXT(@star_top_to_center)))
191 GEOMETRYCOLLECTION EMPTY
192 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_bottom_left),ST_GEOMFROMTEXT(@star_top_to_center)));
193 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_bottom_left),ST_GEOMFROMTEXT(@star_top_to_center)))
194 POINT(5 0)
195 SELECT ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_top_to_center))) FROM gis_geometrycollection WHERE fid=104;
196 ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_top_to_center)))
197 GEOMETRYCOLLECTION EMPTY
198 #====================================================================================
199 # point,multilinestring
200 #====================================================================================
201 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top),ST_GEOMFROMTEXT(@star_lines_near_vertical)));
202 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top),ST_GEOMFROMTEXT(@star_lines_near_vertical)))
203 GEOMETRYCOLLECTION EMPTY
204 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_center),ST_GEOMFROMTEXT(@star_lines_near_vertical)));
205 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_center),ST_GEOMFROMTEXT(@star_lines_near_vertical)))
206 POINT(15 10)
207 SELECT ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_lines_near_vertical))) FROM gis_geometrycollection WHERE fid=104;
208 ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_lines_near_vertical)))
209 GEOMETRYCOLLECTION EMPTY
210 #====================================================================================
211 # point,polygon
212 #====================================================================================
213 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top),ST_GEOMFROMTEXT(@star)));
214 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top),ST_GEOMFROMTEXT(@star)))
215 GEOMETRYCOLLECTION EMPTY
216 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top_3),ST_GEOMFROMTEXT(@star)));
217 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top_3),ST_GEOMFROMTEXT(@star)))
218 POINT(75 25)
219 SELECT ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star))) FROM gis_geometrycollection WHERE fid=104;
220 ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star)))
221 GEOMETRYCOLLECTION EMPTY
222 #====================================================================================
223 # point,multipolygon
224 #====================================================================================
225 #====================================================================================
226 # point,geometrycollection
227 #====================================================================================
228 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()')));
229 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()')))
230 POINT(0 0)
231 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))')));
232 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))')))
233 POINT(0 0)
234 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(4 4)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(3 3),LINESTRING(0 0,1 1))')));
235 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(4 4)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(3 3),LINESTRING(0 0,1 1))')))
236 POINT(4 4)
237 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)))')));
238 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)))')))
239 GEOMETRYCOLLECTION EMPTY
240 #====================================================================================
241 # multipoint,point
242 #====================================================================================
243 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_top)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(15 10),(25 0))'));
244 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_top)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(15 10),(25 0))'))
245 1
246 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_bottom_points),ST_GEOMFROMTEXT(@star_center)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(25 0))'));
247 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_bottom_points),ST_GEOMFROMTEXT(@star_center)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(25 0))'))
248 1
249 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_bottom_left)),ST_GEOMFROMTEXT('POINT(25 0)')) FROM gis_geometrycollection WHERE fid=108;
250 ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_bottom_left)),ST_GEOMFROMTEXT('POINT(25 0)'))
251 1
252 #====================================================================================
253 # multipoint,multipoint
254 #====================================================================================
255 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_bottom_points)),ST_GEOMFROMTEXT('MULTIPOINT((15 10),(15 25))'));
256 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_bottom_points)),ST_GEOMFROMTEXT('MULTIPOINT((15 10),(15 25))'))
257 1
258 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_bottom_points),ST_GEOMFROMTEXT(@star_all_points)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
259 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_bottom_points),ST_GEOMFROMTEXT(@star_all_points)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'))
260 1
261 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_all_points_1)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
262 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_all_points_1)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'))
263 1
264 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_all_points)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY')) FROM gis_geometrycollection WHERE fid=108;
265 ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_all_points)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'))
266 1
267 #====================================================================================
268 # multipoint,linestring
269 #====================================================================================
270 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_top_to_center)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(25 0))'));
271 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_top_to_center)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(25 0))'))
272 1
273 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_top_to_center)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(25 0))'));
274 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_top_to_center)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(25 0))'))
275 1
276 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;
277 ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_top_to_center)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(25 0))'))
278 1
279 #====================================================================================
280 # multipoint,multilinestring
281 #====================================================================================
282 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))'));
283 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_lines_near_vertical)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(15 10),(25 0))'))
284 1
285 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))'));
286 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_lines_near_horizontal)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(15 10),(15 25))'))
287 1
288 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;
289 ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_lines_near_horizontal)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(15 10),(15 25))'))
290 1
291 #====================================================================================
292 # multipoint,polygon
293 #====================================================================================
294 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_elem_vertical)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
295 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_elem_vertical)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'))
296 1
297 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_elem_horizontal)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(15 25))'));
298 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_all_points),ST_GEOMFROMTEXT(@star_elem_horizontal)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(15 25))'))
299 1
300 SELECT ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_elem_horizontal)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(15 25))')) FROM gis_geometrycollection WHERE fid=109;
301 ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_elem_horizontal)),ST_GEOMFROMTEXT('MULTIPOINT((5 0),(15 25))'))
302 1
303 #====================================================================================
304 # multipoint,multipolygon
305 #====================================================================================
306 #====================================================================================
307 # multipoint,geometrycollection
308 #====================================================================================
309 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTIPOINT(0 0,10 10)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()')),ST_GEOMFROMTEXT('MULTIPOINT((0 0),(10 10))'));
310 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTIPOINT(0 0,10 10)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()')),ST_GEOMFROMTEXT('MULTIPOINT((0 0),(10 10))'))
311 1
312 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))'));
313 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))'))
314 1
315 #====================================================================================
316 # linestring,point
317 #====================================================================================
318 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top_to_center),ST_GEOMFROMTEXT(@star_center)),ST_GEOMFROMTEXT('LINESTRING(15 25,15 10)'));
319 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top_to_center),ST_GEOMFROMTEXT(@star_center)),ST_GEOMFROMTEXT('LINESTRING(15 25,15 10)'))
320 1
321 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('LINESTRING(15 25,15 10)'),ST_GEOMFROMTEXT('POINT(15 10)')),ST_GEOMFROMTEXT('LINESTRING(15 25,15 10)'));
322 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('LINESTRING(15 25,15 10)'),ST_GEOMFROMTEXT('POINT(15 10)')),ST_GEOMFROMTEXT('LINESTRING(15 25,15 10)'))
323 1
324 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top_to_center),ST_GEOMFROMTEXT(@star_bottom_left)),ST_GEOMFROMTEXT('LINESTRING(15 25,15 10)'));
325 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top_to_center),ST_GEOMFROMTEXT(@star_bottom_left)),ST_GEOMFROMTEXT('LINESTRING(15 25,15 10)'))
326 1
327 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;
328 ST_EQUALS(ST_DIFFERENCE(g,g2),ST_GEOMFROMTEXT('LINESTRING(10 15,20 15)'))
329 1
330 #====================================================================================
331 # linestring,multipoint
332 #====================================================================================
333 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_vertical),ST_GEOMFROMTEXT('MULTIPOINT(15 10,15 25)')),ST_GEOMFROMTEXT('LINESTRING(15 5,15 25)'));
334 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_vertical),ST_GEOMFROMTEXT('MULTIPOINT(15 10,15 25)')),ST_GEOMFROMTEXT('LINESTRING(15 5,15 25)'))
335 1
336 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_vertical),ST_GEOMFROMTEXT(@star_all_points)),ST_GEOMFROMTEXT('LINESTRING(15 5,15 25)'));
337 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_vertical),ST_GEOMFROMTEXT(@star_all_points)),ST_GEOMFROMTEXT('LINESTRING(15 5,15 25)'))
338 1
339 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;
340 ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT('MULTIPOINT(15 10,15 25)')),ST_GEOMFROMTEXT('LINESTRING(15 5,15 25)'))
341 1
342 #====================================================================================
343 # linestring,linestring
344 #====================================================================================
345 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_vertical),ST_GEOMFROMTEXT(@star_top_to_center)),ST_GEOMFROMTEXT('LINESTRING(15 5,15 10)'));
346 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_vertical),ST_GEOMFROMTEXT(@star_top_to_center)),ST_GEOMFROMTEXT('LINESTRING(15 5,15 10)'))
347 1
348 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top_to_center),ST_GEOMFROMTEXT(@star_line_vertical)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
349 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top_to_center),ST_GEOMFROMTEXT(@star_line_vertical)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'))
350 1
351 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top_to_center),ST_GEOMFROMTEXT(@star_top_to_center_1)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
352 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top_to_center),ST_GEOMFROMTEXT(@star_top_to_center_1)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'))
353 1
354 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;
355 ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_top_to_center)),ST_GEOMFROMTEXT('LINESTRING(15 5,15 10)'))
356 1
357 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))'));
358 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))'))
359 1
360 #====================================================================================
361 # linestring,multilinestring
362 #====================================================================================
363 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_horizontal),ST_GEOMFROMTEXT(@star_lines_near_horizontal)),ST_GEOMFROMTEXT('LINESTRING(10 15,20 15)'));
364 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_horizontal),ST_GEOMFROMTEXT(@star_lines_near_horizontal)),ST_GEOMFROMTEXT('LINESTRING(10 15,20 15)'))
365 1
366 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_horizontal),ST_GEOMFROMTEXT(@star_lines_near_vertical)),ST_GEOMFROMTEXT('LINESTRING(10 15,20 15)'));
367 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_horizontal),ST_GEOMFROMTEXT(@star_lines_near_vertical)),ST_GEOMFROMTEXT('LINESTRING(10 15,20 15)'))
368 1
369 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;
370 ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_lines_near_horizontal)),ST_GEOMFROMTEXT('LINESTRING(10 15,20 15)'))
371 1
372 #====================================================================================
373 # linestring,polygon
374 #====================================================================================
375 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))'));
376 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))'))
377 1
378 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_horizontal),ST_GEOMFROMTEXT(@star_elem_horizontal)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
379 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_horizontal),ST_GEOMFROMTEXT(@star_elem_horizontal)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'))
380 1
381 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;
382 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))'))
383 1
384 #====================================================================================
385 # linestring,multipolygon
386 #====================================================================================
387 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))'));
388 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))'))
389 1
390 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;
391 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))'))
392 1
393 #====================================================================================
394 # linestring,geometrycollection
395 #====================================================================================
396 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_horizontal),ST_GEOMFROMTEXT(@star_collection_elems_3)),ST_GEOMFROMTEXT('LINESTRING(10 15,20 15)'));
397 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_line_horizontal),ST_GEOMFROMTEXT(@star_collection_elems_3)),ST_GEOMFROMTEXT('LINESTRING(10 15,20 15)'))
398 1
399 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;
400 ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_collection_elems_3)),ST_GEOMFROMTEXT('LINESTRING(10 15,20 15)'))
401 1
402 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()')),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)'));
403 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()')),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)'))
404 1
405 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)'));
406 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)'))
407 1
408 #====================================================================================
409 # multilinestring,point
410 #====================================================================================
411 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_top)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'));
412 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_top)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'))
413 1
414 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_center)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'));
415 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_center)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'))
416 1
417 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;
418 ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_top)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'))
419 1
420 #====================================================================================
421 # multilinestring,multipoint
422 #====================================================================================
423 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)'));
424 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_bottom_points)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'))
425 1
426 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)'));
427 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_all_points)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'))
428 1
429 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;
430 ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_bottom_points)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'))
431 1
432 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;
433 ST_EQUALS(ST_DIFFERENCE(g,g2),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'))
434 1
435 #====================================================================================
436 # multilinestring,linestring
437 #====================================================================================
438 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)'));
439 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_line_vertical)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'))
440 1
441 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)'));
442 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_line_horizontal)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'))
443 1
444 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;
445 ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_line_vertical)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'))
446 1
447 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))'),ST_GEOMFROMTEXT('LINESTRING(0 0, 1 1)')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
448 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))'),ST_GEOMFROMTEXT('LINESTRING(0 0, 1 1)')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'))
449 1
450 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))'));
451 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))'))
452 1
453 #====================================================================================
454 # multilinestring,multilinestring
455 #====================================================================================
456 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)'));
457 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)'))
458 1
459 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)'));
460 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)'))
461 1
462 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;
463 ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT('MULTILINESTRING((0 5,15 25,0 15))')),ST_GEOMFROMTEXT('LINESTRING(15 25,0 25)'))
464 1
465 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;
466 ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_line_vertical)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'))
467 1
468 #====================================================================================
469 # multilinestring,polygon
470 #====================================================================================
471 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)'));
472 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_lines_near_vertical),ST_GEOMFROMTEXT(@star_elem_vertical)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'))
473 1
474 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))'));
475 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))'))
476 1
477 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;
478 ST_EQUALS(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_elem_vertical)),ST_GEOMFROMTEXT('LINESTRING(0 5,15 25,0 25)'))
479 1
480 #====================================================================================
481 # multilinestring,multipolygon
482 #====================================================================================
483 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))'));
484 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))'))
485 1
486 #====================================================================================
487 # multilinestring,geometrycollection
488 #====================================================================================
489 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))'));
490 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))'))
491 1
492 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTILINESTRING((0 0,1 1,2 2))'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION('
493 'GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))')),ST_GEOMFROMTEXT('LINESTRING(0 0,1 1,2 2)'));
494 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTILINESTRING((0 0,1 1,2 2))'),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION('
495 'GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))
496 1
497 #====================================================================================
498 # polygon,point
499 #====================================================================================
500 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))'));
501 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))'))
502 1
503 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))'));
504 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))'))
505 1
506 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;
507 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))'))
508 1
509 #====================================================================================
510 # polygon,multipoint
511 #====================================================================================
512 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))'));
513 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))'))
514 1
515 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))'));
516 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))'))
517 1
518 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;
519 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))'))
520 1
521 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))'));
522 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))'))
523 1
524 #====================================================================================
525 # polygon,linestring
526 #====================================================================================
527 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))'));
528 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))'))
529 1
530 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))'));
531 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))'))
532 1
533 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;
534 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))'))
535 1
536 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;
537 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))'))
538 1
539 #====================================================================================
540 # polygon,multilinestring
541 #====================================================================================
542 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))'));
543 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))'))
544 1
545 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))'));
546 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))'))
547 1
548 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;
549 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))'))
550 1
551 #====================================================================================
552 # polygon,polygon
553 #====================================================================================
554 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)))'));
555 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)))'))
556 1
557 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_elem_vertical),ST_GEOMFROMTEXT(@star)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
558 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_elem_vertical),ST_GEOMFROMTEXT(@star)),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'))
559 1
560 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'));
561 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'))
562 1
563 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;
564 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)))'))
565 1
566 #====================================================================================
567 # polygon,multipolygon
568 #====================================================================================
569 #====================================================================================
570 # polygon,geometrycollection
571 #====================================================================================
572 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))'));
573 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))'))
574 1
575 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))'));
576 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))'))
577 1
578 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))'),
579 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))'));
580 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))'),
581 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))
582 1
583 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))'),
584 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))'));
585 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))'),
586 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))
587 1
588 #====================================================================================
589 # multipolygon,point
590 #====================================================================================
591 #====================================================================================
592 # multipolygon,multipoint
593 #====================================================================================
594 #====================================================================================
595 # multipolygon,linestring
596 #====================================================================================
597 #====================================================================================
598 # multipolygon,multilinestring
599 #====================================================================================
600 #====================================================================================
601 # multipolygon,polygon
602 #====================================================================================
603 #====================================================================================
604 # multipolygon,multipolygon
605 #====================================================================================
606 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)))'),
607 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'));
608 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)))'),
609 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'))
610 1
611 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)))'),
612 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))'));
613 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)))'),
614 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))'))
615 1
616 #====================================================================================
617 # multipolygon,geometrycollectionn
618 #====================================================================================
619 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))'));
620 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))'))
621 1
622 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)))'),
623 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))')),
624 ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))'));
625 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)))'),
626 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))')),
627 ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6
628 1
629 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)))'),
630 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)))'));
631 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)))'),
632 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))')),ST_GEOMFROMTEXT('MULTIPOLYGON(((4 4,4 6,6 6,6 4,4 4)),((0 0,0 10
633 1
634 #====================================================================================
635 # geometrycollection,point
636 #====================================================================================
637 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;
638 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))'))
639 1
640 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))'));
641 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))'))
642 1
643 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))'));
644 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))'))
645 1
646 #====================================================================================
647 # geometrycollection,multipoint
648 #====================================================================================
649 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;
650 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))'))
651 1
652 #====================================================================================
653 # geometrycollection,linestring
654 #====================================================================================
655 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;
656 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))'))
657 1
658 #====================================================================================
659 # geometrycollection,multilinestring
660 #====================================================================================
661 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;
662 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))'))
663 1
664 #====================================================================================
665 # geometrycollection,polygon
666 #====================================================================================
667 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;
668 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)))'))
669 1
670 #====================================================================================
671 # geometrycollection,multipolygon
672 #====================================================================================
673 SELECT ST_EQUALS(ST_DIFFERENCE(g,g2),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY')) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=115 and fid2=215;
674 ST_EQUALS(ST_DIFFERENCE(g,g2),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'))
675 1
676 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'));
677 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'))
678 1
679 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'));
680 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'))
681 1
682 #====================================================================================
683 # geometrycollection,geometrycollectionn
684 #====================================================================================
685 SELECT ST_EQUALS(ST_DIFFERENCE(g,g2),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY')) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=115 and fid2=215;
686 ST_EQUALS(ST_DIFFERENCE(g,g2),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'))
687 1
688 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(1 1,2 2))'),
689 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),POINT(5 5))')),ST_GEOMFROMTEXT('LINESTRING(1 1,2 2)'));
690 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(1 1,2 2))'),
691 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),POINT(5 5))')),ST_GEOMFROMTEXT('LINESTRING(1 1,2 2)'))
692 1
693 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(2 2),POLYGON((0 0,0 5,5 5,5 0,0 0)))'),
694 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),POINT(5 5))')),ST_GEOMFROMTEXT('POLYGON((0 0,5 0,5 5,0 5,0 0))'));
695 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(2 2),POLYGON((0 0,0 5,5 5,5 0,0 0)))'),
696 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),POINT(5 5))')),ST_GEOMFROMTEXT('POLYGON((0 0,5 0,5 5,0 5,0 0))'))
697 1
698 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())'),
699 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())))')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
700 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())'),
701 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())))')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'))
702 1
703 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())'),
704 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(1 1), GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())))')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
705 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())'),
706 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(1 1), GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())))')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'))
707 1
708 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(1 1)))'),
709 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())))')),ST_GEOMFROMTEXT('POINT(1 1)'));
710 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(1 1)))'),
711 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())))')),ST_GEOMFROMTEXT('POINT(1 1)'))
712 1
713 SELECT ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(1 1)))'),
714 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(1 1), GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())))')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
715 ST_EQUALS(ST_DIFFERENCE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(1 1)))'),
716 ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(1 1), GEOMETRYCOLLECTION(GEOMETRYCOLLECTION())))')),ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'))
717 1
718 #####################################################################################
719 # Testing ST_DIFFERENCE() with 2 geometries of same SRID
720 #####################################################################################
721 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,0),ST_GEOMFROMTEXT(@star_center,0)));
722 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,0),ST_GEOMFROMTEXT(@star_center,0)))
723 POINT(15 25)
724 DO ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,4145),ST_GEOMFROMTEXT(@star_center,4145)));
725 DO ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,4326),ST_GEOMFROMTEXT(@star_center,4326)));
726 DO ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,4135),ST_GEOMFROMTEXT(@star_center,4135)));
727 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,-1),ST_GEOMFROMTEXT(@star_center,-1)));
728 ERROR 22003: SRID value is out of range in 'st_geomfromtext'
729 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,-1024),ST_GEOMFROMTEXT(@star_center,-1024)));
730 ERROR 22003: SRID value is out of range in 'st_geomfromtext'
731 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,4294967295000),ST_GEOMFROMTEXT(@star_center,4294967295000)));
732 ERROR 22003: SRID value is out of range in 'st_geomfromtext'
733 #####################################################################################
734 # Invalid function calls
735 #####################################################################################
736 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top),ST_GEOMFROMTEXT(@star_center,4326)));
737 ERROR HY000: Binary geometry function st_difference given two geometries of different srids: 0 and 4326, which should have been identical.
738 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,4145),ST_GEOMFROMTEXT(@star_center,4326)));
739 ERROR HY000: Binary geometry function st_difference given two geometries of different srids: 4145 and 4326, which should have been identical.
740 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,1048576),ST_GEOMFROMTEXT(@star_center,1048576)));
741 ERROR SR001: There's no spatial reference system with SRID 1048576.
742 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,1048575),ST_GEOMFROMTEXT(@star_center,1048575)));
743 ERROR SR001: There's no spatial reference system with SRID 1048575.
744 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,0),ST_GEOMFROMTEXT(@star_center,0)));
745 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,0),ST_GEOMFROMTEXT(@star_center,0)))
746 POINT(15 25)
747 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_top,-1),ST_GEOMFROMTEXT(@star_center,-1)));
748 ERROR 22003: SRID value is out of range in 'st_geomfromtext'
749 SELECT ST_ASTEXT(ST_DIFFERENCE(NULL,ST_GEOMFROMTEXT(@star_top)));
750 ST_ASTEXT(ST_DIFFERENCE(NULL,ST_GEOMFROMTEXT(@star_top)))
751 NULL
752 # Invalid geometry in input
753 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),NULL));
754 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),NULL))
755 NULL
756 # Invalid geometry in input
757 SELECT ST_ASTEXT(ST_DIFFERENCE(g,NULL)) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103;
758 ST_ASTEXT(ST_DIFFERENCE(g,NULL))
759 NULL
760 SELECT ST_ASTEXT(ST_DIFFERENCE(NULL,NULL));
761 ST_ASTEXT(ST_DIFFERENCE(NULL,NULL))
762 NULL
763 # Invalid geometry in input
764 SELECT ST_ASTEXT(ST_DIFFERENCE(fid,NULL)) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103;
765 ST_ASTEXT(ST_DIFFERENCE(fid,NULL))
766 NULL
767 # Invalid geometry in input
768 SELECT ST_ASTEXT(ST_DIFFERENCE(fid,ST_GEOMFROMTEXT(@star_top))) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103;
769 ERROR 22023: Invalid GIS data provided to function st_difference.
770 # Invalid geometry in input
771 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),fid)) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103;
772 ERROR 22023: Invalid GIS data provided to function st_difference.
773 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(a 0)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
774 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
775 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(! 0)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
776 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
777 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT('!' 0)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
778 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)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')))' at line 1
779 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(12,34 0)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
780 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
781 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('LINESTRING(a 0,10 10)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
782 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
783 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('LINESTRING(0 0,! 10)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
784 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
785 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('LINESTRING('!' 0,10 10)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
786 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)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')))' at line 1
787 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('LINESTRING(12,34 0,10 10)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
788 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
789 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POLYGON((a 0,10 10,10 0,0 0))'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
790 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
791 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POLYGON((0 0,! 10,10 0,0 0))'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
792 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
793 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POLYGON(('!' 0,10 10,10 0,0 0))'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
794 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))'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')))' at line 1
795 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POLYGON((12,34 0,10 10,10 0,0 0))'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
796 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
797 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POLYGON((12,34 0,10 10,10 0,0 0)'),ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')));
798 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
799 SELECT ST_ASTEXT(ST_DIFFERENCE(,));
800 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 ',))' at line 1
801 # Invalid geometry in input
802 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),));
803 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 '))' at line 1
804 SELECT ST_ASTEXT(ST_DIFFERENCE(,ST_GEOMFROMTEXT(@star_top)));
805 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 ',ST_GEOMFROMTEXT(@star_top)))' at line 1
806 # Invalid geometry in input
807 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_elem_vertical),ST_GEOMFROMTEXT(@star_of_elems)));
808 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_elem_vertical),ST_GEOMFROMTEXT(@star_of_elems)))
809 GEOMETRYCOLLECTION EMPTY
810 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_elem_vertical),ST_GEOMFROMTEXT(@star_collection_elems)));
811 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_elem_vertical),ST_GEOMFROMTEXT(@star_collection_elems)))
812 GEOMETRYCOLLECTION EMPTY
813 # Invalid geometry in input
814 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT(@star_of_elems)));
815 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT(@star_of_elems)))
816 GEOMETRYCOLLECTION EMPTY
817 # Invalid geometry in input
818 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),
819 ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,15 25,35 0,0 0)),((5 0,15 25,25 0,15 5,5 0)))')));
820 ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),
821 ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,15 25,35 0,0 0)),((5 0,15 25,25 0,15 5,5 0)))')))
822 MULTIPOLYGON(((6.617647058823529 11.029411764705882,9 15,0 15,6.617647058823529 11.029411764705882)),((23 15,25.333333333333332 12.083333333333334,30 15,23 15)))
823 # Invalid geometry in input
824 SELECT ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_of_elems))) FROM gis_geometrycollection WHERE fid=103;
825 ST_ASTEXT(ST_DIFFERENCE(g,ST_GEOMFROMTEXT(@star_of_elems)))
826 GEOMETRYCOLLECTION EMPTY
827 # Invalid geometry in input
828 SELECT ST_ASTEXT(ST_DIFFERENCE(g,g2)) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103;
829 ST_ASTEXT(ST_DIFFERENCE(g,g2))
830 GEOMETRYCOLLECTION EMPTY
831 #clean up
832 DROP TABLE gis_geometrycollection;
833 DROP TABLE gis_geometrycollection_2;
834 #
835 # Bug#21977775 ST_DIFFERENCE(POLYGON, POLYGON) RETURNS INVALID GEOMETRY
836 #
837 SELECT ST_ASTEXT(ST_DIFFERENCE(ST_GEOMFROMTEXT('POLYGON((8 6,5 7,-1
838 4,-8 -7,0 -17,8 6),(3 6,5 5,0 -2,3 6))'), ST_GEOMFROMTEXT('POLYGON((3 5,-17
839 11,-8 -3,3 5))'))) AS result;
840 result
841 MULTIPOLYGON(((1.75 5.375,2.662921348314607 5.101123595505618,3 6,1.75 5.375)),((-3.2615384615384615 0.44615384615384635,-8 -7,0 -17,8 6,5 7,3 6,5 5,0 -2,2.484375 4.625,-3.2615384615384615 0.44615384615384635)))
842 #
843 # WL#8579 Spatial Reference Systems
844 #
845 # SRID 0 (should pass)
846 SELECT ST_ASTEXT(ST_DIFFERENCE(
847 ST_GEOMFROMTEXT('POINT(0 0)', 0),
848 ST_GEOMFROMTEXT('POINT(0 0)', 0)
849 ));
850 ST_ASTEXT(ST_DIFFERENCE(
851 ST_GEOMFROMTEXT('POINT(0 0)', 0),
852 ST_GEOMFROMTEXT('POINT(0 0)', 0)
853 ))
854 GEOMETRYCOLLECTION EMPTY
855 # Projected SRS (should pass)
856 SELECT ST_ASTEXT(ST_DIFFERENCE(
857 ST_GEOMFROMTEXT('POINT(0 0)', 2000),
858 ST_GEOMFROMTEXT('POINT(0 0)', 2000)
859 ));
860 ST_ASTEXT(ST_DIFFERENCE(
861 ST_GEOMFROMTEXT('POINT(0 0)', 2000),
862 ST_GEOMFROMTEXT('POINT(0 0)', 2000)
863 ))
864 GEOMETRYCOLLECTION EMPTY
865 SELECT ST_ASTEXT(ST_DIFFERENCE(
866 ST_GEOMFROMTEXT('POINT(0 0)', 4326),
867 ST_GEOMFROMTEXT('POINT(0 0)', 4326)
868 ));
869 ST_ASTEXT(ST_DIFFERENCE(
870 ST_GEOMFROMTEXT('POINT(0 0)', 4326),
871 ST_GEOMFROMTEXT('POINT(0 0)', 4326)
872 ))
873 GEOMETRYCOLLECTION EMPTY
874 #
875 # WL#11096 Don't do Cartesian computations on geographic geometries
876 #
877 # Assume SRID 10 is not defined.
878 DO ST_DIFFERENCE(
879 x'0A000000010100000000000000000000000000000000000000',
880 x'0A000000010100000000000000000000000000000000000000'
881 );
882 ERROR SR001: There's no spatial reference system with SRID 10.