MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
gis_generic.inc
1 --source include/have_geometry.inc
2 
3 #
4 # Spatial objects
5 #
6 
7 --disable_warnings
8 DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
9 --enable_warnings
10 
11 CREATE TABLE gis_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT);
12 CREATE TABLE gis_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g LINESTRING);
13 CREATE TABLE gis_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POLYGON);
14 CREATE TABLE gis_multi_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOINT);
15 CREATE TABLE gis_multi_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTILINESTRING);
16 CREATE TABLE gis_multi_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOLYGON);
17 CREATE TABLE gis_geometrycollection (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRYCOLLECTION);
18 CREATE TABLE gis_geometry (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRY);
19 
20 SHOW CREATE TABLE gis_point;
21 SHOW FIELDS FROM gis_point;
22 SHOW FIELDS FROM gis_line;
23 SHOW FIELDS FROM gis_polygon;
24 SHOW FIELDS FROM gis_multi_point;
25 SHOW FIELDS FROM gis_multi_line;
26 SHOW FIELDS FROM gis_multi_polygon;
27 SHOW FIELDS FROM gis_geometrycollection;
28 SHOW FIELDS FROM gis_geometry;
29 
30 
31 INSERT INTO gis_point VALUES
32 (101, PointFromText('POINT(10 10)')),
33 (102, PointFromText('POINT(20 10)')),
34 (103, PointFromText('POINT(20 20)')),
35 (104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
36 
37 INSERT INTO gis_line VALUES
38 (105, LineFromText('LINESTRING(0 0,0 10,10 0)')),
39 (106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
40 (107, LineStringFromWKB(LineString(Point(10, 10), Point(40, 10))));
41 
42 INSERT INTO gis_polygon VALUES
43 (108, PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
44 (109, PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
45 (110, PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))));
46 
47 INSERT INTO gis_multi_point VALUES
48 (111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
49 (112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
50 (113, MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10))));
51 
52 INSERT INTO gis_multi_line VALUES
53 (114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
54 (115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
55 (116, MLineFromWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))));
56 
57 
58 INSERT INTO gis_multi_polygon VALUES
59 (117, MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')),
60 (118, MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')),
61 (119, MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))));
62 
63 INSERT INTO gis_geometrycollection VALUES
64 (120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
65 (121, GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))));
66 
67 INSERT into gis_geometry SELECT * FROM gis_point;
68 INSERT into gis_geometry SELECT * FROM gis_line;
69 INSERT into gis_geometry SELECT * FROM gis_polygon;
70 INSERT into gis_geometry SELECT * FROM gis_multi_point;
71 INSERT into gis_geometry SELECT * FROM gis_multi_line;
72 INSERT into gis_geometry SELECT * FROM gis_multi_polygon;
73 INSERT into gis_geometry SELECT * FROM gis_geometrycollection;
74 
75 -- disable_query_log
76 -- disable_result_log
77 ANALYZE TABLE gis_point;
78 ANALYZE TABLE gis_line;
79 ANALYZE TABLE gis_polygon;
80 ANALYZE TABLE gis_multi_point;
81 ANALYZE TABLE gis_multi_line;
82 ANALYZE TABLE gis_multi_polygon;
83 ANALYZE TABLE gis_geometrycollection;
84 ANALYZE TABLE gis_geometry;
85 -- enable_result_log
86 -- enable_query_log
87 
88 SELECT fid, AsText(g) FROM gis_point ORDER by fid;
89 SELECT fid, AsText(g) FROM gis_line ORDER by fid;
90 SELECT fid, AsText(g) FROM gis_polygon ORDER by fid;
91 SELECT fid, AsText(g) FROM gis_multi_point ORDER by fid;
92 SELECT fid, AsText(g) FROM gis_multi_line ORDER by fid;
93 SELECT fid, AsText(g) FROM gis_multi_polygon ORDER by fid;
94 SELECT fid, AsText(g) FROM gis_geometrycollection ORDER by fid;
95 SELECT fid, AsText(g) FROM gis_geometry ORDER by fid;
96 
97 SELECT fid, Dimension(g) FROM gis_geometry ORDER by fid;
98 SELECT fid, GeometryType(g) FROM gis_geometry ORDER by fid;
99 SELECT fid, IsEmpty(g) FROM gis_geometry ORDER by fid;
100 SELECT fid, AsText(Envelope(g)) FROM gis_geometry ORDER by fid;
101 explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from gis_geometry;
102 
103 SELECT fid, X(g) FROM gis_point ORDER by fid;
104 SELECT fid, Y(g) FROM gis_point ORDER by fid;
105 explain extended select X(g),Y(g) FROM gis_point;
106 
107 SELECT fid, AsText(StartPoint(g)) FROM gis_line ORDER by fid;
108 SELECT fid, AsText(EndPoint(g)) FROM gis_line ORDER by fid;
109 SELECT fid, GLength(g) FROM gis_line ORDER by fid;
110 SELECT fid, NumPoints(g) FROM gis_line ORDER by fid;
111 SELECT fid, AsText(PointN(g, 2)) FROM gis_line ORDER by fid;
112 SELECT fid, IsClosed(g) FROM gis_line ORDER by fid;
113 explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM gis_line;
114 
115 SELECT fid, AsText(Centroid(g)) FROM gis_polygon ORDER by fid;
116 SELECT fid, Area(g) FROM gis_polygon ORDER by fid;
117 SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon ORDER by fid;
118 SELECT fid, NumInteriorRings(g) FROM gis_polygon ORDER by fid;
119 SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid;
120 explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM gis_polygon;
121 
122 SELECT fid, IsClosed(g) FROM gis_multi_line ORDER by fid;
123 
124 SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon ORDER by fid;
125 SELECT fid, Area(g) FROM gis_multi_polygon ORDER by fid;
126 
127 SELECT fid, NumGeometries(g) from gis_multi_point ORDER by fid;
128 SELECT fid, NumGeometries(g) from gis_multi_line ORDER by fid;
129 SELECT fid, NumGeometries(g) from gis_multi_polygon ORDER by fid;
130 SELECT fid, NumGeometries(g) from gis_geometrycollection ORDER by fid;
131 explain extended SELECT fid, NumGeometries(g) from gis_multi_point;
132 
133 SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point ORDER by fid;
134 SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line ORDER by fid;
135 SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon ORDER by fid;
136 SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection ORDER by fid;
137 SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection ORDER by fid;
138 explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
139 
140 SELECT g1.fid as first, g2.fid as second,
141 Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
142 Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
143 Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
144 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
145 explain extended SELECT g1.fid as first, g2.fid as second,
146 Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
147 Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
148 Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
149 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
150 
151 DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
152 
153 #
154 # Check that ALTER TABLE doesn't loose geometry type
155 #
156 CREATE TABLE t1 (
157  a INTEGER PRIMARY KEY AUTO_INCREMENT,
158  gp point,
159  ln linestring,
160  pg polygon,
161  mp multipoint,
162  mln multilinestring,
163  mpg multipolygon,
164  gc geometrycollection,
165  gm geometry
166 );
167 
168 SHOW FIELDS FROM t1;
169 ALTER TABLE t1 ADD fid INT;
170 SHOW FIELDS FROM t1;
171 DROP TABLE t1;
172 
173 create table t1 (pk integer primary key auto_increment, a geometry not null);
174 insert into t1 (a) values (GeomFromText('Point(1 2)'));
175 -- error 1416
176 insert into t1 (a) values ('Garbage');
177 -- error 1416
178 insert IGNORE into t1 (a) values ('Garbage');
179 
180 drop table t1;
181 
182 create table t1 (pk integer primary key auto_increment, fl geometry not null);
183 --error 1416
184 insert into t1 (fl) values (1);
185 --error 1416
186 insert into t1 (fl) values (1.11);
187 --error 1416
188 insert into t1 (fl) values ("qwerty");
189 --error 1048
190 insert into t1 (fl) values (pointfromtext('point(1,1)'));
191 
192 drop table t1;
193 
194 --echo End of 4.1 tests
195 
196 
197 #
198 # Bug#24563: MBROverlaps does not seem to function propertly
199 # Bug#54888: MBROverlaps missing in 5.1?
200 #
201 
202 # Test all MBR* functions and their non-MBR-prefixed aliases,
203 # using shifted squares to verify the spatial relations.
204 
205 CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY);
206 
207 INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));
208 
209 INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
210 INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
211 
212 INSERT INTO t1 VALUES("up", GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));
213 INSERT INTO t1 VALUES("up2", GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));
214 INSERT INTO t1 VALUES("up3", GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));
215 
216 INSERT INTO t1 VALUES("down", GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));
217 INSERT INTO t1 VALUES("down2", GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));
218 INSERT INTO t1 VALUES("down3", GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
219 
220 INSERT INTO t1 VALUES("right", GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
221 INSERT INTO t1 VALUES("right2", GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
222 INSERT INTO t1 VALUES("right3", GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
223 
224 INSERT INTO t1 VALUES("left", GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));
225 INSERT INTO t1 VALUES("left2", GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));
226 INSERT INTO t1 VALUES("left3", GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))'));
227 
228 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrcontains FROM t1 a1 JOIN t1 a2 ON MBRContains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
229 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrdisjoint FROM t1 a1 JOIN t1 a2 ON MBRDisjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
230 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrequal FROM t1 a1 JOIN t1 a2 ON MBREqual( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
231 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrintersect FROM t1 a1 JOIN t1 a2 ON MBRIntersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
232 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbroverlaps FROM t1 a1 JOIN t1 a2 ON MBROverlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
233 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrtouches FROM t1 a1 JOIN t1 a2 ON MBRTouches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
234 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrwithin FROM t1 a1 JOIN t1 a2 ON MBRWithin( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
235 
236 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS contains FROM t1 a1 JOIN t1 a2 ON Contains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
237 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS disjoint FROM t1 a1 JOIN t1 a2 ON Disjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
238 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS equals FROM t1 a1 JOIN t1 a2 ON Equals( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
239 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS intersect FROM t1 a1 JOIN t1 a2 ON Intersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
240 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS overlaps FROM t1 a1 JOIN t1 a2 ON Overlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
241 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS touches FROM t1 a1 JOIN t1 a2 ON Touches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
242 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS within FROM t1 a1 JOIN t1 a2 ON Within( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
243 
244 # Overlaps needs a few more tests, with point and line dimensions
245 
246 SET @vert1 = GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');
247 SET @horiz1 = GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');
248 SET @horiz2 = GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');
249 SET @horiz3 = GeomFromText('POLYGON ((2 0, 3 0, 2 0))');
250 SET @point1 = GeomFromText('POLYGON ((0 0))');
251 SET @point2 = GeomFromText('POLYGON ((-2 0))');
252 
253 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @vert1) GROUP BY a1.name;
254 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @horiz1) GROUP BY a1.name;
255 SELECT Overlaps(@horiz1, @vert1) FROM DUAL;
256 SELECT Overlaps(@horiz1, @horiz2) FROM DUAL;
257 SELECT Overlaps(@horiz1, @horiz3) FROM DUAL;
258 SELECT Overlaps(@horiz1, @point1) FROM DUAL;
259 SELECT Overlaps(@horiz1, @point2) FROM DUAL;
260 
261 DROP TABLE t1;
262 
263 --echo End of 5.0 tests