Debian Codegraph @

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

raw
1 ###############################################################################
2 # #
3 # This test tests the ST_Union function. #
4 # ST_Union returns the union geometry of two geometries. #
5 # The function is defined by OGC standard documents. #
6 # #
7 ###############################################################################
8
9 --echo #####################################################################################
10 --echo # Creating a spatial Geometry object
11 --echo #####################################################################################
12 USE test;
13 CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
14 # Geometric elements constructing a star
15 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)))';
16 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))';
17 --echo # INSERT star
18 --disable_warnings
19 INSERT INTO gis_geometrycollection VALUES (103,ST_GEOMFROMTEXT(@star_of_elems));
20 --enable_warnings
21 CREATE TABLE gis_geometrycollection_2 SELECT fid as fid2,g as g2 FROM gis_geometrycollection;
22 --echo # Checking the integrity of the above create/insert statements
23 --echo # 1 row.
24 SELECT count(g IS NULL) FROM gis_geometrycollection;
25 --echo # 1 row.
26 SELECT count(g2 is NULL) FROM gis_geometrycollection_2;
27
28 --echo #####################################################################################
29 --echo # Testing ST_Union() with different ways to pass input geometries
30 --echo #####################################################################################
31 SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT(@star_collection_elems)));
32 SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')));
33 SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')));
34 SELECT ST_ASTEXT(ST_UNION(g,ST_GEOMFROMTEXT(@star_of_elems))) FROM gis_geometrycollection WHERE fid=103;
35 SELECT ST_ASTEXT(ST_UNION(g,ST_GEOMFROMTEXT('POINT(0 0)'))) FROM gis_geometrycollection WHERE fid=103;
36 SELECT ST_ASTEXT(ST_UNION(g,g2)) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103;
37
38 --echo #####################################################################################
39 --echo # Verify ST_Union() returns NULL if at least one of its arguments are NULL.
40 --echo #####################################################################################
41 SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT(@star_of_elems),NULL));
42 SELECT ST_ASTEXT(ST_UNION(NULL,ST_GEOMFROMTEXT(@star_of_elems)));
43 SELECT ST_ASTEXT(ST_UNION(NULL,ST_GEOMFROMTEXT('POINT(0 0)')));
44 SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)'),NULL));
45 SELECT ST_ASTEXT(ST_UNION(g,NULL)) FROM gis_geometrycollection WHERE fid=103;
46 SELECT ST_ASTEXT(ST_UNION(NULL,NULL));
47 SELECT ST_ASTEXT(ST_UNION(fid,NULL)) FROM gis_geometrycollection WHERE fid=103;
48
49 --echo #####################################################################################
50 --echo # WL#8579 Spatial Reference Systems
51 --echo #####################################################################################
52 --echo # SRID 0 (should pass)
53 SELECT ST_ASTEXT(ST_UNION(
54 ST_GEOMFROMTEXT('POINT(0 0)', 0),
55 ST_GEOMFROMTEXT('POINT(0 0)', 0)
56 ));
57 --echo # Projected SRS (should pass)
58 SELECT ST_ASTEXT(ST_UNION(
59 ST_GEOMFROMTEXT('POINT(0 0)', 2000),
60 ST_GEOMFROMTEXT('POINT(0 0)', 2000)
61 ));
62 --echo # Geographic SRS (should pass)
63 SELECT ST_ASTEXT(ST_UNION(
64 ST_GEOMFROMTEXT('POINT(0 0)', 4326),
65 ST_GEOMFROMTEXT('POINT(0 0)', 4326)
66 ));
67
68 --echo #####################################################################################
69 --echo # Invalid function calls
70 --echo #####################################################################################
71 --echo # Undefined SRS' in the input geometries should return ER_SRS_NOT_FOUND.
72 # Assume SRID 1000000000 is undefined.
73 --error ER_SRS_NOT_FOUND
74 DO ST_ASTEXT(ST_UNION(x'40420f00010100000000000000000000000000000000000000',x'40420f00010100000000000000000000000000000000000000'));
75
76 --echo # Different SRIDs in the input geometries should return ER_GIS_DIFFERENT_SRIDS.
77 --error ER_GIS_DIFFERENT_SRIDS
78 DO ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POINT(0 1)',4326)));
79 --error ER_GIS_DIFFERENT_SRIDS
80 DO ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)',4145),ST_GEOMFROMTEXT('POINT(0 1)',4326)));
81
82 --echo # Not syntactically well-formed geometry input should result in ER_GIS_INVALID_DATA.
83 --error ER_GIS_INVALID_DATA
84 SELECT ST_ASTEXT(ST_UNION(fid,ST_GEOMFROMTEXT('POINT(0 0)'))) FROM gis_geometrycollection WHERE fid=103;
85 --error ER_GIS_INVALID_DATA
86 SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)'),fid)) FROM gis_geometrycollection WHERE fid=103;
87
88 --echo # Return ER_GEOMETRY_PARAM_(LONGITUDE/LATITUDE)_OUT_OF_RANGE if longitude and latitude values are not
89 --echo # within their respective ranges (-180,180] and [-90,90]).
90 SET @pt = ST_GeomFromText('POINT(0 0)', 4326);
91
92 # SRID=4326, long=0, lat=-91.
93 SET @pt_lat_minus_91 = x'E6100000010100000000000000000000000000000000C056C0';
94
95 # SRID=4326, long=0, lat=91.
96 SET @pt_lat_plus_91 = x'E6100000010100000000000000000000000000000000C05640';
97
98 # SRID=4326, long=-181, lat=0.
99 SET @pt_long_minus_181 = x'E610000001010000000000000000A066C00000000000000000';
100
101 # SRID=4326, long=181, lat=0.
102 SET @pt_long_plus_181 = x'E610000001010000000000000000A066400000000000000000';
103
104 --error ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE
105 DO ST_UNION(@pt_lat_minus_91, @pt);
106 --error ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE
107 DO ST_UNION(@pt_lat_plus_91, @pt);
108 --error ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE
109 DO ST_UNION(@pt_long_minus_181, @pt);
110 --error ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE
111 DO ST_UNION(@pt_long_plus_181, @pt);
112
113 --echo # Cartesian SRS
114 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))')));
115 --echo # Geographic SRS
116 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)));
117
118 --echo #clean up
119 DROP TABLE gis_geometrycollection;
120 DROP TABLE gis_geometrycollection_2;