Debian Codegraph @

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

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