null+****@clear*****
null+****@clear*****
2011年 11月 26日 (土) 17:21:25 JST
Kouhei Sutou 2011-11-26 08:21:25 +0000 (Sat, 26 Nov 2011)
New Revision: 2c626f040902e919e9c72f743c52f4c182d876be
Log:
[storage][test] add tests for adding spatial index.
Added files:
test/sql/suite/groonga_storage/r/alter_table_spatial.result
test/sql/suite/groonga_storage/t/alter_table_spatial.test
Added: test/sql/suite/groonga_storage/r/alter_table_spatial.result (+131 -0) 100644
===================================================================
--- /dev/null
+++ test/sql/suite/groonga_storage/r/alter_table_spatial.result 2011-11-26 08:21:25 +0000 (e0c9637)
@@ -0,0 +1,131 @@
+DROP TABLE IF EXISTS shops;
+CREATE TABLE shops (
+id INT PRIMARY KEY AUTO_INCREMENT,
+name TEXT,
+location GEOMETRY NOT NULL
+);
+INSERT INTO shops (name, location)
+VALUES ('nezu-no-taiyaki',
+GeomFromText('POINT(139.762573 35.720253)'));
+INSERT INTO shops (name, location)
+VALUES ('taiyaki-kataoka',
+GeomFromText('POINT(139.715591 35.712521)'));
+INSERT INTO shops (name, location)
+VALUES ('soba-taiyaki-ku',
+GeomFromText('POINT(139.659088 35.683712)'));
+INSERT INTO shops (name, location)
+VALUES ('kuruma',
+GeomFromText('POINT(139.706207 35.721516)'));
+INSERT INTO shops (name, location)
+VALUES ('hirose-ya',
+GeomFromText('POINT(139.685608 35.714844)'));
+INSERT INTO shops (name, location)
+VALUES ('sazare',
+GeomFromText('POINT(139.685043 35.714653)'));
+INSERT INTO shops (name, location)
+VALUES ('omede-taiyaki',
+GeomFromText('POINT(139.817154 35.700516)'));
+INSERT INTO shops (name, location)
+VALUES ('onaga-ya',
+GeomFromText('POINT(139.81105 35.698254)'));
+INSERT INTO shops (name, location)
+VALUES ('shiro-ya',
+GeomFromText('POINT(139.638611 35.705517)'));
+INSERT INTO shops (name, location)
+VALUES ('fuji-ya',
+GeomFromText('POINT(139.637115 35.703938)'));
+INSERT INTO shops (name, location)
+VALUES ('miyoshi',
+GeomFromText('POINT(139.537323 35.644539)'));
+INSERT INTO shops (name, location)
+VALUES ('juju-ya',
+GeomFromText('POINT(139.695755 35.628922)'));
+INSERT INTO shops (name, location)
+VALUES ('tatsumi-ya',
+GeomFromText('POINT(139.638657 35.665501)'));
+INSERT INTO shops (name, location)
+VALUES ('tetsuji',
+GeomFromText('POINT(139.76857 35.680912)'));
+INSERT INTO shops (name, location)
+VALUES ('gazuma-ya',
+GeomFromText('POINT(139.647598 35.700817)'));
+INSERT INTO shops (name, location)
+VALUES ('honma-mon',
+GeomFromText('POINT(139.652573 35.722736)'));
+INSERT INTO shops (name, location)
+VALUES ('naniwa-ya',
+GeomFromText('POINT(139.796234 35.730061)'));
+INSERT INTO shops (name, location)
+VALUES ('kuro-dai',
+GeomFromText('POINT(139.704834 35.650345)'));
+INSERT INTO shops (name, location)
+VALUES ('daruma',
+GeomFromText('POINT(139.770599 35.681461)'));
+INSERT INTO shops (name, location)
+VALUES ('yanagi-ya',
+GeomFromText('POINT(139.783981 35.685341)'));
+INSERT INTO shops (name, location)
+VALUES ('sharaku',
+GeomFromText('POINT(139.794846 35.716969)'));
+INSERT INTO shops (name, location)
+VALUES ('takane',
+GeomFromText('POINT(139.560913 35.698601)'));
+INSERT INTO shops (name, location)
+VALUES ('chiyoda',
+GeomFromText('POINT(139.652817 35.642601)'));
+INSERT INTO shops (name, location)
+VALUES ('da-ka-po',
+GeomFromText('POINT(139.727356 35.627346)'));
+INSERT INTO shops (name, location)
+VALUES ('matsushima-ya',
+GeomFromText('POINT(139.737381 35.640556)'));
+INSERT INTO shops (name, location)
+VALUES ('kazuya',
+GeomFromText('POINT(139.760895 35.673508)'));
+INSERT INTO shops (name, location)
+VALUES ('furuya-kogane-an',
+GeomFromText('POINT(139.676071 35.680603)'));
+INSERT INTO shops (name, location)
+VALUES ('hachi-no-ie',
+GeomFromText('POINT(139.668106 35.608021)'));
+INSERT INTO shops (name, location)
+VALUES ('azuki-chan',
+GeomFromText('POINT(139.673203 35.64151)'));
+INSERT INTO shops (name, location)
+VALUES ('kuriko-an',
+GeomFromText('POINT(139.796829 35.712013)'));
+INSERT INTO shops (name, location)
+VALUES ('yume-no-aru-machi-no-taiyaki-ya-san',
+GeomFromText('POINT(139.712524 35.616199)'));
+INSERT INTO shops (name, location)
+VALUES ('naze-ya',
+GeomFromText('POINT(139.665833 35.609039)'));
+INSERT INTO shops (name, location)
+VALUES ('sanoki-ya',
+GeomFromText('POINT(139.770721 35.66592)'));
+INSERT INTO shops (name, location)
+VALUES ('shigeta',
+GeomFromText('POINT(139.780273 35.672626)'));
+INSERT INTO shops (name, location)
+VALUES ('nishimi-ya',
+GeomFromText('POINT(139.774628 35.671825)'));
+INSERT INTO shops (name, location)
+VALUES ('hiiragi',
+GeomFromText('POINT(139.711517 35.647701)'));
+ALTER TABLE shops ADD SPATIAL KEY location_index (location);
+SELECT id, name, AsText(location) AS location_text FROM shops
+WHERE MBRContains(GeomFromText('LineString(139.7727 35.6684, 139.7038 35.7121)'), location);
+id name location_text
+14 tetsuji POINT(139.76857 35.680911944444446)
+19 daruma POINT(139.7705988888889 35.68146111111111)
+26 kazuya POINT(139.760895 35.67350805555556)
+SHOW CREATE TABLE shops;
+Table Create Table
+shops CREATE TABLE `shops` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `name` text,
+ `location` geometry NOT NULL,
+ PRIMARY KEY (`id`),
+ SPATIAL KEY `location_index` (`location`)
+) ENGINE=groonga DEFAULT CHARSET=latin1
+DROP TABLE shops;
Added: test/sql/suite/groonga_storage/t/alter_table_spatial.test (+147 -0) 100644
===================================================================
--- /dev/null
+++ test/sql/suite/groonga_storage/t/alter_table_spatial.test 2011-11-26 08:21:25 +0000 (666f151)
@@ -0,0 +1,147 @@
+# Copyright(C) 2011 Kouhei Sutou <kou****@clear*****>
+#
+# This library is free software; you can redistribute it and/or
+# modify it under the terms of the GNU Lesser General Public
+# License as published by the Free Software Foundation; either
+# version 2.1 of the License, or (at your option) any later version.
+#
+# This library is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+# Lesser General Public License for more details.
+#
+# You should have received a copy of the GNU Lesser General Public
+# License along with this library; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+--source include/have_groonga.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS shops;
+--enable_warnings
+
+CREATE TABLE shops (
+ id INT PRIMARY KEY AUTO_INCREMENT,
+ name TEXT,
+ location GEOMETRY NOT NULL
+);
+
+INSERT INTO shops (name, location)
+ VALUES ('nezu-no-taiyaki',
+ GeomFromText('POINT(139.762573 35.720253)'));
+INSERT INTO shops (name, location)
+ VALUES ('taiyaki-kataoka',
+ GeomFromText('POINT(139.715591 35.712521)'));
+INSERT INTO shops (name, location)
+ VALUES ('soba-taiyaki-ku',
+ GeomFromText('POINT(139.659088 35.683712)'));
+INSERT INTO shops (name, location)
+ VALUES ('kuruma',
+ GeomFromText('POINT(139.706207 35.721516)'));
+INSERT INTO shops (name, location)
+ VALUES ('hirose-ya',
+ GeomFromText('POINT(139.685608 35.714844)'));
+INSERT INTO shops (name, location)
+ VALUES ('sazare',
+ GeomFromText('POINT(139.685043 35.714653)'));
+INSERT INTO shops (name, location)
+ VALUES ('omede-taiyaki',
+ GeomFromText('POINT(139.817154 35.700516)'));
+INSERT INTO shops (name, location)
+ VALUES ('onaga-ya',
+ GeomFromText('POINT(139.81105 35.698254)'));
+INSERT INTO shops (name, location)
+ VALUES ('shiro-ya',
+ GeomFromText('POINT(139.638611 35.705517)'));
+INSERT INTO shops (name, location)
+ VALUES ('fuji-ya',
+ GeomFromText('POINT(139.637115 35.703938)'));
+INSERT INTO shops (name, location)
+ VALUES ('miyoshi',
+ GeomFromText('POINT(139.537323 35.644539)'));
+INSERT INTO shops (name, location)
+ VALUES ('juju-ya',
+ GeomFromText('POINT(139.695755 35.628922)'));
+INSERT INTO shops (name, location)
+ VALUES ('tatsumi-ya',
+ GeomFromText('POINT(139.638657 35.665501)'));
+INSERT INTO shops (name, location)
+ VALUES ('tetsuji',
+ GeomFromText('POINT(139.76857 35.680912)'));
+INSERT INTO shops (name, location)
+ VALUES ('gazuma-ya',
+ GeomFromText('POINT(139.647598 35.700817)'));
+INSERT INTO shops (name, location)
+ VALUES ('honma-mon',
+ GeomFromText('POINT(139.652573 35.722736)'));
+INSERT INTO shops (name, location)
+ VALUES ('naniwa-ya',
+ GeomFromText('POINT(139.796234 35.730061)'));
+INSERT INTO shops (name, location)
+ VALUES ('kuro-dai',
+ GeomFromText('POINT(139.704834 35.650345)'));
+INSERT INTO shops (name, location)
+ VALUES ('daruma',
+ GeomFromText('POINT(139.770599 35.681461)'));
+INSERT INTO shops (name, location)
+ VALUES ('yanagi-ya',
+ GeomFromText('POINT(139.783981 35.685341)'));
+INSERT INTO shops (name, location)
+ VALUES ('sharaku',
+ GeomFromText('POINT(139.794846 35.716969)'));
+INSERT INTO shops (name, location)
+ VALUES ('takane',
+ GeomFromText('POINT(139.560913 35.698601)'));
+INSERT INTO shops (name, location)
+ VALUES ('chiyoda',
+ GeomFromText('POINT(139.652817 35.642601)'));
+INSERT INTO shops (name, location)
+ VALUES ('da-ka-po',
+ GeomFromText('POINT(139.727356 35.627346)'));
+INSERT INTO shops (name, location)
+ VALUES ('matsushima-ya',
+ GeomFromText('POINT(139.737381 35.640556)'));
+INSERT INTO shops (name, location)
+ VALUES ('kazuya',
+ GeomFromText('POINT(139.760895 35.673508)'));
+INSERT INTO shops (name, location)
+ VALUES ('furuya-kogane-an',
+ GeomFromText('POINT(139.676071 35.680603)'));
+INSERT INTO shops (name, location)
+ VALUES ('hachi-no-ie',
+ GeomFromText('POINT(139.668106 35.608021)'));
+INSERT INTO shops (name, location)
+ VALUES ('azuki-chan',
+ GeomFromText('POINT(139.673203 35.64151)'));
+INSERT INTO shops (name, location)
+ VALUES ('kuriko-an',
+ GeomFromText('POINT(139.796829 35.712013)'));
+INSERT INTO shops (name, location)
+ VALUES ('yume-no-aru-machi-no-taiyaki-ya-san',
+ GeomFromText('POINT(139.712524 35.616199)'));
+INSERT INTO shops (name, location)
+ VALUES ('naze-ya',
+ GeomFromText('POINT(139.665833 35.609039)'));
+INSERT INTO shops (name, location)
+ VALUES ('sanoki-ya',
+ GeomFromText('POINT(139.770721 35.66592)'));
+INSERT INTO shops (name, location)
+ VALUES ('shigeta',
+ GeomFromText('POINT(139.780273 35.672626)'));
+INSERT INTO shops (name, location)
+ VALUES ('nishimi-ya',
+ GeomFromText('POINT(139.774628 35.671825)'));
+INSERT INTO shops (name, location)
+ VALUES ('hiiragi',
+ GeomFromText('POINT(139.711517 35.647701)'));
+
+ALTER TABLE shops ADD SPATIAL KEY location_index (location);
+
+SELECT id, name, AsText(location) AS location_text FROM shops
+ WHERE MBRContains(GeomFromText('LineString(139.7727 35.6684, 139.7038 35.7121)'), location);
+
+SHOW CREATE TABLE shops;
+
+DROP TABLE shops;
+
+--source include/have_groonga_deinit.inc