gis.test 8.35 KB
Newer Older
1 2
-- source include/have_geometry.inc

unknown's avatar
unknown committed
3 4 5 6 7
#
# Spatial objects
#

--disable_warnings
unknown's avatar
unknown committed
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;
unknown's avatar
unknown committed
9 10
--enable_warnings

unknown's avatar
unknown committed
11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
CREATE TABLE gis_point  (fid INTEGER NOT NULL PRIMARY KEY, g POINT);
CREATE TABLE gis_line  (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING);
CREATE TABLE gis_polygon   (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON);
CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT);
CREATE TABLE gis_multi_line (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING);
CREATE TABLE gis_multi_polygon  (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON);
CREATE TABLE gis_geometrycollection  (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION);
CREATE TABLE gis_geometry (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);

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 
unknown's avatar
unknown committed
31 32 33
(101, PointFromText('POINT(10 10)')),
(102, PointFromText('POINT(20 10)')),
(103, PointFromText('POINT(20 20)')),
unknown's avatar
unknown committed
34
(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
unknown's avatar
unknown committed
35

unknown's avatar
unknown committed
36
INSERT INTO gis_line VALUES
unknown's avatar
unknown committed
37 38
(105, LineFromText('LINESTRING(0 0,0 10,10 0)')),
(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
39
(107, LineStringFromWKB(LineString(Point(10, 10), Point(40, 10))));
unknown's avatar
unknown committed
40

unknown's avatar
unknown committed
41
INSERT INTO gis_polygon VALUES
unknown's avatar
unknown committed
42 43
(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))')),
44
(110, PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))));
unknown's avatar
unknown committed
45

unknown's avatar
unknown committed
46
INSERT INTO gis_multi_point VALUES
unknown's avatar
unknown committed
47 48
(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
49
(113, MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10))));
unknown's avatar
unknown committed
50

unknown's avatar
unknown committed
51
INSERT INTO gis_multi_line VALUES
unknown's avatar
unknown committed
52 53
(114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
(115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
54
(116, MLineFromWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))));
unknown's avatar
unknown committed
55 56


unknown's avatar
unknown committed
57
INSERT INTO gis_multi_polygon VALUES
unknown's avatar
unknown committed
58 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)))')),
(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)))')),
60
(119, MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))));
unknown's avatar
unknown committed
61

unknown's avatar
unknown committed
62
INSERT INTO gis_geometrycollection VALUES
unknown's avatar
unknown committed
63
(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
unknown's avatar
unknown committed
64
(121, GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))));
unknown's avatar
unknown committed
65

unknown's avatar
unknown committed
66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124
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;

SELECT fid, AsText(g) FROM gis_point;
SELECT fid, AsText(g) FROM gis_line;
SELECT fid, AsText(g) FROM gis_polygon;
SELECT fid, AsText(g) FROM gis_multi_point;
SELECT fid, AsText(g) FROM gis_multi_line;
SELECT fid, AsText(g) FROM gis_multi_polygon;
SELECT fid, AsText(g) FROM gis_geometrycollection;
SELECT fid, AsText(g) FROM gis_geometry;

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

SELECT fid, X(g) FROM gis_point;
SELECT fid, Y(g) FROM gis_point;
explain extended select X(g),Y(g) FROM gis_point;

SELECT fid, AsText(StartPoint(g)) FROM gis_line;
SELECT fid, AsText(EndPoint(g)) FROM gis_line;
SELECT fid, GLength(g) FROM gis_line;
SELECT fid, NumPoints(g) FROM gis_line;
SELECT fid, AsText(PointN(g, 2)) FROM gis_line;
SELECT fid, IsClosed(g) FROM gis_line;
explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM gis_line;

SELECT fid, AsText(Centroid(g)) FROM gis_polygon;
SELECT fid, Area(g) FROM gis_polygon;
SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon;
SELECT fid, NumInteriorRings(g) FROM gis_polygon;
SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon;
explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM gis_polygon;

SELECT fid, IsClosed(g) FROM gis_multi_line;

SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon;
SELECT fid, Area(g) FROM gis_multi_polygon;

SELECT fid, NumGeometries(g) from gis_multi_point;
SELECT fid, NumGeometries(g) from gis_multi_line;
SELECT fid, NumGeometries(g) from gis_multi_polygon;
SELECT fid, NumGeometries(g) from gis_geometrycollection;
explain extended SELECT fid, NumGeometries(g) from gis_multi_point;

SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line;
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon;
SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection;
SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection;
explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
unknown's avatar
unknown committed
125 126 127 128 129

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
unknown's avatar
unknown committed
130
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
131 132 133 134
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
unknown's avatar
unknown committed
135
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
unknown's avatar
unknown committed
136

unknown's avatar
unknown committed
137
DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
138 139 140 141

#
# Check that ALTER TABLE doesn't loose geometry type
#
unknown's avatar
unknown committed
142 143
CREATE TABLE t1 (
  gp  point,
144 145
  ln  linestring,
  pg  polygon,
unknown's avatar
unknown committed
146
  mp  multipoint,
147 148 149 150 151 152
  mln multilinestring,
  mpg multipolygon,
  gc  geometrycollection,
  gm  geometry
);

unknown's avatar
unknown committed
153 154 155 156
SHOW FIELDS FROM t1;
ALTER TABLE t1 ADD fid INT NOT NULL;
SHOW FIELDS FROM t1;
DROP TABLE t1;
unknown's avatar
unknown committed
157 158

SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))));
159 160 161 162 163 164
explain extended SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))));
explain extended SELECT AsText(GeometryFromWKB(AsWKB(PointFromText('POINT(1 4)'))));
SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));
explain extended SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));
#select issimple(MultiPoint(Point(3, 6), Point(4, 10))), issimple(Point(3, 6)),issimple(PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),issimple(GeometryFromText('POINT(1 4)')), issimple(AsWKB(GeometryFromText('POINT(1 4)')));
explain extended select issimple(MultiPoint(Point(3, 6), Point(4, 10))), issimple(Point(3, 6));
unknown's avatar
unknown committed
165 166 167 168 169

create table t1 (a geometry not null);
insert into t1 values (GeomFromText('Point(1 2)'));
-- error 1105
insert into t1 values ('Garbage');
unknown's avatar
unknown committed
170 171
-- error 1105
insert IGNORE into t1 values ('Garbage');
unknown's avatar
unknown committed
172 173 174
alter table t1 add spatial index(a);

drop table t1;
175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192

#
# Bug #5219: problem with range optimizer
#

create table t1(a geometry not null, spatial index(a));
insert into t1 values
(GeomFromText('POINT(1 1)')), (GeomFromText('POINT(3 3)')), 
(GeomFromText('POINT(4 4)')), (GeomFromText('POINT(6 6)'));
select AsText(a) from t1 where
  MBRContains(GeomFromText('Polygon((0 0, 0 2, 2 2, 2 0, 0 0))'), a)
  or
  MBRContains(GeomFromText('Polygon((2 2, 2 5, 5 5, 5 2, 2 2))'), a);
select AsText(a) from t1 where
  MBRContains(GeomFromText('Polygon((0 0, 0 2, 2 2, 2 0, 0 0))'), a)
  and
  MBRContains(GeomFromText('Polygon((0 0, 0 7, 7 7, 7 0, 0 0))'), a);
drop table t1;
193 194 195 196

create table t1 select POINT(1,3);
show create table t1;
drop table t1;