Debian Codegraph @

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

raw
1 ###############################################################################
2 # #
3 # CAST(... AS POLYGON) allows casting geometries of type #
4 # LINESTRING, POLYGON, MULTILINESTRING, MULTIPOLYGON and GEOMETRYCOLLECTION #
5 # to type POLYGON. #
6 # #
7 # Creation Date: 2020-08-02 #
8 # Author: Magnus Rushfeldt #
9 # #
10 ###############################################################################
11
12 --echo ########################################
13 --echo # Requirement F-9a: #
14 --echo # CAST(linestring AS POLYGON) #
15 --echo ########################################
16 --echo #
17
18 --echo # Casting from LINESTRING that is a correctly oriented linearring (counter clockwise) to POLYGON is allowed.
19 --echo # Must return POLYGON.
20 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 4 1, 4 4, 1 4, 1 1)') AS POLYGON));
21 --echo # Must return 1.
22 SELECT ST_EQUALS(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))'), CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 4 1, 4 4, 1 4, 1 1)') AS POLYGON));
23 --echo #
24
25 --echo # Casting from LINESTRING that is not a linearring to POLYGON is not allowed.
26 --echo # Must raise error.
27 --error ER_INVALID_CAST_TO_GEOMETRY
28 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 4 1, 4 4, 1 4)') AS POLYGON));
29 --error ER_INVALID_CAST_TO_GEOMETRY
30 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 4 4, 1 1)') AS POLYGON));
31 --echo #
32
33 --echo # Casting from LINESTRING that is not a counter clockwise oriented linearring to POLYGON is not allowed.
34 --echo # Must raise error.
35 --error ER_INVALID_CAST_POLYGON_RING_DIRECTION
36 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 1 4, 4 4, 4 1, 1 1)') AS POLYGON));
37 --echo #
38
39 --echo ########################################
40 --echo # Requirement F-9b: #
41 --echo # CAST(polygon AS POLYGON) #
42 --echo ########################################
43 --echo #
44
45 --echo # Casting from POLYGON to POLYGON is always allowed.
46 --echo # Must return POLYGON.
47 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1), (2 2, 2 3, 3 3, 3 2, 2 2))') AS POLYGON));
48 --echo # Must return 1.
49 SELECT ST_EQUALS(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1), (2 2, 2 3, 3 3, 3 2, 2 2))'), CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1), (2 2, 2 3, 3 3, 3 2, 2 2))') AS POLYGON));
50 --echo #
51
52 --echo # Cast may reverse direction of some rings in polygon if
53 --echo # one or more rings are oriented the wrong way. This is still the same polygon.
54 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 1 4, 4 4, 4 1, 1 1), (2 2, 3 2, 3 3, 2 3, 2 2))') AS POLYGON));
55 --echo # Must return 1.
56 SELECT ST_EQUALS(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1), (2 2, 2 3, 3 3, 3 2, 2 2))'), CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 1 4, 4 4, 4 1, 1 1), (2 2, 3 2, 3 3, 2 3, 2 2))') AS POLYGON));
57 --echo #
58
59 --echo #
60 --echo ########################################
61 --echo # Requirement F-9c: #
62 --echo # CAST(multilinestring AS POLYGON) #
63 --echo ########################################
64 --echo #
65
66 --echo # Casting from MULTILINESTRING with only correctly oriented linearrings (first ring counter clockwise, remaining rings clockwise) to POLYGON is allowed.
67 --echo # Must return POLYGON.
68 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 4 1, 4 4, 1 4, 1 1), (5 5, 5 6, 6 6, 6 5, 5 5))') AS POLYGON));
69 --echo # Must return 1.
70 SELECT ST_EQUALS(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1), (5 5, 5 6, 6 6, 6 5, 5 5))'), CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 4 1, 4 4, 1 4, 1 1), (5 5, 5 6, 6 6, 6 5, 5 5))') AS POLYGON));
71 --echo #
72
73 --echo # Casting from MULTILINESTRING with not only linearrings to POLYGON is not allowed.
74 --echo # Must raise error.
75 --error ER_INVALID_CAST_TO_GEOMETRY
76 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 4 1, 4 4, 1 4, 1 1), (5 5, 5 6, 6 6, 6 5))') AS POLYGON));
77 --error ER_INVALID_CAST_TO_GEOMETRY
78 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 4 4, 1 1), (5 5, 5 6, 6 6, 6 5, 5 5))') AS POLYGON));
79 --echo #
80
81 --echo # Casting from MULTILINESTRING with not only correctly oriented linearrings to POLYGON is not allowed.
82 --echo # Must raise error.
83 --error ER_INVALID_CAST_POLYGON_RING_DIRECTION
84 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 4 1, 4 4, 1 4, 1 1), (5 5, 6 5, 6 6, 5 6, 5 5))') AS POLYGON));
85 --echo #
86
87 --echo ########################################
88 --echo # Requirement F-9d: #
89 --echo # CAST(multipolygon AS POLYGON) #
90 --echo ########################################
91 --echo #
92
93 --echo # Casting from MULTIPOLYGON with only one polygon to POLYGON is allowed.
94 --echo # Must return POLYGON.
95 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)))') AS POLYGON));
96 --echo # Must return 1.
97 SELECT ST_EQUALS(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1), (5 5, 5 6, 6 6, 6 5, 5 5))'), CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 4 1, 4 4, 1 4, 1 1), (5 5, 5 6, 6 6, 6 5, 5 5))') AS POLYGON));
98 --echo #
99
100 --echo # Casting from MULTIPOLYGON with multiple polygons to POLYGON is not allowed.
101 --echo # Must raise error.
102 --error ER_INVALID_CAST_TO_GEOMETRY
103 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((5 5, 5 6, 6 6, 6 5, 5 5)))') AS POLYGON));
104
105 --echo #
106 --echo ########################################
107 --echo # Requirement F-9e: #
108 --echo # CAST(geometrycollection AS POLYGON) #
109 --echo ########################################
110 --echo #
111
112 --echo # Casting from GEOMETRYCOLLECTION with only one polygon to POLYGON is allowed.
113 --echo # Must return POLYGON.
114 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POLYGON((1 1, 4 1, 4 4, 1 4, 1 1)))') AS POLYGON));
115 --echo # Must return 1.
116 SELECT ST_EQUALS(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))'), CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POLYGON((1 1, 4 1, 4 4, 1 4, 1 1)))') AS POLYGON));
117 --echo #
118
119 --echo # Casting from empty GEOMETRYCOLLECTION to POLYGON is not allowed.
120 --echo # Must raise error.
121 --error ER_INVALID_CAST_TO_GEOMETRY
122 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()') AS POLYGON));
123 --echo #
124
125 --echo # Casting from GEOMETRYCOLLECTION with multiple polygons to POLYGON is not allowed.
126 --echo # Must raise error.
127 --error ER_INVALID_CAST_TO_GEOMETRY
128 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POLYGON((1 1, 4 1, 4 4, 1 4, 1 1)), POLYGON((5 5, 5 6, 6 6, 6 5, 5 5)))') AS POLYGON));
129 --echo #
130
131 --echo # Casting from GEOMETRYCOLLECTION with not only polygons to POLYGON is not allowed.
132 --echo # Must raise error.
133 --error ER_INVALID_CAST_TO_GEOMETRY
134 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POLYGON((1 1, 4 1, 4 4, 1 4, 1 1)), GEOMETRYCOLLECTION())') AS POLYGON));
135
136 --echo #
137 --echo ########################################
138 --echo # Requirement F-9f: #
139 --echo # Disallowed casts to POLYGON #
140 --echo ########################################
141 --echo #
142
143 --echo # Casting from POINT to POLYGON is not allowed.
144 --echo # Must raise error.
145 --error ER_INVALID_CAST_TO_GEOMETRY
146 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS POLYGON));
147 --echo #
148
149 --echo # Casting from MULTIPOINT to POLYGON is not allowed.
150 --echo # Must raise error.
151 --error ER_INVALID_CAST_TO_GEOMETRY
152 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1), (2 2))') AS POLYGON));
153
154 --echo #
155 --echo #############################################
156 --echo # Testing result of WKT input #
157 --echo #############################################
158 --echo #
159
160 --echo # Casting from ST_GEOMFROMTEXT(<WKT>) to POLYGON must return expected polygon.
161 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 1, 2 2, 1 2, 1 1)') AS POLYGON));
162 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('POLYGON((3 3, 4 3, 4 4, 3 4, 3 3), (5 5, 5 6, 6 6, 6 5, 5 5))') AS POLYGON));
163 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTILINESTRING((7 7, 8 7, 8 8, 7 8, 7 7), (9 9, 9 10, 10 10, 10 9, 9 9))') AS POLYGON));
164 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((11 11, 12 11, 12 12, 11 12, 11 11)))') AS POLYGON));
165 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POLYGON((13 13, 14 13, 14 14, 13 14, 13 13)))') AS POLYGON));
166 --echo #
167
168 --echo #############################################
169 --echo # Testing result of user variable input #
170 --echo #############################################
171 --echo #
172
173 SET @linestring = ST_GEOMFROMTEXT('LINESTRING(1 1, 2 1, 2 2, 1 2, 1 1)');
174 SET @polygon = ST_GEOMFROMTEXT('POLYGON((3 3, 4 3, 4 4, 3 4, 3 3), (5 5, 5 6, 6 6, 6 5, 5 5))');
175 SET @multilinestring = ST_GEOMFROMTEXT('MULTILINESTRING((7 7, 8 7, 8 8, 7 8, 7 7), (9 9, 9 10, 10 10, 10 9, 9 9))');
176 SET @multipolygon = ST_GEOMFROMTEXT('MULTIPOLYGON(((11 11, 12 11, 12 12, 11 12, 11 11)))');
177 SET @geomcollection = ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POLYGON((13 13, 14 13, 14 14, 13 14, 13 13)))');
178 --echo #
179
180 --echo # Casting from geometry in user variable to POLYGON must return expected polygon.
181 SELECT ST_ASTEXT(CAST(@linestring AS POLYGON));
182 SELECT ST_ASTEXT(CAST(@polygon AS POLYGON));
183 SELECT ST_ASTEXT(CAST(@multilinestring AS POLYGON));
184 SELECT ST_ASTEXT(CAST(@multipolygon AS POLYGON));
185 SELECT ST_ASTEXT(CAST(@geomcollection AS POLYGON));
186 --echo #
187
188 --echo #############################################
189 --echo # Testing result of table column input #
190 --echo #############################################
191 --echo #
192
193 CREATE TABLE gis_polygon_castable (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
194 INSERT INTO gis_polygon_castable VALUES
195 (101, ST_GEOMFROMTEXT('LINESTRING(1 1, 2 1, 2 2, 1 2, 1 1)')),
196 (102, ST_GEOMFROMTEXT('POLYGON((3 3, 4 3, 4 4, 3 4, 3 3), (5 5, 5 6, 6 6, 6 5, 5 5))')),
197 (103, ST_GEOMFROMTEXT('MULTILINESTRING((7 7, 8 7, 8 8, 7 8, 7 7), (9 9, 9 10, 10 10, 10 9, 9 9))')),
198 (104, ST_GEOMFROMTEXT('MULTIPOLYGON(((11 11, 12 11, 12 12, 11 12, 11 11)))')),
199 (105, ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POLYGON((13 13, 14 13, 14 14, 13 14, 13 13)))'));
200 --echo #
201
202 --echo # Casting from geometry column to POLYGON must return expected polygon.
203 SELECT ST_ASTEXT(CAST(g AS POLYGON)) FROM gis_polygon_castable;
204 --echo #
205
206 --echo # Cleanup.
207 DROP TABLE gis_polygon_castable;