地球とSQL

とあるサービスのリリースのためにSQLを書いていたら、地球を感じられたお話です
社内で行われたエンジニア懇親会の余興です

Transcript

1. 地球とSQL Engineer Drink Up 2018 1Q @onunu
2. Hello! おぬまです @onunu IESHILでSQLを書く仕事をし てます:) Twitter: @onunu_ Github: onunu
3. 2017年9月 IESHIL CONNECT がリリースされました！
4. IESHIL CONNECTってなに?
5. IESHIL CONNECTってなに? 不動産営業マン向けの エージェントツール 物件周辺の災害時のリ スク状況などがわかる 最近レポート機能が追加 されたよ (デフォルトでWordが出力 されてすごい)
6. Q. このサービスの本質はなにか？
7. ◎ Raw data(アジア航測と提携) -> 5m dem ◦ 769,500,000 records ◎ Mesh Codes(地域メッシュ) -> 約250m^2 ◦ 364,800 records IESHIL CONNECTのデータ A . 詳細なデータと運用設計
9. 地球感じられますね？
10. 今日はそのデータの話 をします
11. IESHIL CONNECTのデータワークフロー
12. ◎ Raw dataはTresureDataにいれておく ◎ 各タスクはdigdagでワークフロー定義 ◦ サービスのフロントDBに書き出すところまでがお仕 事 ◎ データの整形, 加工はSQLとembulkでやる IESHIL CONNECTのデータワークフロー 中身はこんな感じ(一部)
13. ◎ TresureData製のワークフローエン ジン ◎ taskの並列実行/実行待ちをyaml 的な階層構造で表現する ◦ 拡張子は.dig, mapのキー順 序を維持する必要があるた め、明示的に分けてる ◎ Taskの実行結果を状態としてもつ ◦ 異常終了した時、次回実行 時は成功したtaskをskipする digdagとは?
14. digdagとは? ◎ +hogeでタスク定義 ◎ fuga>は何かしらの処理 ◎ _piyoはprepared statement
15. Digdagいいぞ 使っていこう
16. ではSQLの話をします (ここからが本番)
17. Q. mesh codeの範囲(view port)をSQL によって計算したい Mesh code(地域メッシュ)
18. A. mesh codeの仕様はとてもアレです 頑張って計算します Mesh code(地域メッシュ)
19. Mesh code(地域メッシュ)とは ◎ 日本工業規格 JIS X 0410 ◎ 内容は以下 ◦ 全国の地域を 1 度ごとの経 線と ◦ 偶数緯度及びその間隔を 3 等分した緯度における緯線 とによって ◦ 分割して第 1 次地域区画を 作る ◎ 紙の地図によくあるアレです http://www.sinfonica.or.jp/mesh/mimginfo.html
20. Mesh code(地域メッシュ)とは ◎ 1次地域区画 ◎ 南端緯度×1.5を上2桁 ◎ 西端経度の下2桁 ◎ 東京あたりは「5339」
21. ◎ 2次地域区画 ◎ 1次地域区画の細分化(8x8) ◎ さっきの東京のメッシュでいくと ◦ 5339-23 Mesh code(地域メッシュ)とは
22. ◎ 3次地域区画 ◦ 基準地域メッシュ ◎ 2次地域区画の細分化(10x10) ◦ 5339-23-43 Mesh code(地域メッシュ)とは
23. ◎ 2分の1地域メッシュ ◦ (4次地域区画) ◎ 2次地域区画の細分化(2x2) ◎ 1桁で表す ◦ 5339-23-43-1 ◎ n分の1地域メッシュは以下同じ Mesh code(地域メッシュ)とは
24. Mesh code(地域メッシュ)
25. Mesh code(地域メッシュ) これで南西端の緯度経度は計算できた あとは1meshの緯度経度を足せばviewport が計算できる！
26. Q. ある建物が、 震災時危険地域から50m以内にある時 フラグを立てたい 距離
27. A. ある建物と(最も)近い危険地域までの 測地距離(?)を頑張って計算します 距離
28. 距離 ◎ 緯度経度わかっているわけなので ◎ 三平方の定理つかったらいけそう
29. でも地球は球なので(しかも回転楕円体) ちゃんと計算しないといけない 距離
30. 距離 ◎ 回転楕円体上の距離計算にはいく つかの公式がある ◎ もっともよく使われるヒュベニの公式 (国土地理院とかでも使われてる)は 距離が長くなると精度おちるらしい ◦ もし日本全土がサービス提供圏 になっても大丈夫！ ◎ IESHILでは測地線航海算法という 計算式をつかっています 測地線航海算法の証明しようと思ったけど インターネッツに情報がほとんどない。 (みんな距離の計算しないの?)
31. 距離 A点(l A ,L A )と B点(l B ,L B )の測地線長(ρ) (l A ,L A )： A点の測地緯度，測地経度　　(l B ,L B )： B点の測地緯度，測地経度 X=cos-1[sinφ A ･sinφ B +cosφ A ･cosφ B ･cos(L A -L B )] 赤道半径と極半径を用いて化成緯度を作成することで回 転楕円体として 扁平率を考慮した計算にすることもできる (今のIESHILでは残念ながらそこまではせず、 球であることを前提として大圏航海算法になっています)
32. 距離 X=cos-1[sinφ A ･sinφ B +cosφ A ･cosφ B ･cos(L A -L B )] これをSQLで書くと、 三角関数用意されてるので余裕ですね：） (SQLで三角関数使うときはラジアンに変換する のを忘れないようにしましょう！)
33. 勧誘 IESHILではデータエンジニアが 不足してます ◎ 地球が好きな人 ◎ データとアプリケーションの両方をやっていきたい人 ◎ 大量のデータを扱いたい人 ◎ 不動産鑑定士の資格とか実は持ってる人 興味ある人は話しかけてください
34. ありがとうございました！