GISを中心に
多彩なデータ処理をPostgreSQLで
国府田 諭 Satoshi Koda
http://kenpg.bitbucket.org/
このスライドは
↓（kenpg myna で検索）
http://kenpg.bitbucket.org/blog/201602/20.html
今⽇話したいこと
ポスグレ単体でなく PostgreSuite としての魅⼒
エンドユーザの⾃分はこんな⾵に⼯夫して使ってます
Spatial is not special !
GIS（地理情報システム）は特殊なデータ処理じゃない
…… ですが、リクエストがあったので昨年のカンファレンス （11/27）、
今⽇の本題に戻って、具体的には（1）
PostgreSQL 9.5 の新機能 IMPORT FOREIGN SCHEMA に、 GIS関連の外部データラッパ「 OGR_FDW 」が早速対応
・ローカルフォルダを、仮想的なスキーマとして扱える
・フォルダ内のGISデータを⼀括インポート可能（テーブルや列を⾒なくていい）
具体的には（2）
エンドユーザの⾃分はこんな⾵に⼯夫して使ってます 最近とくに熱いのは PostGIS + PL/v8 + ウィンドウ関数⾃作 + 直接、SVGに出⼒ ・PostGISの地理データ型を、PL/v8のストアド関数に渡す ・ウィンドウ関数がある「だけじゃない」、簡単に⾃作できる ・GISデータのビュワーがない -> SVGに出してブラウザで⾒る今⽇のスコープ / 本来の関⼼事
データを料理に例えれば 材料の調達 ↓ データ⼊⼿ ↓ 仕込み ↓ 整理 ↓ 調理 ↓ 分析 ↓ 盛り付け ↓ 可視化・⽂章化 ↓ いただきます 社会へアウトプット （⾒て楽しむ料理もアリだけど…）今⽇のスコープ / 本来の関⼼事
データを料理に例えれば 材料の調達 ↓ データ⼊⼿ ↓ 仕込み ↓ 整理 ↓ 調理 ↓ 分析 ↓ 盛り付け ↓ 可視化・⽂章化 ↓ いただきます 社会へアウトプット （⾒て楽しむ料理もアリだけど…）よくある GIS と、PostGIS の違い
GISとは ? 地理情報システム Geographic Information System ・エンドユーザにとって、たいてい「地図を作らないと始まらない」 ・Excelが「表を作らないと始まらない」のと同様 GISデータベース（空間データベース）は違う ・地図でなくデータそのものを格納・管理・演算（編集）する ・GUIでなくSQLが操作の中⼼ ・地図は、アウトプットの⼀形態にすぎない
PostGIS & 周辺について
PostGIS : PostgreSQLの機能拡張の⼀つ ・基本は、ユーザが⾃分でインストールする ・Amazon RDS や Postgres.app など「最初から⼊ってる」ケースもあり ・追加されるのは、データ型・関数・コマンドラインツールなど PostGISに対する拡張、外部ツールも存在 ・pgRouting : 経路探索（カーナビ的なあれ） ・pointcloud_postgis : 点群データ操作（3次元スキャナ的なあれ） ・OGR_FDW : 様々なGISデータを外部テーブル化 ← 今⽇の本題（1）OGR_FDWでCSV⼀括インポート（1）
インストール
・⾃分は Windows 7 x64 + PostgreSQL 9.5.0 で （PostGIS のWindows⽤バイナリ配布サイトにある） ・CSV読み込みだけなら、PostGISがなくてもOK
・CentOSでは yum install ogr_fdw95
PostGIS 公式サイト -> Download -> Windows にリンクがある http://winnie.postgis.net/download/windows/pg95/buildbot/
OGR_FDWでCSV（2）サンプルデータ準備
何でもいいけど、WHOの結核に関するページから8個のCSVファイル ・http://www.who.int/tb/country/data/download/en/ ・PostgreSQLのサーバから読める位置に、適当なフォルダを作って置く あとはクエリで ・外部テーブルを置くスキーマを作る or 決める ・CREATE SERVERクエリの要点、補⾜
フォルダ 常にこの仮想的スキーマ名を使う ・⾃動的に⾏番号の列fidが追加される ・ヘッダ⾏（列名）がない場合、列名はfield_1, field_2, , , ・UTF-8なら⽇本語もOK。SJISは⽂字化けするがASCII部分は読める⽇本語（SJIS）を含むCSVをインポートしてみると
⽇本語は⽂字化けするが、数値フィールドは使える
OGRって何なのか
OpenGIS Simple Features Reference Implementation の略
単体で存在するのでなく、Geospatial Data Abstraction Library (GDAL) やたら種類が多いGISデータの相互変換のため、CSVもその⼀つ
CSVと同様、GISデータ（Shape File）も
⼀括インポートできる
Shape File : GISデータのうち「ベクタ」形式の代表的なもの。 多くのデータがこれで配布されている
例 : 地球地図プロジェクト（⽇本のページ）
⼀つのZIPに15種類のShape File、約60ファイル
本題（2）PostGIS
+ PL/v8
+ ウィンドウ関数⾃作
+ SVGでの可視化
説明の前にデモ : ・⽇本地図の上で、都道府県間の⼈⼝移動を動的に表⽰するSVG ・地図 : 先ほどOGR_FDWでインポートしたうち、⾏政界 ・利⽤した統計 : 総務省統計局 e-stat の WebAPI （住⺠基本台帳⼈⼝移動報告）デモでやっていたこと
⼤枠 : ・データ抽出〜SVG出⼒まで、⼀連の処理をストアド（PL/pgSQL） にまとめて実⾏。 ・返った結果をCOPY⽂でそのままテキストファイルに保存。 → 単独で使えるSVGになる ストアド内の処理 1. 都道府県間の⼈⼝移動データを準備（必要な部分を抽出） 2. これを、PostGISで事前に作った都道府県間のパスと結合 3. SVGの属性（⼈⼝移動の円の⼤きさ、動作タイミング等）を設定 4. 座標系を設定してSVGとして地図化ポスグレならではの部分
（1）WebAPI で得た JSON データの処理
統計表の⽇本語（⾮アスキー）がユニコードエスケープ形式だったけど、 JSONBにキャストしたら⾃動的にデコードしてくれた 統計表がオブジェクトと配列の複雑な構造になっている。キーを中⼼に ざっと⾒るストアドを PL/v8 で作り、⾒通しが良くなった JSONの構造が分かり、どのデータを使うか決めれば、PostgreSQLの JSON関数で普通のテーブルにするのは簡単PL/v8ストアドの実⾏結果例
正常に終了しました。
総務省統計局のWebAPIデータの例
ポスグレならではの部分 （2）ウィンドウ関数でSVGの
アニメーション設定
SVGでは、各アニメーションにIDを付け、タグに begin = "前のアニメー ションID.end" を⼊れれば、「順番に動く動作」を実現できる 都道府県間の直線 円の半径 今回、アニメーション⼀つ⼀つ = 都道府県間の直線上の動き。 各直線に適当なIDを振り、ウィンドウ関数のlag()で⼀⾏前のIDを取ったPostGIS + PL/v8の連携について（1）
基本的には、PL/v8が扱える型はJavaScriptと同じ。当然ながら PostGIS のネイティブデータは扱えない。
しかしPL/v8のストアド関数を作る際、引数の型にはgeometryを使える。 実際にgeometryを渡すと、PL/v8の側では⽂字列として扱われる。
PostGIS + PL/v8の連携について（2）
この⽂字列は、PostGISデータ（バイナリ）の16進数表現。クエリ中で、 この⽂字列をPostGISのデータ型にキャストすると、ちゃんと復元される。
ということは、PL/v8のストアド内部で上のようにキャストを付け加え、 それをPostgreSQLへのクエリに使えばよい。例えば…
PostGIS + PL/v8の連携について（3）
PostGISデータを「そのまま使える」SVGにするため、
PL/v8のウィンドウ関数を作成（1）
PostGISのST_AsSVG関数は、⼀つのジオメトリから「SVGのPATH要素に ⼊れる座標群」を作成するだけ。そのままではSVGにならない ⼀つのジオメトリ対象でなく、集約関数のように「列をまるごと渡して」 そのまま使えるSVGにしたい。例えば ↓ のテーブルに対しウィンドウ関数作成（2）集約関数にしなかった理由
最初はSQL や PL/pgSQLで、集約関数として作ってみたが遅い。 ⽇本の全市区町村図（約3000ジオメトリ）で11.5秒。 引数の追加が柔軟にできない。 状態遷移関数（sfunc）－最終計算関数（finalfunc）－集約関数の三つと、 複数の値を遷移させるための複合型を修正しなければいけない。 ⼀⽅、ウィンドウ関数で作る際の注意点 通常は「全ての⾏を返す」のがウィンドウ関数。でも今回は ・先頭⾏の処理で全⾏のデータをスキャンしてSVGを作り、 ・⼆⾏⽬以降は何も返さない。 という動作にし、 ・実際使う時はクエリの最後に LIMIT 1 を付けることにした。実際の使⽤例
さらに「塗り分け」機能を追加して便利に
この値で自動的に色の濃度を付ける 色はここで自由に設定
