diff options
Diffstat (limited to 'mysql-test/r/ndb_gis.result')
-rw-r--r-- | mysql-test/r/ndb_gis.result | 1100 |
1 files changed, 0 insertions, 1100 deletions
diff --git a/mysql-test/r/ndb_gis.result b/mysql-test/r/ndb_gis.result deleted file mode 100644 index c01c1673e44..00000000000 --- a/mysql-test/r/ndb_gis.result +++ /dev/null @@ -1,1100 +0,0 @@ -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; Using join cache -1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 -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; Using join cache -1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 -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 |