1 --source include/have_geometry.inc
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;
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);
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;
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)'))));
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))));
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)))));
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))));
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)))));
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))))));
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)))));
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;
75 SELECT fid, AsText(g) FROM gis_point ORDER by fid;
76 SELECT fid, AsText(g) FROM gis_line ORDER by fid;
77 SELECT fid, AsText(g) FROM gis_polygon ORDER by fid;
78 SELECT fid, AsText(g) FROM gis_multi_point ORDER by fid;
79 SELECT fid, AsText(g) FROM gis_multi_line ORDER by fid;
80 SELECT fid, AsText(g) FROM gis_multi_polygon ORDER by fid;
81 SELECT fid, AsText(g) FROM gis_geometrycollection ORDER by fid;
82 SELECT fid, AsText(g) FROM gis_geometry ORDER by fid;
84 SELECT fid, Dimension(g) FROM gis_geometry ORDER by fid;
85 SELECT fid, GeometryType(g) FROM gis_geometry ORDER by fid;
86 SELECT fid, IsEmpty(g) FROM gis_geometry ORDER by fid;
87 SELECT fid, AsText(Envelope(g)) FROM gis_geometry ORDER by fid;
88 explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from gis_geometry;
90 SELECT fid, X(g) FROM gis_point ORDER by fid;
91 SELECT fid, Y(g) FROM gis_point ORDER by fid;
92 explain extended select X(g),Y(g) FROM gis_point;
94 SELECT fid, AsText(StartPoint(g)) FROM gis_line ORDER by fid;
95 SELECT fid, AsText(EndPoint(g)) FROM gis_line ORDER by fid;
96 SELECT fid, GLength(g) FROM gis_line ORDER by fid;
97 SELECT fid, NumPoints(g) FROM gis_line ORDER by fid;
98 SELECT fid, AsText(PointN(g, 2)) FROM gis_line ORDER by fid;
99 SELECT fid, IsClosed(g) FROM gis_line ORDER by fid;
100 explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM gis_line;
102 SELECT fid, AsText(Centroid(g)) FROM gis_polygon ORDER by fid;
103 SELECT fid, Area(g) FROM gis_polygon ORDER by fid;
104 SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon ORDER by fid;
105 SELECT fid, NumInteriorRings(g) FROM gis_polygon ORDER by fid;
106 SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid;
107 explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM gis_polygon;
109 SELECT fid, IsClosed(g) FROM gis_multi_line ORDER by fid;
111 SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon ORDER by fid;
112 SELECT fid, Area(g) FROM gis_multi_polygon ORDER by fid;
114 SELECT fid, NumGeometries(g) from gis_multi_point ORDER by fid;
115 SELECT fid, NumGeometries(g) from gis_multi_line ORDER by fid;
116 SELECT fid, NumGeometries(g) from gis_multi_polygon ORDER by fid;
117 SELECT fid, NumGeometries(g) from gis_geometrycollection ORDER by fid;
118 explain extended SELECT fid, NumGeometries(g) from gis_multi_point;
120 SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point ORDER by fid;
121 SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line ORDER by fid;
122 SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon ORDER by fid;
123 SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection ORDER by fid;
124 SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection ORDER by fid;
125 explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
127 SELECT g1.fid as first, g2.fid as second,
128 Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
129 Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
130 Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
131 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
132 explain extended SELECT g1.fid as first, g2.fid as second,
133 Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
134 Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
135 Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
136 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
138 DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
141 # Check that ALTER TABLE doesn't loose geometry type
144 a INTEGER PRIMARY KEY AUTO_INCREMENT,
151 gc geometrycollection,
156 ALTER TABLE t1 ADD fid INT;
160 create table t1 (pk integer primary key auto_increment, a geometry not null);
161 insert into t1 (a) values (GeomFromText('Point(1 2)'));
163 insert into t1 (a) values ('Garbage');
165 insert IGNORE into t1 (a) values ('Garbage');
169 create table t1 (pk integer primary key auto_increment, fl geometry not null);
171 insert into t1 (fl) values (1);
173 insert into t1 (fl) values (1.11);
175 insert into t1 (fl) values ("qwerty");
177 insert into t1 (fl) values (pointfromtext('point(1,1)'));
181 --echo End of 4.1 tests
185 # Bug#24563: MBROverlaps does not seem to function propertly
186 # Bug#54888: MBROverlaps missing in 5.1?
189 # Test all MBR* functions and their non-MBR-prefixed aliases,
190 # using shifted squares to verify the spatial relations.
192 CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY);
194 INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));
196 INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
197 INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
199 INSERT INTO t1 VALUES("up", GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));
200 INSERT INTO t1 VALUES("up2", GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));
201 INSERT INTO t1 VALUES("up3", GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));
203 INSERT INTO t1 VALUES("down", GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));
204 INSERT INTO t1 VALUES("down2", GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));
205 INSERT INTO t1 VALUES("down3", GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
207 INSERT INTO t1 VALUES("right", GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
208 INSERT INTO t1 VALUES("right2", GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
209 INSERT INTO t1 VALUES("right3", GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
211 INSERT INTO t1 VALUES("left", GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));
212 INSERT INTO t1 VALUES("left2", GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));
213 INSERT INTO t1 VALUES("left3", GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))'));
215 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;
216 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;
217 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;
218 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;
219 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;
220 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;
221 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;
223 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;
224 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;
225 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;
226 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;
227 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;
228 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;
229 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;
231 # Overlaps needs a few more tests, with point and line dimensions
233 SET @vert1 = GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');
234 SET @horiz1 = GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');
235 SET @horiz2 = GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');
236 SET @horiz3 = GeomFromText('POLYGON ((2 0, 3 0, 2 0))');
237 SET @point1 = GeomFromText('POLYGON ((0 0))');
238 SET @point2 = GeomFromText('POLYGON ((-2 0))');
240 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @vert1) GROUP BY a1.name;
241 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @horiz1) GROUP BY a1.name;
242 SELECT Overlaps(@horiz1, @vert1) FROM DUAL;
243 SELECT Overlaps(@horiz1, @horiz2) FROM DUAL;
244 SELECT Overlaps(@horiz1, @horiz3) FROM DUAL;
245 SELECT Overlaps(@horiz1, @point1) FROM DUAL;
246 SELECT Overlaps(@horiz1, @point2) FROM DUAL;
250 --echo End of 5.0 tests