Debian Codegraph @

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

raw
1 ###############################################################################
2 # #
3 # CAST(... AS GEOMETRYCOLLECTION) allows casting geometries of type #
4 # POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON and #
5 # GEOMETRYCOLLECTION to type GEOMETRYCOLLECTION. #
6 # #
7 # Creation Date: 2020-08-02 #
8 # Author: Magnus Rushfeldt #
9 # #
10 ###############################################################################
11
12 --echo ####################################################
13 --echo # Requirement F-13a: #
14 --echo # CAST(... AS GEOMCOLLECTION) must give the same #
15 --echo # same result as CAST(... AS GEOMETRYCOLLECTION) #
16 --echo ####################################################
17 --echo #
18
19 --echo # Casting using CAST(... AS GEOMETRYCOLLECTION) must be allowed.
20 --echo # Must return geometrycollection.
21 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS GEOMETRYCOLLECTION));
22 --echo #
23
24 --echo # Casting using CAST(... AS GEOMCOLLECTION) must be allowed.
25 --echo # Must return geometrycollection.
26 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS GEOMCOLLECTION));
27 --echo #
28
29 --echo ####################################################
30 --echo # Requirement F-13b: #
31 --echo # CAST(point AS GEOMETRYCOLLECTION) #
32 --echo ####################################################
33 --echo #
34
35 --echo # Casting from POINT to GEOMETRYCOLLECTION is always allowed.
36 --echo # Must return geometrycollection.
37 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS GEOMETRYCOLLECTION));
38 --echo # Must return 1.
39 SELECT ST_EQUALS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1))'), CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS GEOMETRYCOLLECTION));
40 --echo #
41
42 --echo ####################################################
43 --echo # Requirement F-13c: #
44 --echo # CAST(linestring AS GEOMETRYCOLLECTION) #
45 --echo ####################################################
46 --echo #
47
48 --echo # Casting from LINESTRING to GEOMETRYCOLLECTION is always allowed.
49 --echo # Must return geometrycollection.
50 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS GEOMETRYCOLLECTION));
51 --echo # Must return 1.
52 SELECT ST_EQUALS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(LINESTRING(1 1, 2 2))'), CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS GEOMETRYCOLLECTION));
53 --echo #
54
55 --echo ####################################################
56 --echo # Requirement F-13d: #
57 --echo # CAST(polygon AS GEOMETRYCOLLECTION) #
58 --echo ####################################################
59 --echo #
60
61 --echo # Casting from POLYGON to GEOMETRYCOLLECTION is always allowed.
62 --echo # Must return geometrycollection.
63 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1), (2 2, 3 2, 3 3, 2 3, 2 2))') AS GEOMETRYCOLLECTION));
64 --echo # Must return 1.
65 SELECT ST_EQUALS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POLYGON((1 1, 4 1, 4 4, 1 4, 1 1), (2 2, 3 2, 3 3, 2 3, 2 2)))'), CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1), (2 2, 3 2, 3 3, 2 3, 2 2))') AS GEOMETRYCOLLECTION));
66 --echo #
67
68 --echo ####################################################
69 --echo # Requirement F-13e: #
70 --echo # CAST(multipoint AS GEOMETRYCOLLECTION) #
71 --echo ####################################################
72 --echo #
73
74 --echo # Casting from MULTIPOINT to GEOMETRYCOLLECTION is always allowed.
75 --echo # Must return geometrycollection.
76 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1), (2 2))') AS GEOMETRYCOLLECTION));
77 --echo # Must return 1.
78 SELECT ST_EQUALS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), POINT(2 2))'), CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1), (2 2))') AS GEOMETRYCOLLECTION));
79 --echo #
80
81 --echo ####################################################
82 --echo # Requirement F-13f: #
83 --echo # CAST(multilinestring AS GEOMETRYCOLLECTION) #
84 --echo ####################################################
85 --echo #
86
87 --echo # Casting from MULTILINESTRING to GEOMETRYCOLLECTION is always allowed.
88 --echo # Must return geometrycollection.
89 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS GEOMETRYCOLLECTION));
90 --echo # Must return 1.
91 SELECT ST_EQUALS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(LINESTRING(1 1, 2 2), LINESTRING(3 3, 4 4))'), CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS GEOMETRYCOLLECTION));
92 --echo #
93
94 --echo ####################################################
95 --echo # Requirement F-13g: #
96 --echo # CAST(multipolygon AS GEOMETRYCOLLECTION) #
97 --echo ####################################################
98 --echo #
99
100 --echo # Casting from MULTIPOLYGON to GEOMETRYCOLLECTION is always allowed.
101 --echo # Must return geometrycollection.
102 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((5 5, 6 5, 6 6, 5 6, 5 5)))') AS GEOMETRYCOLLECTION));
103 --echo # Must return 1.
104 SELECT ST_EQUALS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POLYGON((1 1, 4 1, 4 4, 1 4, 1 1)), POLYGON((5 5, 6 5, 6 6, 5 6, 5 5)))'), CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((5 5, 6 5, 6 6, 5 6, 5 5)))') AS GEOMETRYCOLLECTION));
105 --echo #
106
107 --echo ####################################################
108 --echo # Requirement F-13h: #
109 --echo # CAST(geometrycollection AS GEOMETRYCOLLECTION) #
110 --echo ####################################################
111 --echo #
112
113 --echo # Casting from GEOMETRYCOLLECTION to GEOMETRYCOLLECTION is always allowed.
114 --echo # Must return geometrycollection.
115 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION));
116 --echo # Must return 1.
117 SELECT ST_EQUALS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))'), CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION));
118 --echo #
119
120 --echo #############################################
121 --echo # Testing result of WKT input #
122 --echo #############################################
123 --echo #
124
125 --echo # Casting from ST_GEOMFROMTEXT(<WKT>) to GEOMETRYCOLLECTION must return expected linestring.
126 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS GEOMETRYCOLLECTION));
127 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('LINESTRING(2 2, 3 3)') AS GEOMETRYCOLLECTION));
128 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('POLYGON((4 4, 5 4, 5 5, 4 5, 4 4))') AS GEOMETRYCOLLECTION));
129 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTIPOINT((6 6), (7 7))') AS GEOMETRYCOLLECTION));
130 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTILINESTRING((8 8, 9 9), (10 10, 11 11))') AS GEOMETRYCOLLECTION));
131 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((12 12, 13 12, 13 13, 12 13, 12 12)), ((14 14, 15 14, 15 15, 14 15, 14 14)))') AS GEOMETRYCOLLECTION));
132 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(16 16), LINESTRING(17 17, 18 18))') AS GEOMETRYCOLLECTION));
133 --echo #
134
135 --echo #############################################
136 --echo # Testing result of user variable input #
137 --echo #############################################
138 --echo #
139
140 SET @point = ST_GEOMFROMTEXT('POINT(1 1)');
141 SET @linestring = ST_GEOMFROMTEXT('LINESTRING(2 2, 3 3)');
142 SET @polygon = ST_GEOMFROMTEXT('POLYGON((4 4, 5 4, 5 5, 4 5, 4 4))');
143 SET @multipoint = ST_GEOMFROMTEXT('MULTIPOINT((6 6), (7 7))');
144 SET @multilinestring = ST_GEOMFROMTEXT('MULTILINESTRING((8 8, 9 9), (10 10, 11 11))');
145 SET @multipolygon = ST_GEOMFROMTEXT('MULTIPOLYGON(((12 12, 13 12, 13 13, 12 13, 12 12)), ((14 14, 15 14, 15 15, 14 15, 14 14)))');
146 SET @geomcollection = ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(16 16), LINESTRING(17 17, 18 18))');
147 --echo #
148
149 --echo # Casting from geometry in user variable to GEOMETRYCOLLECTION must return expected geometrycollection.
150 SELECT ST_ASTEXT(CAST(@point AS GEOMETRYCOLLECTION));
151 SELECT ST_ASTEXT(CAST(@linestring AS GEOMETRYCOLLECTION));
152 SELECT ST_ASTEXT(CAST(@polygon AS GEOMETRYCOLLECTION));
153 SELECT ST_ASTEXT(CAST(@multipoint AS GEOMETRYCOLLECTION));
154 SELECT ST_ASTEXT(CAST(@multilinestring AS GEOMETRYCOLLECTION));
155 SELECT ST_ASTEXT(CAST(@multipolygon AS GEOMETRYCOLLECTION));
156 SELECT ST_ASTEXT(CAST(@geomcollection AS GEOMETRYCOLLECTION));
157 --echo #
158
159 --echo #############################################
160 --echo # Testing result of table column input #
161 --echo #############################################
162 --echo #
163
164 CREATE TABLE gis_geometrycollection_castable (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
165 INSERT INTO gis_geometrycollection_castable VALUES
166 (101, ST_GEOMFROMTEXT('POINT(1 1)')),
167 (102, ST_GEOMFROMTEXT('LINESTRING(2 2, 3 3)')),
168 (103, ST_GEOMFROMTEXT('POLYGON((4 4, 5 4, 5 5, 4 5, 4 4))')),
169 (104, ST_GEOMFROMTEXT('MULTIPOINT((6 6), (7 7))')),
170 (105, ST_GEOMFROMTEXT('MULTILINESTRING((8 8, 9 9), (10 10, 11 11))')),
171 (106, ST_GEOMFROMTEXT('MULTIPOLYGON(((12 12, 13 12, 13 13, 12 13, 12 12)), ((14 14, 15 14, 15 15, 14 15, 14 14)))')),
172 (107, ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(16 16), LINESTRING(17 17, 18 18))'));
173 --echo #
174
175 --echo # Casting from geometry column to GEOMETRYCOLLECTION must return expected geometrycollection.
176 SELECT ST_ASTEXT(CAST(g AS GEOMETRYCOLLECTION)) FROM gis_geometrycollection_castable;
177 --echo #
178
179 --echo # Cleanup.
180 DROP TABLE gis_geometrycollection_castable;