# 日報(2024-02-10) JSON だと時間かかり遅すぎるので SQLite にしたら 1/20 に速くなった
# 背景
OpenWeatherMap を WEB で使う時は https://openweathermap.org/city/1865714 (opens new window) みたいな感じで city ID (ここだと 1865714) を指定して開きます。いつも 川崎 の ID で開いてたのですが溝の口と川崎って以外と天気が違って、うちのあたりが晴れてても川崎のあたりは雨ふってたりすることがよくあります。もっと近場で OpenWeatherMap に登録されてる City がないか探したい、と思ったのが話の発端です
# city.list.json
登録されている City のリストは city.list.json (opens new window)にありまして、ID と都市名、国、経度・緯度などの情報が JSON 形式で 20万件ちょっと登録されていますので、これを読んで溝の口の座標と比較していって一番近い City を探します
Raspberry Pi でやってみると JSON の parse に 20秒、20万件の配列からの検索に 4秒、合計 25秒 かかりました。ちな、溝の口から一番近い city は下小田中なんだそうですが、あんな 富士通 ぐらいしかないようなところのどこに気象観測所があるんでしょうね（ちなみに富士通があるのは 上小田中 で、南武線をはさんで反対側ですが）
# JSON から SQLite のファイルに変換する
使っている encoding/json よりも速い JSON の parser があるのかもしれませんが、そもそも JSON String にシリアライズされたデータを毎度わざわざ parse して構造データに戻して使う事の意味がなにもないです
最終的には任意の座標を入れればすぐに最寄りの city を返してくれるようにしたいという思いもあるので、JSON ではなくて構造データとして保存して使いたいです
そこで JSON を読み込んでそのまま SQLite に書き出してみました
サイズも 1/5 ぐらいに小さくなっていい感じです
-rw-r--r-- 1 pi pi 41700904 Feb 8 16:39 city.list.json
-rw-r--r-- 1 pi pi 8912896 Feb 8 23:46 city.sql3
測ってみると SQLite のオープンに 77ms、select に900μs といい感じですが、20万件の rows から順番に Scan して一番近いのを探すのに 15秒 かかっていてイマイチです
# Select する city を絞る
同じ演算を一斉に適用するような処理がめちゃ速いのが SQL のメリットです
なので全ての都市を SQL で select した後にアプリ側で一番近い都市を探すのではなく、一番近い都市まで SQL で探してしまえばかなり速くなりそうな期待感がもてます
二点間の距離は計算が軽そうな 経度の差の２乗 + 緯度の差の２乗 で比較する事にします、これの平方根に地球の半径掛けたら局所的には本物のユークリッド距離っぽいのですが距離が遠くなったらユークリッド距離との乖離が酷くなるのですが、そんな状況でも３平方の定理は満たしてるので距離といえば距離ですね
で、近いと判断する閾値がムズくて、city の分布って一様じゃなくて、例えば関東のあたりだとゴチャゴチャと、例えば溝の口の近辺だけでも下小田中とか田園調布とか狛江とか多数登録されているのですが、両極の近辺とか太平洋上とか最寄りの city がめちゃ遠かったりします
で、距離の閾値をます 0.1 から 10倍ずつしていきながら count(*) がゼロじゃない cities に絞ることにします
/city $ ./city 35.596 139.6100
2024/02/10 11:33:20.249706 sqlite.go:180: closerThan 0.1
2024/02/10 11:33:20.798568 sqlite.go:182: Cities 81
2024/02/10 11:33:20.798375 erapse.go:14: eraps main.getNumberOfClosingCities: 548489 μs
2024/02/10 11:33:21.557238 sqlite.go:168: nearest city Shimokodanaka
2024/02/10 11:33:21.557520 sqlite.go:169: id 1852278
2024/02/10 11:33:21.557705 sqlite.go:170: lat 35.566669
2024/02/10 11:33:21.557887 sqlite.go:171: lon 139.633331
2024/02/10 11:33:21.558298 erapse.go:14: eraps main.main: 1316797 μs
0.1 一発で Cities が 81個にまで絞れる溝の口だと 1.3 秒でできました。これだとサービスにしても問題なさそうです。距離を計算して比較する where 句を 20万個のデータに対して 500ms ぐらいでやってくれるあたり、同じ演算を一斉にやってもあまり時間がかからないあたりが RDB の魅力です。しかもこれ Raspberry Pi でやってて 500ms
北極点あたりだと
./city 90 0
2024/02/10 11:37:26.762942 sqlite.go:180: closerThan 0.1
2024/02/10 11:37:27.292606 erapse.go:14: eraps main.getNumberOfClosingCities: 529468 μs
2024/02/10 11:37:27.292837 sqlite.go:180: closerThan 1
2024/02/10 11:37:28.115561 erapse.go:14: eraps main.getNumberOfClosingCities: 822566 μs
2024/02/10 11:37:28.115807 sqlite.go:180: closerThan 10
2024/02/10 11:37:28.934999 erapse.go:14: eraps main.getNumberOfClosingCities: 819004 μs
2024/02/10 11:37:28.935253 sqlite.go:180: closerThan 100
2024/02/10 11:37:29.754220 erapse.go:14: eraps main.getNumberOfClosingCities: 818788 μs
2024/02/10 11:37:29.754457 sqlite.go:180: closerThan 1000
2024/02/10 11:37:30.589050 erapse.go:14: eraps main.getNumberOfClosingCities: 834420 μs
2024/02/10 11:37:30.589288 sqlite.go:182: Cities 616
2024/02/10 11:37:31.461587 sqlite.go:168: nearest city Nybyen
2024/02/10 11:37:31.462277 sqlite.go:169: id 2729456
2024/02/10 11:37:31.462551 sqlite.go:170: lat 78.201851
2024/02/10 11:37:31.462765 sqlite.go:171: lon 15.59119
2024/02/10 11:37:31.463224 erapse.go:14: eraps main.main: 4708889 μs
closerThan を４回広げて 4.7 秒かかりましたが Nybyen という city が出てきました。map でググるとオスロの近所に同名の街があって紛らわしいのですが経度と緯度からみるとスバールバル諸島のスピッツベルゲン島みたいで、キャビンとホステルがあって泊まることぐらいはできそうなのですが航空写真でみると他になにもなさそうな所でした
# erapse
go だと関数の経過時間を測るのに eraps (opens new window)が手前味噌ではありますが便利です
関数の最初に以下のように描いておくと経過時間を標準出力にだしてくれる優れものです
import (
"time"
"github.com/UedaTakeyuki/erapse"
)
func main() {
defer erapse.ShowErapsedTIme(time.Now())
elapse ではなく erapse なあたりが母語に l と r の区別がない私っぽくて可愛らしいなと思い後悔はないのですが、もし人生で一度だけなにかをやり直せるとしたら l に治したいなと Ken Thompson (opens new window) みたいに思うのでありました^1