mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 04:46:15 +01:00
1118 lines
44 KiB
Text
1118 lines
44 KiB
Text
SET default_storage_engine=InnoDB;
|
|
SET innodb_strict_mode=OFF;
|
|
SET @save_innodb_stats_persistent=@@GLOBAL.innodb_stats_persistent;
|
|
SET GLOBAL innodb_stats_persistent=0;
|
|
CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT) ENGINE=InnoDB;
|
|
CREATE TABLE gis_line (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING) ENGINE=InnoDB;
|
|
CREATE TABLE gis_polygon (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON) ENGINE=InnoDB;
|
|
CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT) ENGINE=InnoDB;
|
|
CREATE TABLE gis_multi_line (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING) ENGINE=InnoDB;
|
|
CREATE TABLE gis_multi_polygon (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON) ENGINE=InnoDB;
|
|
CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION) ENGINE=InnoDB;
|
|
CREATE TABLE gis_geometry (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY) ENGINE=InnoDB;
|
|
SHOW FIELDS FROM gis_point;
|
|
Field Type Null Key Default Extra
|
|
fid int(11) NO PRI NULL
|
|
g point YES NULL
|
|
SHOW FIELDS FROM gis_line;
|
|
Field Type Null Key Default Extra
|
|
fid int(11) NO PRI NULL
|
|
g linestring YES NULL
|
|
SHOW FIELDS FROM gis_polygon;
|
|
Field Type Null Key Default Extra
|
|
fid int(11) NO PRI NULL
|
|
g polygon YES NULL
|
|
SHOW FIELDS FROM gis_multi_point;
|
|
Field Type Null Key Default Extra
|
|
fid int(11) NO PRI NULL
|
|
g multipoint YES NULL
|
|
SHOW FIELDS FROM gis_multi_line;
|
|
Field Type Null Key Default Extra
|
|
fid int(11) NO PRI NULL
|
|
g multilinestring YES NULL
|
|
SHOW FIELDS FROM gis_multi_polygon;
|
|
Field Type Null Key Default Extra
|
|
fid int(11) NO PRI NULL
|
|
g multipolygon YES NULL
|
|
SHOW FIELDS FROM gis_geometrycollection;
|
|
Field Type Null Key Default Extra
|
|
fid int(11) NO PRI NULL
|
|
g geometrycollection YES NULL
|
|
SHOW FIELDS FROM gis_geometry;
|
|
Field Type Null Key Default Extra
|
|
fid int(11) NO PRI NULL
|
|
g geometry YES NULL
|
|
#INSERT using all WKT & WKB functions
|
|
INSERT INTO gis_point VALUES
|
|
(101, ST_PointFromText('POINT(10 10)')),
|
|
(102, ST_PointFromText('POINT(20 10)')),
|
|
(103, ST_PointFromText('POINT(20 20)')),
|
|
(104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
|
|
INSERT INTO gis_line VALUES
|
|
(105, ST_LineFromText('LINESTRING(0 0,0 10,10 0)')),
|
|
(106, ST_LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
|
|
(107, ST_LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(40, 10)))));
|
|
INSERT INTO gis_polygon VALUES
|
|
(108, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
|
|
(109, ST_PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
|
|
(110, ST_PolyFromWKB(ST_AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))));
|
|
INSERT INTO gis_multi_point VALUES
|
|
(111, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
|
|
(112, ST_MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
|
|
(113, ST_MPointFromWKB(ST_AsWKB(MultiPoint(Point(3, 6), Point(4, 10)))));
|
|
INSERT INTO gis_multi_line VALUES
|
|
(114, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
|
|
(115, ST_MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
|
|
(116, ST_MLineFromWKB(ST_AsWKB(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, ST_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, ST_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, ST_MPolyFromWKB(ST_AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))));
|
|
INSERT INTO gis_geometrycollection VALUES
|
|
(120, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
|
|
(121, ST_GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(10 10), LINESTRING(10 10,20 20))')),
|
|
(122, ST_GeometryFromWKB(ST_AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))))));
|
|
#check INSERT using SELECT query
|
|
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;
|
|
#check format conversion functions & spatial values
|
|
SELECT fid, ST_AsText(g) FROM gis_point;
|
|
fid ST_AsText(g)
|
|
101 POINT(10 10)
|
|
102 POINT(20 10)
|
|
103 POINT(20 20)
|
|
104 POINT(10 20)
|
|
SELECT fid, ST_AsText(g) FROM gis_line;
|
|
fid ST_AsText(g)
|
|
105 LINESTRING(0 0,0 10,10 0)
|
|
106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
|
|
107 LINESTRING(10 10,40 10)
|
|
SELECT fid, ST_AsText(g) FROM gis_polygon;
|
|
fid ST_AsText(g)
|
|
108 POLYGON((10 10,20 10,20 20,10 20,10 10))
|
|
109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
|
|
110 POLYGON((0 0,30 0,30 30,0 0))
|
|
SELECT fid, ST_AsText(g) FROM gis_multi_point;
|
|
fid ST_AsText(g)
|
|
111 MULTIPOINT(0 0,10 10,10 20,20 20)
|
|
112 MULTIPOINT(1 1,11 11,11 21,21 21)
|
|
113 MULTIPOINT(3 6,4 10)
|
|
SELECT fid, ST_AsText(g) FROM gis_multi_line;
|
|
fid ST_AsText(g)
|
|
114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
|
|
115 MULTILINESTRING((10 48,10 21,10 0))
|
|
116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
|
|
SELECT fid, ST_AsText(g) FROM gis_multi_polygon;
|
|
fid ST_AsText(g)
|
|
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)))
|
|
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)))
|
|
119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
|
|
SELECT fid, ST_AsText(g) FROM gis_geometrycollection;
|
|
fid ST_AsText(g)
|
|
120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
|
|
121 GEOMETRYCOLLECTION(POINT(10 10),LINESTRING(10 10,20 20))
|
|
122 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
|
|
SELECT fid, ST_AsText(g) FROM gis_geometry;
|
|
fid ST_AsText(g)
|
|
101 POINT(10 10)
|
|
102 POINT(20 10)
|
|
103 POINT(20 20)
|
|
104 POINT(10 20)
|
|
105 LINESTRING(0 0,0 10,10 0)
|
|
106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
|
|
107 LINESTRING(10 10,40 10)
|
|
108 POLYGON((10 10,20 10,20 20,10 20,10 10))
|
|
109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
|
|
110 POLYGON((0 0,30 0,30 30,0 0))
|
|
111 MULTIPOINT(0 0,10 10,10 20,20 20)
|
|
112 MULTIPOINT(1 1,11 11,11 21,21 21)
|
|
113 MULTIPOINT(3 6,4 10)
|
|
114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
|
|
115 MULTILINESTRING((10 48,10 21,10 0))
|
|
116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
|
|
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)))
|
|
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)))
|
|
119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
|
|
120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
|
|
121 GEOMETRYCOLLECTION(POINT(10 10),LINESTRING(10 10,20 20))
|
|
122 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
|
|
#2.1 check DDL functionality on GIS datatypes
|
|
CREATE TABLE tab(c1 POINT,c2 LINESTRING,c3 POLYGON,C4 MULTIPOINT,c5 MULTILINESTRING ,
|
|
c6 MULTIPOLYGON,c7 GEOMETRYCOLLECTION ,c8 GEOMETRY) ENGINE=InnoDB;
|
|
#check information schema for all the columns refer to 14, except POINT which is 15
|
|
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/tab'
|
|
ORDER BY sc.name;
|
|
name pos mtype
|
|
c1 0 14
|
|
c2 1 14
|
|
c3 2 14
|
|
C4 3 14
|
|
c5 4 14
|
|
c6 5 14
|
|
c7 6 14
|
|
c8 7 14
|
|
#check Perform convesrion before INSERT using WKT functions
|
|
SET @c1=ST_PointFromText('POINT(10 10)');
|
|
SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
|
|
SET @c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
|
|
SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
|
|
SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
|
|
SET @c6=ST_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)))');
|
|
SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
|
|
SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
|
|
#Insert the spatial values
|
|
INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
|
|
#check index on GIS datatypes
|
|
CREATE INDEX idx1 on tab(c2(5) DESC) USING BTREE;
|
|
CREATE INDEX idx3 on tab(c3(5) ASC) USING BTREE;
|
|
CREATE UNIQUE INDEX idx2 on tab(c8(5) ASC) ;
|
|
#check equality predicate on the index columns
|
|
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
|
|
WHERE c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE tab ref idx1 idx1 8 const # Using where
|
|
#check index with WKB function
|
|
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
|
|
FROM tab WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE tab ref idx1 idx1 8 const # Using where
|
|
#check index with WKT function
|
|
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
|
|
WHERE c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE tab ref idx3 idx3 8 const # Using where
|
|
#check index with WKB function
|
|
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
|
|
FROM tab
|
|
WHERE c3=ST_PolyFromWKB(ST_AsWkB(ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))')));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE tab ref idx3 idx3 8 const # Using where
|
|
#check index with WKT function
|
|
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
|
|
WHERE c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE tab const idx2 idx2 8 const #
|
|
#check index with WKB function
|
|
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
|
|
FROM tab
|
|
WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE tab const idx2 idx2 8 const #
|
|
#check index with DELETE operation
|
|
EXPLAIN DELETE FROM tab
|
|
WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE tab range idx2 idx2 8 NULL # Using where
|
|
#check the spatial values
|
|
SELECT ST_AsText(c1) FROM tab;
|
|
ST_AsText(c1)
|
|
POINT(10 10)
|
|
SELECT ST_AsText(c2) FROM tab;
|
|
ST_AsText(c2)
|
|
LINESTRING(10 10,20 20,30 30)
|
|
SELECT ST_AsText(c3) FROM tab;
|
|
ST_AsText(c3)
|
|
POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
|
|
SELECT ST_AsText(c4) FROM tab;
|
|
ST_AsText(c4)
|
|
MULTIPOINT(0 0,5 5,10 10,20 20)
|
|
SELECT ST_AsText(c5) FROM tab;
|
|
ST_AsText(c5)
|
|
MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
|
|
SELECT ST_AsText(c6) FROM tab;
|
|
ST_AsText(c6)
|
|
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)))
|
|
SELECT ST_AsText(c7) FROM tab;
|
|
ST_AsText(c7)
|
|
GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
|
|
SELECT ST_AsText(c8) From tab;
|
|
ST_AsText(c8)
|
|
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
|
|
#check by modify the column type
|
|
ALTER TABLE tab MODIFY COLUMN c1 Geometry;
|
|
ALTER TABLE tab MODIFY COLUMN c2 Geometry;
|
|
ALTER TABLE tab MODIFY COLUMN c3 Geometry;
|
|
ALTER TABLE tab MODIFY COLUMN c4 Geometry;
|
|
ALTER TABLE tab MODIFY COLUMN c5 Geometry;
|
|
ALTER TABLE tab MODIFY COLUMN c6 Geometry;
|
|
ALTER TABLE tab MODIFY COLUMN c7 Geometry;
|
|
#check column datatypes
|
|
SHOW FIELDS FROM tab;
|
|
Field Type Null Key Default Extra
|
|
c1 geometry YES NULL
|
|
c2 geometry YES MUL NULL
|
|
c3 geometry YES MUL NULL
|
|
c4 geometry YES NULL
|
|
c5 geometry YES NULL
|
|
c6 geometry YES NULL
|
|
c7 geometry YES NULL
|
|
c8 geometry YES UNI NULL
|
|
#check the data after modify
|
|
SELECT ST_AsText(c1) FROM tab;
|
|
ST_AsText(c1)
|
|
POINT(10 10)
|
|
SELECT ST_AsText(c2) FROM tab;
|
|
ST_AsText(c2)
|
|
LINESTRING(10 10,20 20,30 30)
|
|
SELECT ST_AsText(c3) FROM tab;
|
|
ST_AsText(c3)
|
|
POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
|
|
SELECT ST_AsText(c4) FROM tab;
|
|
ST_AsText(c4)
|
|
MULTIPOINT(0 0,5 5,10 10,20 20)
|
|
SELECT ST_AsText(c5) FROM tab;
|
|
ST_AsText(c5)
|
|
MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
|
|
SELECT ST_AsText(c6) FROM tab;
|
|
ST_AsText(c6)
|
|
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)))
|
|
SELECT ST_AsText(c7) FROM tab;
|
|
ST_AsText(c7)
|
|
GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
|
|
SELECT ST_AsText(c8) From tab;
|
|
ST_AsText(c8)
|
|
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
|
|
#check by modify the column type
|
|
ALTER TABLE tab MODIFY COLUMN c1 POINT;
|
|
ALTER TABLE tab MODIFY COLUMN c2 LINESTRING;
|
|
ALTER TABLE tab MODIFY COLUMN c3 POLYGON;
|
|
ALTER TABLE tab MODIFY COLUMN c4 MULTIPOINT;
|
|
ALTER TABLE tab MODIFY COLUMN c5 MULTILINESTRING;
|
|
ALTER TABLE tab MODIFY COLUMN c6 MULTIPOLYGON;
|
|
ALTER TABLE tab MODIFY COLUMN c7 GEOMETRYCOLLECTION;
|
|
#check column datatypes
|
|
SHOW FIELDS FROM tab;
|
|
Field Type Null Key Default Extra
|
|
c1 point YES NULL
|
|
c2 linestring YES MUL NULL
|
|
c3 polygon YES MUL NULL
|
|
c4 multipoint YES NULL
|
|
c5 multilinestring YES NULL
|
|
c6 multipolygon YES NULL
|
|
c7 geometrycollection YES NULL
|
|
c8 geometry YES UNI NULL
|
|
#check the data after modify
|
|
SELECT ST_AsText(c1) FROM tab;
|
|
ST_AsText(c1)
|
|
POINT(10 10)
|
|
SELECT ST_AsText(c2) FROM tab;
|
|
ST_AsText(c2)
|
|
LINESTRING(10 10,20 20,30 30)
|
|
SELECT ST_AsText(c3) FROM tab;
|
|
ST_AsText(c3)
|
|
POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
|
|
SELECT ST_AsText(c4) FROM tab;
|
|
ST_AsText(c4)
|
|
MULTIPOINT(0 0,5 5,10 10,20 20)
|
|
SELECT ST_AsText(c5) FROM tab;
|
|
ST_AsText(c5)
|
|
MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
|
|
SELECT ST_AsText(c6) FROM tab;
|
|
ST_AsText(c6)
|
|
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)))
|
|
SELECT ST_AsText(c7) FROM tab;
|
|
ST_AsText(c7)
|
|
GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
|
|
SELECT ST_AsText(c8) From tab;
|
|
ST_AsText(c8)
|
|
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
|
|
#check change the column type
|
|
ALTER TABLE tab MODIFY COLUMN c1 BLOB;
|
|
#check column datatypes
|
|
SHOW CREATE TABLE tab;
|
|
Table Create Table
|
|
tab CREATE TABLE `tab` (
|
|
`c1` blob DEFAULT NULL,
|
|
`c2` linestring DEFAULT NULL,
|
|
`c3` polygon DEFAULT NULL,
|
|
`c4` multipoint DEFAULT NULL,
|
|
`c5` multilinestring DEFAULT NULL,
|
|
`c6` multipolygon DEFAULT NULL,
|
|
`c7` geometrycollection DEFAULT NULL,
|
|
`c8` geometry DEFAULT NULL,
|
|
UNIQUE KEY `idx2` (`c8`(5)),
|
|
KEY `idx1` (`c2`(5) DESC) USING BTREE,
|
|
KEY `idx3` (`c3`(5)) USING BTREE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
#check the data after modify
|
|
SELECT ST_AsText(c1) FROM tab;
|
|
ST_AsText(c1)
|
|
POINT(10 10)
|
|
#change the column name
|
|
ALTER TABLE tab CHANGE COLUMN c1 c0 GEOMETRY ;
|
|
#check column datatypes
|
|
SHOW FIELDS FROM tab;
|
|
Field Type Null Key Default Extra
|
|
c0 geometry YES NULL
|
|
c2 linestring YES MUL NULL
|
|
c3 polygon YES MUL NULL
|
|
c4 multipoint YES NULL
|
|
c5 multilinestring YES NULL
|
|
c6 multipolygon YES NULL
|
|
c7 geometrycollection YES NULL
|
|
c8 geometry YES UNI NULL
|
|
#add primary key
|
|
ALTER TABLE tab ADD PRIMARY KEY pk2(c8(5));
|
|
Warnings:
|
|
Warning 1280 Name 'pk2' ignored for PRIMARY key.
|
|
#check columns
|
|
SHOW FIELDS FROM tab;
|
|
Field Type Null Key Default Extra
|
|
c0 geometry YES NULL
|
|
c2 linestring YES MUL NULL
|
|
c3 polygon YES MUL NULL
|
|
c4 multipoint YES NULL
|
|
c5 multilinestring YES NULL
|
|
c6 multipolygon YES NULL
|
|
c7 geometrycollection YES NULL
|
|
c8 geometry NO PRI NULL
|
|
#drop key
|
|
ALTER TABLE tab DROP PRIMARY KEY;
|
|
#check columns
|
|
SHOW FIELDS FROM tab;
|
|
Field Type Null Key Default Extra
|
|
c0 geometry YES NULL
|
|
c2 linestring YES MUL NULL
|
|
c3 polygon YES MUL NULL
|
|
c4 multipoint YES NULL
|
|
c5 multilinestring YES NULL
|
|
c6 multipolygon YES NULL
|
|
c7 geometrycollection YES NULL
|
|
c8 geometry NO UNI NULL
|
|
#cleanup the table
|
|
TRUNCATE TABLE tab;
|
|
#check with procedures
|
|
#crate proc with INOUT params
|
|
CREATE PROCEDURE geom_insert(IN c1 POINT,IN c2 LINESTRING,IN c3 POLYGON,
|
|
IN c4 MULTIPOINT,IN c5 MULTILINESTRING, IN c6 MULTIPOLYGON,IN c7 GEOMETRYCOLLECTION,
|
|
IN c8 GEOMETRY)
|
|
BEGIN
|
|
INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
|
|
END |
|
|
#set the spatial values
|
|
SET @c1=ST_PointFromText('POINT(10 10)');
|
|
SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
|
|
SET @c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
|
|
SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
|
|
SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
|
|
SET @c6=ST_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)))');
|
|
SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
|
|
SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
|
|
#call the proc
|
|
CALL geom_insert(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
|
|
#check the values after proc call
|
|
SELECT ST_AsText(c0) FROM tab;
|
|
ST_AsText(c0)
|
|
POINT(10 10)
|
|
SELECT ST_AsText(c2) FROM tab;
|
|
ST_AsText(c2)
|
|
LINESTRING(10 10,20 20,30 30)
|
|
SELECT ST_AsText(c3) FROM tab;
|
|
ST_AsText(c3)
|
|
POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
|
|
SELECT ST_AsText(c4) FROM tab;
|
|
ST_AsText(c4)
|
|
MULTIPOINT(0 0,5 5,10 10,20 20)
|
|
SELECT ST_AsText(c5) FROM tab;
|
|
ST_AsText(c5)
|
|
MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
|
|
SELECT ST_AsText(c6) FROM tab;
|
|
ST_AsText(c6)
|
|
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)))
|
|
SELECT ST_AsText(c7) FROM tab;
|
|
ST_AsText(c7)
|
|
GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
|
|
SELECT ST_AsText(c8) From tab;
|
|
ST_AsText(c8)
|
|
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
|
|
#set the input spatial value
|
|
SET @c9=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
|
|
#crate a proc with INOUT params and pass a value.
|
|
CREATE PROCEDURE geominout(INOUT c9 GEOMETRY)
|
|
BEGIN
|
|
SELECT ST_AsText(@c9);
|
|
SET c9=ST_GeomCollFromText('GeometryCollection(Point(10 10),LineString(20 20, 30 30))');
|
|
END|
|
|
#call the proc and then change the spatial value of c8
|
|
CALL geominout(@c9);
|
|
ST_AsText(@c9)
|
|
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
|
|
#now check the out spatial values should be changed to new values
|
|
SELECT ST_AsText(@c9);
|
|
ST_AsText(@c9)
|
|
GEOMETRYCOLLECTION(POINT(10 10),LINESTRING(20 20,30 30))
|
|
#delete the records
|
|
TRUNCATE TABLE tab;
|
|
#create another table same as tab
|
|
CREATE TABLE tab2 AS SELECT * FROM tab;
|
|
#check the table definition
|
|
SHOW CREATE TABLE tab2;
|
|
Table Create Table
|
|
tab2 CREATE TABLE `tab2` (
|
|
`c0` geometry DEFAULT NULL,
|
|
`c2` linestring DEFAULT NULL,
|
|
`c3` polygon DEFAULT NULL,
|
|
`c4` multipoint DEFAULT NULL,
|
|
`c5` multilinestring DEFAULT NULL,
|
|
`c6` multipolygon DEFAULT NULL,
|
|
`c7` geometrycollection DEFAULT NULL,
|
|
`c8` geometry NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
#create a trigger and populate the values into tab2
|
|
CREATE TRIGGER geom_trigger AFTER INSERT ON tab
|
|
FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO tab2 VALUES (@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
|
|
END|
|
|
#set the spatial values
|
|
SET @c1=ST_PointFromText('POINT(10 10)');
|
|
SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
|
|
SET @c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
|
|
SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
|
|
SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
|
|
SET @c6=ST_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)))');
|
|
SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
|
|
SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
|
|
#Insert the spatial values
|
|
INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
|
|
#check the values whether populated
|
|
SELECT ST_AsText(c0) FROM tab2;
|
|
ST_AsText(c0)
|
|
POINT(10 10)
|
|
SELECT ST_AsText(c2) FROM tab2;
|
|
ST_AsText(c2)
|
|
LINESTRING(10 10,20 20,30 30)
|
|
SELECT ST_AsText(c3) FROM tab2;
|
|
ST_AsText(c3)
|
|
POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
|
|
SELECT ST_AsText(c4) FROM tab2;
|
|
ST_AsText(c4)
|
|
MULTIPOINT(0 0,5 5,10 10,20 20)
|
|
SELECT ST_AsText(c5) FROM tab2;
|
|
ST_AsText(c5)
|
|
MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
|
|
SELECT ST_AsText(c6) FROM tab2;
|
|
ST_AsText(c6)
|
|
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)))
|
|
SELECT ST_AsText(c7) FROM tab2;
|
|
ST_AsText(c7)
|
|
GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
|
|
SELECT ST_AsText(c8) From tab2;
|
|
ST_AsText(c8)
|
|
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
|
|
#check Cursor with Geometry
|
|
CREATE PROCEDURE geom_cursor()
|
|
BEGIN
|
|
DECLARE v GEOMETRY;
|
|
DECLARE c CURSOR FOR SELECT c8 FROM tab2;
|
|
OPEN c;
|
|
FETCH c INTO v;
|
|
CLOSE c;
|
|
SELECT ST_AsText(v);
|
|
END|
|
|
# the following line was commented due the Bug#16282246
|
|
# Once it is fixed, the comment will be removed.
|
|
# right now bug fixing date is unknown.
|
|
check self join
|
|
SELECT ST_AsText(a.c0),ST_AsText(a.c2),ST_AsText(a.c3)
|
|
FROM tab a,tab2 b WHERE a.c0=b.c0 AND a.c2=b.c2
|
|
AND a.c3=b.c3 AND a.c4=b.c4 AND a.c5=b.c5 AND a.c6=b.c6
|
|
AND a.c7=b.c7 AND a.c8=b.c8;
|
|
ST_AsText(a.c0) ST_AsText(a.c2) ST_AsText(a.c3)
|
|
POINT(10 10) LINESTRING(10 10,20 20,30 30) POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
|
|
#check equi join
|
|
SELECT ST_AsText(a.c2),ST_AsText(b.c2) FROM tab a,tab2 b WHERE a.c2=b.c2;
|
|
ST_AsText(a.c2) ST_AsText(b.c2)
|
|
LINESTRING(10 10,20 20,30 30) LINESTRING(10 10,20 20,30 30)
|
|
#check DELETE stmt with Where clause and a constant predicate
|
|
DELETE FROM tab
|
|
WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
|
|
SELECT * FROM tab;
|
|
c0 c2 c3 c4 c5 c6 c7 c8
|
|
#check UPDATE stmt with Where clause and a constant predicate
|
|
SET @c8=ST_GeomCollFromText('GeometryCollection(Point(10 10),LineString(20 20, 30 30))');
|
|
UPDATE tab2 SET c8=@c8
|
|
WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
|
|
#check the column should be modified to LINESTRING(10 10,20 20,30 30)
|
|
SELECT ST_AsText(c8) From tab2;
|
|
ST_AsText(c8)
|
|
GEOMETRYCOLLECTION(POINT(10 10),LINESTRING(20 20,30 30))
|
|
#check GIS datatypes with aggregate functions
|
|
SELECT COUNT(ST_AsText(g)) FROM gis_point;
|
|
COUNT(ST_AsText(g))
|
|
4
|
|
SELECT COUNT(ST_AsText(g)) FROM gis_line;
|
|
COUNT(ST_AsText(g))
|
|
3
|
|
SELECT COUNT(ST_AsText(g)) FROM gis_polygon;
|
|
COUNT(ST_AsText(g))
|
|
3
|
|
SELECT COUNT(ST_AsText(g)) FROM gis_multi_point;
|
|
COUNT(ST_AsText(g))
|
|
3
|
|
SELECT COUNT(ST_AsText(g)) FROM gis_multi_line;
|
|
COUNT(ST_AsText(g))
|
|
3
|
|
SELECT COUNT(ST_AsText(g)) FROM gis_multi_polygon;
|
|
COUNT(ST_AsText(g))
|
|
3
|
|
SELECT COUNT(ST_AsText(g)) FROM gis_geometrycollection;
|
|
COUNT(ST_AsText(g))
|
|
3
|
|
SELECT COUNT(ST_AsText(g)) FROM gis_geometry;
|
|
COUNT(ST_AsText(g))
|
|
22
|
|
SELECT SUM(ST_AsText(g)) FROM gis_point;
|
|
SUM(ST_AsText(g))
|
|
0
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'POINT(10 10)'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'POINT(20 10)'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'POINT(20 20)'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'POINT(10 20)'
|
|
SELECT SUM(ST_AsText(g)) FROM gis_line;
|
|
SUM(ST_AsText(g))
|
|
0
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(0 0,0 10,10 0)'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(10 10,20 10,20 20,10 20,10 10)'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(10 10,40 10)'
|
|
SELECT AVG(ST_AsText(g)) FROM gis_polygon;
|
|
AVG(ST_AsText(g))
|
|
0
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'POLYGON((10 10,20 10,20 20,10 20,10 10))'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'POLYGON((0 0,30 0,30 30,0 0))'
|
|
#here it show some string value no meaning
|
|
SELECT MAX(ST_AsText(g)) FROM gis_multi_point;
|
|
MAX(ST_AsText(g))
|
|
MULTIPOINT(3 6,4 10)
|
|
#here it show some string value no meaning
|
|
SELECT MIN(ST_AsText(g)) FROM gis_multi_line;
|
|
MIN(ST_AsText(g))
|
|
MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
|
|
SELECT STD(ST_AsText(g)) FROM gis_multi_polygon;
|
|
STD(ST_AsText(g))
|
|
0
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: '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)))'
|
|
Warning 1292 Truncated incorrect DOUBLE value: '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)))'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'MULTIPOLYGON(((0 3,3 3,3 0,0 3)))'
|
|
SELECT COUNT(ST_AsText(g)) FROM gis_geometrycollection;
|
|
COUNT(ST_AsText(g))
|
|
3
|
|
SELECT AVG(ST_AsText(g)) FROM gis_geometry;
|
|
AVG(ST_AsText(g))
|
|
0
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'POINT(10 10)'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'POINT(20 10)'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'POINT(20 20)'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'POINT(10 20)'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(0 0,0 10,10 0)'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(10 10,20 10,20 20,10 20,10 10)'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'LINESTRING(10 10,40 10)'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'POLYGON((10 10,20 10,20 20,10 20,10 10))'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'POLYGON((0 0,30 0,30 30,0 0))'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'MULTIPOINT(0 0,10 10,10 20,20 20)'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'MULTIPOINT(1 1,11 11,11 21,21 21)'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'MULTIPOINT(3 6,4 10)'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'MULTILINESTRING((10 48,10 21,10 0))'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))'
|
|
Warning 1292 Truncated incorrect DOUBLE value: '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)))'
|
|
Warning 1292 Truncated incorrect DOUBLE value: '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)))'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'MULTIPOLYGON(((0 3,3 3,3 0,0 3)))'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'GEOMETRYCOLLECTION(POINT(10 10),LINESTRING(10 10,20 20))'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))'
|
|
#check Datatypes with compression tables
|
|
CREATE TABLE tab3(c1 POINT,c2 LINESTRING,c3 POLYGON,C4 MULTIPOINT,c5 MULTILINESTRING ,
|
|
c6 MULTIPOLYGON,c7 GEOMETRYCOLLECTION ,c8 GEOMETRY) ENGINE=InnoDB
|
|
KEY_BLOCK_SIZE=8 ROW_FORMAT=COMPRESSED;
|
|
#check file format
|
|
SHOW CREATE TABLE tab3;
|
|
Table Create Table
|
|
tab3 CREATE TABLE `tab3` (
|
|
`c1` point DEFAULT NULL,
|
|
`c2` linestring DEFAULT NULL,
|
|
`c3` polygon DEFAULT NULL,
|
|
`C4` multipoint DEFAULT NULL,
|
|
`c5` multilinestring DEFAULT NULL,
|
|
`c6` multipolygon DEFAULT NULL,
|
|
`c7` geometrycollection DEFAULT NULL,
|
|
`c8` geometry DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
|
|
ALTER TABLE tab3 KEY_BLOCK_SIZE=16 ROW_FORMAT=Dynamic;
|
|
#check file format
|
|
SHOW CREATE TABLE tab3;
|
|
Table Create Table
|
|
tab3 CREATE TABLE `tab3` (
|
|
`c1` point DEFAULT NULL,
|
|
`c2` linestring DEFAULT NULL,
|
|
`c3` polygon DEFAULT NULL,
|
|
`C4` multipoint DEFAULT NULL,
|
|
`c5` multilinestring DEFAULT NULL,
|
|
`c6` multipolygon DEFAULT NULL,
|
|
`c7` geometrycollection DEFAULT NULL,
|
|
`c8` geometry DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=16
|
|
#check information schema for all the columns refer to 14, except POINT which is 15
|
|
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/tab3'
|
|
ORDER BY sc.name;
|
|
name pos mtype
|
|
c1 0 14
|
|
c2 1 14
|
|
c3 2 14
|
|
C4 3 14
|
|
c5 4 14
|
|
c6 5 14
|
|
c7 6 14
|
|
c8 7 14
|
|
#check Perform convesrion before INSERT using WKT functions
|
|
SET @c1=ST_PointFromText('POINT(10 10)');
|
|
SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
|
|
SET @c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
|
|
SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
|
|
SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
|
|
SET @c6=ST_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)))');
|
|
SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
|
|
SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
|
|
#Insert the spatial values
|
|
INSERT INTO tab3 VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
|
|
#check index on GIS datatypes
|
|
CREATE INDEX idx1 on tab3(c2(5) DESC) USING BTREE;
|
|
CREATE INDEX idx3 on tab3(c3(5) ASC) USING BTREE;
|
|
CREATE UNIQUE INDEX idx2 on tab3(c8(5) ASC) ;
|
|
#check equality predicate on the index columns
|
|
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
|
|
WHERE c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE tab3 ref idx1 idx1 8 const # Using where
|
|
#check index with WKB function
|
|
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
|
|
FROM tab3 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE tab3 ref idx1 idx1 8 const # Using where
|
|
#check index with WKT function
|
|
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
|
|
WHERE c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE tab3 ref idx3 idx3 8 const # Using where
|
|
#check index with WKB function
|
|
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
|
|
FROM tab3
|
|
WHERE c3=ST_PolyFromWKB(ST_AsWkB(ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))')));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE tab3 ref idx3 idx3 8 const # Using where
|
|
#check index with WKT function
|
|
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
|
|
WHERE c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE tab3 const idx2 idx2 8 const #
|
|
#check index with WKB function
|
|
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
|
|
FROM tab3
|
|
WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE tab3 const idx2 idx2 8 const #
|
|
#check index with DELETE operation
|
|
EXPLAIN DELETE FROM tab3
|
|
WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE tab3 range idx2 idx2 8 NULL # Using where
|
|
#check the spatial values
|
|
SELECT ST_AsText(c1) FROM tab3;
|
|
ST_AsText(c1)
|
|
POINT(10 10)
|
|
SELECT ST_AsText(c2) FROM tab3;
|
|
ST_AsText(c2)
|
|
LINESTRING(10 10,20 20,30 30)
|
|
SELECT ST_AsText(c3) FROM tab3;
|
|
ST_AsText(c3)
|
|
POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))
|
|
SELECT ST_AsText(c4) FROM tab3;
|
|
ST_AsText(c4)
|
|
MULTIPOINT(0 0,5 5,10 10,20 20)
|
|
SELECT ST_AsText(c5) FROM tab3;
|
|
ST_AsText(c5)
|
|
MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))
|
|
SELECT ST_AsText(c6) FROM tab3;
|
|
ST_AsText(c6)
|
|
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)))
|
|
SELECT ST_AsText(c7) FROM tab3;
|
|
ST_AsText(c7)
|
|
GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
|
|
SELECT ST_AsText(c8) From tab3;
|
|
ST_AsText(c8)
|
|
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
|
|
#check with Row_format = Dynamic
|
|
ALTER TABLE tab3 ROW_FORMAT=Dynamic;
|
|
#check file format
|
|
SHOW CREATE TABLE tab3;
|
|
Table Create Table
|
|
tab3 CREATE TABLE `tab3` (
|
|
`c1` point DEFAULT NULL,
|
|
`c2` linestring DEFAULT NULL,
|
|
`c3` polygon DEFAULT NULL,
|
|
`C4` multipoint DEFAULT NULL,
|
|
`c5` multilinestring DEFAULT NULL,
|
|
`c6` multipolygon DEFAULT NULL,
|
|
`c7` geometrycollection DEFAULT NULL,
|
|
`c8` geometry DEFAULT NULL,
|
|
UNIQUE KEY `idx2` (`c8`(5)),
|
|
KEY `idx1` (`c2`(5) DESC) USING BTREE,
|
|
KEY `idx3` (`c3`(5)) USING BTREE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=16
|
|
#check index with WKB function
|
|
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
|
|
FROM tab3 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE tab3 ref idx1 idx1 8 const # Using where
|
|
#check index with WKT function
|
|
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
|
|
WHERE c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE tab3 ref idx3 idx3 8 const # Using where
|
|
#check the Geometry property functions
|
|
SELECT fid, ST_Dimension(g) FROM gis_geometry;
|
|
fid ST_Dimension(g)
|
|
101 0
|
|
102 0
|
|
103 0
|
|
104 0
|
|
105 1
|
|
106 1
|
|
107 1
|
|
108 2
|
|
109 2
|
|
110 2
|
|
111 0
|
|
112 0
|
|
113 0
|
|
114 1
|
|
115 1
|
|
116 1
|
|
117 2
|
|
118 2
|
|
119 2
|
|
120 1
|
|
121 1
|
|
122 1
|
|
SELECT fid, ST_GeometryType(g) FROM gis_geometry;
|
|
fid ST_GeometryType(g)
|
|
101 POINT
|
|
102 POINT
|
|
103 POINT
|
|
104 POINT
|
|
105 LINESTRING
|
|
106 LINESTRING
|
|
107 LINESTRING
|
|
108 POLYGON
|
|
109 POLYGON
|
|
110 POLYGON
|
|
111 MULTIPOINT
|
|
112 MULTIPOINT
|
|
113 MULTIPOINT
|
|
114 MULTILINESTRING
|
|
115 MULTILINESTRING
|
|
116 MULTILINESTRING
|
|
117 MULTIPOLYGON
|
|
118 MULTIPOLYGON
|
|
119 MULTIPOLYGON
|
|
120 GEOMETRYCOLLECTION
|
|
121 GEOMETRYCOLLECTION
|
|
122 GEOMETRYCOLLECTION
|
|
SELECT fid, ST_IsEmpty(g) FROM gis_geometry;
|
|
fid ST_IsEmpty(g)
|
|
101 0
|
|
102 0
|
|
103 0
|
|
104 0
|
|
105 0
|
|
106 0
|
|
107 0
|
|
108 0
|
|
109 0
|
|
110 0
|
|
111 0
|
|
112 0
|
|
113 0
|
|
114 0
|
|
115 0
|
|
116 0
|
|
117 0
|
|
118 0
|
|
119 0
|
|
120 0
|
|
121 0
|
|
122 0
|
|
SELECT fid, ST_AsText(ST_Envelope(g)) FROM gis_geometry;
|
|
fid ST_AsText(ST_Envelope(g))
|
|
101 POLYGON((10 10,10 10,10 10,10 10,10 10))
|
|
102 POLYGON((20 10,20 10,20 10,20 10,20 10))
|
|
103 POLYGON((20 20,20 20,20 20,20 20,20 20))
|
|
104 POLYGON((10 20,10 20,10 20,10 20,10 20))
|
|
105 POLYGON((0 0,10 0,10 10,0 10,0 0))
|
|
106 POLYGON((10 10,20 10,20 20,10 20,10 10))
|
|
107 POLYGON((10 10,40 10,40 10,10 10,10 10))
|
|
108 POLYGON((10 10,20 10,20 20,10 20,10 10))
|
|
109 POLYGON((0 0,50 0,50 50,0 50,0 0))
|
|
110 POLYGON((0 0,30 0,30 30,0 30,0 0))
|
|
111 POLYGON((0 0,20 0,20 20,0 20,0 0))
|
|
112 POLYGON((1 1,21 1,21 21,1 21,1 1))
|
|
113 POLYGON((3 6,4 6,4 10,3 10,3 6))
|
|
114 POLYGON((10 0,16 0,16 48,10 48,10 0))
|
|
115 POLYGON((10 0,10 0,10 48,10 48,10 0))
|
|
116 POLYGON((1 2,21 2,21 8,1 8,1 2))
|
|
117 POLYGON((28 0,84 0,84 42,28 42,28 0))
|
|
118 POLYGON((28 0,84 0,84 42,28 42,28 0))
|
|
119 POLYGON((0 0,3 0,3 3,0 3,0 0))
|
|
120 POLYGON((0 0,10 0,10 10,0 10,0 0))
|
|
121 POLYGON((10 10,20 10,20 20,10 20,10 10))
|
|
122 POLYGON((3 6,44 6,44 9,3 9,3 6))
|
|
explain extended select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE gis_geometry ALL NULL NULL NULL NULL # 100.00
|
|
Warnings:
|
|
Note 1003 select st_dimension(`test`.`gis_geometry`.`g`) AS `ST_Dimension(g)`,st_geometrytype(`test`.`gis_geometry`.`g`) AS `ST_GeometryType(g)`,st_isempty(`test`.`gis_geometry`.`g`) AS `ST_IsEmpty(g)`,st_astext(st_envelope(`test`.`gis_geometry`.`g`)) AS `ST_AsText(ST_Envelope(g))` from `test`.`gis_geometry`
|
|
#check Geometry point functions
|
|
SELECT fid, ST_X(g) FROM gis_point;
|
|
fid ST_X(g)
|
|
101 10
|
|
102 20
|
|
103 20
|
|
104 10
|
|
SELECT fid, ST_Y(g) FROM gis_point;
|
|
fid ST_Y(g)
|
|
101 10
|
|
102 10
|
|
103 20
|
|
104 20
|
|
explain extended select ST_X(g),ST_Y(g) FROM gis_point;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE gis_point ALL NULL NULL NULL NULL # 100.00
|
|
Warnings:
|
|
Note 1003 select st_x(`test`.`gis_point`.`g`) AS `ST_X(g)`,st_y(`test`.`gis_point`.`g`) AS `ST_Y(g)` from `test`.`gis_point`
|
|
SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line;
|
|
fid ST_AsText(ST_StartPoint(g))
|
|
105 POINT(0 0)
|
|
106 POINT(10 10)
|
|
107 POINT(10 10)
|
|
SELECT fid, ST_AsText(ST_EndPoint(g)) FROM gis_line;
|
|
fid ST_AsText(ST_EndPoint(g))
|
|
105 POINT(10 0)
|
|
106 POINT(10 10)
|
|
107 POINT(40 10)
|
|
SELECT fid, ST_Length(g) FROM gis_line;
|
|
fid ST_Length(g)
|
|
105 24.14213562373095
|
|
106 40
|
|
107 30
|
|
SELECT fid, ST_NumPoints(g) FROM gis_line;
|
|
fid ST_NumPoints(g)
|
|
105 3
|
|
106 5
|
|
107 2
|
|
SELECT fid, ST_AsText(ST_PointN(g, 2)) FROM gis_line;
|
|
fid ST_AsText(ST_PointN(g, 2))
|
|
105 POINT(0 10)
|
|
106 POINT(20 10)
|
|
107 POINT(40 10)
|
|
SELECT fid, ST_IsClosed(g) FROM gis_line;
|
|
fid ST_IsClosed(g)
|
|
105 0
|
|
106 1
|
|
107 0
|
|
explain extended select ST_AsText(ST_StartPoint(g)),ST_AsText(ST_EndPoint(g)),
|
|
ST_Length(g),ST_NumPoints(g),ST_AsText(ST_PointN(g, 2)),ST_IsClosed(g) FROM gis_line;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE gis_line ALL NULL NULL NULL NULL # 100.00
|
|
Warnings:
|
|
Note 1003 select st_astext(st_startpoint(`test`.`gis_line`.`g`)) AS `ST_AsText(ST_StartPoint(g))`,st_astext(st_endpoint(`test`.`gis_line`.`g`)) AS `ST_AsText(ST_EndPoint(g))`,st_length(`test`.`gis_line`.`g`) AS `ST_Length(g)`,st_numpoints(`test`.`gis_line`.`g`) AS `ST_NumPoints(g)`,st_astext(st_pointn(`test`.`gis_line`.`g`,2)) AS `ST_AsText(ST_PointN(g, 2))`,st_isclosed(`test`.`gis_line`.`g`) AS `ST_IsClosed(g)` from `test`.`gis_line`
|
|
SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon;
|
|
fid ST_AsText(ST_Centroid(g))
|
|
108 POINT(15 15)
|
|
109 POINT(25.416666666666668 25.416666666666668)
|
|
110 POINT(20 10)
|
|
SELECT fid, ST_Area(g) FROM gis_polygon;
|
|
fid ST_Area(g)
|
|
108 100
|
|
109 2400
|
|
110 450
|
|
SELECT fid, ST_AsText(ST_ExteriorRing(g)) FROM gis_polygon;
|
|
fid ST_AsText(ST_ExteriorRing(g))
|
|
108 LINESTRING(10 10,20 10,20 20,10 20,10 10)
|
|
109 LINESTRING(0 0,50 0,50 50,0 50,0 0)
|
|
110 LINESTRING(0 0,30 0,30 30,0 0)
|
|
SELECT fid, ST_NumInteriorRings(g) FROM gis_polygon;
|
|
fid ST_NumInteriorRings(g)
|
|
108 0
|
|
109 1
|
|
110 0
|
|
SELECT fid, ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
|
|
fid ST_AsText(ST_InteriorRingN(g, 1))
|
|
108 NULL
|
|
109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
|
|
110 NULL
|
|
explain extended select ST_AsText(ST_Centroid(g)),ST_Area(g),ST_AsText(ST_ExteriorRing(g)),
|
|
ST_NumInteriorRings(g),ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE gis_polygon ALL NULL NULL NULL NULL # 100.00
|
|
Warnings:
|
|
Note 1003 select st_astext(st_centroid(`test`.`gis_polygon`.`g`)) AS `ST_AsText(ST_Centroid(g))`,st_area(`test`.`gis_polygon`.`g`) AS `ST_Area(g)`,st_astext(st_exteriorring(`test`.`gis_polygon`.`g`)) AS `ST_AsText(ST_ExteriorRing(g))`,st_numinteriorrings(`test`.`gis_polygon`.`g`) AS `ST_NumInteriorRings(g)`,st_astext(st_interiorringn(`test`.`gis_polygon`.`g`,1)) AS `ST_AsText(ST_InteriorRingN(g, 1))` from `test`.`gis_polygon`
|
|
SELECT fid, ST_IsClosed(g) FROM gis_multi_line;
|
|
fid ST_IsClosed(g)
|
|
114 0
|
|
115 0
|
|
116 0
|
|
SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_multi_polygon;
|
|
fid ST_AsText(ST_Centroid(g))
|
|
117 POINT(57.98031067576927 17.854754130800433)
|
|
118 POINT(57.98031067576927 17.854754130800433)
|
|
119 POINT(2 2)
|
|
SELECT fid, ST_Area(g) FROM gis_multi_polygon;
|
|
fid ST_Area(g)
|
|
117 1684.5
|
|
118 1684.5
|
|
119 4.5
|
|
SELECT fid, ST_NumGeometries(g) from gis_multi_point;
|
|
fid ST_NumGeometries(g)
|
|
111 4
|
|
112 4
|
|
113 2
|
|
SELECT fid, ST_NumGeometries(g) from gis_multi_line;
|
|
fid ST_NumGeometries(g)
|
|
114 2
|
|
115 1
|
|
116 2
|
|
SELECT fid, ST_NumGeometries(g) from gis_multi_polygon;
|
|
fid ST_NumGeometries(g)
|
|
117 2
|
|
118 2
|
|
119 1
|
|
SELECT fid, ST_NumGeometries(g) from gis_geometrycollection;
|
|
fid ST_NumGeometries(g)
|
|
120 2
|
|
121 2
|
|
122 2
|
|
explain extended SELECT fid, ST_NumGeometries(g) from gis_multi_point;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL # 100.00
|
|
Warnings:
|
|
Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,st_numgeometries(`test`.`gis_multi_point`.`g`) AS `ST_NumGeometries(g)` from `test`.`gis_multi_point`
|
|
SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
|
|
fid ST_AsText(ST_GeometryN(g, 2))
|
|
111 POINT(10 10)
|
|
112 POINT(11 11)
|
|
113 POINT(4 10)
|
|
SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_line;
|
|
fid ST_AsText(ST_GeometryN(g, 2))
|
|
114 LINESTRING(16 0,16 23,16 48)
|
|
115 NULL
|
|
116 LINESTRING(2 5,5 8,21 7)
|
|
SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_polygon;
|
|
fid ST_AsText(ST_GeometryN(g, 2))
|
|
117 POLYGON((59 18,67 18,67 13,59 13,59 18))
|
|
118 POLYGON((59 18,67 18,67 13,59 13,59 18))
|
|
119 NULL
|
|
SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_geometrycollection;
|
|
fid ST_AsText(ST_GeometryN(g, 2))
|
|
120 LINESTRING(0 0,10 10)
|
|
121 LINESTRING(10 10,20 20)
|
|
122 LINESTRING(3 6,7 9)
|
|
SELECT fid, ST_AsText(ST_GeometryN(g, 1)) from gis_geometrycollection;
|
|
fid ST_AsText(ST_GeometryN(g, 1))
|
|
120 POINT(0 0)
|
|
121 POINT(10 10)
|
|
122 POINT(44 6)
|
|
explain extended SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL # 100.00
|
|
Warnings:
|
|
Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,st_astext(st_geometryn(`test`.`gis_multi_point`.`g`,2)) AS `ST_AsText(ST_GeometryN(g, 2))` from `test`.`gis_multi_point`
|
|
SELECT g1.fid as first, g2.fid as second,
|
|
MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
|
|
MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
|
|
MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
|
|
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
|
|
first second w c o e d t i r
|
|
120 120 1 1 0 1 0 0 1 0
|
|
120 121 0 0 0 0 0 1 1 1
|
|
120 122 0 0 1 0 0 0 1 0
|
|
121 120 0 0 0 0 0 1 1 1
|
|
121 121 1 1 0 1 0 0 1 0
|
|
121 122 0 0 0 0 1 0 0 0
|
|
122 120 0 0 1 0 0 0 1 0
|
|
122 121 0 0 0 0 1 0 0 0
|
|
122 122 1 1 0 1 0 0 1 0
|
|
explain extended SELECT g1.fid as first, g2.fid as second,
|
|
MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
|
|
MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
|
|
MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
|
|
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE g1 ALL NULL NULL NULL NULL # 100.00 Using temporary; Using filesort
|
|
1 SIMPLE g2 ALL NULL NULL NULL NULL # 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,mbrwithin(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,mbrcontains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,mbroverlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,mbrequals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,mbrdisjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,st_touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,mbrintersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,st_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`
|
|
# check support of Foreign Key constraint
|
|
CREATE TABLE parent (id GEOMETRY NOT NULL,PRIMARY KEY (id(10))) ENGINE=INNODB;
|
|
CREATE TABLE child (id GEOMETRY, parent_id GEOMETRY,
|
|
INDEX par_ind (parent_id(10)),
|
|
FOREIGN KEY (parent_id(10)) REFERENCES parent(id)
|
|
ON DELETE CASCADE
|
|
) ENGINE=INNODB;
|
|
ERROR HY000: Can't create table `test`.`child` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
#check partition table support
|
|
CREATE TABLE emp2(
|
|
id GEOMETRY NOT NULL,
|
|
store_name VARCHAR(30),
|
|
parts VARCHAR(30),
|
|
store_id GEOMETRY
|
|
)
|
|
PARTITION BY LIST(store_id) (
|
|
PARTITION pNorth VALUES IN (10,20,30) ,
|
|
PARTITION pEast VALUES IN (40,50,60) ,
|
|
PARTITION pWest VALUES IN (70,80,100)
|
|
);
|
|
ERROR HY000: A BLOB field is not allowed in partition function
|
|
#check start transaction commit & Rollback
|
|
START TRANSACTION;
|
|
DELETE FROM tab3;
|
|
SELECT * FROM tab3;
|
|
c1 c2 c3 C4 c5 c6 c7 c8
|
|
ROLLBACK;
|
|
SELECT COUNT(*) FROM tab3;
|
|
COUNT(*)
|
|
1
|
|
START TRANSACTION;
|
|
DELETE FROM tab3;
|
|
COMMIT;
|
|
SELECT * FROM tab3;
|
|
c1 c2 c3 C4 c5 c6 c7 c8
|
|
DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point;
|
|
DROP TABLE gis_multi_line, gis_multi_polygon;
|
|
DROP TABLE gis_geometrycollection, gis_geometry;
|
|
DROP TABLE tab,tab2,tab3,parent;
|
|
DROP PROCEDURE geominout;
|
|
DROP PROCEDURE geom_insert;
|
|
DROP PROCEDURE geom_cursor;
|
|
SET GLOBAL innodb_stats_persistent=@save_innodb_stats_persistent;
|