Debian Codegraph @

mysql-test/suite/gis/r/st_intersection.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(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_Intersection() with different ways to pass input geometries
22 #####################################################################################
23 SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT(@star_collection_elems)));
24 ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT(@star_collection_elems)))
25 GEOMETRYCOLLECTION(POLYGON((15 25,11 15,13.8 15,15 18,16.2 15,19 15,15 25)),POLYGON((30 15,19 15,17.133333333333333 13,21 13,18 10,20 6,15 8,10.957446808510639 6.382978723404255,5 0,15 5,25 0,22 10,30 15)),LINESTRING(13 7.2,12.179487179487179 7.6923076923076925),LINESTRING(15 18,15 15),LINESTRING(15 10.714285714285715,15 10))
26 SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')));
27 ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')))
28 GEOMETRYCOLLECTION EMPTY
29 SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')));
30 ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')))
31 GEOMETRYCOLLECTION EMPTY
32 SELECT ST_ASTEXT(ST_INTERSECTION(g,ST_GEOMFROMTEXT(@star_of_elems))) FROM gis_geometrycollection WHERE fid=103;
33 ST_ASTEXT(ST_INTERSECTION(g,ST_GEOMFROMTEXT(@star_of_elems)))
34 MULTIPOLYGON(((19 15,5 0,15 5,25 0,22 10,30 15,19 15)),((11 15,19 15,15 25,11 15)))
35 SELECT ST_ASTEXT(ST_INTERSECTION(g,ST_GEOMFROMTEXT('POINT(0 0)'))) FROM gis_geometrycollection WHERE fid=103;
36 ST_ASTEXT(ST_INTERSECTION(g,ST_GEOMFROMTEXT('POINT(0 0)')))
37 GEOMETRYCOLLECTION EMPTY
38 SELECT ST_ASTEXT(ST_INTERSECTION(g,g2)) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103;
39 ST_ASTEXT(ST_INTERSECTION(g,g2))
40 MULTIPOLYGON(((19 15,5 0,15 5,25 0,22 10,30 15,19 15)),((11 15,19 15,15 25,11 15)))
41 #####################################################################################
42 # Verify ST_Intersection() returns NULL if at least one of its arguments are NULL.
43 #####################################################################################
44 SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT(@star_of_elems),NULL));
45 ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT(@star_of_elems),NULL))
46 NULL
47 SELECT ST_ASTEXT(ST_INTERSECTION(NULL,ST_GEOMFROMTEXT(@star_of_elems)));
48 ST_ASTEXT(ST_INTERSECTION(NULL,ST_GEOMFROMTEXT(@star_of_elems)))
49 NULL
50 SELECT ST_ASTEXT(ST_INTERSECTION(NULL,ST_GEOMFROMTEXT('POINT(0 0)')));
51 ST_ASTEXT(ST_INTERSECTION(NULL,ST_GEOMFROMTEXT('POINT(0 0)')))
52 NULL
53 SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)'),NULL));
54 ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)'),NULL))
55 NULL
56 SELECT ST_ASTEXT(ST_INTERSECTION(g,NULL)) FROM gis_geometrycollection WHERE fid=103;
57 ST_ASTEXT(ST_INTERSECTION(g,NULL))
58 NULL
59 SELECT ST_ASTEXT(ST_INTERSECTION(NULL,NULL));
60 ST_ASTEXT(ST_INTERSECTION(NULL,NULL))
61 NULL
62 SELECT ST_ASTEXT(ST_INTERSECTION(fid,NULL)) FROM gis_geometrycollection WHERE fid=103;
63 ST_ASTEXT(ST_INTERSECTION(fid,NULL))
64 NULL
65 #####################################################################################
66 # WL#8579 Spatial Reference Systems
67 #####################################################################################
68 # SRID 0 (should pass)
69 SELECT ST_ASTEXT(ST_INTERSECTION(
70 ST_GEOMFROMTEXT('POINT(0 0)', 0),
71 ST_GEOMFROMTEXT('POINT(0 0)', 0)
72 ));
73 ST_ASTEXT(ST_INTERSECTION(
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_INTERSECTION(
80 ST_GEOMFROMTEXT('POINT(0 0)', 2000),
81 ST_GEOMFROMTEXT('POINT(0 0)', 2000)
82 ));
83 ST_ASTEXT(ST_INTERSECTION(
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_INTERSECTION(
90 ST_GEOMFROMTEXT('POINT(0 0)', 4326),
91 ST_GEOMFROMTEXT('POINT(0 0)', 4326)
92 ))
93 #####################################################################################
94 # Invalid function calls
95 #####################################################################################
96 # Undefined SRS' in the input geometries should return ER_SRS_NOT_FOUND.
97 DO ST_ASTEXT(ST_INTERSECTION(x'40420f00010100000000000000000000000000000000000000',x'40420f00010100000000000000000000000000000000000000'));
98 ERROR SR001: There's no spatial reference system with SRID 1000000.
99 # Different SRIDs in the input geometries should return ER_GIS_DIFFERENT_SRIDS.
100 DO ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POINT(0 1)',4326)));
101 ERROR HY000: Binary geometry function st_intersection given two geometries of different srids: 0 and 4326, which should have been identical.
102 DO ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)',4145),ST_GEOMFROMTEXT('POINT(0 1)',4326)));
103 ERROR HY000: Binary geometry function st_intersection given two geometries of different srids: 4145 and 4326, which should have been identical.
104 # Not syntactically well-formed geometry input should result in ER_GIS_INVALID_DATA.
105 SELECT ST_ASTEXT(ST_INTERSECTION(fid,ST_GEOMFROMTEXT('POINT(0 0)'))) FROM gis_geometrycollection WHERE fid=103;
106 ERROR 22023: Invalid GIS data provided to function st_intersection.
107 SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)'),fid)) FROM gis_geometrycollection WHERE fid=103;
108 ERROR 22023: Invalid GIS data provided to function st_intersection.
109 # Return ER_GEOMETRY_PARAM_(LONGITUDE/LATITUDE)_OUT_OF_RANGE if longitude and latitude values are not
110 # within their respective ranges (-180,180] and [-90,90]).
111 SET @pt = ST_GeomFromText('POINT(0 0)', 4326);
112 SET @pt_lat_minus_91 = x'E6100000010100000000000000000000000000000000C056C0';
113 SET @pt_lat_plus_91 = x'E6100000010100000000000000000000000000000000C05640';
114 SET @pt_long_minus_181 = x'E610000001010000000000000000A066C00000000000000000';
115 SET @pt_long_plus_181 = x'E610000001010000000000000000A066400000000000000000';
116 DO ST_INTERSECTION(@pt_lat_minus_91, @pt);
117 ERROR 22S03: A parameter of function st_intersection contains a geometry with latitude -91.000000, which is out of range. It must be within [-90.000000, 90.000000].
118 DO ST_INTERSECTION(@pt_lat_plus_91, @pt);
119 ERROR 22S03: A parameter of function st_intersection contains a geometry with latitude 91.000000, which is out of range. It must be within [-90.000000, 90.000000].
120 DO ST_INTERSECTION(@pt_long_minus_181, @pt);
121 ERROR 22S02: A parameter of function st_intersection contains a geometry with longitude -181.000000, which is out of range. It must be within (-180.000000, 180.000000].
122 DO ST_INTERSECTION(@pt_long_plus_181, @pt);
123 ERROR 22S02: A parameter of function st_intersection contains a geometry with longitude 181.000000, which is out of range. It must be within (-180.000000, 180.000000].
124 SELECT ST_ASTEXT(ST_INTERSECTION(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))')));
125 ST_ASTEXT(ST_INTERSECTION(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 POLYGON((1 2,1 1,1.5 1,1 2))
127 #clean up
128 DROP TABLE gis_geometrycollection;
129 DROP TABLE gis_geometrycollection_2;