~drizzle-trunk/drizzle/development : revision 1819.9.63

1

SET storage_engine=innodb;

2

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

3

CREATE TABLE gis_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT);

4

CREATE TABLE gis_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g LINESTRING);

5

CREATE TABLE gis_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POLYGON);

6

CREATE TABLE gis_multi_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOINT);

7

CREATE TABLE gis_multi_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTILINESTRING);

8

CREATE TABLE gis_multi_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOLYGON);

9

CREATE TABLE gis_geometrycollection (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRYCOLLECTION);

10

CREATE TABLE gis_geometry (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRY);

11

SHOW CREATE TABLE gis_point;

12

Table Create Table

13

gis_point CREATE TABLE `gis_point` (

14

`fid` int(11) NOT NULL AUTO_INCREMENT,

15

`g` point DEFAULT NULL,

16

PRIMARY KEY (`fid`)

17

) ENGINE=InnoDB DEFAULT CHARSET=latin1

18

SHOW FIELDS FROM gis_point;

19

Field Type Null Key Default Extra

20

fid int(11) NO PRI NULL auto_increment

21

g point YES NULL

22

SHOW FIELDS FROM gis_line;

23

Field Type Null Key Default Extra

24

fid int(11) NO PRI NULL auto_increment

25

g linestring YES NULL

26

SHOW FIELDS FROM gis_polygon;

27

Field Type Null Key Default Extra

28

fid int(11) NO PRI NULL auto_increment

29

g polygon YES NULL

30

SHOW FIELDS FROM gis_multi_point;

31

Field Type Null Key Default Extra

32

fid int(11) NO PRI NULL auto_increment

33

g multipoint YES NULL

34

SHOW FIELDS FROM gis_multi_line;

35

Field Type Null Key Default Extra

36

fid int(11) NO PRI NULL auto_increment

37

g multilinestring YES NULL

38

SHOW FIELDS FROM gis_multi_polygon;

39

Field Type Null Key Default Extra

40

fid int(11) NO PRI NULL auto_increment

41

g multipolygon YES NULL

42

SHOW FIELDS FROM gis_geometrycollection;

43

Field Type Null Key Default Extra

44

fid int(11) NO PRI NULL auto_increment

45

g geometrycollection YES NULL

46

SHOW FIELDS FROM gis_geometry;

47

Field Type Null Key Default Extra

48

fid int(11) NO PRI NULL auto_increment

49

g geometry YES NULL

50

INSERT INTO gis_point VALUES

51

(101, PointFromText('POINT(10 10)')),

52

(102, PointFromText('POINT(20 10)')),

53

(103, PointFromText('POINT(20 20)')),

54

(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));

55

INSERT INTO gis_line VALUES

56

(105, LineFromText('LINESTRING(0 0,0 10,10 0)')),

57

(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),

58

(107, LineStringFromWKB(LineString(Point(10, 10), Point(40, 10))));

59

INSERT INTO gis_polygon VALUES

60

(108, PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),

61

(109, PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),

62

(110, PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))));

63

INSERT INTO gis_multi_point VALUES

64

(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),

65

(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),

66

(113, MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10))));

67

INSERT INTO gis_multi_line VALUES

68

(114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),

69

(115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),

70

(116, MLineFromWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))));

71

INSERT INTO gis_multi_polygon VALUES

72

(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)))')),

73

(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)))')),

74

(119, MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))));

75

INSERT INTO gis_geometrycollection VALUES

76

(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),

77

(121, GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))));

78

INSERT into gis_geometry SELECT * FROM gis_point;

79

INSERT into gis_geometry SELECT * FROM gis_line;

80

INSERT into gis_geometry SELECT * FROM gis_polygon;

81

INSERT into gis_geometry SELECT * FROM gis_multi_point;

82

INSERT into gis_geometry SELECT * FROM gis_multi_line;

83

INSERT into gis_geometry SELECT * FROM gis_multi_polygon;

84

INSERT into gis_geometry SELECT * FROM gis_geometrycollection;

85

SELECT fid, AsText(g) FROM gis_point ORDER by fid;

86

fid AsText(g)

87

101 POINT(10 10)

88

102 POINT(20 10)

89

103 POINT(20 20)

90

104 POINT(10 20)

91

SELECT fid, AsText(g) FROM gis_line ORDER by fid;

92

fid AsText(g)

93

105 LINESTRING(0 0,0 10,10 0)

94

106 LINESTRING(10 10,20 10,20 20,10 20,10 10)

95

107 LINESTRING(10 10,40 10)

96

SELECT fid, AsText(g) FROM gis_polygon ORDER by fid;

97

fid AsText(g)

98

108 POLYGON((10 10,20 10,20 20,10 20,10 10))

99

109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))

100

110 POLYGON((0 0,30 0,30 30,0 0))

101

SELECT fid, AsText(g) FROM gis_multi_point ORDER by fid;

102

fid AsText(g)

103

111 MULTIPOINT(0 0,10 10,10 20,20 20)

104

112 MULTIPOINT(1 1,11 11,11 21,21 21)

105

113 MULTIPOINT(3 6,4 10)

106

SELECT fid, AsText(g) FROM gis_multi_line ORDER by fid;

107

fid AsText(g)

108

114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))

109

115 MULTILINESTRING((10 48,10 21,10 0))

110

116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))

111

SELECT fid, AsText(g) FROM gis_multi_polygon ORDER by fid;

112

fid AsText(g)

113

117 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)))

114

118 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)))

115

119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))

116

SELECT fid, AsText(g) FROM gis_geometrycollection ORDER by fid;

117

fid AsText(g)

118

120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))

119

121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))

120

SELECT fid, AsText(g) FROM gis_geometry ORDER by fid;

121

fid AsText(g)

122

101 POINT(10 10)

123

102 POINT(20 10)

124

103 POINT(20 20)

125

104 POINT(10 20)

126

105 LINESTRING(0 0,0 10,10 0)

127

106 LINESTRING(10 10,20 10,20 20,10 20,10 10)

128

107 LINESTRING(10 10,40 10)

129

108 POLYGON((10 10,20 10,20 20,10 20,10 10))

130

109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))

131

110 POLYGON((0 0,30 0,30 30,0 0))

132

111 MULTIPOINT(0 0,10 10,10 20,20 20)

133

112 MULTIPOINT(1 1,11 11,11 21,21 21)

134

113 MULTIPOINT(3 6,4 10)

135

114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))

136

115 MULTILINESTRING((10 48,10 21,10 0))

137

116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))

138

117 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)))

139

118 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)))

140

119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))

141

120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))

142

121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))

143

SELECT fid, Dimension(g) FROM gis_geometry ORDER by fid;

144

fid Dimension(g)

145

101 0

146

102 0

147

103 0

148

104 0

149

105 1

150

106 1

151

107 1

152

108 2

153

109 2

154

110 2

155

111 0

156

112 0

157

113 0

158

114 1

159

115 1

160

116 1

161

117 2

162

118 2

163

119 2

164

120 1

165

121 1

166

SELECT fid, GeometryType(g) FROM gis_geometry ORDER by fid;

167

fid GeometryType(g)

168

101 POINT

169

102 POINT

170

103 POINT

171

104 POINT

172

105 LINESTRING

173

106 LINESTRING

174

107 LINESTRING

175

108 POLYGON

176

109 POLYGON

177

110 POLYGON

178

111 MULTIPOINT

179

112 MULTIPOINT

180

113 MULTIPOINT

181

114 MULTILINESTRING

182

115 MULTILINESTRING

183

116 MULTILINESTRING

184

117 MULTIPOLYGON

185

118 MULTIPOLYGON

186

119 MULTIPOLYGON

187

120 GEOMETRYCOLLECTION

188

121 GEOMETRYCOLLECTION

189

SELECT fid, IsEmpty(g) FROM gis_geometry ORDER by fid;

190

fid IsEmpty(g)

191

101 0

192

102 0

193

103 0

194

104 0

195

105 0

196

106 0

197

107 0

198

108 0

199

109 0

200

110 0

201

111 0

202

112 0

203

113 0

204

114 0

205

115 0

206

116 0

207

117 0

208

118 0

209

119 0

210

120 0

211

121 0

212

SELECT fid, AsText(Envelope(g)) FROM gis_geometry ORDER by fid;

213

fid AsText(Envelope(g))

214

101 POLYGON((10 10,10 10,10 10,10 10,10 10))

215

102 POLYGON((20 10,20 10,20 10,20 10,20 10))

216

103 POLYGON((20 20,20 20,20 20,20 20,20 20))

217

104 POLYGON((10 20,10 20,10 20,10 20,10 20))

218

105 POLYGON((0 0,10 0,10 10,0 10,0 0))

219

106 POLYGON((10 10,20 10,20 20,10 20,10 10))

220

107 POLYGON((10 10,40 10,40 10,10 10,10 10))

221

108 POLYGON((10 10,20 10,20 20,10 20,10 10))

222

109 POLYGON((0 0,50 0,50 50,0 50,0 0))

223

110 POLYGON((0 0,30 0,30 30,0 30,0 0))

224

111 POLYGON((0 0,20 0,20 20,0 20,0 0))

225

112 POLYGON((1 1,21 1,21 21,1 21,1 1))

226

113 POLYGON((3 6,4 6,4 10,3 10,3 6))

227

114 POLYGON((10 0,16 0,16 48,10 48,10 0))

228

115 POLYGON((10 0,10 0,10 48,10 48,10 0))

229

116 POLYGON((1 2,21 2,21 8,1 8,1 2))

230

117 POLYGON((28 0,84 0,84 42,28 42,28 0))

231

118 POLYGON((28 0,84 0,84 42,28 42,28 0))

232

119 POLYGON((0 0,3 0,3 3,0 3,0 0))

233

120 POLYGON((0 0,10 0,10 10,0 10,0 0))

234

121 POLYGON((3 6,44 6,44 9,3 9,3 6))

235

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

236

id select_type table type possible_keys key key_len ref rows filtered Extra

237

1 SIMPLE gis_geometry ALL NULL NULL NULL NULL 21 100.00

238

Warnings:

239

Note 1003 select dimension(`test`.`gis_geometry`.`g`) AS `Dimension(g)`,geometrytype(`test`.`gis_geometry`.`g`) AS `GeometryType(g)`,isempty(`test`.`gis_geometry`.`g`) AS `IsEmpty(g)`,astext(envelope(`test`.`gis_geometry`.`g`)) AS `AsText(Envelope(g))` from `test`.`gis_geometry`

240

SELECT fid, X(g) FROM gis_point ORDER by fid;

241

fid X(g)

242

101 10

243

102 20

244

103 20

245

104 10

246

SELECT fid, Y(g) FROM gis_point ORDER by fid;

247

fid Y(g)

248

101 10

249

102 10

250

103 20

251

104 20

252

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

253

id select_type table type possible_keys key key_len ref rows filtered Extra

254

1 SIMPLE gis_point ALL NULL NULL NULL NULL 4 100.00

255

Warnings:

256

Note 1003 select x(`test`.`gis_point`.`g`) AS `X(g)`,y(`test`.`gis_point`.`g`) AS `Y(g)` from `test`.`gis_point`

257

SELECT fid, AsText(StartPoint(g)) FROM gis_line ORDER by fid;

258

fid AsText(StartPoint(g))

259

105 POINT(0 0)

260

106 POINT(10 10)

261

107 POINT(10 10)

262

SELECT fid, AsText(EndPoint(g)) FROM gis_line ORDER by fid;

263

fid AsText(EndPoint(g))

264

105 POINT(10 0)

265

106 POINT(10 10)

266

107 POINT(40 10)

267

SELECT fid, GLength(g) FROM gis_line ORDER by fid;

268

fid GLength(g)

269

105 24.142135623731

270

106 40

271

107 30

272

SELECT fid, NumPoints(g) FROM gis_line ORDER by fid;

273

fid NumPoints(g)

274

105 3

275

106 5

276

107 2

277

SELECT fid, AsText(PointN(g, 2)) FROM gis_line ORDER by fid;

278

fid AsText(PointN(g, 2))

279

105 POINT(0 10)

280

106 POINT(20 10)

281

107 POINT(40 10)

282

SELECT fid, IsClosed(g) FROM gis_line ORDER by fid;

283

fid IsClosed(g)

284

105 0

285

106 1

286

107 0

287

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

288

id select_type table type possible_keys key key_len ref rows filtered Extra

289

1 SIMPLE gis_line ALL NULL NULL NULL NULL 3 100.00

290

Warnings:

291

Note 1003 select astext(startpoint(`test`.`gis_line`.`g`)) AS `AsText(StartPoint(g))`,astext(endpoint(`test`.`gis_line`.`g`)) AS `AsText(EndPoint(g))`,glength(`test`.`gis_line`.`g`) AS `GLength(g)`,numpoints(`test`.`gis_line`.`g`) AS `NumPoints(g)`,astext(pointn(`test`.`gis_line`.`g`,2)) AS `AsText(PointN(g, 2))`,isclosed(`test`.`gis_line`.`g`) AS `IsClosed(g)` from `test`.`gis_line`

292

SELECT fid, AsText(Centroid(g)) FROM gis_polygon ORDER by fid;

293

fid AsText(Centroid(g))

294

108 POINT(15 15)

295

109 POINT(25.4166666666667 25.4166666666667)

296

110 POINT(20 10)

297

SELECT fid, Area(g) FROM gis_polygon ORDER by fid;

298

fid Area(g)

299

108 100

300

109 2400

301

110 450

302

SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon ORDER by fid;

303

fid AsText(ExteriorRing(g))

304

108 LINESTRING(10 10,20 10,20 20,10 20,10 10)

305

109 LINESTRING(0 0,50 0,50 50,0 50,0 0)

306

110 LINESTRING(0 0,30 0,30 30,0 0)

307

SELECT fid, NumInteriorRings(g) FROM gis_polygon ORDER by fid;

308

fid NumInteriorRings(g)

309

108 0

310

109 1

311

110 0

312

SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid;

313

fid AsText(InteriorRingN(g, 1))

314

108 NULL

315

109 LINESTRING(10 10,20 10,20 20,10 20,10 10)

316

110 NULL

317

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

318

id select_type table type possible_keys key key_len ref rows filtered Extra

319

1 SIMPLE gis_polygon ALL NULL NULL NULL NULL 3 100.00

320

Warnings:

321

Note 1003 select astext(centroid(`test`.`gis_polygon`.`g`)) AS `AsText(Centroid(g))`,area(`test`.`gis_polygon`.`g`) AS `Area(g)`,astext(exteriorring(`test`.`gis_polygon`.`g`)) AS `AsText(ExteriorRing(g))`,numinteriorrings(`test`.`gis_polygon`.`g`) AS `NumInteriorRings(g)`,astext(interiorringn(`test`.`gis_polygon`.`g`,1)) AS `AsText(InteriorRingN(g, 1))` from `test`.`gis_polygon`

322

SELECT fid, IsClosed(g) FROM gis_multi_line ORDER by fid;

323

fid IsClosed(g)

324

114 0

325

115 0

326

116 0

327

SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon ORDER by fid;

328

fid AsText(Centroid(g))

329

117 POINT(55.5885277530424 17.426536064114)

330

118 POINT(55.5885277530424 17.426536064114)

331

119 POINT(2 2)

332

SELECT fid, Area(g) FROM gis_multi_polygon ORDER by fid;

333

fid Area(g)

334

117 1684.5

335

118 1684.5

336

119 4.5

337

SELECT fid, NumGeometries(g) from gis_multi_point ORDER by fid;

338

fid NumGeometries(g)

339

111 4

340

112 4

341

113 2

342

SELECT fid, NumGeometries(g) from gis_multi_line ORDER by fid;

343

fid NumGeometries(g)

344

114 2

345

115 1

346

116 2

347

SELECT fid, NumGeometries(g) from gis_multi_polygon ORDER by fid;

348

fid NumGeometries(g)

349

117 2

350

118 2

351

119 1

352

SELECT fid, NumGeometries(g) from gis_geometrycollection ORDER by fid;

353

fid NumGeometries(g)

354

120 2

355

121 2

356

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

357

id select_type table type possible_keys key key_len ref rows filtered Extra

358

1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00

359

Warnings:

360

Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from `test`.`gis_multi_point`

361

SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point ORDER by fid;

362

fid AsText(GeometryN(g, 2))

363

111 POINT(10 10)

364

112 POINT(11 11)

365

113 POINT(4 10)

366

SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line ORDER by fid;

367

fid AsText(GeometryN(g, 2))

368

114 LINESTRING(16 0,16 23,16 48)

369

115 NULL

370

116 LINESTRING(2 5,5 8,21 7)

371

SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon ORDER by fid;

372

fid AsText(GeometryN(g, 2))

373

117 POLYGON((59 18,67 18,67 13,59 13,59 18))

374

118 POLYGON((59 18,67 18,67 13,59 13,59 18))

375

119 NULL

376

SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection ORDER by fid;

377

fid AsText(GeometryN(g, 2))

378

120 LINESTRING(0 0,10 10)

379

121 LINESTRING(3 6,7 9)

380

SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection ORDER by fid;

381

fid AsText(GeometryN(g, 1))

382

120 POINT(0 0)

383

121 POINT(44 6)

384

explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;

385

id select_type table type possible_keys key key_len ref rows filtered Extra

386

1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00

387

Warnings:

388

Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,astext(geometryn(`test`.`gis_multi_point`.`g`,2)) AS `AsText(GeometryN(g, 2))` from `test`.`gis_multi_point`

389

SELECT g1.fid as first, g2.fid as second,

390

Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,

391

Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,

392

Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r

393

FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;

394

first second w c o e d t i r

395

120 120 1 1 0 1 0 0 1 0

396

120 121 0 0 1 0 0 0 1 0

397

121 120 0 0 1 0 0 0 1 0

398

121 121 1 1 0 1 0 0 1 0

399

explain extended SELECT g1.fid as first, g2.fid as second,

400

Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,

401

Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,

402

Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r

403

FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;

404

id select_type table type possible_keys key key_len ref rows filtered Extra

405

1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort

406

1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer

407

Warnings:

408

Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`

409

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

410

CREATE TABLE t1 (

411

a INTEGER PRIMARY KEY AUTO_INCREMENT,

412

gp point,

413

ln linestring,

414

pg polygon,

415

mp multipoint,

416

mln multilinestring,

417

mpg multipolygon,

418

gc geometrycollection,

419

gm geometry

420

);

421

SHOW FIELDS FROM t1;

422

Field Type Null Key Default Extra

423

a int(11) NO PRI NULL auto_increment

424

gp point YES NULL

425

ln linestring YES NULL

426

pg polygon YES NULL

427

mp multipoint YES NULL

428

mln multilinestring YES NULL

429

mpg multipolygon YES NULL

430

gc geometrycollection YES NULL

431

gm geometry YES NULL

432

ALTER TABLE t1 ADD fid INT;

433

SHOW FIELDS FROM t1;

434

Field Type Null Key Default Extra

435

a int(11) NO PRI NULL auto_increment

436

gp point YES NULL

437

ln linestring YES NULL

438

pg polygon YES NULL

439

mp multipoint YES NULL

440

mln multilinestring YES NULL

441

mpg multipolygon YES NULL

442

gc geometrycollection YES NULL

443

gm geometry YES NULL

444

fid int(11) YES NULL

445

DROP TABLE t1;

446

create table t1 (pk integer primary key auto_increment, a geometry not null);

447

insert into t1 (a) values (GeomFromText('Point(1 2)'));

448

insert into t1 (a) values ('Garbage');

449

ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field

450

insert IGNORE into t1 (a) values ('Garbage');

451

ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field

452

drop table t1;

453

create table t1 (pk integer primary key auto_increment, fl geometry not null);

454

insert into t1 (fl) values (1);

455

ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field

456

insert into t1 (fl) values (1.11);

457

ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field

458

insert into t1 (fl) values ("qwerty");

459

ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field

460

insert into t1 (fl) values (pointfromtext('point(1,1)'));

461

ERROR 23000: Column 'fl' cannot be null

462

drop table t1;

463

End of 4.1 tests

464

CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY);

465

INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));

466

INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));

467

INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));

468

INSERT INTO t1 VALUES("up", GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));

469

INSERT INTO t1 VALUES("up2", GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));

470

INSERT INTO t1 VALUES("up3", GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));

471

INSERT INTO t1 VALUES("down", GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));

472

INSERT INTO t1 VALUES("down2", GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));

473

INSERT INTO t1 VALUES("down3", GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));

474

INSERT INTO t1 VALUES("right", GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));

475

INSERT INTO t1 VALUES("right2", GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));

476

INSERT INTO t1 VALUES("right3", GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));

477

INSERT INTO t1 VALUES("left", GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));

478

INSERT INTO t1 VALUES("left2", GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));

479

INSERT INTO t1 VALUES("left3", GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))'));

480

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;

481

mbrcontains

482

center,small

483

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;

484

mbrdisjoint

485

down3,left3,right3,up3

486

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;

487

mbrequal

488

center

489

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;

490

mbrintersect

491

big,center,down,down2,left,left2,right,right2,small,up,up2

492

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;

493

mbroverlaps

494

down,left,right,up

495

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;

496

mbrtouches

497

down2,left2,right2,up2

498

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;

499

mbrwithin

500

big,center

501

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;

502

contains

503

center,small

504

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;

505

disjoint

506

down3,left3,right3,up3

507

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;

508

equals

509

center

510

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;

511

intersect

512

big,center,down,down2,left,left2,right,right2,small,up,up2

513

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;

514

overlaps

515

down,left,right,up

516

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;

517

touches

518

down2,left2,right2,up2

519

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;

520

within

521

big,center

522

SET @vert1 = GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');

523

SET @horiz1 = GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');

524

SET @horiz2 = GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');

525

SET @horiz3 = GeomFromText('POLYGON ((2 0, 3 0, 2 0))');

526

SET @point1 = GeomFromText('POLYGON ((0 0))');

527

SET @point2 = GeomFromText('POLYGON ((-2 0))');

528

SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @vert1) GROUP BY a1.name;

529

overlaps

530

SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @horiz1) GROUP BY a1.name;

531

overlaps

532

SELECT Overlaps(@horiz1, @vert1) FROM DUAL;

533

Overlaps(@horiz1, @vert1)

534

0

535

SELECT Overlaps(@horiz1, @horiz2) FROM DUAL;

536

Overlaps(@horiz1, @horiz2)

537

1

538

SELECT Overlaps(@horiz1, @horiz3) FROM DUAL;

539

Overlaps(@horiz1, @horiz3)

540

0

541

SELECT Overlaps(@horiz1, @point1) FROM DUAL;

542

Overlaps(@horiz1, @point1)

543

0

544

SELECT Overlaps(@horiz1, @point2) FROM DUAL;

545

Overlaps(@horiz1, @point2)

546

0

547

DROP TABLE t1;

548

End of 5.0 tests

549

CREATE TABLE t1 (p POINT);

550

CREATE TABLE t2 (p POINT, INDEX(p));

551

INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));

552

INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));

553

SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');

554

COUNT(*)

555

1

556

EXPLAIN

557

SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');

558

id select_type table type possible_keys key key_len ref rows Extra

559

1 SIMPLE t2 ref p p 28 const 1 Using where

560

SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');

561

COUNT(*)

562

1

563

INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));

564

INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));

565

EXPLAIN

566

SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');

567

id select_type table type possible_keys key key_len ref rows Extra

568

1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where

569

SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');

570

COUNT(*)

571

2

572

EXPLAIN

573

SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');

574

id select_type table type possible_keys key key_len ref rows Extra

575

1 SIMPLE t2 ref p p 28 const 2 Using where

576

SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');

577

COUNT(*)

578

2

579

EXPLAIN

580

SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');

581

id select_type table type possible_keys key key_len ref rows Extra

582

1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where

583

SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');

584

COUNT(*)

585

2

586

DROP TABLE t1, t2;

587

End of 5.0 tests

588

create table t1 (g geometry not null, spatial gk(g)) engine=innodb;

589

ERROR HY000: The used table type doesn't support SPATIAL indexes