1
-- source include/have_geometry.inc
4
# test of rtree (using with spatial data)
6
-- source include/engine_not_pbxt.inc # PBXT: GEOMETRY support required
9
DROP TABLE IF EXISTS t1, t2;
13
fid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
24
eval INSERT INTO t1 (g) VALUES (GeomFromText('LineString($1 $1, $2 $2)'));
29
SELECT count(*) FROM t1;
30
EXPLAIN SELECT fid, AsText(g) FROM t1 WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))'));
31
SELECT fid, AsText(g) FROM t1 WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))'));
36
fid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
46
eval INSERT INTO t2 (g) VALUES (GeometryFromWKB(LineString(Point($1 * 10 - 9, $2 * 10 - 9), Point($1 * 10, $2 * 10))));
52
ALTER TABLE t2 ADD SPATIAL KEY(g);
54
SELECT count(*) FROM t2;
55
EXPLAIN SELECT fid, AsText(g) FROM t2 WHERE Within(g,
56
GeomFromText('Polygon((40 40,60 40,60 60,40 60,40 40))'));
57
SELECT fid, AsText(g) FROM t2 WHERE Within(g,
58
GeomFromText('Polygon((40 40,60 40,60 60,40 60,40 40))'));
66
eval DELETE FROM t2 WHERE Within(g, Envelope(GeometryFromWKB(LineString(Point($1 * 10 - 9, $2 * 10 - 9), Point($1 * 10, $2 * 10)))));
67
SELECT count(*) FROM t2;
75
drop table if exists t1;
76
CREATE TABLE t1 (a geometry NOT NULL, SPATIAL (a));
77
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
78
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
79
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
80
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
81
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
82
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
83
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
84
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
85
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
86
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
87
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
88
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
89
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
90
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
91
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
92
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
93
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
94
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
95
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
96
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
97
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
98
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
99
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
100
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
101
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
102
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
103
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
104
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
105
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)"));
111
# The following crashed gis
115
fid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
120
INSERT INTO t1 (g) VALUES (GeomFromText('LineString(1 2, 2 3)')),(GeomFromText('LineString(1 2, 2 4)'));
121
#select * from t1 where g<GeomFromText('LineString(1 2, 2 3)');
125
line LINESTRING NOT NULL,
126
kind ENUM('po', 'pp', 'rr', 'dr', 'rd', 'ts', 'cl') NOT NULL DEFAULT 'po',
134
ALTER TABLE t1 DISABLE KEYS;
135
INSERT INTO t1 (name, kind, line) VALUES
136
("Aadaouane", "pp", GeomFromText("POINT(32.816667 35.983333)")),
137
("Aadassiye", "pp", GeomFromText("POINT(35.816667 36.216667)")),
138
("Aadbel", "pp", GeomFromText("POINT(34.533333 36.100000)")),
139
("Aadchit", "pp", GeomFromText("POINT(33.347222 35.423611)")),
140
("Aadchite", "pp", GeomFromText("POINT(33.347222 35.423611)")),
141
("Aadchit el Qoussair", "pp", GeomFromText("POINT(33.283333 35.483333)")),
142
("Aaddaye", "pp", GeomFromText("POINT(36.716667 40.833333)")),
143
("'Aadeissa", "pp", GeomFromText("POINT(32.823889 35.698889)")),
144
("Aaderup", "pp", GeomFromText("POINT(55.216667 11.766667)")),
145
("Qalaat Aades", "pp", GeomFromText("POINT(33.503333 35.377500)")),
146
("A ad'ino", "pp", GeomFromText("POINT(54.812222 38.209167)")),
147
("Aadi Noia", "pp", GeomFromText("POINT(13.800000 39.833333)")),
148
("Aad La Macta", "pp", GeomFromText("POINT(35.779444 -0.129167)")),
149
("Aadland", "pp", GeomFromText("POINT(60.366667 5.483333)")),
150
("Aadliye", "pp", GeomFromText("POINT(33.366667 36.333333)")),
151
("Aadloun", "pp", GeomFromText("POINT(33.403889 35.273889)")),
152
("Aadma", "pp", GeomFromText("POINT(58.798333 22.663889)")),
153
("Aadma Asundus", "pp", GeomFromText("POINT(58.798333 22.663889)")),
154
("Aadmoun", "pp", GeomFromText("POINT(34.150000 35.650000)")),
155
("Aadneram", "pp", GeomFromText("POINT(59.016667 6.933333)")),
156
("Aadneskaar", "pp", GeomFromText("POINT(58.083333 6.983333)")),
157
("Aadorf", "pp", GeomFromText("POINT(47.483333 8.900000)")),
158
("Aadorp", "pp", GeomFromText("POINT(52.366667 6.633333)")),
159
("Aadouane", "pp", GeomFromText("POINT(32.816667 35.983333)")),
160
("Aadoui", "pp", GeomFromText("POINT(34.450000 35.983333)")),
161
("Aadouiye", "pp", GeomFromText("POINT(34.583333 36.183333)")),
162
("Aadouss", "pp", GeomFromText("POINT(33.512500 35.601389)")),
163
("Aadra", "pp", GeomFromText("POINT(33.616667 36.500000)")),
164
("Aadzi", "pp", GeomFromText("POINT(38.100000 64.850000)"));
166
ALTER TABLE t1 ENABLE KEYS;
167
INSERT INTO t1 (name, kind, line) VALUES ("austria", "pp", GeomFromText('LINESTRING(14.9906 48.9887,14.9946 48.9904,14.9947 48.9916)'));
170
CREATE TABLE t1 (st varchar(100));
171
INSERT INTO t1 VALUES ("Fake string");
172
CREATE TABLE t2 (geom GEOMETRY NOT NULL, SPATIAL KEY gk(geom));
174
INSERT INTO t2 SELECT GeomFromText(st) FROM t1;
177
CREATE TABLE t1 (`geometry` geometry NOT NULL default '',SPATIAL KEY `gndx` (`geometry`(32))) ENGINE=MyISAM DEFAULT CHARSET=latin1;
179
INSERT INTO t1 (geometry) VALUES
180
(PolygonFromText('POLYGON((-18.6086111000 -66.9327777000, -18.6055555000
181
-66.8158332999, -18.7186111000 -66.8102777000, -18.7211111000 -66.9269443999,
182
-18.6086111000 -66.9327777000))'));
184
INSERT INTO t1 (geometry) VALUES
185
(PolygonFromText('POLYGON((-65.7402776999 -96.6686111000, -65.7372222000
186
-96.5516666000, -65.8502777000 -96.5461111000, -65.8527777000 -96.6627777000,
187
-65.7402776999 -96.6686111000))'));
188
check table t1 extended;
193
# Bug#17877 - Corrupted spatial index
196
c1 geometry NOT NULL default '',
197
SPATIAL KEY i1 (c1(32))
198
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
199
INSERT INTO t1 (c1) VALUES (
200
PolygonFromText('POLYGON((-18.6086111000 -66.9327777000,
201
-18.6055555000 -66.8158332999,
202
-18.7186111000 -66.8102777000,
203
-18.7211111000 -66.9269443999,
204
-18.6086111000 -66.9327777000))'));
205
# This showed a missing key.
206
CHECK TABLE t1 EXTENDED;
210
c1 geometry NOT NULL default '',
211
SPATIAL KEY i1 (c1(32))
212
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
213
INSERT INTO t1 (c1) VALUES (
214
PolygonFromText('POLYGON((-18.6086111000 -66.9327777000,
215
-18.6055555000 -66.8158332999,
216
-18.7186111000 -66.8102777000,
217
-18.7211111000 -66.9269443999,
218
-18.6086111000 -66.9327777000))'));
219
INSERT INTO t1 (c1) VALUES (
220
PolygonFromText('POLYGON((-65.7402776999 -96.6686111000,
221
-65.7372222000 -96.5516666000,
222
-65.8502777000 -96.5461111000,
223
-65.8527777000 -96.6627777000,
224
-65.7402776999 -96.6686111000))'));
225
# This is the same as the first insert to get a non-unique key.
226
INSERT INTO t1 (c1) VALUES (
227
PolygonFromText('POLYGON((-18.6086111000 -66.9327777000,
228
-18.6055555000 -66.8158332999,
229
-18.7186111000 -66.8102777000,
230
-18.7211111000 -66.9269443999,
231
-18.6086111000 -66.9327777000))'));
232
# This showed (and still shows) OK.
233
CHECK TABLE t1 EXTENDED;