再帰クエリによる地名展開
ゆるいテキスト検索の一環として、地名を展開してpg_bigmの条件値にする方式をやってみた。
なお、ぬこの世界観では
町田は(東京都でもあるけど)本当は神奈川県
なので、そのルールで地名展開用のテーブルを作成することにした。
なお、相模原在住の過激派の主張では
町田は相模原の一部
らしいので、その主張も取り入れてみた。特に深い意味はない。
実行例
とりあえず地名を与えると、その地名に属する下位の地名を(pg_bigmのlikequery()を適用した)TEXT配列を返却する expand_area()という関数を定義し、それを LIKE ANY (expand_area()) 比較演算子で評価するようにした。
例えばこんな感じ。
wareki=# SELECT id, data, ts2w24(ts) FROM lifelog WHERE data LIKE ANY (expand_area('横浜'));
id | data | ts2w24
-----+--------------------------------------+--------
190 | 港南区で鶏白湯らーめんを食べた。 | 処暑
197 | 鶴屋町で醤油ラーメンを食べた。 | 白露
198 | 西区で醤油ラーメンを食べた。 | 秋分
204 | 南区で醤油ラーメンを食べた。 | 秋分
205 | 中山でつけ麺を食べた。 | 秋分
212 | 西区で豚骨ラーメンを食べた。 | 寒露
234 | 港南区でサンマーメンを食べた。 | 立冬
235 | 白楽で二郎インスパイアを食べた。 | 立冬
238 | 矢向で醤油ラーメンを食べた。 | 立冬
257 | 横浜駅近くで醤油ラーメンを食べた。 | 冬至
261 | 桜木町でネギラーメンを食べた。 | 冬至
265 | 西区でサンマーメンを食べた。 | 冬至
271 | 横浜駅で醤油ラーメンを食べた。 | 大寒
275 | 中区で激辛ラーメンを食べた。 | 大寒
278 | 中区で刀削麺を食べた。 | 立春
284 | みなとみらいで炸醤刀削麺を食べた。 | 雨水
・・・
361 | 横浜で油そばを食べた。 | 夏至
366 | 鶴屋町で微妙な味噌ラーメンを食べた。 | 大暑
375 | 横浜で醤油ラーメンを食べた。 | 立秋
(38 rows)横浜市は「中区」「西区」・・・「港南区」などの区名に展開され、各区名もさらに下位の町名などに展開する。例えば上記の例では「神奈川区」からさらに「鶴屋町」「白楽」などに展開している。
wareki=# SELECT id, data, ts2w24(ts) FROM lifelog WHERE data LIKE ANY (expand_area('東京都'));
id | data | ts2w24
-----+------------------------------------------+--------
190 | 港南区で鶏白湯らーめんを食べた。 | 処暑
203 | 大井町(東京)で醤油ラーメンを食べた。 | 秋分
220 | 大井町(東京)で家系ラーメンを食べた。 | 霜降
221 | 大井町(東京)で担々麺を食べた。 | 霜降
233 | 大井町(東京)で二郎インスパイアを食べた。 | 立冬
234 | 港南区でサンマーメンを食べた。 | 立冬
244 | 大井松田で小田原系ラーメンを食べた。 | 小雪
262 | 大井町(東京)で家系ラーメンを食べた。 | 冬至
270 | 大井町(東京)で家系ラーメンを食べた。 | 小寒
283 | 大井町(東京)で二郎インスパイアを食べた。 | 雨水
304 | 大井町で豚骨魚介ラーメンを食べた。 | 春分
351 | 大井町(東京)で塩ラーメンを食べた。 | 芒種
352 | 大井町(神奈川)で塩ラーメンを食べた。 | 芒種
364 | 町田で魚介系の醤油ラーメンを食べた。 | 小暑
(14 rows)一応、東京でも「町田」がヒットするようにはしました(嫌々だけどｗ)
あと、神奈川にも「大井町」があるから、こういうノイズが入るのは仕方がないのか・・・
wareki=# SELECT id, data, ts2w24(ts) FROM lifelog WHERE data LIKE ANY (expand_area('相模原'));
id | data | ts2w24
-----+--------------------------------------+--------
190 | 港南区で鶏白湯らーめんを食べた。 | 処暑
204 | 南区で醤油ラーメンを食べた。 | 秋分
234 | 港南区でサンマーメンを食べた。 | 立冬
326 | 南区で醤油ラーメンを食べた。 | 穀雨
337 | 南区で醤油ラーメンを食べた。 | 立夏
339 | 南区で味噌ラーメンを食べた。 | 立夏
364 | 町田で魚介系の醤油ラーメンを食べた。 | 小暑
365 | 淵野辺で味噌つけめんを食べた。 | 大暑
377 | 相模原で豚骨魚介ラーメンを食べた。 | 立秋
(9 rows)しつこいようですが町田は神奈川の領土ですｗ さらに言えば相模原の領土ですｗ
「港南区」がヒットしてしまうのは相模原市に「南区」があるからなんだよな・・・
相模原市は「南区」「緑区」を別の区名にすべきです(ぉ
テーブル構成
で、これを実現するために地名展開用のテーブルを定義します。
定義はこんな感じ。
wareki=# \d area
Table "public.area"
Column | Type | Modifiers
--------+-----------+-----------
id | integer |
name | text |
childs | integer[] | ポイントはchildsカラム。ここに展開する地名のidを記述する。
データの例はこんな感じ。
1000 東京都 {1100,1200,1300,1400}
1100 港区 {1101,1102,1004,1004}
1101 赤坂 {}
1102 港南 {}
1103 新橋 {}
（略）
1400 町田市 {1401,1402,1403,1404}
1401 鶴間 {}
1402 成瀬 {}
1403 原町田 {}
1404 つくし野 {}
2000 神奈川県 {1400,2100,2200,2300,2400,2500,2600}
2001 湘南 {2500,2600,2700}
2100 横浜市 {2110,2120,2130,2140,2150,2160}
（略）3つ目のカラムはinteder配列になっているが、ここに展開する下位エリアのidを記述する。
(町田(id=1400)はしつこいようだが東京でもあり神奈川でもあるので、両方の都道府県から展開可能としている)
展開クエリ
このテーブルを展開するためにPostgreSQLの再帰クエリを使ってみる。
上記の expand_area関数は再帰クエリを使って実装している。
実装例はこんな感じ。
CREATE OR REPLACE FUNCTION expand_area(area_name TEXT) RETURNS TEXT[] AS $$ SELECT array_append(array_agg(likequery(regexp_replace(res.name, '(市|県|都)$', ''))), likequery(area_name)) FROM (WITH RECURSIVE ar AS (SELECT * FROM area AS ar1 WHERE ar1.name LIKE likequery(area_name) UNION ALL SELECT ar2.* FROM ar, area as ar2 WHERE ar2.id = ANY (ar.childs)) SELECT distinct name FROM ar) as res; $$ LANGUAGE sql;
なお、検索結果はpg_bigmのlikequeryを使って前後にメタ文字'%'を付与するようにしている。また、展開された地名が市名、都道府県名の場合には末端の「市」や「県」「都」を削除するようにしている。これは、(自分で書くときの癖かもしれないが)「神奈川県」「東京都」「横浜市」と表記せずに「神奈川」「東京」「横浜」と書くことが多いからだ。
課題
課題はいろいろ多い・・・。
しかし地名という性質上どうにもならないことも多いなあ。
