gis_generic.inc 7.9 KB
Newer Older
1
--source include/have_geometry.inc
2 3 4 5 6 7 8 9 10

#
# Spatial objects
#

--disable_warnings
DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
--enable_warnings

11 12 13 14 15 16 17 18
CREATE TABLE gis_point  (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT);
CREATE TABLE gis_line  (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g LINESTRING);
CREATE TABLE gis_polygon   (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POLYGON);
CREATE TABLE gis_multi_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOINT);
CREATE TABLE gis_multi_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTILINESTRING);
CREATE TABLE gis_multi_polygon  (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOLYGON);
CREATE TABLE gis_geometrycollection  (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRYCOLLECTION);
CREATE TABLE gis_geometry (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRY);
19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74

SHOW CREATE TABLE gis_point;
SHOW FIELDS FROM gis_point;
SHOW FIELDS FROM gis_line;
SHOW FIELDS FROM gis_polygon;
SHOW FIELDS FROM gis_multi_point;
SHOW FIELDS FROM gis_multi_line;
SHOW FIELDS FROM gis_multi_polygon;
SHOW FIELDS FROM gis_geometrycollection;
SHOW FIELDS FROM gis_geometry;


INSERT INTO gis_point VALUES 
(101, PointFromText('POINT(10 10)')),
(102, PointFromText('POINT(20 10)')),
(103, PointFromText('POINT(20 20)')),
(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));

INSERT INTO gis_line VALUES
(105, LineFromText('LINESTRING(0 0,0 10,10 0)')),
(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
(107, LineStringFromWKB(LineString(Point(10, 10), Point(40, 10))));

INSERT INTO gis_polygon VALUES
(108, PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
(109, PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
(110, PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))));

INSERT INTO gis_multi_point VALUES
(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
(113, MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10))));

INSERT INTO gis_multi_line VALUES
(114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
(115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
(116, MLineFromWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))));


INSERT INTO gis_multi_polygon VALUES
(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)))')),
(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)))')),
(119, MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))));

INSERT INTO gis_geometrycollection VALUES
(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
(121, GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))));

INSERT into gis_geometry SELECT * FROM gis_point;
INSERT into gis_geometry SELECT * FROM gis_line;
INSERT into gis_geometry SELECT * FROM gis_polygon;
INSERT into gis_geometry SELECT * FROM gis_multi_point;
INSERT into gis_geometry SELECT * FROM gis_multi_line;
INSERT into gis_geometry SELECT * FROM gis_multi_polygon;
INSERT into gis_geometry SELECT * FROM gis_geometrycollection;

75 76 77 78 79 80 81 82 83 84 85 86 87
SELECT fid, AsText(g) FROM gis_point ORDER by fid;
SELECT fid, AsText(g) FROM gis_line ORDER by fid;
SELECT fid, AsText(g) FROM gis_polygon ORDER by fid;
SELECT fid, AsText(g) FROM gis_multi_point ORDER by fid;
SELECT fid, AsText(g) FROM gis_multi_line ORDER by fid;
SELECT fid, AsText(g) FROM gis_multi_polygon ORDER by fid;
SELECT fid, AsText(g) FROM gis_geometrycollection ORDER by fid;
SELECT fid, AsText(g) FROM gis_geometry ORDER by fid;

SELECT fid, Dimension(g) FROM gis_geometry ORDER by fid;
SELECT fid, GeometryType(g) FROM gis_geometry ORDER by fid;
SELECT fid, IsEmpty(g) FROM gis_geometry ORDER by fid;
SELECT fid, AsText(Envelope(g)) FROM gis_geometry ORDER by fid;
88 89
explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from gis_geometry;

90 91
SELECT fid, X(g) FROM gis_point ORDER by fid;
SELECT fid, Y(g) FROM gis_point ORDER by fid;
92 93
explain extended select X(g),Y(g) FROM gis_point;

94 95 96 97 98 99
SELECT fid, AsText(StartPoint(g)) FROM gis_line ORDER by fid;
SELECT fid, AsText(EndPoint(g)) FROM gis_line ORDER by fid;
SELECT fid, GLength(g) FROM gis_line ORDER by fid;
SELECT fid, NumPoints(g) FROM gis_line ORDER by fid;
SELECT fid, AsText(PointN(g, 2)) FROM gis_line ORDER by fid;
SELECT fid, IsClosed(g) FROM gis_line ORDER by fid;
100 101
explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM gis_line;

102 103 104 105 106
SELECT fid, AsText(Centroid(g)) FROM gis_polygon ORDER by fid;
SELECT fid, Area(g) FROM gis_polygon ORDER by fid;
SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon ORDER by fid;
SELECT fid, NumInteriorRings(g) FROM gis_polygon ORDER by fid;
SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid;
107 108
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;
110

111 112
SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon ORDER by fid;
SELECT fid, Area(g) FROM gis_multi_polygon ORDER by fid;
113

114 115 116 117
SELECT fid, NumGeometries(g) from gis_multi_point ORDER by fid;
SELECT fid, NumGeometries(g) from gis_multi_line ORDER by fid;
SELECT fid, NumGeometries(g) from gis_multi_polygon ORDER by fid;
SELECT fid, NumGeometries(g) from gis_geometrycollection ORDER by fid;
118 119
explain extended SELECT fid, NumGeometries(g) from gis_multi_point;

120 121 122 123 124
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point ORDER by fid;
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line ORDER by fid;
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon ORDER by fid;
SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection ORDER by fid;
SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection ORDER by fid;
125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143
explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;

SELECT g1.fid as first, g2.fid as second,
Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
explain extended SELECT g1.fid as first, g2.fid as second,
Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;

DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;

#
# Check that ALTER TABLE doesn't loose geometry type
#
CREATE TABLE t1 (
144
  a INTEGER PRIMARY KEY AUTO_INCREMENT,
145 146 147 148 149 150 151 152 153 154 155 156 157 158 159
  gp  point,
  ln  linestring,
  pg  polygon,
  mp  multipoint,
  mln multilinestring,
  mpg multipolygon,
  gc  geometrycollection,
  gm  geometry
);

SHOW FIELDS FROM t1;
ALTER TABLE t1 ADD fid INT;
SHOW FIELDS FROM t1;
DROP TABLE t1;

160 161
create table t1 (pk integer primary key auto_increment, a geometry not null);
insert into t1 (a) values (GeomFromText('Point(1 2)'));
162
-- error 1416
163
insert into t1 (a) values ('Garbage');
164
-- error 1416
165
insert IGNORE into t1 (a) values ('Garbage');
166 167 168

drop table t1;

169
create table t1 (pk integer primary key auto_increment, fl geometry);
170
--error 1416
171
insert into t1 (fl) values (1);
172
--error 1416
173
insert into t1 (fl) values (1.11);
174
--error 1416
175
insert into t1 (fl) values ("qwerty");
176
--error 1416
177
insert into t1 (fl) values (pointfromtext('point(1,1)'));
178 179 180

drop table t1;

181
# End of 5.0 tests