Optimized GIS functions

1 files changed, 96 insertions, 95 deletions

diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test
index c8f50e968ab..35f2cff8f9a 100644
--- a/mysql-test/t/gis.test
+++ b/mysql-test/t/gis.test
@@ -3,154 +3,155 @@
 #
 
 --disable_warnings
-DROP TABLE IF EXISTS pt, ls, p, mpt, mls, mp, gc, geo;
+DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
 --enable_warnings
 
-CREATE TABLE pt  (fid INTEGER NOT NULL PRIMARY KEY, g POINT);
-CREATE TABLE ls  (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING);
-CREATE TABLE p   (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON);
-CREATE TABLE mpt (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT);
-CREATE TABLE mls (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING);
-CREATE TABLE mp  (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON);
-CREATE TABLE gc  (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION);
-CREATE TABLE geo (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
-
-SHOW FIELDS FROM pt;
-SHOW FIELDS FROM ls;
-SHOW FIELDS FROM p;
-SHOW FIELDS FROM mpt;
-SHOW FIELDS FROM mls;
-SHOW FIELDS FROM mp;
-SHOW FIELDS FROM gc;
-SHOW FIELDS FROM geo;
-
-
-INSERT INTO pt VALUES 
+CREATE TABLE gis_point  (fid INTEGER NOT NULL PRIMARY KEY, g POINT);
+CREATE TABLE gis_line  (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING);
+CREATE TABLE gis_polygon   (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON);
+CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT);
+CREATE TABLE gis_multi_line (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING);
+CREATE TABLE gis_multi_polygon  (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON);
+CREATE TABLE gis_geometrycollection  (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION);
+CREATE TABLE gis_geometry (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
+
+SHOW FIELDS FROM gis_point;
+SHOW FIELDS FROM gis_line;
+SHOW FIELDS FROM gis_polygon;
+SHOW FIELDS FROM gis_multi_point;
+SHOW FIELDS FROM gis_multi_line;
+SHOW FIELDS FROM gis_multi_polygon;
+SHOW FIELDS FROM gis_geometrycollection;
+SHOW FIELDS FROM gis_geometry;
+
+
+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 ls VALUES
+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 p VALUES
+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 mpt VALUES
+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 mls VALUES
+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 mp VALUES
+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 gc VALUES
+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 geo SELECT * FROM pt;
-INSERT into geo SELECT * FROM ls;
-INSERT into geo SELECT * FROM p;
-INSERT into geo SELECT * FROM mpt;
-INSERT into geo SELECT * FROM mls;
-INSERT into geo SELECT * FROM mp;
-INSERT into geo SELECT * FROM gc;
-
-SELECT fid, AsText(g) FROM pt;
-SELECT fid, AsText(g) FROM ls;
-SELECT fid, AsText(g) FROM p;
-SELECT fid, AsText(g) FROM mpt;
-SELECT fid, AsText(g) FROM mls;
-SELECT fid, AsText(g) FROM mp;
-SELECT fid, AsText(g) FROM gc;
-SELECT fid, AsText(g) FROM geo;
-
-SELECT fid, Dimension(g) FROM geo;
-SELECT fid, GeometryType(g) FROM geo;
-SELECT fid, IsEmpty(g) FROM geo;
-SELECT fid, AsText(Envelope(g)) FROM geo;
-explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from geo;
-
-SELECT fid, X(g) FROM pt;
-SELECT fid, Y(g) FROM pt;
-explain extended select X(g),Y(g) FROM pt;
-
-SELECT fid, AsText(StartPoint(g)) FROM ls;
-SELECT fid, AsText(EndPoint(g)) FROM ls;
-SELECT fid, GLength(g) FROM ls;
-SELECT fid, NumPoints(g) FROM ls;
-SELECT fid, AsText(PointN(g, 2)) FROM ls;
-SELECT fid, IsClosed(g) FROM ls;
-explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM ls;
-
-SELECT fid, AsText(Centroid(g)) FROM p;
-SELECT fid, Area(g) FROM p;
-SELECT fid, AsText(ExteriorRing(g)) FROM p;
-SELECT fid, NumInteriorRings(g) FROM p;
-SELECT fid, AsText(InteriorRingN(g, 1)) FROM p;
-explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM p;
-
-SELECT fid, IsClosed(g) FROM mls;
-
-SELECT fid, AsText(Centroid(g)) FROM mp;
-SELECT fid, Area(g) FROM mp;
-
-SELECT fid, NumGeometries(g) from mpt;
-SELECT fid, NumGeometries(g) from mls;
-SELECT fid, NumGeometries(g) from mp;
-SELECT fid, NumGeometries(g) from gc;
-explain extended SELECT fid, NumGeometries(g) from mpt;
-
-SELECT fid, AsText(GeometryN(g, 2)) from mpt;
-SELECT fid, AsText(GeometryN(g, 2)) from mls;
-SELECT fid, AsText(GeometryN(g, 2)) from mp;
-SELECT fid, AsText(GeometryN(g, 2)) from gc;
-explain extended SELECT fid, AsText(GeometryN(g, 2)) from mpt;
+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;
+SELECT fid, AsText(g) FROM gis_line;
+SELECT fid, AsText(g) FROM gis_polygon;
+SELECT fid, AsText(g) FROM gis_multi_point;
+SELECT fid, AsText(g) FROM gis_multi_line;
+SELECT fid, AsText(g) FROM gis_multi_polygon;
+SELECT fid, AsText(g) FROM gis_geometrycollection;
+SELECT fid, AsText(g) FROM gis_geometry;
+
+SELECT fid, Dimension(g) FROM gis_geometry;
+SELECT fid, GeometryType(g) FROM gis_geometry;
+SELECT fid, IsEmpty(g) FROM gis_geometry;
+SELECT fid, AsText(Envelope(g)) FROM gis_geometry;
+explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from gis_geometry;
+
+SELECT fid, X(g) FROM gis_point;
+SELECT fid, Y(g) FROM gis_point;
+explain extended select X(g),Y(g) FROM gis_point;
+
+SELECT fid, AsText(StartPoint(g)) FROM gis_line;
+SELECT fid, AsText(EndPoint(g)) FROM gis_line;
+SELECT fid, GLength(g) FROM gis_line;
+SELECT fid, NumPoints(g) FROM gis_line;
+SELECT fid, AsText(PointN(g, 2)) FROM gis_line;
+SELECT fid, IsClosed(g) FROM gis_line;
+explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM gis_line;
+
+SELECT fid, AsText(Centroid(g)) FROM gis_polygon;
+SELECT fid, Area(g) FROM gis_polygon;
+SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon;
+SELECT fid, NumInteriorRings(g) FROM gis_polygon;
+SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon;
+explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM gis_polygon;
+
+SELECT fid, IsClosed(g) FROM gis_multi_line;
+
+SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon;
+SELECT fid, Area(g) FROM gis_multi_polygon;
+
+SELECT fid, NumGeometries(g) from gis_multi_point;
+SELECT fid, NumGeometries(g) from gis_multi_line;
+SELECT fid, NumGeometries(g) from gis_multi_polygon;
+SELECT fid, NumGeometries(g) from gis_geometrycollection;
+explain extended SELECT fid, NumGeometries(g) from gis_multi_point;
+
+SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
+SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line;
+SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon;
+SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection;
+SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection;
+explain extended SELECT fid, AsText(GeometryN(g, 2)) from 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 gc g1, gc g2 ORDER BY first, second;
+FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
 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 gc g1, gc g2 ORDER BY first, second;
+FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
 
-DROP TABLE pt, ls, p, mpt, mls, mp, gc, geo;
+DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
 
 #
 # Check that ALTER TABLE doesn't loose geometry type
 #
-CREATE TABLE g1 (
-  pt  point,
+CREATE TABLE t1 (
+  gp  point,
   ln  linestring,
   pg  polygon,
-  mpt multipoint,
+  mp  multipoint,
   mln multilinestring,
   mpg multipolygon,
   gc  geometrycollection,
   gm  geometry
 );
 
-SHOW FIELDS FROM g1;
-ALTER TABLE g1 ADD fid INT NOT NULL;
-SHOW FIELDS FROM g1;
-DROP TABLE g1;
+SHOW FIELDS FROM t1;
+ALTER TABLE t1 ADD fid INT NOT NULL;
+SHOW FIELDS FROM t1;
+DROP TABLE t1;
 
 SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))));
 explain extended SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))));