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