Tweet
Tweet

Slide 1

Slide 1 text

地球とSQL Engineer Drink Up 2018 1Q @onunu

Slide 2

Slide 2 text

Hello! おぬまです @onunu IESHILでSQLを書く仕事をし てます:) Twitter: @onunu_ Github: onunu

Slide 3

Slide 3 text

2017年9月 IESHIL CONNECT がリリースされました！

Slide 4

Slide 4 text

IESHIL CONNECTってなに?

Slide 5

Slide 5 text

IESHIL CONNECTってなに? 不動産営業マン向けの エージェントツール 物件周辺の災害時のリ スク状況などがわかる 最近レポート機能が追加 されたよ (デフォルトでWordが出力 されてすごい)

Slide 6

Slide 6 text

Q. このサービスの本質はなにか？

Slide 7

Slide 7 text

◎ Raw data(アジア航測と提携) -> 5m dem ○ 769,500,000 records ◎ Mesh Codes(地域メッシュ) -> 約250m^2 ○ 364,800 records IESHIL CONNECTのデータ A . 詳細なデータと運用設計

Slide 8

Slide 8 text

No content

Slide 9

Slide 9 text

地球感じられますね？

Slide 10

Slide 10 text

今日はそのデータの話 をします

Slide 11

Slide 11 text

IESHIL CONNECTのデータワークフロー

Slide 12

Slide 12 text

◎ Raw dataはTresureDataにいれておく ◎ 各タスクはdigdagでワークフロー定義 ○ サービスのフロントDBに書き出すところまでがお仕 事 ◎ データの整形, 加工はSQLとembulkでやる IESHIL CONNECTのデータワークフロー 中身はこんな感じ(一部)

Slide 13

Slide 13 text

◎ TresureData製のワークフローエン ジン ◎ taskの並列実行/実行待ちをyaml 的な階層構造で表現する ○ 拡張子は.dig, mapのキー順 序を維持する必要があるた め、明示的に分けてる ◎ Taskの実行結果を状態としてもつ ○ 異常終了した時、次回実行 時は成功したtaskをskipする digdagとは?

Slide 14

Slide 14 text

digdagとは? ◎ +hogeでタスク定義 ◎ fuga>は何かしらの処理 ◎ _piyoはprepared statement

Slide 15

Slide 15 text

Digdagいいぞ 使っていこう

Slide 16

Slide 16 text

ではSQLの話をします (ここからが本番)

Slide 17

Slide 17 text

Q. mesh codeの範囲(view port)をSQL によって計算したい Mesh code(地域メッシュ)

Slide 18

Slide 18 text

A. mesh codeの仕様はとてもアレです 頑張って計算します Mesh code(地域メッシュ)

Slide 19

Slide 19 text

Mesh code(地域メッシュ)とは ◎ 日本工業規格 JIS X 0410 ◎ 内容は以下 ○ 全国の地域を 1 度ごとの経 線と ○ 偶数緯度及びその間隔を 3 等分した緯度における緯線 とによって ○ 分割して第 1 次地域区画を 作る ◎ 紙の地図によくあるアレです http://www.sinfonica.or.jp/mesh/mimginfo.html

Slide 20

Slide 20 text

Mesh code(地域メッシュ)とは ◎ 1次地域区画 ◎ 南端緯度×1.5を上2桁 ◎ 西端経度の下2桁 ◎ 東京あたりは「5339」

Slide 21

Slide 21 text

◎ 2次地域区画 ◎ 1次地域区画の細分化(8x8) ◎ さっきの東京のメッシュでいくと ○ 5339-23 Mesh code(地域メッシュ)とは

Slide 22

Slide 22 text

◎ 3次地域区画 ○ 基準地域メッシュ ◎ 2次地域区画の細分化(10x10) ○ 5339-23-43 Mesh code(地域メッシュ)とは

Slide 23

Slide 23 text

◎ 2分の1地域メッシュ ○ (4次地域区画) ◎ 2次地域区画の細分化(2x2) ◎ 1桁で表す ○ 5339-23-43-1 ◎ n分の1地域メッシュは以下同じ Mesh code(地域メッシュ)とは

Slide 24

Slide 24 text

Mesh code(地域メッシュ)

Slide 25

Slide 25 text

Mesh code(地域メッシュ) これで南西端の緯度経度は計算できた あとは1meshの緯度経度を足せばviewport が計算できる！

Slide 26

Slide 26 text

Q. ある建物が、 震災時危険地域から50m以内にある時 フラグを立てたい 距離

Slide 27

Slide 27 text

A. ある建物と(最も)近い危険地域までの 測地距離(?)を頑張って計算します 距離

Slide 28

Slide 28 text

距離 ◎ 緯度経度わかっているわけなので ◎ 三平方の定理つかったらいけそう

Slide 29

Slide 29 text

でも地球は球なので(しかも回転楕円体) ちゃんと計算しないといけない 距離

Slide 30

Slide 30 text

距離 ◎ 回転楕円体上の距離計算にはいく つかの公式がある ◎ もっともよく使われるヒュベニの公式 (国土地理院とかでも使われてる)は 距離が長くなると精度おちるらしい ○ もし日本全土がサービス提供圏 になっても大丈夫！ ◎ IESHILでは測地線航海算法という 計算式をつかっています 測地線航海算法の証明しようと思ったけど インターネッツに情報がほとんどない。 (みんな距離の計算しないの?)

Slide 31

Slide 31 text

距離 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では残念ながらそこまではせず、 球であることを前提として大圏航海算法になっています)

Slide 32

Slide 32 text

距離 X=cos-1[sinφ A ･sinφ B +cosφ A ･cosφ B ･cos(L A -L B )] これをSQLで書くと、 三角関数用意されてるので余裕ですね：） (SQLで三角関数使うときはラジアンに変換する のを忘れないようにしましょう！)

Slide 33

Slide 33 text

勧誘 IESHILではデータエンジニアが 不足してます ◎ 地球が好きな人 ◎ データとアプリケーションの両方をやっていきたい人 ◎ 大量のデータを扱いたい人 ◎ 不動産鑑定士の資格とか実は持ってる人 興味ある人は話しかけてください

Slide 34

Slide 34 text

ありがとうございました！