Debian Codegraph @

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

raw
1 #####################################################################################
2 # Creating a spatial Geometry object
3 #####################################################################################
4 USE test;
5 CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
6 SET @star_of_elems='MULTIPOLYGON(((5 0,15 5,25 0,22 10,30 15,19 15,5 0)),((19 15,15 25,11 15,19 15)))';
7 SET @star_collection_elems='GEOMETRYCOLLECTION(MULTIPOLYGON(((5 0,15 5,25 0,22 10,30 15,19 15,5 0)),((19 15,15 25,11 15,19 15))),POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0),(10 6,10 10,7 12,13 13,15 18,17 13,21 13,18 10,20 6,15 8,10 6)),MULTILINESTRING((25 0,0 15,15 30,0 5)),LINESTRING(15 25,15 10),MULTIPOINT(5 0,25 0),POINT(15 25))';
8 # INSERT star
9 INSERT INTO gis_geometrycollection VALUES (103,ST_GEOMFROMTEXT(@star_of_elems));
10 CREATE TABLE gis_geometrycollection_2 SELECT fid as fid2,g as g2 FROM gis_geometrycollection;
11 # Checking the integrity of the above create/insert statements
12 # 1 row.
13 SELECT count(g IS NULL) FROM gis_geometrycollection;
14 count(g IS NULL)
15 1
16 # 1 row.
17 SELECT count(g2 is NULL) FROM gis_geometrycollection_2;
18 count(g2 is NULL)
19 1
20 #####################################################################################
21 # Testing ST_Union() with different ways to pass input geometries
22 #####################################################################################
23 SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT(@star_collection_elems)));
24 ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT(@star_collection_elems)))
25 GEOMETRYCOLLECTION(POLYGON((15 25,10 15,0 15,7 10,5 0,15 5,25 0,22 10,30 15,20 15,15 25),(16.2 15,17 13,17.133333333333333 13,15.074704491725768 10.794326241134751,10.957442978734894 6.382977191493957,10 6,10 10,7 12,13 13,13.8 15,16.2 15)),LINESTRING(12.179486097659181 7.692308341404491,10 9),LINESTRING(0 15,15 30,6 15),LINESTRING(4.2 12,0 5),LINESTRING(15 15,15 10.714285760942136))
26 SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')));
27 ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')))
28 MULTIPOLYGON(((1 1,2 1,2 2,1 2,1 1)),((5 0,15 5,25 0,22 10,30 15,19 15,5 0)),((19 15,15 25,11 15,19 15)))
29 SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')));
30 ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')))
31 GEOMETRYCOLLECTION(POLYGON((1 1,2 1,2 2,1 2,1 1)),POINT(0 0))
32 SELECT ST_ASTEXT(ST_UNION(g,ST_GEOMFROMTEXT(@star_of_elems))) FROM gis_geometrycollection WHERE fid=103;
33 ST_ASTEXT(ST_UNION(g,ST_GEOMFROMTEXT(@star_of_elems)))
34 MULTIPOLYGON(((19 15,5 0,15 5,25 0,22 10,30 15,19 15)),((19 15,15 25,11 15,19 15)))
35 SELECT ST_ASTEXT(ST_UNION(g,ST_GEOMFROMTEXT('POINT(0 0)'))) FROM gis_geometrycollection WHERE fid=103;
36 ST_ASTEXT(ST_UNION(g,ST_GEOMFROMTEXT('POINT(0 0)')))
37 GEOMETRYCOLLECTION(POLYGON((5 0,15 5,25 0,22 10,30 15,19 15,5 0)),POLYGON((19 15,15 25,11 15,19 15)),POINT(0 0))
38 SELECT ST_ASTEXT(ST_UNION(g,g2)) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103;
39 ST_ASTEXT(ST_UNION(g,g2))
40 MULTIPOLYGON(((19 15,5 0,15 5,25 0,22 10,30 15,19 15)),((19 15,15 25,11 15,19 15)))
41 #####################################################################################
42 # Verify ST_Union() returns NULL if at least one of its arguments are NULL.
43 #####################################################################################
44 SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT(@star_of_elems),NULL));
45 ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT(@star_of_elems),NULL))
46 NULL
47 SELECT ST_ASTEXT(ST_UNION(NULL,ST_GEOMFROMTEXT(@star_of_elems)));
48 ST_ASTEXT(ST_UNION(NULL,ST_GEOMFROMTEXT(@star_of_elems)))
49 NULL
50 SELECT ST_ASTEXT(ST_UNION(NULL,ST_GEOMFROMTEXT('POINT(0 0)')));
51 ST_ASTEXT(ST_UNION(NULL,ST_GEOMFROMTEXT('POINT(0 0)')))
52 NULL
53 SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)'),NULL));
54 ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)'),NULL))
55 NULL
56 SELECT ST_ASTEXT(ST_UNION(g,NULL)) FROM gis_geometrycollection WHERE fid=103;
57 ST_ASTEXT(ST_UNION(g,NULL))
58 NULL
59 SELECT ST_ASTEXT(ST_UNION(NULL,NULL));
60 ST_ASTEXT(ST_UNION(NULL,NULL))
61 NULL
62 SELECT ST_ASTEXT(ST_UNION(fid,NULL)) FROM gis_geometrycollection WHERE fid=103;
63 ST_ASTEXT(ST_UNION(fid,NULL))
64 NULL
65 #####################################################################################
66 # WL#8579 Spatial Reference Systems
67 #####################################################################################
68 # SRID 0 (should pass)
69 SELECT ST_ASTEXT(ST_UNION(
70 ST_GEOMFROMTEXT('POINT(0 0)', 0),
71 ST_GEOMFROMTEXT('POINT(0 0)', 0)
72 ));
73 ST_ASTEXT(ST_UNION(
74 ST_GEOMFROMTEXT('POINT(0 0)', 0),
75 ST_GEOMFROMTEXT('POINT(0 0)', 0)
76 ))
77 POINT(0 0)
78 # Projected SRS (should pass)
79 SELECT ST_ASTEXT(ST_UNION(
80 ST_GEOMFROMTEXT('POINT(0 0)', 2000),
81 ST_GEOMFROMTEXT('POINT(0 0)', 2000)
82 ));
83 ST_ASTEXT(ST_UNION(
84 ST_GEOMFROMTEXT('POINT(0 0)', 2000),
85 ST_GEOMFROMTEXT('POINT(0 0)', 2000)
86 ))
87 POINT(0 0)
88 # Geographic SRS (should pass)
89 SELECT ST_ASTEXT(ST_UNION(
90 ST_GEOMFROMTEXT('POINT(0 0)', 4326),
91 ST_GEOMFROMTEXT('POINT(0 0)', 4326)
92 ));
93 ST_ASTEXT(ST_UNION(
94 ST_GEOMFROMTEXT('POINT(0 0)', 4326),
95 ST_GEOMFROMTEXT('POINT(0 0)', 4326)
96 ))
97 POINT(0 0)
98 #####################################################################################
99 # Invalid function calls
100 #####################################################################################
101 # Undefined SRS' in the input geometries should return ER_SRS_NOT_FOUND.
102 DO ST_ASTEXT(ST_UNION(x'40420f00010100000000000000000000000000000000000000',x'40420f00010100000000000000000000000000000000000000'));
103 ERROR SR001: There's no spatial reference system with SRID 1000000.
104 # Different SRIDs in the input geometries should return ER_GIS_DIFFERENT_SRIDS.
105 DO ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POINT(0 1)',4326)));
106 ERROR HY000: Binary geometry function st_union given two geometries of different srids: 0 and 4326, which should have been identical.
107 DO ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)',4145),ST_GEOMFROMTEXT('POINT(0 1)',4326)));
108 ERROR HY000: Binary geometry function st_union given two geometries of different srids: 4145 and 4326, which should have been identical.
109 # Not syntactically well-formed geometry input should result in ER_GIS_INVALID_DATA.
110 SELECT ST_ASTEXT(ST_UNION(fid,ST_GEOMFROMTEXT('POINT(0 0)'))) FROM gis_geometrycollection WHERE fid=103;
111 ERROR 22023: Invalid GIS data provided to function st_union.
112 SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)'),fid)) FROM gis_geometrycollection WHERE fid=103;
113 ERROR 22023: Invalid GIS data provided to function st_union.
114 # Return ER_GEOMETRY_PARAM_(LONGITUDE/LATITUDE)_OUT_OF_RANGE if longitude and latitude values are not
115 # within their respective ranges (-180,180] and [-90,90]).
116 SET @pt = ST_GeomFromText('POINT(0 0)', 4326);
117 SET @pt_lat_minus_91 = x'E6100000010100000000000000000000000000000000C056C0';
118 SET @pt_lat_plus_91 = x'E6100000010100000000000000000000000000000000C05640';
119 SET @pt_long_minus_181 = x'E610000001010000000000000000A066C00000000000000000';
120 SET @pt_long_plus_181 = x'E610000001010000000000000000A066400000000000000000';
121 DO ST_UNION(@pt_lat_minus_91, @pt);
122 ERROR 22S03: A parameter of function st_union contains a geometry with latitude -91.000000, which is out of range. It must be within [-90.000000, 90.000000].
123 DO ST_UNION(@pt_lat_plus_91, @pt);
124 ERROR 22S03: A parameter of function st_union contains a geometry with latitude 91.000000, which is out of range. It must be within [-90.000000, 90.000000].
125 DO ST_UNION(@pt_long_minus_181, @pt);
126 ERROR 22S02: A parameter of function st_union contains a geometry with longitude -181.000000, which is out of range. It must be within (-180.000000, 180.000000].
127 DO ST_UNION(@pt_long_plus_181, @pt);
128 ERROR 22S02: A parameter of function st_union contains a geometry with longitude 181.000000, which is out of range. It must be within (-180.000000, 180.000000].
129 # Cartesian SRS
130 SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))'),ST_GEOMFROMTEXT('POLYGON((0 1,1 1,1 2,0 2,0 1))')));
131 ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))'),ST_GEOMFROMTEXT('POLYGON((0 1,1 1,1 2,0 2,0 1))')))
132 POLYGON((2 2,0 2,0 1,1 1,2 1,2 2))
133 # Geographic SRS
134 SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POLYGON((1 1,1 2,2 2,2 1,1 1))', 4326),ST_GEOMFROMTEXT('POLYGON((0 1,0 2,1 2,1 1,0 1))', 4326)));
135 ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POLYGON((1 1,1 2,2 2,2 1,1 1))', 4326),ST_GEOMFROMTEXT('POLYGON((0 1,0 2,1 2,1 1,0 1))', 4326)))
136 POLYGON((0 2,2 2,2 1,1 1,0 1,0 2))
137 #clean up
138 DROP TABLE gis_geometrycollection;
139 DROP TABLE gis_geometrycollection_2;