Debian Codegraph @

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

raw
1 ###############################################################################
2 # #
3 # This test is aimed to focus on the properties and attributes of the #
4 # geometry class #
5 # #
6 # The geometry class has a number of properties that are common to all #
7 # geometry values created from any of the Geometry subclasses. #
8 # #
9 # Creation Date: 2013-8-1 #
10 # Author : Tanjot Singh Uppal #
11 # #
12 # Description:Test Cases of the geometry base class Properties #
13 # #
14 ###############################################################################
15
16
17 ############################################################################################
18 # Creating the spatial objects #
19 ############################################################################################
20
21 --echo # Creating the spatial Geometry object
22 USE test;
23
24 CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT);
25 CREATE TABLE gis_linestring (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING);
26 CREATE TABLE gis_polygon (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON);
27 CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT);
28 CREATE TABLE gis_multi_linestring (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING);
29 CREATE TABLE gis_multi_polygon (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON);
30 CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION);
31 CREATE TABLE gis_geometry (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
32
33 ############################################################################################
34 # Inserting the values specific to the spatial objects #
35 ############################################################################################
36
37 --echo # Inserting POINT Geometry Values
38 INSERT INTO gis_point VALUES
39 (101, ST_POINTFROMTEXT('POINT(0 0)')),
40 (102, ST_POINTFROMTEXT('POINT(1 0)')),
41 (103, ST_POINTFROMTEXT('POINT(0 1)')),
42 (104, ST_POINTFROMTEXT('POINT(1 1)')),
43 (105, ST_POINTFROMTEXT('POINT(-1 1)'));
44
45 INSERT INTO gis_point VALUES
46 (106, ST_POINTFROMWKB(ST_ASWKB(POINT(0,0)))),
47 (107, ST_POINTFROMWKB(ST_ASWKB(POINT(10,0)))),
48 (108, ST_POINTFROMWKB(ST_ASWKB(POINT(0,10)))),
49 (109, ST_POINTFROMWKB(ST_ASWKB(POINT(-10,0)))),
50 (110, ST_POINTFROMWKB(ST_ASWKB(POINT(0,-10))));
51
52 INSERT INTO gis_point VALUES
53 (111, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(1 1)')))),
54 (112, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(1000 1000)')))),
55 (113, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(1000 -1000)')))),
56 (114, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(-1000 1000)')))),
57 (115, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(-1000 -1000)'))));
58
59 --echo # Inserting LINESTRING Geometry Values
60 INSERT INTO gis_linestring VALUES
61 (201, ST_LINEFROMTEXT('LINESTRING(0 0,5 5)')),
62 (202, ST_LINEFROMTEXT('LINESTRING(0 0,2 2,4 4)')),
63 (203, ST_LINEFROMTEXT('LINESTRING(0 0,5 5,10 10)'));
64
65 INSERT INTO gis_linestring VALUES
66 (204, ST_LINESTRINGFROMTEXT('LINESTRING(10 10,5 5)')),
67 (205, ST_LINESTRINGFROMTEXT('LINESTRING(0 0,12 12,24 24)')),
68 (206, ST_LINESTRINGFROMTEXT('LINESTRING(0 0,50 50,100 100)'));
69
70 INSERT INTO gis_linestring VALUES
71 (207, ST_LINEFROMWKB(ST_ASWKB(LINESTRING(POINT(0,0), POINT(5,5))))),
72 (208, ST_LINEFROMWKB(ST_ASWKB(LINESTRING(POINT(0,0), POINT(-5,-5), POINT(-10,10))))),
73 (209, ST_LINEFROMWKB(ST_ASWKB(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6), POINT(8,8)))));
74
75 INSERT INTO gis_linestring VALUES
76 (210, ST_LINESTRINGFROMWKB(ST_ASWKB(LINESTRING(POINT(0,0), POINT(5,5))))),
77 (211, ST_LINESTRINGFROMWKB(ST_ASWKB(LINESTRING(POINT(0,0), POINT(-50,-50), POINT(10,-10))))),
78 (212, ST_LINESTRINGFROMWKB(ST_ASWKB(LINESTRING(POINT(0,0), POINT(1000,1000), POINT(1000,-1000)))));
79
80 INSERT INTO gis_linestring VALUES
81 (213, ST_LINEFROMWKB(ST_ASWKB(ST_LINEFROMTEXT('LINESTRING(1000 1000,1000 -1000)')))),
82 (214, ST_LINEFROMWKB(ST_ASWKB(ST_LINEFROMTEXT('LINESTRING(1 1,2 2,3 3,4 4,5 5,6 6,7 7,8 8,9 9)')))),
83 (215, ST_LINESTRINGFROMWKB(ST_ASWKB(ST_LINESTRINGFROMTEXT('LINESTRING(10 10,10 -10,-10 -10,-10 10,10 10)'))));
84
85 --echo # Inserting POLYGON Geometry Values
86 INSERT INTO gis_polygon VALUES
87 (301, ST_POLYFROMTEXT('POLYGON((0 0,0 5,5 5, 0 0))')),
88 (302, ST_POLYFROMTEXT('POLYGON((0 0,0 5,5 5,5 0,0 0))')),
89 (303, ST_POLYFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0))'));
90
91 INSERT INTO gis_polygon VALUES
92 (304, ST_POLYGONFROMTEXT('POLYGON((0 0,0 50,50 50,50 0,0 0))')),
93 (305, ST_POLYGONFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))')),
94 (306, ST_POLYGONFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(2 2,2 4,4 4,4 2,2 2),(6 6,6 8,8 8,8 6,6 6))'));
95
96 INSERT INTO gis_polygon VALUES
97 (307, ST_POLYFROMWKB(ST_ASWKB(POLYGON(LINESTRING(POINT(0,0), POINT(0,5), POINT(5,5), POINT(0,0)))))),
98 (308, ST_POLYFROMWKB(ST_ASWKB(POLYGON(LINESTRING(POINT(0,0), POINT(0,15), POINT(15,15), POINT(15,0), POINT(0,0)))))),
99 (309, ST_POLYFROMWKB(ST_ASWKB(POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
100 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))))));
101
102 INSERT INTO gis_polygon VALUES
103 (310, ST_POLYGONFROMWKB(ST_ASWKB(POLYGON(LINESTRING(POINT(0,0), POINT(0,5), POINT(5,5), POINT(0,0)))))),
104 (311, ST_POLYGONFROMWKB(ST_ASWKB(POLYGON(LINESTRING(POINT(10,10), POINT(10,15), POINT(15,15), POINT(15,10), POINT(10,10)))))),
105 (312, ST_POLYGONFROMWKB(ST_ASWKB(POLYGON(LINESTRING(POINT(10,10), POINT(10,20), POINT(20,20), POINT(20,10), POINT(10,10)),
106 LINESTRING(POINT(14,14), POINT(14,16), POINT(16,16), POINT(16,14), POINT(14,14))))));
107
108 INSERT INTO gis_polygon VALUES
109 (313, ST_POLYFROMWKB(ST_ASWKB(ST_POLYFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,5 5,0 0))')))),
110 (314, ST_POLYFROMWKB(ST_ASWKB(ST_POLYGONFROMTEXT('POLYGON((10 0,10 10,0 10,-10 10,-10 0,-10 -10,0 10,10 -10,10 0))')))),
111 (315, ST_POLYGONFROMWKB(ST_ASWKB(ST_POLYGONFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0),(2 2,2 4,4 4,4 2,2 2),(6 6,6 8,8 8,8 6,6 6))'))));
112
113 --echo # Inserting MULTIPOINT Geometry Values
114 INSERT INTO gis_multi_point VALUES
115 (401, ST_MPOINTFROMTEXT('MULTIPOINT(0 0)')),
116 (402, ST_MPOINTFROMTEXT('MULTIPOINT(0 0,2 2,4 4)')),
117 (403, ST_MPOINTFROMTEXT('MULTIPOINT(0 0,5 5,10 10)'));
118
119 INSERT INTO gis_multi_point VALUES
120 (404, ST_MULTIPOINTFROMTEXT('MULTIPOINT(0 0,100 100)')),
121 (405, ST_MULTIPOINTFROMTEXT('MULTIPOINT(0 0,1000 1000)')),
122 (406, ST_MULTIPOINTFROMTEXT('MULTIPOINT(1000 1000,1000 -1000,-1000 1000,-1000 -1000)'));
123
124 INSERT INTO gis_multi_point VALUES
125 (407, ST_MPOINTFROMWKB(ST_ASWKB(MULTIPOINT(POINT(0,0))))),
126 (408, ST_MPOINTFROMWKB(ST_ASWKB(MULTIPOINT(POINT(0,0), POINT(10,10))))),
127 (409, ST_MPOINTFROMWKB(ST_ASWKB(MULTIPOINT(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6)))));
128
129 INSERT INTO gis_multi_point VALUES
130 (410, ST_MULTIPOINTFROMWKB(ST_ASWKB(MULTIPOINT(POINT(0,0))))),
131 (411, ST_MULTIPOINTFROMWKB(ST_ASWKB(MULTIPOINT(POINT(0,0), POINT(1000,1000))))),
132 (412, ST_MULTIPOINTFROMWKB(ST_ASWKB(MULTIPOINT(POINT(1000,1000), POINT(-1000,1000), POINT(1000,-1000), POINT(-1000,-1000)))));
133
134 INSERT INTO gis_multi_point VALUES
135 (413, ST_MPOINTFROMWKB(ST_ASWKB(ST_MPOINTFROMTEXT('MULTIPOINT(0 0)')))),
136 (414, ST_MPOINTFROMWKB(ST_ASWKB(ST_MPOINTFROMTEXT('MULTIPOINT(0 0,1000 1000,-1000 -1000)')))),
137 (415, ST_MPOINTFROMWKB(ST_ASWKB(ST_MPOINTFROMTEXT('MULTIPOINT(1000 1000,1000 -1000,-1000 1000,-1000 -1000,1000 1000)'))));
138
139 --echo # Inserting MULTILINESTRING Geometry Values
140 INSERT INTO gis_multi_linestring VALUES
141 (501, ST_MLINEFROMTEXT('MULTILINESTRING((0 0,2 2))')),
142 (502, ST_MLINEFROMTEXT('MULTILINESTRING((0 0,2 2,4 4))')),
143 (503, ST_MLINEFROMTEXT('MULTILINESTRING((0 0,2 2,4 4),(6 6,8 8,10 10))'));
144
145 INSERT INTO gis_multi_linestring VALUES
146 (504, ST_MULTILINESTRINGFROMTEXT('MULTILINESTRING((0 0,100 100,-100 -100))')),
147 (505, ST_MULTILINESTRINGFROMTEXT('MULTILINESTRING((1000 1000,-1000 -1000))')),
148 (506, ST_MULTILINESTRINGFROMTEXT('MULTILINESTRING((1000 1000,-1000 -1000),(1000 -1000,-1000 1000))'));
149
150 INSERT INTO gis_multi_linestring VALUES
151 (507, ST_MLINEFROMWKB(ST_ASWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2)))))),
152 (508, ST_MLINEFROMWKB(ST_ASWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(12,12), POINT(24,24)))))),
153 (509, ST_MLINEFROMWKB(ST_ASWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4)),
154 LINESTRING(POINT(6,6), POINT(8,8), POINT(10,10))))));
155
156 INSERT INTO gis_multi_linestring VALUES
157 (510, ST_MULTILINESTRINGFROMWKB(ST_ASWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4)))))),
158 (511, ST_MULTILINESTRINGFROMWKB(ST_ASWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(1,1), POINT(2,2)))))),
159 (512, ST_MULTILINESTRINGFROMWKB(ST_ASWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(12,12), POINT(24,24)),
160 LINESTRING(POINT(36,36), POINT(48,48), POINT(50,50))))));
161
162 INSERT INTO gis_multi_linestring VALUES
163 (513, ST_MLINEFROMWKB(ST_ASWKB(ST_MLINEFROMTEXT('MULTILINESTRING((0 0,10 10),(0 10,10 0))')))),
164 (514, ST_MLINEFROMWKB(ST_ASWKB(ST_MULTILINESTRINGFROMTEXT('MULTILINESTRING((0 0,10 10,-10 10,0 0),(0 0,-10 -10,10 -10,0 0))')))),
165 (515, ST_MULTILINESTRINGFROMWKB(ST_ASWKB(ST_MULTILINESTRINGFROMTEXT('MULTILINESTRING((0 0,0 100),(0 0,100 0),(0 0,0 -100),(0 0,-100 0))'))));
166
167 --echo # Inserting MULTIPOLGYON Geometry Values
168 INSERT INTO gis_multi_polygon VALUES
169 (601, ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,0 0)))')),
170 (602, ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,0 0)),((5 5,5 10,10 10,5 5)))')),
171 (603, ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4)))'));
172
173 INSERT INTO gis_multi_polygon VALUES
174 (604, ST_MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5, 0 0)))')),
175 (605, ST_MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4, 4 4)),((0 0,0 -2,-2 -2, 0 0)))')),
176 (606, ST_MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((0 0,5 5,-5 5,0 0)),((0 0,-5 -5,5 -5,0 0)))'));
177
178 INSERT INTO gis_multi_polygon VALUES
179 (607, ST_MPOLYFROMWKB(ST_ASWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(5,0), POINT(5,5), POINT(0,5), POINT(0,0))))))),
180 (608, ST_MPOLYFROMWKB(ST_ASWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(10,0), POINT(10,10), POINT(0,10), POINT(0,0)),
181 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))))))),
182 (609, ST_MPOLYFROMWKB(ST_ASWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(5,0), POINT(5,5), POINT(0,5), POINT(0,0))),
183 POLYGON(LINESTRING(POINT(0,0), POINT(-5,0), POINT(-5,-5), POINT(0,-5), POINT(0,0)))))));
184
185 INSERT INTO gis_multi_polygon VALUES
186 (610, ST_MULTIPOLYGONFROMWKB(ST_ASWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(-5,0), POINT(-5,-5), POINT(0,-5), POINT(0,0))))))),
187 (611, ST_MULTIPOLYGONFROMWKB(ST_ASWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(10,10), POINT(20,10), POINT(20,20), POINT(10,20), POINT(10,10)),
188 LINESTRING(POINT(14,14), POINT(14,16), POINT(16,16), POINT(16,14), POINT(14,14))))))),
189 (612, ST_MULTIPOLYGONFROMWKB(ST_ASWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
190 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))),
191 POLYGON(LINESTRING(POINT(0,0), POINT(-5,0), POINT(-5,-5), POINT(0,-5), POINT(0,0)))))));
192
193 INSERT INTO gis_multi_polygon VALUES
194 (613, ST_MPOLYFROMWKB(ST_ASWKB(ST_MPOLYFROMTEXT('MULTIPOLYGON(((0 0,5 5,5 -5,0 0)),((0 0,-5 5,-5 -5,0 0)))')))),
195 (614, ST_MPOLYFROMWKB(ST_ASWKB(ST_MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((0 0,10 10,-10 10,0 0)),((0 0,-10 -10,10 -10,0 0)))')))),
196 (615, ST_MULTIPOLYGONFROMWKB(ST_ASWKB(ST_MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((0 0,5 5,10 0,5 -5,0 0)))'))));
197
198 --echo # Inserting GEOMETRYCOLLECTION Geometry Values
199 INSERT INTO gis_geometrycollection VALUES
200 (701, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))')),
201 (702, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
202 (703, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(5 5), POLYGON((0 0,0 10,10 10,10 0,0 0)))'));
203
204 INSERT INTO gis_geometrycollection VALUES
205 (704, ST_GEOMETRYCOLLECTIONFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
206 (705, ST_GEOMETRYCOLLECTIONFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),'
207 'LINESTRING(0 0,10 10),'
208 'POLYGON((0 0,0 10,10 10,10 0, 0 0)))')),
209 (706, ST_GEOMETRYCOLLECTIONFROMTEXT('GEOMETRYCOLLECTION(MULTIPOINT(0 0,5 5,10 10),'
210 'MULTILINESTRING((0 0,10 10),(0 10,10 0)),'
211 'MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0)),'
212 '((0 0,-10 0,-10 -10,0 -10,0 0))))'));
213
214 INSERT INTO gis_geometrycollection VALUES
215 (707, ST_GEOMCOLLFROMWKB(ST_ASWKB(GEOMETRYCOLLECTION(POINT(0,0))))),
216 (708, ST_GEOMCOLLFROMWKB(ST_ASWKB(GEOMETRYCOLLECTION(POINT(0,0),
217 LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6), POINT(8,8)),
218 POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
219 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))))))),
220 (709, ST_GEOMCOLLFROMWKB(ST_ASWKB(GEOMETRYCOLLECTION(MULTIPOINT(POINT(0,0), POINT(5,5), POINT(10,10)),
221 MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6), POINT(8,8)),
222 LINESTRING(POINT(10,10), POINT(5,5), POINT(0,10))),
223 MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
224 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))),
225 POLYGON(LINESTRING(POINT(0,0), POINT(-10,0), POINT(-10,-10), POINT(0,0))))))));
226
227 INSERT INTO gis_geometrycollection VALUES
228 (710, ST_GEOMETRYCOLLECTIONFROMWKB(ST_ASWKB(GEOMETRYCOLLECTION(POINT(0,0),
229 LINESTRING(POINT(0,0), POINT(100,100)))))),
230 (711, ST_GEOMETRYCOLLECTIONFROMWKB(ST_ASWKB(GEOMETRYCOLLECTION(POINT(10,10),
231 LINESTRING(POINT(10,10), POINT(12,12), POINT(14,14), POINT(16,16), POINT(18,18)),
232 POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
233 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))))))),
234 (712, ST_GEOMETRYCOLLECTIONFROMWKB(ST_ASWKB(GEOMETRYCOLLECTION(MULTIPOINT(POINT(10,10), POINT(15,15), POINT(20,20)),
235 MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6), POINT(8,8)),
236 LINESTRING(POINT(10,10), POINT(5,5), POINT(0,10))),
237 MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
238 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))),
239 POLYGON(LINESTRING(POINT(0,0), POINT(-10,0), POINT(-10,-10), POINT(0,0))))))));
240
241 INSERT INTO gis_geometrycollection VALUES
242 (713, ST_GEOMCOLLFROMWKB(ST_ASWKB(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION('
243 'POINT(0 0),'
244 'LINESTRING(0 0,10 10),'
245 'POLYGON((0 0,0 10,10 10,10 0, 0 0)),'
246 'MULTIPOINT(0 0,2 2,4 4,6 6,8 8,10 10),'
247 'MULTILINESTRING((0 0,10 10),(0 10,10 0)),'
248 'MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5))))')))),
249 (714, ST_GEOMCOLLFROMWKB(ST_ASWKB(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION('
250 'GEOMETRYCOLLECTION('
251 'POINT(0 0)),'
252 'GEOMETRYCOLLECTION('
253 'LINESTRING(0 0,10 10)),'
254 'GEOMETRYCOLLECTION('
255 'POLYGON((0 0,0 10,10 10,10 0, 0 0))),'
256 'GEOMETRYCOLLECTION('
257 'MULTIPOINT(0 0,2 2,4 4,6 6,8 8,10 10)),'
258 'GEOMETRYCOLLECTION('
259 'MULTILINESTRING((0 0,10 10),(0 10,10 0))),'
260 'GEOMETRYCOLLECTION('
261 'MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5)))))')))),
262 (715, ST_GEOMCOLLFROMWKB(ST_ASWKB(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION('
263 'GEOMETRYCOLLECTION(),'
264 'POINT(0 0),'
265 'GEOMETRYCOLLECTION('
266 'LINESTRING(0 0,10 10),'
267 'GEOMETRYCOLLECTION('
268 'GEOMETRYCOLLECTION())),'
269 'GEOMETRYCOLLECTION(),'
270 'GEOMETRYCOLLECTION('
271 'GEOMETRYCOLLECTION()),'
272 'POLYGON((0 0,0 10,10 10,10 0, 0 0)),'
273 'MULTIPOINT(0 0,2 2,4 4,6 6,8 8,10 10),'
274 'MULTILINESTRING((0 0,10 10),(0 10,10 0)),'
275 'MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5))))'))));
276
277 ############################################################################################
278 # Inserting the spatial values of all kinds to the parent class #
279 ############################################################################################
280
281 --echo # Inserting the spatial values of all kinds to the parent class
282 INSERT INTO gis_geometry SELECT * FROM gis_point;
283
284 INSERT INTO gis_geometry SELECT * FROM gis_linestring;
285
286 INSERT INTO gis_geometry SELECT * FROM gis_polygon;
287
288 INSERT INTO gis_geometry SELECT * FROM gis_multi_point;
289
290 INSERT INTO gis_geometry SELECT * FROM gis_multi_linestring;
291
292 INSERT INTO gis_geometry SELECT * FROM gis_multi_polygon;
293
294 INSERT INTO gis_geometry SELECT * FROM gis_geometrycollection;
295
296 --echo # Checking the integrity of the above insert statements
297 SELECT COUNT(g) FROM gis_geometry;
298
299 SELECT COUNT(ST_ASTEXT(g)) FROM gis_geometry;
300
301 SELECT COUNT(DISTINCT(g)) FROM gis_geometry;
302
303 SELECT COUNT(DISTINCT(ST_ASTEXT(g))) FROM gis_geometry;
304
305 ############################################################################################
306 # Displaying the spatial data FROM all the spatial objects created #
307 ############################################################################################
308
309 --echo # Displaying the inserted spatial Data
310 SELECT fid, ST_ASTEXT(g) FROM gis_point;
311
312 SELECT fid, ST_ASTEXT(g) FROM gis_linestring;
313
314 SELECT fid, ST_ASTEXT(g) FROM gis_polygon;
315
316 SELECT fid, ST_ASTEXT(g) FROM gis_multi_point;
317
318 SELECT fid, ST_ASTEXT(g) FROM gis_multi_linestring;
319
320 SELECT fid, ST_ASTEXT(g) FROM gis_multi_polygon;
321
322 SELECT fid, ST_ASTEXT(g) FROM gis_geometrycollection;
323
324 SELECT fid, ST_ASTEXT(g) FROM gis_geometry;
325
326 ############################################################################################
327 # Checking the geometry type of the spatial objects inserted into GEOMETRY class #
328 ############################################################################################
329
330 --echo # Checking the geometry type of the spatial objects inserted into GEOMETRY class
331 SELECT fid, ST_GEOMETRYTYPE(g) FROM gis_geometry;
332
333 SELECT COUNT(ST_GEOMETRYTYPE(g)) FROM gis_geometry;
334
335 SELECT COUNT(DISTINCT(ST_GEOMETRYTYPE(g))) FROM gis_geometry;
336
337 ############################################################################################
338 # Checking the SRID of the spatial objects inserted into GEOMETRY class #
339 ############################################################################################
340
341 --echo # Checking the ST_SRID of the spatial objects inserted into GEOMETRY class
342 SELECT fid, ST_SRID(g) FROM gis_geometry;
343
344 SELECT COUNT(ST_SRID(g)) FROM gis_geometry;
345
346 SELECT COUNT(DISTINCT(ST_SRID(g))) FROM gis_geometry;
347
348 ############################################################################################
349 # Checking the Dimension of the spatial objects inserted into GEOMETRY class #
350 ############################################################################################
351
352 --echo # Checking the Dimension of the spatial objects inserted into GEOMETRY class
353 SELECT fid, ST_DIMENSION(g) FROM gis_geometry;
354
355 SELECT COUNT(ST_DIMENSION(g)) FROM gis_geometry;
356
357 SELECT COUNT(DISTINCT(ST_DIMENSION(g))) FROM gis_geometry;
358
359 ############################################################################################
360 # Checking if the geometries are simple or not #
361 ############################################################################################
362
363 --echo # Checking if the geometries are simple or not
364 SELECT fid, ST_ISSIMPLE(g) FROM gis_geometry;
365
366 SELECT COUNT(ST_ISSIMPLE(g)) FROM gis_geometry;
367
368 SELECT COUNT(DISTINCT(ST_ISSIMPLE(g))) FROM gis_geometry;
369
370 ############################################################################################
371 # Checking the Envelope of the spatial objects inserted into GEOMETRY class #
372 ############################################################################################
373
374 --echo # Checking the Envelope of the spatial objects inserted into GEOMETRY class
375 SELECT fid, ST_ASTEXT(ST_ENVELOPE(g)) FROM gis_geometry;
376
377 SELECT COUNT(ST_ENVELOPE(g)) FROM gis_geometry;
378
379 SELECT COUNT(DISTINCT(ST_ENVELOPE(g))) FROM gis_geometry;
380
381 ############################################################################################
382 # Checking the Centroid of the spatial objects inserted into GEOMETRY class #
383 ############################################################################################
384
385 --echo # Checking the Centroid of the spatial objects inserted into GEOMETRY class
386 --replace_numeric_round 10
387 SELECT fid, ST_ASTEXT(ST_CENTROID(g)) FROM gis_geometry;
388 --replace_numeric_round 10
389 SELECT COUNT(ST_CENTROID(g)) FROM gis_geometry;
390 --replace_numeric_round 10
391 SELECT COUNT(DISTINCT(ST_CENTROID(g))) FROM gis_geometry;
392
393 ############################################################################################
394 # Checking the ConvexHull of the spatial objects inserted into GEOMETRY class #
395 ############################################################################################
396
397 --echo # Checking the ConvexHull of the spatial objects inserted into GEOMETRY class
398 SELECT fid, ST_ASTEXT(ST_CONVEXHULL(g)) FROM gis_geometry;
399
400 SELECT COUNT(ST_CONVEXHULL(g)) FROM gis_geometry;
401
402 SELECT COUNT(DISTINCT(ST_CONVEXHULL(g))) FROM gis_geometry;
403
404 ############################################################################################
405 # Checking if the geometries are empty or not #
406 ############################################################################################
407
408 --echo # Checking if the geometries are empty or not
409 SELECT fid, ST_ISEMPTY(g) FROM gis_geometry;
410
411 SELECT COUNT(ST_ISEMPTY(g)) FROM gis_geometry;
412
413 SELECT COUNT(DISTINCT(ST_ISEMPTY(g))) FROM gis_geometry;
414
415 ############################################################################################
416 # Checking the procedure to work with the Geometry type properties #
417 ############################################################################################
418
419 --echo # Check the procedure to work with the Geometry type properties
420
421 DELIMITER |;
422 CREATE PROCEDURE geom_attri()
423 BEGIN
424 SELECT fid, ST_GEOMETRYTYPE(g) FROM gis_geometry;
425 SELECT fid, ST_SRID(g) FROM gis_geometry;
426 SELECT fid, ST_DIMENSION(g) FROM gis_geometry;
427 SELECT fid, ST_ISSIMPLE(g) FROM gis_geometry;
428 SELECT fid, ST_ASTEXT(ST_ENVELOPE(g)) FROM gis_geometry;
429 SELECT fid, ST_ASTEXT(ST_CENTROID(g)) FROM gis_geometry;
430 SELECT fid, ST_ASTEXT(ST_CONVEXHULL(g)) FROM gis_geometry;
431 SELECT fid, ST_ISEMPTY(g) FROM gis_geometry;
432 END |
433 DELIMITER ;|
434
435 --echo # Call the proc
436 --replace_numeric_round 10
437 CALL geom_attri;
438
439 --echo # Dropping the created procedure
440 DROP PROCEDURE geom_attri;
441
442 ############################################################################################
443 # Checking the trigger to work with the Geometry type properties #
444 ############################################################################################
445
446 --echo # Creating an empty table as gis_geometry_1
447 CREATE TABLE gis_geometry_1 AS SELECT * FROM gis_geometry;
448 TRUNCATE TABLE gis_geometry_1;
449
450 --echo # Create a tigger to populate the data FROM gis_geometry to gis_geometry_1
451 DELIMITER |;
452 CREATE TRIGGER geom_trigger AFTER UPDATE ON gis_geometry
453 FOR EACH ROW
454 BEGIN
455 INSERT INTO gis_geometry_1 SELECT * FROM gis_geometry;
456 END|
457 DELIMITER ;|
458
459 --echo # Calling the trigger
460 UPDATE gis_geometry SET fid = 999 where fid = 111;
461
462 --echo # Checking the GEOMETRY class properties on the spatial data in the gis_geometry_1 table
463 SELECT fid, ST_GEOMETRYTYPE(g) FROM gis_geometry_1;
464 SELECT fid, ST_SRID(g) FROM gis_geometry_1;
465 SELECT fid, ST_DIMENSION(g) FROM gis_geometry_1;
466 SELECT fid, ST_ISSIMPLE(g) FROM gis_geometry_1;
467 SELECT fid, ST_ASTEXT(ST_ENVELOPE(g)) FROM gis_geometry_1;
468 --replace_numeric_round 10
469 SELECT fid, ST_ASTEXT(ST_CENTROID(g)) FROM gis_geometry_1;
470 SELECT fid, ST_ASTEXT(ST_CONVEXHULL(g)) FROM gis_geometry_1;
471 SELECT fid, ST_ISEMPTY(g) FROM gis_geometry_1;
472
473 --echo # Cleaning up the trigger
474 DROP TRIGGER geom_trigger;
475
476 ############################################################################################
477 # Checking the Geometry class properties within the cursor #
478 ############################################################################################
479
480 --echo # Checking the Geometry class properties within the cursor
481
482 DELIMITER |;
483 CREATE PROCEDURE geom_cursor()
484 BEGIN
485 DECLARE v GEOMETRY;
486 DECLARE c CURSOR FOR SELECT g FROM gis_geometry;
487 OPEN c;
488 FETCH c INTO v;
489 CLOSE c;
490 SELECT ST_GEOMETRYTYPE(v);
491 SELECT ST_SRID(v);
492 SELECT ST_DIMENSION(v);
493 SELECT ST_ASTEXT(ST_ENVELOPE(v));
494 SELECT ST_ISSIMPLE(v);
495 SELECT ST_ASTEXT(ST_CENTROID(v));
496 SELECT ST_ASTEXT(ST_CONVEXHULL(v));
497 SELECT ST_ISEMPTY(v);
498 END|
499 DELIMITER ;|
500
501 --echo # Calling the cursor
502 CALL geom_cursor();
503
504 --echo # Dropping the created cursor
505 DROP PROCEDURE geom_cursor;
506
507 ############################################################################################
508 # Checking the Self join with the geometry properties #
509 ############################################################################################
510
511 --echo # Checking the Self join with the geometry properties
512
513 SELECT
514 ST_GEOMETRYTYPE(tableX.g), ST_GEOMETRYTYPE(tableY.g),
515 ST_SRID(tableX.g), ST_SRID(tableX.g),
516 ST_DIMENSION(tableX.g), ST_DIMENSION(tableX.g),
517 ST_ISSIMPLE(tableX.g), ST_ISSIMPLE(tableX.g),
518 ST_ISEMPTY(tableX.g), ST_ISEMPTY(tableX.g)
519 FROM gis_geometry AS tableX, gis_geometry AS tableY
520 WHERE
521 ST_GEOMETRYTYPE(tableX.g) = ST_GEOMETRYTYPE(tableY.g) AND
522 ST_SRID(tableX.g) = ST_SRID(tableY.g) AND
523 ST_DIMENSION(tableX.g) = ST_DIMENSION(tableY.g) AND
524 ST_ISSIMPLE(tableX.g) = ST_ISSIMPLE(tableY.g) AND
525 ST_ISEMPTY(tableX.g) = ST_ISEMPTY(tableY.g);
526
527 ############################################################################################
528 # Checking the aggregate functions on the geometry class properties #
529 ############################################################################################
530
531 --echo # Checking the aggregate functions on the geometry class properties
532
533 --echo # Checking the SUM function
534 SELECT SUM(ST_GEOMETRYTYPE(g)) FROM gis_geometry WHERE fid % 100 = 1;
535
536 SELECT SUM(ST_SRID(g)) FROM gis_geometry;
537
538 SELECT SUM(ST_DIMENSION(g)) FROM gis_geometry;
539
540 SELECT SUM(ST_ISSIMPLE(g)) FROM gis_geometry;
541
542 SELECT SUM(ST_ISEMPTY(g)) FROM gis_geometry;
543
544 --echo # Checking the MAX function
545 SELECT MAX(ST_GEOMETRYTYPE(g)) FROM gis_geometry;
546
547 SELECT MAX(ST_SRID(g)) FROM gis_geometry;
548
549 SELECT MAX(ST_DIMENSION(g)) FROM gis_geometry;
550
551 SELECT MAX(ST_ISSIMPLE(g)) FROM gis_geometry;
552
553 SELECT MAX(ST_ISEMPTY(g)) FROM gis_geometry;
554
555 --echo # Checking the MIN function
556 SELECT MIN(ST_GEOMETRYTYPE(g)) FROM gis_geometry;
557
558 SELECT MIN(ST_SRID(g)) FROM gis_geometry;
559
560 SELECT MIN(ST_DIMENSION(g)) FROM gis_geometry;
561
562 SELECT MIN(ST_ISSIMPLE(g)) FROM gis_geometry;
563
564 SELECT MIN(ST_ISEMPTY(g)) FROM gis_geometry;
565
566 --echo # Checking the STD function
567 SELECT STD(ST_GEOMETRYTYPE(g)) FROM gis_geometry WHERE fid % 100 = 1;
568
569 SELECT STD(ST_SRID(g)) FROM gis_geometry;
570
571 --replace_numeric_round 10
572 SELECT STD(ST_DIMENSION(g)) FROM gis_geometry;
573
574 SELECT STD(ST_ISSIMPLE(g)) FROM gis_geometry;
575
576 SELECT STD(ST_ISEMPTY(g)) FROM gis_geometry;
577
578 --echo # Checking the AVG function
579 SELECT AVG(ST_GEOMETRYTYPE(g)) FROM gis_geometry WHERE fid % 100 = 1;
580
581 SELECT AVG(ST_SRID(g)) FROM gis_geometry;
582
583 SELECT AVG(ST_DIMENSION(g)) FROM gis_geometry;
584
585 SELECT AVG(ST_ISSIMPLE(g)) FROM gis_geometry;
586
587 SELECT AVG(ST_ISEMPTY(g)) FROM gis_geometry;
588
589 --echo # Checking the DISTINCT function
590 SELECT COUNT(DISTINCT(ST_GEOMETRYTYPE(g))) FROM gis_geometry;
591
592 SELECT COUNT(DISTINCT(ST_SRID(g))) FROM gis_geometry;
593
594 SELECT COUNT(DISTINCT(ST_DIMENSION(g))) FROM gis_geometry;
595
596 SELECT COUNT(DISTINCT(ST_ENVELOPE(g))) FROM gis_geometry;
597
598 SELECT COUNT(DISTINCT(ST_ISSIMPLE(g))) FROM gis_geometry;
599
600 SELECT COUNT(DISTINCT(ST_CENTROID(g))) FROM gis_geometry;
601
602 SELECT COUNT(DISTINCT(ST_CONVEXHULL(g))) FROM gis_geometry;
603
604 SELECT COUNT(DISTINCT(ST_ISEMPTY(g))) FROM gis_geometry;
605
606 --echo # Final cleanup
607 DROP TABLE gis_point;
608 DROP TABLE gis_linestring;
609 DROP TABLE gis_polygon;
610 DROP TABLE gis_multi_point;
611 DROP TABLE gis_multi_linestring;
612 DROP TABLE gis_multi_polygon;
613 DROP TABLE gis_geometrycollection;
614 DROP TABLE gis_geometry;
615 DROP TABLE gis_geometry_1;