Debian Codegraph @

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

raw
1 ###############################################################################
2 # #
3 # CAST(... AS POINT) allows casting geometries of type #
4 # Point, Multipoint and Geometrycollection #
5 # to type Point. #
6 # #
7 # Creation Date: 2020-08-02 #
8 # Author: Magnus Rushfeldt #
9 # #
10 ###############################################################################
11
12 --echo ########################################
13 --echo # Requirement F-7a: #
14 --echo # CAST(point AS POINT) #
15 --echo ########################################
16 --echo #
17
18 --echo # Casting from POINT to POINT is always allowed.
19 --echo # Must return Point.
20 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS POINT));
21 --echo # Must return 1.
22 SELECT ST_EQUALS(ST_GEOMFROMTEXT('POINT(1 1)'), CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS POINT));
23 --echo #
24
25 --echo ########################################
26 --echo # Requirement F-7b: #
27 --echo # CAST(multipoint AS POINT) #
28 --echo ########################################
29 --echo #
30
31 --echo # Casting from MULTIPOINT with only one point to POINT is allowed.
32 --echo # Must return Point.
33 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1))') AS POINT));
34 --echo # Must return 1.
35 SELECT ST_EQUALS(ST_GEOMFROMTEXT('POINT(1 1)'), CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1))') AS POINT));
36 --echo #
37
38 --echo # Casting from MULTIPOINT with multiple points to POINT is not allowed.
39 --echo # Must raise error.
40 --error ER_INVALID_CAST_TO_GEOMETRY
41 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1), (2 2))') AS POINT));
42 --echo #
43
44 --echo ########################################
45 --echo # Requirement F-7c: #
46 --echo # CAST(geometrycollection AS POINT) #
47 --echo ########################################
48 --echo #
49
50 --echo # Casting from GEOMETRYCOLLECTION with only one point to POINT is allowed.
51 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1))') AS POINT));
52 --echo # Must return 1.
53 SELECT ST_EQUALS(ST_GEOMFROMTEXT('POINT(1 1)'), CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1))') AS POINT));
54 --echo #
55
56 --echo # Casting from empty GEOMETRYCOLLECTION to POINT is not allowed.
57 --echo # Must raise error.
58 --error ER_INVALID_CAST_TO_GEOMETRY
59 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()') AS POINT));
60 --echo #
61
62 --echo # Casting from GEOMETRYCOLLECTION with multiple points to POINT is not allowed.
63 --echo # Must raise error.
64 --error ER_INVALID_CAST_TO_GEOMETRY
65 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), POINT(2 2))') AS POINT));
66 --echo #
67
68 --echo # Casting from GEOMETRYCOLLECTION with not only points to POINT is not allowed.
69 --echo # Must raise error.
70 --error ER_INVALID_CAST_TO_GEOMETRY
71 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), GEOMETRYCOLLECTION())') AS POINT));
72 --echo #
73
74 --echo ########################################
75 --echo # Requirement F-7d: #
76 --echo # Disallowed casts to POINT #
77 --echo ########################################
78 --echo #
79
80 --echo # Casting from LINESTRING to POINT is not allowed.
81 --echo # Must raise error.
82 --error ER_INVALID_CAST_TO_GEOMETRY
83 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS POINT));
84 --echo #
85
86 --echo # Casting from POLYGON to POINT is not allowed.
87 --echo # Must raise error.
88 --error ER_INVALID_CAST_TO_GEOMETRY
89 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 2 2, 3 3, 1 1))') AS POINT));
90 --echo #
91
92 --echo # Casting from MULTILINESTRING to POINT is not allowed.
93 --echo # Must raise error.
94 --error ER_INVALID_CAST_TO_GEOMETRY
95 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (1 1, 2 2))') AS POINT));
96 --echo #
97
98 --echo # Casting from MULTIPOLYGON to POINT is not allowed.
99 --echo # Must raise error.
100 --error ER_INVALID_CAST_TO_GEOMETRY
101 DO ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 2 2, 3 3, 1 1)), ((1 1, 2 2, 3 3, 1 1)))') AS POINT));
102 --echo #
103
104 --echo #############################################
105 --echo # Testing result of WKT input #
106 --echo #############################################
107 --echo #
108
109 --echo # Casting from ST_GEOMFROMTEXT(<wkt>) to POINT must return expected point.
110 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS POINT));
111 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTIPOINT((2 2))') AS POINT));
112 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(3 3))') AS POINT));
113 --echo #
114
115 --echo #############################################
116 --echo # Testing result of user variable input #
117 --echo #############################################
118 --echo #
119
120 SET @point = ST_GEOMFROMTEXT('POINT(1 1)');
121 SET @multipoint = ST_GEOMFROMTEXT('MULTIPOINT((2 2))');
122 SET @geomcollection = ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(3 3))');
123 --echo #
124
125 --echo # Casting from geometry in user variable to POINT must return expected point.
126 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS POINT));
127 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('MULTIPOINT((2 2))') AS POINT));
128 SELECT ST_ASTEXT(CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(3 3))') AS POINT));
129 --echo #
130
131 --echo #############################################
132 --echo # Testing result of table column input #
133 --echo #############################################
134 --echo #
135
136 CREATE TABLE gis_point_castable (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
137 INSERT INTO gis_point_castable values
138 (101, ST_GEOMFROMTEXT('POINT(1 1)')),
139 (102, ST_GEOMFROMTEXT('MULTIPOINT((2 2))')),
140 (103, ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(3 3))'));
141 --echo #
142
143 --echo # Casting from geometry column to POINT must return expected point.
144 SELECT ST_ASTEXT(CAST(g AS POINT)) FROM gis_point_castable;
145 --echo #
146
147 --echo # Cleanup.
148 DROP TABLE gis_point_castable;