1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
# Copyright(C) 2011 Kouhei Sutou <kou@clear-code.com>
#
# 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_innodb.inc
--source include/have_geometry.inc
--source ../../include/mroonga/have_version_56_or_later.inc
--source ../../include/mroonga/have_mroonga.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,
spatial key location_index (location)
) comment = 'engine "innodb"';
show create table shops;
insert into shops (name, location)
values ('nezu-no-taiyaki',
ST_GeomFromText('POINT(139.762573 35.720253)'));
insert into shops (name, location)
values ('taiyaki-kataoka',
ST_GeomFromText('POINT(139.715591 35.712521)'));
insert into shops (name, location)
values ('soba-taiyaki-ku',
ST_GeomFromText('POINT(139.659088 35.683712)'));
insert into shops (name, location)
values ('kuruma',
ST_GeomFromText('POINT(139.706207 35.721516)'));
insert into shops (name, location)
values ('hirose-ya',
ST_GeomFromText('POINT(139.685608 35.714844)'));
insert into shops (name, location)
values ('sazare',
ST_GeomFromText('POINT(139.685043 35.714653)'));
insert into shops (name, location)
values ('omede-taiyaki',
ST_GeomFromText('POINT(139.817154 35.700516)'));
insert into shops (name, location)
values ('onaga-ya',
ST_GeomFromText('POINT(139.81105 35.698254)'));
insert into shops (name, location)
values ('shiro-ya',
ST_GeomFromText('POINT(139.638611 35.705517)'));
insert into shops (name, location)
values ('fuji-ya',
ST_GeomFromText('POINT(139.637115 35.703938)'));
insert into shops (name, location)
values ('miyoshi',
ST_GeomFromText('POINT(139.537323 35.644539)'));
insert into shops (name, location)
values ('juju-ya',
ST_GeomFromText('POINT(139.695755 35.628922)'));
insert into shops (name, location)
values ('tatsumi-ya',
ST_GeomFromText('POINT(139.638657 35.665501)'));
insert into shops (name, location)
values ('tetsuji',
ST_GeomFromText('POINT(139.76857 35.680912)'));
insert into shops (name, location)
values ('gazuma-ya',
ST_GeomFromText('POINT(139.647598 35.700817)'));
insert into shops (name, location)
values ('honma-mon',
ST_GeomFromText('POINT(139.652573 35.722736)'));
insert into shops (name, location)
values ('naniwa-ya',
ST_GeomFromText('POINT(139.796234 35.730061)'));
insert into shops (name, location)
values ('kuro-dai',
ST_GeomFromText('POINT(139.704834 35.650345)'));
insert into shops (name, location)
values ('daruma',
ST_GeomFromText('POINT(139.770599 35.681461)'));
insert into shops (name, location)
values ('yanagi-ya',
ST_GeomFromText('POINT(139.783981 35.685341)'));
insert into shops (name, location)
values ('sharaku',
ST_GeomFromText('POINT(139.794846 35.716969)'));
insert into shops (name, location)
values ('takane',
ST_GeomFromText('POINT(139.560913 35.698601)'));
insert into shops (name, location)
values ('chiyoda',
ST_GeomFromText('POINT(139.652817 35.642601)'));
insert into shops (name, location)
values ('da-ka-po',
ST_GeomFromText('POINT(139.727356 35.627346)'));
insert into shops (name, location)
values ('matsushima-ya',
ST_GeomFromText('POINT(139.737381 35.640556)'));
insert into shops (name, location)
values ('kazuya',
ST_GeomFromText('POINT(139.760895 35.673508)'));
insert into shops (name, location)
values ('furuya-kogane-an',
ST_GeomFromText('POINT(139.676071 35.680603)'));
insert into shops (name, location)
values ('hachi-no-ie',
ST_GeomFromText('POINT(139.668106 35.608021)'));
insert into shops (name, location)
values ('azuki-chan',
ST_GeomFromText('POINT(139.673203 35.64151)'));
insert into shops (name, location)
values ('kuriko-an',
ST_GeomFromText('POINT(139.796829 35.712013)'));
insert into shops (name, location)
values ('yume-no-aru-machi-no-taiyaki-ya-san',
ST_GeomFromText('POINT(139.712524 35.616199)'));
insert into shops (name, location)
values ('naze-ya',
ST_GeomFromText('POINT(139.665833 35.609039)'));
insert into shops (name, location)
values ('sanoki-ya',
ST_GeomFromText('POINT(139.770721 35.66592)'));
insert into shops (name, location)
values ('shigeta',
ST_GeomFromText('POINT(139.780273 35.672626)'));
insert into shops (name, location)
values ('nishimi-ya',
ST_GeomFromText('POINT(139.774628 35.671825)'));
insert into shops (name, location)
values ('hiiragi',
ST_GeomFromText('POINT(139.711517 35.647701)'));
select id, name, ST_AsText(location) as location_text from shops;
select id, name, ST_AsText(location) as location_text from shops
where MBRContains(ST_GeomFromText('LineString(139.7727 35.6684, 139.7038 35.7121)'), location);
drop table shops;
--source ../../include/mroonga/have_mroonga_deinit.inc
|
