summaryrefslogtreecommitdiff
path: root/mysql-test/suite/ndb/r/ndb_gis.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/ndb/r/ndb_gis.result')
-rw-r--r--mysql-test/suite/ndb/r/ndb_gis.result1100
1 files changed, 1100 insertions, 0 deletions
diff --git a/mysql-test/suite/ndb/r/ndb_gis.result b/mysql-test/suite/ndb/r/ndb_gis.result
new file mode 100644
index 00000000000..3af7b71d72d
--- /dev/null
+++ b/mysql-test/suite/ndb/r/ndb_gis.result
@@ -0,0 +1,1100 @@
+SET storage_engine=ndbcluster;
+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=ndbcluster 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, PointFromText('POINT(10 10)')),
+(102, PointFromText('POINT(20 10)')),
+(103, PointFromText('POINT(20 20)')),
+(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
+INSERT INTO gis_line VALUES
+(105, LineFromText('LINESTRING(0 0,0 10,10 0)')),
+(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
+(107, LineStringFromWKB(LineString(Point(10, 10), Point(40, 10))));
+INSERT INTO gis_polygon VALUES
+(108, PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
+(109, PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
+(110, PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))));
+INSERT INTO gis_multi_point VALUES
+(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
+(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
+(113, MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10))));
+INSERT INTO gis_multi_line VALUES
+(114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
+(115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
+(116, MLineFromWKB(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, 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, 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, MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))));
+INSERT INTO gis_geometrycollection VALUES
+(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
+(121, GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))));
+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, AsText(g) FROM gis_point ORDER by fid;
+fid AsText(g)
+101 POINT(10 10)
+102 POINT(20 10)
+103 POINT(20 20)
+104 POINT(10 20)
+SELECT fid, AsText(g) FROM gis_line ORDER by fid;
+fid 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, AsText(g) FROM gis_polygon ORDER by fid;
+fid 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, AsText(g) FROM gis_multi_point ORDER by fid;
+fid 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, AsText(g) FROM gis_multi_line ORDER by fid;
+fid 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, AsText(g) FROM gis_multi_polygon ORDER by fid;
+fid 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, AsText(g) FROM gis_geometrycollection ORDER by fid;
+fid AsText(g)
+120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
+121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
+SELECT fid, AsText(g) FROM gis_geometry ORDER by fid;
+fid 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, Dimension(g) FROM gis_geometry ORDER by fid;
+fid 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, GeometryType(g) FROM gis_geometry ORDER by fid;
+fid 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, IsEmpty(g) FROM gis_geometry ORDER by fid;
+fid 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, AsText(Envelope(g)) FROM gis_geometry ORDER by fid;
+fid AsText(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((3 6,44 6,44 9,3 9,3 6))
+explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(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 21 100.00
+Warnings:
+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`
+SELECT fid, X(g) FROM gis_point ORDER by fid;
+fid X(g)
+101 10
+102 20
+103 20
+104 10
+SELECT fid, Y(g) FROM gis_point ORDER by fid;
+fid Y(g)
+101 10
+102 10
+103 20
+104 20
+explain extended select X(g),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 4 100.00
+Warnings:
+Note 1003 select x(`test`.`gis_point`.`g`) AS `X(g)`,y(`test`.`gis_point`.`g`) AS `Y(g)` from `test`.`gis_point`
+SELECT fid, AsText(StartPoint(g)) FROM gis_line ORDER by fid;
+fid AsText(StartPoint(g))
+105 POINT(0 0)
+106 POINT(10 10)
+107 POINT(10 10)
+SELECT fid, AsText(EndPoint(g)) FROM gis_line ORDER by fid;
+fid AsText(EndPoint(g))
+105 POINT(10 0)
+106 POINT(10 10)
+107 POINT(40 10)
+SELECT fid, GLength(g) FROM gis_line ORDER by fid;
+fid GLength(g)
+105 24.142135623731
+106 40
+107 30
+SELECT fid, NumPoints(g) FROM gis_line ORDER by fid;
+fid NumPoints(g)
+105 3
+106 5
+107 2
+SELECT fid, AsText(PointN(g, 2)) FROM gis_line ORDER by fid;
+fid AsText(PointN(g, 2))
+105 POINT(0 10)
+106 POINT(20 10)
+107 POINT(40 10)
+SELECT fid, IsClosed(g) FROM gis_line ORDER by fid;
+fid IsClosed(g)
+105 0
+106 1
+107 0
+explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),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 3 100.00
+Warnings:
+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`
+SELECT fid, AsText(Centroid(g)) FROM gis_polygon ORDER by fid;
+fid AsText(Centroid(g))
+108 POINT(15 15)
+109 POINT(25.416666666667 25.416666666667)
+110 POINT(20 10)
+SELECT fid, Area(g) FROM gis_polygon ORDER by fid;
+fid Area(g)
+108 100
+109 2400
+110 450
+SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon ORDER by fid;
+fid AsText(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, NumInteriorRings(g) FROM gis_polygon ORDER by fid;
+fid NumInteriorRings(g)
+108 0
+109 1
+110 0
+SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid;
+fid AsText(InteriorRingN(g, 1))
+108 NULL
+109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+110 NULL
+explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(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 3 100.00
+Warnings:
+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`
+SELECT fid, IsClosed(g) FROM gis_multi_line ORDER by fid;
+fid IsClosed(g)
+114 0
+115 0
+116 0
+SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon ORDER by fid;
+fid AsText(Centroid(g))
+117 POINT(55.588527753042 17.426536064114)
+118 POINT(55.588527753042 17.426536064114)
+119 POINT(2 2)
+SELECT fid, Area(g) FROM gis_multi_polygon ORDER by fid;
+fid Area(g)
+117 1684.5
+118 1684.5
+119 4.5
+SELECT fid, NumGeometries(g) from gis_multi_point ORDER by fid;
+fid NumGeometries(g)
+111 4
+112 4
+113 2
+SELECT fid, NumGeometries(g) from gis_multi_line ORDER by fid;
+fid NumGeometries(g)
+114 2
+115 1
+116 2
+SELECT fid, NumGeometries(g) from gis_multi_polygon ORDER by fid;
+fid NumGeometries(g)
+117 2
+118 2
+119 1
+SELECT fid, NumGeometries(g) from gis_geometrycollection ORDER by fid;
+fid NumGeometries(g)
+120 2
+121 2
+explain extended SELECT fid, 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 3 100.00
+Warnings:
+Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from `test`.`gis_multi_point`
+SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point ORDER by fid;
+fid AsText(GeometryN(g, 2))
+111 POINT(10 10)
+112 POINT(11 11)
+113 POINT(4 10)
+SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line ORDER by fid;
+fid AsText(GeometryN(g, 2))
+114 LINESTRING(16 0,16 23,16 48)
+115 NULL
+116 LINESTRING(2 5,5 8,21 7)
+SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon ORDER by fid;
+fid AsText(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, AsText(GeometryN(g, 2)) from gis_geometrycollection ORDER by fid;
+fid AsText(GeometryN(g, 2))
+120 LINESTRING(0 0,10 10)
+121 LINESTRING(3 6,7 9)
+SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection ORDER by fid;
+fid AsText(GeometryN(g, 1))
+120 POINT(0 0)
+121 POINT(44 6)
+explain extended SELECT fid, AsText(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 3 100.00
+Warnings:
+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`
+SELECT g1.fid as first, g2.fid as second,
+Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
+Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
+Intersects(g1.g, g2.g) as i, 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,
+Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
+Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
+Intersects(g1.g, g2.g) as i, 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 2 100.00 Using temporary; Using filesort
+1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer
+Warnings:
+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`
+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 (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 (pointfromtext('point(1,1)'));
+ERROR 23000: Column 'fl' cannot be null
+drop table t1;
+End of 4.1 tests
+CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY);
+Warnings:
+Error 1466 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK'
+INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));
+INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
+INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
+INSERT INTO t1 VALUES("up", GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));
+INSERT INTO t1 VALUES("up2", GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));
+INSERT INTO t1 VALUES("up3", GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));
+INSERT INTO t1 VALUES("down", GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));
+INSERT INTO t1 VALUES("down2", GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));
+INSERT INTO t1 VALUES("down3", GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
+INSERT INTO t1 VALUES("right", GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
+INSERT INTO t1 VALUES("right2", GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
+INSERT INTO t1 VALUES("right3", GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
+INSERT INTO t1 VALUES("left", GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));
+INSERT INTO t1 VALUES("left2", GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));
+INSERT INTO t1 VALUES("left3", 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 mbrequal FROM t1 a1 JOIN t1 a2 ON MBREqual( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
+mbrequal
+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 contains FROM t1 a1 JOIN t1 a2 ON Contains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
+contains
+center,small
+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;
+disjoint
+down3,left3,right3,up3
+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;
+equals
+center
+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;
+intersect
+big,center,down,down2,left,left2,right,right2,small,up,up2
+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;
+overlaps
+down,left,right,up
+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;
+touches
+down2,left2,right2,up2
+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;
+within
+big,center
+SET @vert1 = GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');
+SET @horiz1 = GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');
+SET @horiz2 = GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');
+SET @horiz3 = GeomFromText('POLYGON ((2 0, 3 0, 2 0))');
+SET @point1 = GeomFromText('POLYGON ((0 0))');
+SET @point2 = GeomFromText('POLYGON ((-2 0))');
+SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @vert1) GROUP BY a1.name;
+overlaps
+SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @horiz1) GROUP BY a1.name;
+overlaps
+SELECT Overlaps(@horiz1, @vert1) FROM DUAL;
+Overlaps(@horiz1, @vert1)
+0
+SELECT Overlaps(@horiz1, @horiz2) FROM DUAL;
+Overlaps(@horiz1, @horiz2)
+1
+SELECT Overlaps(@horiz1, @horiz3) FROM DUAL;
+Overlaps(@horiz1, @horiz3)
+0
+SELECT Overlaps(@horiz1, @point1) FROM DUAL;
+Overlaps(@horiz1, @point1)
+0
+SELECT Overlaps(@horiz1, @point2) FROM DUAL;
+Overlaps(@horiz1, @point2)
+0
+DROP TABLE t1;
+End of 5.0 tests
+set engine_condition_pushdown = on;
+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=ndbcluster 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, PointFromText('POINT(10 10)')),
+(102, PointFromText('POINT(20 10)')),
+(103, PointFromText('POINT(20 20)')),
+(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
+INSERT INTO gis_line VALUES
+(105, LineFromText('LINESTRING(0 0,0 10,10 0)')),
+(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
+(107, LineStringFromWKB(LineString(Point(10, 10), Point(40, 10))));
+INSERT INTO gis_polygon VALUES
+(108, PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
+(109, PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
+(110, PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))));
+INSERT INTO gis_multi_point VALUES
+(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
+(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
+(113, MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10))));
+INSERT INTO gis_multi_line VALUES
+(114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
+(115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
+(116, MLineFromWKB(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, 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, 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, MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))));
+INSERT INTO gis_geometrycollection VALUES
+(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
+(121, GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))));
+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, AsText(g) FROM gis_point ORDER by fid;
+fid AsText(g)
+101 POINT(10 10)
+102 POINT(20 10)
+103 POINT(20 20)
+104 POINT(10 20)
+SELECT fid, AsText(g) FROM gis_line ORDER by fid;
+fid 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, AsText(g) FROM gis_polygon ORDER by fid;
+fid 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, AsText(g) FROM gis_multi_point ORDER by fid;
+fid 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, AsText(g) FROM gis_multi_line ORDER by fid;
+fid 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, AsText(g) FROM gis_multi_polygon ORDER by fid;
+fid 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, AsText(g) FROM gis_geometrycollection ORDER by fid;
+fid AsText(g)
+120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
+121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
+SELECT fid, AsText(g) FROM gis_geometry ORDER by fid;
+fid 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, Dimension(g) FROM gis_geometry ORDER by fid;
+fid 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, GeometryType(g) FROM gis_geometry ORDER by fid;
+fid 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, IsEmpty(g) FROM gis_geometry ORDER by fid;
+fid 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, AsText(Envelope(g)) FROM gis_geometry ORDER by fid;
+fid AsText(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((3 6,44 6,44 9,3 9,3 6))
+explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(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 21 100.00
+Warnings:
+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`
+SELECT fid, X(g) FROM gis_point ORDER by fid;
+fid X(g)
+101 10
+102 20
+103 20
+104 10
+SELECT fid, Y(g) FROM gis_point ORDER by fid;
+fid Y(g)
+101 10
+102 10
+103 20
+104 20
+explain extended select X(g),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 4 100.00
+Warnings:
+Note 1003 select x(`test`.`gis_point`.`g`) AS `X(g)`,y(`test`.`gis_point`.`g`) AS `Y(g)` from `test`.`gis_point`
+SELECT fid, AsText(StartPoint(g)) FROM gis_line ORDER by fid;
+fid AsText(StartPoint(g))
+105 POINT(0 0)
+106 POINT(10 10)
+107 POINT(10 10)
+SELECT fid, AsText(EndPoint(g)) FROM gis_line ORDER by fid;
+fid AsText(EndPoint(g))
+105 POINT(10 0)
+106 POINT(10 10)
+107 POINT(40 10)
+SELECT fid, GLength(g) FROM gis_line ORDER by fid;
+fid GLength(g)
+105 24.142135623731
+106 40
+107 30
+SELECT fid, NumPoints(g) FROM gis_line ORDER by fid;
+fid NumPoints(g)
+105 3
+106 5
+107 2
+SELECT fid, AsText(PointN(g, 2)) FROM gis_line ORDER by fid;
+fid AsText(PointN(g, 2))
+105 POINT(0 10)
+106 POINT(20 10)
+107 POINT(40 10)
+SELECT fid, IsClosed(g) FROM gis_line ORDER by fid;
+fid IsClosed(g)
+105 0
+106 1
+107 0
+explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),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 3 100.00
+Warnings:
+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`
+SELECT fid, AsText(Centroid(g)) FROM gis_polygon ORDER by fid;
+fid AsText(Centroid(g))
+108 POINT(15 15)
+109 POINT(25.416666666667 25.416666666667)
+110 POINT(20 10)
+SELECT fid, Area(g) FROM gis_polygon ORDER by fid;
+fid Area(g)
+108 100
+109 2400
+110 450
+SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon ORDER by fid;
+fid AsText(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, NumInteriorRings(g) FROM gis_polygon ORDER by fid;
+fid NumInteriorRings(g)
+108 0
+109 1
+110 0
+SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid;
+fid AsText(InteriorRingN(g, 1))
+108 NULL
+109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+110 NULL
+explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(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 3 100.00
+Warnings:
+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`
+SELECT fid, IsClosed(g) FROM gis_multi_line ORDER by fid;
+fid IsClosed(g)
+114 0
+115 0
+116 0
+SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon ORDER by fid;
+fid AsText(Centroid(g))
+117 POINT(55.588527753042 17.426536064114)
+118 POINT(55.588527753042 17.426536064114)
+119 POINT(2 2)
+SELECT fid, Area(g) FROM gis_multi_polygon ORDER by fid;
+fid Area(g)
+117 1684.5
+118 1684.5
+119 4.5
+SELECT fid, NumGeometries(g) from gis_multi_point ORDER by fid;
+fid NumGeometries(g)
+111 4
+112 4
+113 2
+SELECT fid, NumGeometries(g) from gis_multi_line ORDER by fid;
+fid NumGeometries(g)
+114 2
+115 1
+116 2
+SELECT fid, NumGeometries(g) from gis_multi_polygon ORDER by fid;
+fid NumGeometries(g)
+117 2
+118 2
+119 1
+SELECT fid, NumGeometries(g) from gis_geometrycollection ORDER by fid;
+fid NumGeometries(g)
+120 2
+121 2
+explain extended SELECT fid, 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 3 100.00
+Warnings:
+Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from `test`.`gis_multi_point`
+SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point ORDER by fid;
+fid AsText(GeometryN(g, 2))
+111 POINT(10 10)
+112 POINT(11 11)
+113 POINT(4 10)
+SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line ORDER by fid;
+fid AsText(GeometryN(g, 2))
+114 LINESTRING(16 0,16 23,16 48)
+115 NULL
+116 LINESTRING(2 5,5 8,21 7)
+SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon ORDER by fid;
+fid AsText(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, AsText(GeometryN(g, 2)) from gis_geometrycollection ORDER by fid;
+fid AsText(GeometryN(g, 2))
+120 LINESTRING(0 0,10 10)
+121 LINESTRING(3 6,7 9)
+SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection ORDER by fid;
+fid AsText(GeometryN(g, 1))
+120 POINT(0 0)
+121 POINT(44 6)
+explain extended SELECT fid, AsText(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 3 100.00
+Warnings:
+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`
+SELECT g1.fid as first, g2.fid as second,
+Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
+Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
+Intersects(g1.g, g2.g) as i, 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,
+Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
+Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
+Intersects(g1.g, g2.g) as i, 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 2 100.00 Using temporary; Using filesort
+1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer
+Warnings:
+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`
+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 (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 (pointfromtext('point(1,1)'));
+ERROR 23000: Column 'fl' cannot be null
+drop table t1;
+End of 4.1 tests
+CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY);
+Warnings:
+Error 1466 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK'
+INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));
+INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
+INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
+INSERT INTO t1 VALUES("up", GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));
+INSERT INTO t1 VALUES("up2", GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));
+INSERT INTO t1 VALUES("up3", GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));
+INSERT INTO t1 VALUES("down", GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));
+INSERT INTO t1 VALUES("down2", GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));
+INSERT INTO t1 VALUES("down3", GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
+INSERT INTO t1 VALUES("right", GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
+INSERT INTO t1 VALUES("right2", GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
+INSERT INTO t1 VALUES("right3", GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
+INSERT INTO t1 VALUES("left", GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));
+INSERT INTO t1 VALUES("left2", GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));
+INSERT INTO t1 VALUES("left3", 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 mbrequal FROM t1 a1 JOIN t1 a2 ON MBREqual( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
+mbrequal
+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 contains FROM t1 a1 JOIN t1 a2 ON Contains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
+contains
+center,small
+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;
+disjoint
+down3,left3,right3,up3
+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;
+equals
+center
+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;
+intersect
+big,center,down,down2,left,left2,right,right2,small,up,up2
+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;
+overlaps
+down,left,right,up
+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;
+touches
+down2,left2,right2,up2
+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;
+within
+big,center
+SET @vert1 = GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');
+SET @horiz1 = GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');
+SET @horiz2 = GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');
+SET @horiz3 = GeomFromText('POLYGON ((2 0, 3 0, 2 0))');
+SET @point1 = GeomFromText('POLYGON ((0 0))');
+SET @point2 = GeomFromText('POLYGON ((-2 0))');
+SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @vert1) GROUP BY a1.name;
+overlaps
+SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @horiz1) GROUP BY a1.name;
+overlaps
+SELECT Overlaps(@horiz1, @vert1) FROM DUAL;
+Overlaps(@horiz1, @vert1)
+0
+SELECT Overlaps(@horiz1, @horiz2) FROM DUAL;
+Overlaps(@horiz1, @horiz2)
+1
+SELECT Overlaps(@horiz1, @horiz3) FROM DUAL;
+Overlaps(@horiz1, @horiz3)
+0
+SELECT Overlaps(@horiz1, @point1) FROM DUAL;
+Overlaps(@horiz1, @point1)
+0
+SELECT Overlaps(@horiz1, @point2) FROM DUAL;
+Overlaps(@horiz1, @point2)
+0
+DROP TABLE t1;
+End of 5.0 tests
View Lorry Controller Status