Default page

Edit File: 0.result

SET default_storage_engine=innodb;
DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
CREATE TABLE gis_point  (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT);
CREATE TABLE gis_line  (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g LINESTRING);
CREATE TABLE gis_polygon   (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POLYGON);
CREATE TABLE gis_multi_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOINT);
CREATE TABLE gis_multi_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTILINESTRING);
CREATE TABLE gis_multi_polygon  (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOLYGON);
CREATE TABLE gis_geometrycollection  (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRYCOLLECTION);
CREATE TABLE gis_geometry (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRY);
SHOW CREATE TABLE gis_point;
Table	Create Table
gis_point	CREATE TABLE `gis_point` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `g` point DEFAULT NULL,
  PRIMARY KEY (`fid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW FIELDS FROM gis_point;
Field	Type	Null	Key	Default	Extra
fid	int(11)	NO	PRI	NULL	auto_increment
g	point	YES		NULL	
SHOW FIELDS FROM gis_line;
Field	Type	Null	Key	Default	Extra
fid	int(11)	NO	PRI	NULL	auto_increment
g	linestring	YES		NULL	
SHOW FIELDS FROM gis_polygon;
Field	Type	Null	Key	Default	Extra
fid	int(11)	NO	PRI	NULL	auto_increment
g	polygon	YES		NULL	
SHOW FIELDS FROM gis_multi_point;
Field	Type	Null	Key	Default	Extra
fid	int(11)	NO	PRI	NULL	auto_increment
g	multipoint	YES		NULL	
SHOW FIELDS FROM gis_multi_line;
Field	Type	Null	Key	Default	Extra
fid	int(11)	NO	PRI	NULL	auto_increment
g	multilinestring	YES		NULL	
SHOW FIELDS FROM gis_multi_polygon;
Field	Type	Null	Key	Default	Extra
fid	int(11)	NO	PRI	NULL	auto_increment
g	multipolygon	YES		NULL	
SHOW FIELDS FROM gis_geometrycollection;
Field	Type	Null	Key	Default	Extra
fid	int(11)	NO	PRI	NULL	auto_increment
g	geometrycollection	YES		NULL	
SHOW FIELDS FROM gis_geometry;
Field	Type	Null	Key	Default	Extra
fid	int(11)	NO	PRI	NULL	auto_increment
g	geometry	YES		NULL	
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(LineString(Point(10, 10), Point(40, 10))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
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(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
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(MultiPoint(Point(3, 6), Point(4, 10))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
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(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
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(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
INSERT INTO gis_geometrycollection VALUES
(120, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
(121, ST_GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))));
Warnings:
Warning	3195	st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead.
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, ST_AsText(g) FROM gis_point ORDER by fid;
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 ORDER by fid;
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 ORDER by fid;
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 ORDER by fid;
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 ORDER by fid;
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 ORDER by fid;
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 ORDER by fid;
fid	ST_AsText(g)
120	GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
121	GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
SELECT fid, ST_AsText(g) FROM gis_geometry ORDER by fid;
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(44 6),LINESTRING(3 6,7 9))
SELECT fid, ST_Dimension(g) FROM gis_geometry ORDER by fid;
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
SELECT fid, ST_GeometryType(g) FROM gis_geometry ORDER by fid;
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
SELECT fid, ST_IsEmpty(g) FROM gis_geometry ORDER by fid;
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
SELECT fid, ST_AsText(ST_Envelope(g)) FROM gis_geometry ORDER by fid;
fid	ST_AsText(ST_Envelope(g))
101	POINT(10 10)
102	POINT(20 10)
103	POINT(20 20)
104	POINT(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	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))
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	LINESTRING(10 0,10 48)
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((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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gis_geometry	NULL	ALL	NULL	NULL	NULL	NULL	21	100.00	NULL
Warnings:
Warning	1681	'EXTENDED' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ 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`
SELECT fid, ST_X(g) FROM gis_point ORDER by fid;
fid	ST_X(g)
101	10
102	20
103	20
104	10
SELECT fid, ST_Y(g) FROM gis_point ORDER by fid;
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gis_point	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
Warnings:
Warning	1681	'EXTENDED' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ 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 ORDER by fid;
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 ORDER by fid;
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 ORDER by fid;
fid	ST_Length(g)
105	24.14213562373095
106	40
107	30
SELECT fid, ST_NumPoints(g) FROM gis_line ORDER by fid;
fid	ST_NumPoints(g)
105	3
106	5
107	2
SELECT fid, ST_AsText(ST_PointN(g, 2)) FROM gis_line ORDER by fid;
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 ORDER by fid;
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gis_line	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
Warnings:
Warning	1681	'EXTENDED' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ 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 ORDER by fid;
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 ORDER by fid;
fid	ST_Area(g)
108	100
109	2400
110	450
SELECT fid, ST_AsText(ST_ExteriorRing(g)) FROM gis_polygon ORDER by fid;
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 ORDER by fid;
fid	ST_NumInteriorRings(g)
108	0
109	1
110	0
SELECT fid, ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid;
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gis_polygon	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
Warnings:
Warning	1681	'EXTENDED' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ 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 ORDER by fid;
fid	ST_IsClosed(g)
114	0
115	0
116	0
SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_multi_polygon ORDER by fid;
fid	ST_AsText(ST_Centroid(g))
117	POINT(57.98031067576927 17.854754130800437)
118	POINT(57.98031067576927 17.854754130800437)
119	POINT(2 2)
SELECT fid, ST_Area(g) FROM gis_multi_polygon ORDER by fid;
fid	ST_Area(g)
117	1684.5
118	1684.5
119	4.5
SELECT fid, ST_NumGeometries(g) from gis_multi_point ORDER by fid;
fid	ST_NumGeometries(g)
111	4
112	4
113	2
SELECT fid, ST_NumGeometries(g) from gis_multi_line ORDER by fid;
fid	ST_NumGeometries(g)
114	2
115	1
116	2
SELECT fid, ST_NumGeometries(g) from gis_multi_polygon ORDER by fid;
fid	ST_NumGeometries(g)
117	2
118	2
119	1
SELECT fid, ST_NumGeometries(g) from gis_geometrycollection ORDER by fid;
fid	ST_NumGeometries(g)
120	2
121	2
explain extended SELECT fid, ST_NumGeometries(g) from gis_multi_point;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gis_multi_point	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
Warnings:
Warning	1681	'EXTENDED' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ 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 ORDER by fid;
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 ORDER by fid;
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 ORDER by fid;
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 ORDER by fid;
fid	ST_AsText(ST_GeometryN(g, 2))
120	LINESTRING(0 0,10 10)
121	LINESTRING(3 6,7 9)
SELECT fid, ST_AsText(ST_GeometryN(g, 1)) from gis_geometrycollection ORDER by fid;
fid	ST_AsText(ST_GeometryN(g, 1))
120	POINT(0 0)
121	POINT(44 6)
explain extended SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gis_multi_point	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
Warnings:
Warning	1681	'EXTENDED' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ 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	1	0	0	0	1	0
121	120	0	0	1	0	0	0	1	0
121	121	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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	g1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
1	SIMPLE	g2	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (Block Nested Loop)
Warnings:
Warning	1681	'EXTENDED' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ 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 `first`,`second`
DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
CREATE TABLE t1 (
a INTEGER PRIMARY KEY AUTO_INCREMENT,
gp  point,
ln  linestring,
pg  polygon,
mp  multipoint,
mln multilinestring,
mpg multipolygon,
gc  geometrycollection,
gm  geometry
);
SHOW FIELDS FROM t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	NO	PRI	NULL	auto_increment
gp	point	YES		NULL	
ln	linestring	YES		NULL	
pg	polygon	YES		NULL	
mp	multipoint	YES		NULL	
mln	multilinestring	YES		NULL	
mpg	multipolygon	YES		NULL	
gc	geometrycollection	YES		NULL	
gm	geometry	YES		NULL	
ALTER TABLE t1 ADD fid INT;
SHOW FIELDS FROM t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	NO	PRI	NULL	auto_increment
gp	point	YES		NULL	
ln	linestring	YES		NULL	
pg	polygon	YES		NULL	
mp	multipoint	YES		NULL	
mln	multilinestring	YES		NULL	
mpg	multipolygon	YES		NULL	
gc	geometrycollection	YES		NULL	
gm	geometry	YES		NULL	
fid	int(11)	YES		NULL	
DROP TABLE t1;
create table t1 (pk integer primary key auto_increment, a geometry not null);
insert into t1 (a) values (ST_GeomFromText('Point(1 2)'));
insert into t1 (a) values ('Garbage');
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
insert IGNORE into t1 (a) values ('Garbage');
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
drop table t1;
create table t1 (pk integer primary key auto_increment, fl geometry not null);
insert into t1 (fl) values (1);
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
insert into t1 (fl) values (1.11);
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
insert into t1 (fl) values ("qwerty");
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
insert into t1 (fl) values (ST_pointfromtext('point(1,1)'));
ERROR 22023: Invalid GIS data provided to function st_geometryfromtext.
drop table t1;
End of 4.1 tests
CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY);
INSERT INTO t1 VALUES("center", ST_GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));
INSERT INTO t1 VALUES("small",  ST_GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
INSERT INTO t1 VALUES("big",    ST_GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
INSERT INTO t1 VALUES("up",     ST_GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));
INSERT INTO t1 VALUES("up2",    ST_GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));
INSERT INTO t1 VALUES("up3",    ST_GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));
INSERT INTO t1 VALUES("down",   ST_GeomFromText('POLYGON (( 0 -1, 0  1, 2  1, 2 -1, 0 -1))'));
INSERT INTO t1 VALUES("down2",  ST_GeomFromText('POLYGON (( 0 -2, 0  0, 2  0, 2 -2, 0 -2))'));
INSERT INTO t1 VALUES("down3",  ST_GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
INSERT INTO t1 VALUES("right",  ST_GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
INSERT INTO t1 VALUES("right2", ST_GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
INSERT INTO t1 VALUES("right3", ST_GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
INSERT INTO t1 VALUES("left",   ST_GeomFromText('POLYGON (( -1 0, -1 2,  1 2,  1 0, -1 0))'));
INSERT INTO t1 VALUES("left2",  ST_GeomFromText('POLYGON (( -2 0, -2 2,  0 2,  0 0, -2 0))'));
INSERT INTO t1 VALUES("left3",  ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))'));
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;
mbrcontains
center,small
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;
mbrdisjoint
down3,left3,right3,up3
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrequals     FROM t1 a1 JOIN t1 a2 ON MBREquals(      a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
mbrequals
center
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;
mbrintersect
big,center,down,down2,left,left2,right,right2,small,up,up2
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;
mbroverlaps
down,left,right,up
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;
mbrtouches
down2,left2,right2,up2
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;
mbrwithin
big,center
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;
MBRcontains
center,small
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;
MBRdisjoint
down3,left3,right3,up3
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRequals       FROM t1 a1 JOIN t1 a2 ON MBREquals(        a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
MBRequals
center
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS intersect    FROM t1 a1 JOIN t1 a2 ON MBRIntersects(    a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
intersect
big,center,down,down2,left,left2,right,right2,small,up,up2
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;
MBRoverlaps
down,left,right,up
SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS ST_touches      FROM t1 a1 JOIN t1 a2 ON ST_Touches(       a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
ST_touches
down2,left2,right2,up2
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;
MBRwithin
big,center
SET @vert1   = ST_GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');
ERROR 22023: Invalid GIS data provided to function st_geometryfromtext.
SET @horiz1  = ST_GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');
ERROR 22023: Invalid GIS data provided to function st_geometryfromtext.
SET @horiz2 = ST_GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');
ERROR 22023: Invalid GIS data provided to function st_geometryfromtext.
SET @horiz3 = ST_GeomFromText('POLYGON ((2 0, 3 0, 2 0))');
ERROR 22023: Invalid GIS data provided to function st_geometryfromtext.
SET @point1 = ST_GeomFromText('POLYGON ((0 0))');
ERROR 22023: Invalid GIS data provided to function st_geometryfromtext.
SET @point2 = ST_GeomFromText('POLYGON ((-2 0))');
ERROR 22023: Invalid GIS data provided to function st_geometryfromtext.
SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @vert1) GROUP BY a1.name;
MBRoverlaps
SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @horiz1) GROUP BY a1.name;
MBRoverlaps
SELECT MBROverlaps(@horiz1, @vert1) FROM DUAL;
MBROverlaps(@horiz1, @vert1)
NULL
SELECT MBROverlaps(@horiz1, @horiz2) FROM DUAL;
MBROverlaps(@horiz1, @horiz2)
NULL
SELECT MBROverlaps(@horiz1, @horiz3) FROM DUAL;
MBROverlaps(@horiz1, @horiz3)
NULL
SELECT MBROverlaps(@horiz1, @point1) FROM DUAL;
MBROverlaps(@horiz1, @point1)
NULL
SELECT MBROverlaps(@horiz1, @point2) FROM DUAL;
MBROverlaps(@horiz1, @point2)
NULL
DROP TABLE t1;
End of 5.0 tests
CREATE TABLE t1 (p POINT);
CREATE TABLE t2 (p POINT, INDEX(p));
INSERT INTO t1 VALUES (ST_POINTFROMTEXT('POINT(1 2)'));
INSERT INTO t2 VALUES (ST_POINTFROMTEXT('POINT(1 2)'));
SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
COUNT(*)
1
EXPLAIN 
SELECT COUNT(*) FROM t2 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ref	p	p	28	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t2` where (`test`.`t2`.`p` = <cache>(st_geometryfromtext('POINT(1 2)')))
SELECT COUNT(*) FROM t2 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
COUNT(*)
1
INSERT INTO t1 VALUES (ST_POINTFROMTEXT('POINT(1 2)'));
INSERT INTO t2 VALUES (ST_POINTFROMTEXT('POINT(1 2)'));
EXPLAIN 
SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`p` = <cache>(st_geometryfromtext('POINT(1 2)')))
SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
COUNT(*)
2
EXPLAIN 
SELECT COUNT(*) FROM t2 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ref	p	p	28	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t2` where (`test`.`t2`.`p` = <cache>(st_geometryfromtext('POINT(1 2)')))
SELECT COUNT(*) FROM t2 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
COUNT(*)
2
EXPLAIN 
SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t2` IGNORE INDEX (`p`) where (`test`.`t2`.`p` = <cache>(st_geometryfromtext('POINT(1 2)')))
SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
COUNT(*)
2
DROP TABLE t1, t2;
End of 5.0 tests
#
# Test for bug #58650 "Failing assertion: primary_key_no == -1 ||
#                      primary_key_no == 0".
#
drop table if exists t1;
# The minimal test case.
create table t1 (a int not null, b linestring not null, unique key b (b(12)), unique key a (a));
drop table t1;
# The original test case.
create table t1 (a int not null, b linestring not null, unique key b (b(12)));
create unique index a on t1(a);
drop table t1;
create table t1 (g geometry not null, spatial gk(g)) engine=innodb;
DROP TABLE t1;
create table t1 (c1 int, c2 geometry not null, spatial index (c2))engine=innodb;
start transaction;
insert into t1 values(1, Point(1,1));
set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))');
set transaction isolation level read uncommitted;
select count(*) from t1 where ST_Within(t1.c2, @g1);
count(*)
1
commit;
drop table t1;