GIS を中心に多彩なデータ処理を PostgreSQL で国府田諭 Satoshi Koda このスライドは (kenpg myna で検索 )

Size: px

Start display at page:

Download "GIS を中心に多彩なデータ処理を PostgreSQL で国府田諭 Satoshi Koda このスライドは (kenpg myna で検索 )"

はすなてらわ
4 years ago
Views:

1 GIS を中心に多彩なデータ処理を PostgreSQL で国府田諭 Satoshi Koda このスライドは (kenpg myna で検索 )

2 今話したいこと 1 ポスグレ単体でなく PostgreSuite としての魅エンドユーザの分はこんなに夫して使ってます Spatial is not special! GIS( 地理情報システム ) は特殊なデータ処理じゃないですがリクエストがあったので昨年のカンファレンス (11/27) アンカンファレンス (12/12) から要点を少しだけ

3 今の本題に戻って具体的には (1) 2 PostgreSQL 9.5 の新機能 IMPORT FOREIGN SCHEMA に GIS 関連の外部データラッパ OGR_FDW が早速対応ローカルフォルダを仮想的なスキーマとして扱えるフォルダ内の GIS データを括インポート可能 ( テーブルや列をなくていい ) GIS だけでなく CSV のインポートも同様にできる!

4 具体的には (2) 3 エンドユーザの分はこんなに夫して使ってます最近とくに熱いのは PostGIS + PL/v8 + ウィンドウ関数作 + 直接 SVG に出 PostGIS の地理データ型を PL/v8 のストアド関数に渡すウィンドウ関数があるだけじゃない簡単に作できる GIS データのビュワーがない -> SVG に出してブラウザでる

5 具体的には (2) 後ほどデモ 4

6 今のスコープ / 本来の関事 5 データを料理に例えれば材料の調達仕込み調理盛り付けいただきますデータ整理分析可視化章化社会へアウトプット ( て楽しむ料理もアリだけど )

7 今のスコープ / 本来の関事 6 データを料理に例えれば材料の調達仕込み調理盛り付けいただきますデータ整理分析可視化章化社会へアウトプット ( て楽しむ料理もアリだけど )

8 本題の前に PostGIS & 周辺の簡単な紹介 7

9 よくある GIS と PostGIS の違い 8 GIS とは? 地理情報システム Geographic Information System エンドユーザにとってたいてい地図を作らないと始まらない Excel が表を作らないと始まらないのと同様 GIS データベース ( 空間データベース ) は違う地図でなくデータそのものを格納管理演算( 編集 ) する GUIでなくSQLが操作の中地図はアウトプットの形態にすぎないオープンソースでは : PostGIS, MySQL, SpatiaLite(SQLiteの拡張 )

10 PostGIS & 周辺について 9 PostGIS : PostgreSQL の機能拡張のつ基本はユーザが分でインストールする Amazon RDS や Postgres.app など最初からってるケースもあり追加されるのはデータ型関数コマンドラインツールなど PostGIS に対する拡張外部ツールも存在 pgrouting : 経路探索 ( カーナビ的なあれ ) pointcloud_postgis : 点群データ操作 (3 次元スキャナ的なあれ ) OGR_FDW : 様々な GIS データを外部テーブル化今の本題 (1)

11 OGR_FDW で CSV 括インポート (1) 10 インストール分は Windows 7 x64 + PostgreSQL で (PostGIS の Windows バイナリ配布サイトにある ) CSV 読み込みだけなら PostGIS がなくても OK CentOS では yum install ogr_fdw95 他の拡張と同様 CREATE EXTENSION で好きな DB にれる

12 PostGIS 公式サイト -> Download -> Windows にリンクがある 11

13 OGR_FDW で CSV(2) サンプルデータ準備 12 何でもいいけど WHO の結核に関するページから 8 個の CSV ファイル PostgreSQL のサーバから読める位置に適当なフォルダを作って置くあとはクエリで外部テーブルを置くスキーマを作る or 決める CREATE SERVER IMPORT FOREIGN SCHEMA 普通の FDW と少し違う ( 詳細は次 )

14 13

15 インポートできたテーブルのつ (26 列もある ) 14

16 クエリの要点補 15 フォルダ常にこの仮想的スキーマ名を使う動的に番号の列 fid が追加されるヘッダ ( 列名 ) がない場合列名は field_1, field_2,,, UTF-8 なら本語も OK SJIS は字化けするが ASCII 部分は読める

17 本語 (SJIS) を含む CSV をインポートしてみると 16 本語は字化けするが数値フィールドは使える嗘

18 OGR って何なのか 17 OpenGIS Simple Features Reference Implementation の略単体で存在するのでなく Geospatial Data Abstraction Library (GDAL) やたら種類が多い GIS データの相互変換のため CSV もそのつ

19 CSVと同様 GISデータ (Shape File) も括インポートできる 18 Shape File : GISデータのうちベクタ形式の代表的なもの多くのデータがこれで配布されている例 : 地球地図プロジェクト ( 本のページ )

20 19

21 つの ZIP に 15 種類の Shape File 約 60 ファイル 20 これも OGR_FDW と IMPORT FOREIGN SCHEMA で括インポート!

22 本題 (2)PostGIS + PL/v8 + ウィンドウ関数作 + SVGでの可視化 21 説明の前にデモ : 本地図の上で都道府県間の移動を動的に表するSVG 地図 : 先ほどOGR_FDWでインポートしたうち政界利した統計 : 総務省統計局 e-stat の WebAPI ( 住基本台帳移動報告 )

23 デモでやっていたこと 22 枠 : データ抽出 SVG 出まで連の処理をストアド (PL/pgSQL) にまとめて実返った結果をCOPY でそのままテキストファイルに保存単独で使えるSVGになるストアド内の処理 1. 都道府県間の移動データを準備 ( 必要な部分を抽出 ) 2. これを PostGISで事前に作った都道府県間のパスと結合 3. SVGの属性 ( 移動の円のきさ動作タイミング等 ) を設定 4. 座標系を設定してSVGとして地図化

24 ポスグレならではの部分 (1)WebAPI で得た JSON データの処理 23 統計表の本語 ( アスキー ) がユニコードエスケープ形式だったけど JSONBにキャストしたら動的にデコードしてくれた統計表がオブジェクトと配列の複雑な構造になっているキーを中にざっとるストアドを PL/v8 で作り通しが良くなった JSONの構造が分かりどのデータを使うか決めれば PostgreSQLの JSON 関数で普通のテーブルにするのは簡単

25 PL/v8 ストアド : JSON の構造をざっとる 24

26 PL/v8 ストアドの実結果例 25 正常に終了しました調査していないため該当数値がないもの

27 総務省統計局の WebAPI データの例 26 同じ統計 ( 住基本台帳移動 ) の構造がある年で変わったり

28 ポスグレならではの部分 (2) ウィンドウ関数でSVGのアニメーション設定 27 SVGでは各アニメーションにIDを付けタグに begin = " 前のアニメーションID.end" をれれば順番に動く動作を実現できる円の半径都道府県間の直線今回アニメーションつつ = 都道府県間の直線上の動き各直線に適当な ID を振りウィンドウ関数の lag() で前の ID を取った

29 PostGIS + PL/v8 の連携について (1) 28 基本的には PL/v8が扱える型はJavaScriptと同じ当然ながら PostGIS のネイティブデータは扱えないしかしPL/v8のストアド関数を作る際引数の型にはgeometryを使える実際にgeometryを渡すと PL/v8の側では字列として扱われる

30 PostGIS + PL/v8 の連携について (2) 29 この字列は PostGISデータ ( バイナリ ) の16 進数表現クエリ中でこの字列をPostGISのデータ型にキャストするとちゃんと復元されるということは PL/v8のストアド内部で上のようにキャストを付け加えそれをPostgreSQLへのクエリに使えばよい例えば

31 PostGIS + PL/v8 の連携について (3) 30 PL/v8 ストアドで PostGIS データを受け取って積を返す例

32 PostGISデータをそのまま使える SVGにするため PL/v8のウィンドウ関数を作成 (1) 31 PostGISのST_AsSVG 関数はつのジオメトリから SVGのPATH 要素にれる座標群を作成するだけそのままではSVGにならないつのジオメトリ対象でなく集約関数のように列をまるごと渡してそのまま使えるSVGにしたい例えばのテーブルに対し select make_svg(geom) from mytable; とか

33 ウィンドウ関数作成 (2) 集約関数にしなかった理由 32 最初はSQL や PL/pgSQLで集約関数として作ってみたが遅い本の全市区町村図 ( 約 3000ジオメトリ ) で11.5 秒引数の追加が柔軟にできない状態遷移関数 (sfunc)- 最終計算関数 (finalfunc)- 集約関数の三つと複数の値を遷移させるための複合型を修正しなければいけないウィンドウ関数で作る際の注意点通常は全てのを返すのがウィンドウ関数でも今回は先頭の処理で全のデータをスキャンしてSVGを作り以降は何も返さないという動作にし実際使う時はクエリの最後に LIMIT 1 を付けることにした

34 実際の使例 33 さらに塗り分け機能を追加して便利に色はここで自由に設定この値で自動的に色の濃度を付ける

35 最後に & 今後やりたいこと 34 空間データベースは地図ありきでなくデータとをより近づけるオープンソースの空間 DB 全体がもっと普及してほしいというわけでMySQLの今後に期待してます :) 今後分としては PL/v8 体で PostGIS データの処理とくにラスタ ( ビットマップ ) SVG で地図だけでなくグラフ ( チャート ) も PostgreSQL から出試錯誤がつきもののデータ分析に合う統合的な環境の模索とか出来たらいいなぁ! 今後ともよろしくお願いします m(_)m

PowerPoint プレゼンテーション

PowerPoint プレゼンテーション JAN コード登録マニュアル項目説明 CSV で商品データを upload するに当たり間違えやすいカラムについてまとめました項目説明備考コントロールカラム CSV 上で当該商品情報をどうするのか ( 更新削除等 ) 指示するコード "u": 更新 "d": 削除等商品管理番号出来上がった商品ページURLの一部であり入力がない場合は自動採番される web 上で商品を特定するキーコード

GIS を中心に 多彩なデータ処理を PostgreSQL で 国府田諭 Satoshi Koda このスライドは (kenpg myna で検索 )

GIS を中心に多彩なデータ処理を PostgreSQL で国府田諭 Satoshi Koda このスライドは (kenpg myna で検索 )