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
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
|
drop table if exists shops;
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;
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=Mroonga DEFAULT CHARSET=latin1 COMMENT='engine "innodb"'
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)'));
select id, name, AsText(location) as location_text from shops;
id name location_text
1 nezu-no-taiyaki POINT(139.762573 35.720253)
2 taiyaki-kataoka POINT(139.715591 35.712521)
3 soba-taiyaki-ku POINT(139.659088 35.683712)
4 kuruma POINT(139.706207 35.721516)
5 hirose-ya POINT(139.685608 35.714844)
6 sazare POINT(139.685043 35.714653)
7 omede-taiyaki POINT(139.817154 35.700516)
8 onaga-ya POINT(139.81105 35.698254)
9 shiro-ya POINT(139.638611 35.705517)
10 fuji-ya POINT(139.637115 35.703938)
11 miyoshi POINT(139.537323 35.644539)
12 juju-ya POINT(139.695755 35.628922)
13 tatsumi-ya POINT(139.638657 35.665501)
14 tetsuji POINT(139.76857 35.680912)
15 gazuma-ya POINT(139.647598 35.700817)
16 honma-mon POINT(139.652573 35.722736)
17 naniwa-ya POINT(139.796234 35.730061)
18 kuro-dai POINT(139.704834 35.650345)
19 daruma POINT(139.770599 35.681461)
20 yanagi-ya POINT(139.783981 35.685341)
21 sharaku POINT(139.794846 35.716969)
22 takane POINT(139.560913 35.698601)
23 chiyoda POINT(139.652817 35.642601)
24 da-ka-po POINT(139.727356 35.627346)
25 matsushima-ya POINT(139.737381 35.640556)
26 kazuya POINT(139.760895 35.673508)
27 furuya-kogane-an POINT(139.676071 35.680603)
28 hachi-no-ie POINT(139.668106 35.608021)
29 azuki-chan POINT(139.673203 35.64151)
30 kuriko-an POINT(139.796829 35.712013)
31 yume-no-aru-machi-no-taiyaki-ya-san POINT(139.712524 35.616199)
32 naze-ya POINT(139.665833 35.609039)
33 sanoki-ya POINT(139.770721 35.66592)
34 shigeta POINT(139.780273 35.672626)
35 nishimi-ya POINT(139.774628 35.671825)
36 hiiragi POINT(139.711517 35.647701)
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.680912)
19 daruma POINT(139.770599 35.681461)
26 kazuya POINT(139.760895 35.673508)
drop table shops;
|
