Debian Codegraph @

mysql-test/suite/gis/r/st_symdifference.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_SymDifference() with different ways to pass input geometries
22 #####################################################################################
23 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT(@star_collection_elems)));
24 ST_ASTEXT(ST_SYMDIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT(@star_collection_elems)))
25 GEOMETRYCOLLECTION(MULTIPOLYGON(((15.0747044917 10.7943262411,10.9574429787 6.3829771915,10.9574468085 6.3829787234,15.0747044917 10.7943262411)),((15.0747044917 10.7943262411,19 15,16.2 15,17 13,17.1333333333 13,15.0747044917 10.7943262411)),((10.9574429787 6.3829771915,10 6,10 10,7 12,13 13,13.8 15,11 15,15 25,10 15,0 15,7 10,5 0,10.9574429787 6.3829771915)),((15 25,19 15,20 15,15 25))),MULTILINESTRING((12.1794871795 7.6923076923,10 9),(0 15,15 30,6 15),(4.2 12,0 5),(15 15,15 10.7142857143)))
26 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')));
27 ST_ASTEXT(ST_SYMDIFFERENCE(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_SYMDIFFERENCE(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')));
30 ST_ASTEXT(ST_SYMDIFFERENCE(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_SYMDIFFERENCE(g,ST_GEOMFROMTEXT(@star_of_elems))) FROM gis_geometrycollection WHERE fid=103;
33 ST_ASTEXT(ST_SYMDIFFERENCE(g,ST_GEOMFROMTEXT(@star_of_elems)))
34 GEOMETRYCOLLECTION EMPTY
35 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(g,ST_GEOMFROMTEXT('POINT(0 0)'))) FROM gis_geometrycollection WHERE fid=103;
36 ST_ASTEXT(ST_SYMDIFFERENCE(g,ST_GEOMFROMTEXT('POINT(0 0)')))
37 GEOMETRYCOLLECTION(MULTIPOLYGON(((5 0,15 5,25 0,22 10,30 15,19 15,5 0)),((19 15,15 25,11 15,19 15))),POINT(0 0))
38 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(g,g2)) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103;
39 ST_ASTEXT(ST_SYMDIFFERENCE(g,g2))
40 GEOMETRYCOLLECTION EMPTY
41 #####################################################################################
42 # Verify ST_SymDifference() returns NULL if at least one of its arguments are NULL.
43 #####################################################################################
44 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),NULL));
45 ST_ASTEXT(ST_SYMDIFFERENCE(ST_GEOMFROMTEXT(@star_of_elems),NULL))
46 NULL
47 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(NULL,ST_GEOMFROMTEXT(@star_of_elems)));
48 ST_ASTEXT(ST_SYMDIFFERENCE(NULL,ST_GEOMFROMTEXT(@star_of_elems)))
49 NULL
50 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(NULL,ST_GEOMFROMTEXT('POINT(0 0)')));
51 ST_ASTEXT(ST_SYMDIFFERENCE(NULL,ST_GEOMFROMTEXT('POINT(0 0)')))
52 NULL
53 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(ST_GEOMFROMTEXT('POINT(0 0)'),NULL));
54 ST_ASTEXT(ST_SYMDIFFERENCE(ST_GEOMFROMTEXT('POINT(0 0)'),NULL))
55 NULL
56 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(g,NULL)) FROM gis_geometrycollection WHERE fid=103;
57 ST_ASTEXT(ST_SYMDIFFERENCE(g,NULL))
58 NULL
59 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(NULL,NULL));
60 ST_ASTEXT(ST_SYMDIFFERENCE(NULL,NULL))
61 NULL
62 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(fid,NULL)) FROM gis_geometrycollection WHERE fid=103;
63 ST_ASTEXT(ST_SYMDIFFERENCE(fid,NULL))
64 NULL
65 #####################################################################################
66 # WL#8579 Spatial Reference Systems
67 #####################################################################################
68 # SRID 0 (should pass)
69 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(
70 ST_GEOMFROMTEXT('POINT(0 0)', 0),
71 ST_GEOMFROMTEXT('POINT(0 0)', 0)
72 ));
73 ST_ASTEXT(ST_SYMDIFFERENCE(
74 ST_GEOMFROMTEXT('POINT(0 0)', 0),
75 ST_GEOMFROMTEXT('POINT(0 0)', 0)
76 ))
77 GEOMETRYCOLLECTION EMPTY
78 # Projected SRS (should pass)
79 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(
80 ST_GEOMFROMTEXT('POINT(0 0)', 2000),
81 ST_GEOMFROMTEXT('POINT(0 0)', 2000)
82 ));
83 ST_ASTEXT(ST_SYMDIFFERENCE(
84 ST_GEOMFROMTEXT('POINT(0 0)', 2000),
85 ST_GEOMFROMTEXT('POINT(0 0)', 2000)
86 ))
87 GEOMETRYCOLLECTION EMPTY
88 # Geographic SRS (should pass)
89 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(
90 ST_GEOMFROMTEXT('POINT(0 0)', 4326),
91 ST_GEOMFROMTEXT('POINT(0 0)', 4326)
92 ));
93 ST_ASTEXT(ST_SYMDIFFERENCE(
94 ST_GEOMFROMTEXT('POINT(0 0)', 4326),
95 ST_GEOMFROMTEXT('POINT(0 0)', 4326)
96 ))
97 GEOMETRYCOLLECTION EMPTY
98 #####################################################################################
99 # Invalid function calls
100 #####################################################################################
101 # Undefined SRS' in the input geometries should return ER_SRS_NOT_FOUND.
102 DO ST_ASTEXT(ST_SYMDIFFERENCE(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_SYMDIFFERENCE(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POINT(0 1)',4326)));
106 ERROR HY000: Binary geometry function st_symdifference given two geometries of different srids: 0 and 4326, which should have been identical.
107 DO ST_ASTEXT(ST_SYMDIFFERENCE(ST_GEOMFROMTEXT('POINT(0 0)',4145),ST_GEOMFROMTEXT('POINT(0 1)',4326)));
108 ERROR HY000: Binary geometry function st_symdifference 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_SYMDIFFERENCE(fid,ST_GEOMFROMTEXT('POINT(0 0)'))) FROM gis_geometrycollection WHERE fid=103;
111 ERROR 22023: Invalid GIS data provided to function st_symdifference.
112 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(ST_GEOMFROMTEXT('POINT(0 0)'),fid)) FROM gis_geometrycollection WHERE fid=103;
113 ERROR 22023: Invalid GIS data provided to function st_symdifference.
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 #====================================================================================
122 # DIFFERENCE polygon,polygon
123 #====================================================================================
124 # Cartesian SRS
125 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))'),ST_GEOMFROMTEXT('POLYGON((0 1,1.5 1,1 2,0 2,0 1))')));
126 ST_ASTEXT(ST_SYMDIFFERENCE(ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))'),ST_GEOMFROMTEXT('POLYGON((0 1,1.5 1,1 2,0 2,0 1))')))
127 MULTIPOLYGON(((1 2,1.5 1,2 1,2 2,1 2)),((1 2,0 2,0 1,1 1,1 2)))
128 # Geographic SRS
129 SELECT ST_ASTEXT(ST_SYMDIFFERENCE(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)));
130 ST_ASTEXT(ST_SYMDIFFERENCE(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)))
131 POLYGON((2 1,0 1,0 2,1 2,2 2,2 1))
132 #clean up
133 DROP TABLE gis_geometrycollection;
134 DROP TABLE gis_geometrycollection_2;