1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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
75
76
77
|
CREATE TABLE t_wl6455 ( i INT, g GEOMETRY NOT NULL) ENGINE=InnoDB;
SHOW CREATE TABLE t_wl6455;
Table Create Table
t_wl6455 CREATE TABLE `t_wl6455` (
`i` int(11) DEFAULT NULL,
`g` geometry NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
INSERT INTO t_wl6455 VALUES(1, POINT(1,1));
INSERT INTO t_wl6455 VALUES(2, POINT(2,2));
SET @mp = 'MULTIPOINT(0 0, 20 20, 60 60)';
INSERT INTO t_wl6455 VALUES(3, ST_GeomFromText(@mp));
INSERT INTO t_wl6455 VALUES(4, LINESTRING(POINT(1,1), POINT(4, 4)));
INSERT INTO t_wl6455 VALUES(5, LINESTRING(POINT(2,2), POINT(5, 5)));
SET @mls = 'MultiLineString((1 1,2 2,3 3),(4 4,5 5))';
INSERT INTO t_wl6455 VALUES(6, ST_GeomFromText(@mls));
SET @poly = 'Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))';
INSERT INTO t_wl6455 VALUES(7, ST_GeomFromText(@poly));
SET @mpoly = 'MultiPolygon(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))';
INSERT INTO t_wl6455 VALUES(8, ST_GeomFromText(@mpoly));
SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';
INSERT INTO t_wl6455 VALUES(9, ST_GeomFromText(@gc));
SELECT ST_AsText(g) FROM t_wl6455;
ST_AsText(g)
POINT(1 1)
POINT(2 2)
MULTIPOINT(0 0,20 20,60 60)
LINESTRING(1 1,4 4)
LINESTRING(2 2,5 5)
MULTILINESTRING((1 1,2 2,3 3),(4 4,5 5))
POLYGON((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))
MULTIPOLYGON(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
CREATE INDEX i_p ON t_wl6455 (g(10));
EXPLAIN SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(2,2);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t_wl6455 ref i_p i_p 12 const 2 #
SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(2,2);
ST_AsText(g)
POINT(2 2)
SET AUTOCOMMIT = 0;
INSERT INTO t_wl6455 VALUES(10, POINT(10,10));
SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(10,10);
ST_AsText(g)
POINT(10 10)
ROLLBACK;
SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(10,10);
ST_AsText(g)
INSERT INTO t_wl6455 VALUES(10, POINT(10,10));
COMMIT;
FLUSH TABLES;
INSERT INTO t_wl6455 VALUES(11, POINT(11,11));
BEGIN;
INSERT INTO t_wl6455 VALUES(1, POINT(1,1));
CHECK TABLE t_wl6455;
Table Op Msg_type Msg_text
test.t_wl6455 check status OK
SELECT ST_AsText(g) FROM t_wl6455;
ST_AsText(g)
POINT(1 1)
POINT(2 2)
MULTIPOINT(0 0,20 20,60 60)
LINESTRING(1 1,4 4)
LINESTRING(2 2,5 5)
MULTILINESTRING((1 1,2 2,3 3),(4 4,5 5))
POLYGON((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))
MULTIPOLYGON(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
POINT(10 10)
POINT(11 11)
SELECT sc.name, sc.pos, sc.mtype
FROM information_schema.innodb_sys_columns sc
INNER JOIN information_schema.innodb_sys_tables st
ON sc.TABLE_ID=st.TABLE_ID
WHERE st.NAME='test/t_wl6455' AND sc.NAME='g';
name pos mtype
g 1 14
DROP TABLE t_wl6455;
|
