3. 本日の配布データは以下になります
? フォルダ：docs
? PostGIS入門.pdf (本文書)
? PostGIS 2.2.0devマニュアル日本語訳.pdf
? PostgreSQL&PostGISのインストール.pdf (事前準備マニュアル)
? QGIS2.2_Install.pdf (事前準備マニュアル)
? フォルダ：data
? N01-07L-01-01.0a_GML.zip、N03-130401_01_GML.zip、P02-
06_01_GML.zip(国土数値情報データ。次ページで説明)
? hinan_20140623.csv (室蘭市避難所一覧CSV。次ページで説明)
? muroran_hinan_csv.sql (避難所CSVのインポートSQL)
? フォルダ：shapefiles
? 前記zipを展開したデータ
? フォルダ：tools
? 使用しない予定ですが、インストール漏れなどの際にお使いください
配布データについて
FOSS4G 2014 Hokkaido PostGIS入門 3
4. 本ハンズオンは事前に以下の準備を行ってください
? PostgreSQL & PostGISのインストール
? QGISのインストール
? 国土数値情報からShapefileを取得する(北海道地区)
? 行政区域(H25) N03-130401_01_GML.zip
? 公共施設(H18) P02-06_01_GML.zip
? 道路(統一フォーマット H7) N01-07L-01-01.0a_GML.zip
? むろらんオープンデータライブラリから次のデータを取得する
? 避難場所 CSV (hinan_20140623.csv)
← 講習ではSQLファイルで配布します
事前準備について
FOSS4G 2014 Hokkaido PostGIS入門 4
8. PostGISについて学ぶにはここをチェック
? PostGISオフィシャルサイト(Refractions Research)
? http://www.refractions.net/products/postgis/
? PostGISマニュアル日本語訳(農研機構)
? http://www.finds.jp/docs/pgisman/
? PostgreSQLオフィシャルサイト
? http://www.postgresql.org/
? 日本PostgreSQLユーザ会
? https://www.postgresql.jp/
? Linuxのパッケージは http://postgis.net/install/ を参照
(標準パッケージは古いことが多々あるようです)
? WindowsはOSGeo4W(http://trac.osgeo.org/osgeo4w/)でGDAL/OGRも使えるように
すると便利
PostGISについて学ぶ
FOSS4G 2014 Hokkaido PostGIS入門 8
9. 主なDBMSのGIS拡張
? PostGIS
? PostgreSQL標準の幾何データ対応
? SpatiaLite(スペイシャライト、ファイルDBであるSQLiteの拡張)
? MySQL(5.6) OpenGIS対応
? Oracle Spatial(スペイシャル)
? SQL Server Spatialサポート
? ArcGIS ジオデータベース
主なGISファイル形式
? ESRI Shapefile(デファクトスタンダード)
? MapInfo(TAB、MIF/MID)
? GeoJSON(JavaScriptのオブジェクト配列)
? GML、KML(XMLの拡張)
DBMSとGIS
FOSS4G 2014 Hokkaido PostGIS入門 9
10. PostGIS(PostgreSQL)のメリット
? オープンソース(GPL)
? 標準規格への対応(OpenGIS Consortium、標準SQL)
? マルチプラットフォーム
? ネットワーク共有が簡単便利(TCP/IP、トランザクショナル)
? 豊富な連携ソフトウェア(GDAL/OGRなど)
? 情報?知見が得やすい
? 拡張性、可用性、保守性に優れる
☆つまり使いやすい
PostGIS(PostgreSQL)のデメリット
? ファイルベースのデータは手軽(インストール、運用、データコピーetc)
? サーバに負荷がかかり易い
PostGIS(GIS DB)を使うべき理由
FOSS4G 2014 Hokkaido PostGIS入門 10
11. スタンダードフォーマットであるShapefileとの比較
Shapefileとの比較
FOSS4G 2014 Hokkaido PostGIS入門 11
PostGIS Shapefile
サイズ制限 十分に大きい .shp(形状)と.dbf(属性)は2GBまで
座標点数制限 十分に大きい 7,000万ポイントが目安(2GB)
フィールド名制限 63バイト 10バイト
フィールド数制限 十分に大きい 255
文字列属性 TEXT、VARCHAR2など 固定長のみ(無駄が多い)
文字コード(日本語) 内部はUTF-8、EUC_JP
Shift-JIS(cp932)はクライアントのみ
Shift-JISが推奨、UTF-8のサポートが拡
がるが...
※本来はANSI
空間インデックス サポート(汎用検索ツリー) サポート(性能は良くないとされる、アプ
リ毎に異なるファイル)
共有アクセス 同テーブルへの読み書き可能
トランザクション、ロックが可能
高度で安全な共有は期待出来ない
12. 図形タイプ(ジオメトリタイプ)
? (シングル?シンプル)ポイント、ライン、ポリゴン
? マルチポイント、マルチライン、マルチポリゴン
? ジオメトリコレクション、TIN、多面体サーフェイス
空間参照系(SRID)
? 座標系、測地系、投影法、ジオイドなどの定義
? SRIDはQGISなどと共通
? spatial_ref_sysテーブルに格納されている
WKT(Well-Known Text)形式
? ベクタ図形情報をテキスト形式で表現するフォーマット
? OpenGIS Consortium(OGC)による標準定義
? DB(ジオメトリ)にはバイナリ形式(WKB)で格納されている
PostGISで使われる用語
FOSS4G 2014 Hokkaido PostGIS入門 12
24. ちなみにPostGIS 1.xでは...
? AddGeometryCoumn関数は後方互換のため残されている
? ツールによっては、まだこちらが使われている
? ツールが古いなどの理由で互換性が必要な場合、PostGISに同梱されているlegacy.sqlを実行
する。
テーブルの作成(PostGIS 1.x)
FOSS4G 2014 Hokkaido PostGIS入門 24
CREATE TABLE sample_point (
id SERIAL PRIMARY KEY
);
SELECT AddGeometryColumn ('sample_point', 'the_geom', 4612, 'POINT', 2);
25. よく使われる空間参照系のSRID一覧
よく使われるSRID一覧
FOSS4G 2014 Hokkaido PostGIS入門 25
測地系 座標系 SRID
日本測地系
緯度経度 4301
UTM座標系 51N - 55N 102151 - 102156
平面直角座標系 1 - 19系 30161 - 30179
世界測地系
JGD2000
緯度経度 4612
UTM座標系 51N - 55N 3097 - 3101
平面直角座標系 1 - 19系 2443 - 2461
WGS84 緯度経度 4326
Google 球体メルカトル(m) 900913
3857 (EPSG)
26. INSERT文で図形を作成してみます
? 文字列 'POINT(141.347 43.071)' がWKT
? 対になっているXとYはスペースで区切る
? [geom]の出力はバイナリ形式(普通の人には読めない)
? ST_asText関数により読みやすい形式にする
図形の作成
FOSS4G 2014 Hokkaido PostGIS入門 26
INSERT INTO sample_point (geom)
VALUES (
ST_GeomFromText('POINT(141.347 43.071)', 4612)
);
SELECT geom FROM sample_point;
SELECT ST_asText(geom) FROM sample_point;
空間参照系を指定するテキストをジオメトリ型に変換
27. ラインジオメトリを持つテーブルを作成します
図形の作成
FOSS4G 2014 Hokkaido PostGIS入門 27
CREATE TABLE sample_line (
id SERIAL PRIMARY KEY,
geom GEOMETRY (LINESTRING, 4612)
);
INSERT INTO sample_line (geom)
VALUES (
ST_GeomFromText('LINESTRING(
141.347 43.071,
141.349 43.075,
141.342 43.075,
141.345 43.071
)', 4612)
); 頂点の区切りはカンマ(,)
28. ポリゴンジオメトリを持つテーブルを作成、図形を作成します
図形の作成
FOSS4G 2014 Hokkaido PostGIS入門 28
INSERT INTO sample_polygon (geom)
VALUES (
ST_GeomFromText('POLYGON(
(
141.347 43.071, 141.349 43.075, 141.342 43.075, 141.345 43.071,
141.347 43.071
)
)', 4612)
);
始点終点は一致させる(閉じる)
CREATE TABLE sample_polygon (
id SERIAL PRIMARY KEY,
geom GEOMETRY (POLYGON, 4612)
);
INSERT INTO sample_polygon (geom)
VALUES (
ST_GeomFromText('POLYGON(
(141.353 43.072, 141.345 43.070, 141.351 43.076,
141.353 43.072),
(141.351 43.072, 141.351 43.073, 141.350 43.073, 141.351 43.072)
)', 4612)
);
穴あきポリゴン
ポリゴンは()が増える
右回り?左回りどちらでもよい
29. ジオメトリ出力関数で図形を確認してみます
図形を確認する(ジオメトリ出力)
FOSS4G 2014 Hokkaido PostGIS入門 29
SELECT id, ST_asText(geom) FROM sample_polygon;
→ 1, POLYGON((141.347 43.071,141.349 43.075, ...))
→ 2, POLYGON((141.347 43.071,...),(141.347 43.072,...))
SELECT ST_asKML(geom) FROM sample_line;
→ <LineString><coordinates>141.347,... </coordinates></LineString>
WKT表現(ポピュラー)
KML、GeoJSON表現(ジオアプリ向け)
SELECT ST_asGeoJSON(geom) FROM sample_line;
→"{"type":"LineString","coordinates":[141.347,43.071], ...[141.345,43.071]]}
KML、GeoJSONとも属性値は含められないのでogr2ogrや自作プログラムで対処する
30. ジオメトリアクセサ関数で図形の構成を確認します
図形を確認する(ジオメトリアクセサ)
FOSS4G 2014 Hokkaido PostGIS入門 30
SELECT ST_X(geom), ST_Y(geom), ST_SRID(geom) FROM sample_point;
→ 141.347, 43.071, 4612
座標を取り出す
SELECT ST_asText(ST_PointN(geom, 3)) FROM sample_line;
→ POINT(141.342 43.075)
N番目の図形を取り出す
SELECT ST_NRings(geom), ST_X(ST_StartPoint(ST_ExteriorRing (geom)))
FROM sample_polygon;
→1, 141.347
→2, 141.347
ポリゴンは少し複雑です
始点:1から始まる
外環(LINESTRING)始点(Start Point)
35. XY列をポイントジオメトリに変換、更に緯度経度に変換します
1. 数値をポイントジオメトリに変換し、空間参照系を付与する
2. JGD2000緯度経度に投影変換する
3. 今のテーブルにジオメトリ追加する
4. ジオメトリ属性をアップデート
XYからジオメトリ(投影変換)
FOSS4G 2014 Hokkaido PostGIS入門 35
SELECT ST_setSRID(ST_MakePoint(x, y), 2454) FROM muroran_hinan_csv;
平面直角(12)空間参照系の付与
SELECT ST_Transform(ST_setSRID(ST_MakePoint(x, y), 2454), 4612)
FROM muroran_hinan_csv;
緯度経度座標変換(投影変換)
ALTER TABLE muroran_hinan_csv ADD geom GEOMETRY(POINT, 4612);
UPDATE muroran_hinan_csv
SET geom = ST_Transform(ST_setSRID(ST_MakePoint(x, y), 2454), 4612);
39. 1. "C:Program FilesPostgreSQL9.3pg_env.bat" (を実行)
2. shp2pgsql
3. cd <配布のShapefilesフォルダ>
4. shp2pgsql -p -I -S -s 4612 -W cp932 N03-13_01_130401 polygon_adm
-p Prepareモード
-I 空間インデックス作成
-S シンプルジオメトリで定義(デフォルトはマルチジオメトリ)
-s SRID ShapefileのSRID指定
-W encoding Shapefileの文字コード(通常のShapefileはcp932 or UTF-8)
5.shp2pgsql -c -D -I -S -s 4612 -W cp932 N03-13_01_130401 polygon_adm > polygon_adm.sql
-c Createモード(デフォルト)
-D データはダンプ形式(指定しないとINSERT文になるので遅い)
6. psql -U demo -d handson -f polygon_adm.sql
施設と道路データもインポートする
7. shp2pgsql -c -D -I -S -s 4612 -W cp932 P02-06_01-g_PublicFacility point_public | psql -U
demo -d handson
8. shp2pgsql -c -D -I -S -s 4612 -W cp932 N01-07L-2K-01_Road line_road | psql -U demo -d
handson
?-p,-cの代わりに-aで追記モードになります。
同じテーブルに異なるファイルの内容を次々に入力したい場合に便利です。
?Linux(OSGeo4W)も全く同じ使用方法です
shp2pgsqlの使い方(コマンドプロンプト)
FOSS4G 2014 Hokkaido PostGIS入門 39
ファイル名を指定
.shpは省略しても良い
テーブル名
インデックスについては後ほど
文字コード変換(SJIS→UTF8)
パイプラインで
渡しても良い
41. ? PostGISからShapefileをエクスポートするには pgsql2shp が良く使われます
? テーブルまたはビューを指定、あるいはSQLクエリ()が利用できます
? 文字コードはクライアントのエンコーディングに依存します(注意が必要)
? テーブル/ビューをエクスポート
1. SET PGCLIENTENCODING=SJIS
2. pgsql2shp -u demo handson point_public
? SQLクエリを利用
? pgsql2shp -u demo -f asahikawa handson "select * from polygon_adm where
n03_007 = '01204'"
pgsql2shpの使い方(エクスポート)
FOSS4G 2014 Hokkaido PostGIS入門 41
SETしないエクスポートも
試してみましょう
データベースとテーブル名を指定
ファイル名を指定
データベースとSQLクエリを指定旭川市の行政コード
43. 前セクションでインポートした国土数値情報の簡単な説明
テーブル定義
FOSS4G 2014 Hokkaido PostGIS入門 43
行政区域
n03_001 都道府県名
n03_002 支庁名(市はNULL)
n03_003 郡?政令市名
n03_004 市区町村名
n03_007 行政区域コード(5桁)
公共施設
p02_001 行政区域コード
p02_002 公共施設大分類
p02_003 公共施設小分類
p02_004 名称
p02_005 所在地
P02_006 管理者コード
p02_007 原典資料名
道路
n01_001 道路種別コード
n01_002 路線名
n01_003 線名
n01_004 通称
44. PostGISを利用することにより、データベースで行える通常の属性検索に加えて、豊富な空間検索が利
用できます。
? 属性により札幌市の各区を検索し、面積を計算する
? 札幌市内の郵便局を区毎に数える(図形的に)
PostGISの検索機能
FOSS4G 2014 Hokkaido PostGIS入門 44
SELECT n03_003, n03_004, n03_007,
ST_Area(ST_Transform(geom, 2454)),
ST_Area(ST_Transform(geom, 32654))
FROM polygon_adm
WHERE n03_003 = '札幌市';
2454:平面直角(12)
32654:UTM54
SELECT
n03_004,
count(*)
FROM polygon_adm a, point_public p
WHERE n03_003 = '札幌市'
AND st_contains(a.geom, p.geom)
AND p.p02_002 = '18'
GROUP BY n03_004
郵便局:p02_002 = '18'
st_contains(a, b)
aがbを完全に含むならTRUE
45. 空間インデックスの有無による検索速度を体験してみます
1. ○○町にある郵便局をカウント
2. 空間インデックスを削除
3. ふたたび1.で検索すると遅くなっているはず
4. 空間インデックスを再作成
5. ふたたび1.で検索する
6. 1.のSELECTの前にEXPLAIN句をつけて、2.～5.を実行してみる
空間インデックスの効果を体験する
FOSS4G 2014 Hokkaido PostGIS入門 45
SELECT count(*)
FROM polygon_adm a, point_public p
WHERE n03_004 like '%町' AND st_contains(a.geom, p.geom) AND p.p02_002 = '18'
DROP INDEX point_public_geom_gist;
DROP INDEX polygon_adm_geom_gist;
CREATE INDEX point_public_geom_gist ON point_public USING GiST (geom);
CREATE INDEX polygon_adm_geom_gist ON polygon_adm USING GiST (geom);
EXPLAIN SELECT count(*)
FROM polygon_adm a, point_public p
WHERE n03_004 like '%町' AND st_contains(a.geom, p.geom) AND p.p02_002 = '18'
47. ? 図形を合成してみます。このSQLそれぞれ結果が1件のみになります(合成の結果)
? ST_Collect()とST_Union()の違い
図形の集合
FOSS4G 2014 Hokkaido PostGIS入門 47
SELECT n03_003, ST_Collect(geom)
FROM polygon_adm
WHERE n03_003 = '札幌市'
GROUP BY n03_003;
集合条件の指定
SELECT n03_003, ST_Union(geom)
FROM polygon_adm
WHERE n03_003 = '札幌市'
GROUP BY n03_003;
CREATE VIEW collect_polygon as
SELECT max(id) id, ST_Collect(geom)
FROM sample_polygon;
CREATE VIEW union_polygon as
SELECT max(id) id, ST_Union(geom)
FROM sample_polygon;
重複部の座標はそのまま 重複部の座標は無くなる
48. ある領域に含まれる部分だけを切り出してみます
? 札幌市の行政界線で道路を切り出します
1. line_roadテーブルは行政界で分割されているので、路線毎にマージされた作業用テーブルを作成
します。
2. ジオメトリ間の共有部分を求めるビュー(テーブル)を作成し、Shapefileにエクスポートします。
図形の切り出し
FOSS4G 2014 Hokkaido PostGIS入門 48
CREATE TABLE union_road AS
SELECT n01_002 roadname,
(ST_Dump(ST_LineMerge(ST_Union(geom)))).Geom geom
FROM line_road
GROUP BY n01_002;
定型文として覚えておくと便利
ジオメトリが、シンプル→マルチ→マルチ→シンプルと変換される
CREATE INDEX union_line_geom_gist ON union_road USING GiST (geom);
空間インデックスも忘れずに
CREATE VIEW sapporo_road AS
SELECT l.roadname, p.n03_004,
ST_Intersection(p.geom, l.geom) geom
FROM polygon_adm p, union_road l
WHERE p.geom && l.geom
AND St_Intersects(p.geom, l.geom)
AND n03_003 = '札幌市';
QGISはなぜか
エラーになってしまうので???
&&をつけると(少し!?)速くなる
50. ? 外部データのインポート/エクスポートを頻繁に行うならLinuxがお勧めです
(Windows + GUIでやってみたら結構大変でした???)
? OSGeo4Wというパッケージを使うとWindowsでもFOSS4GツールがCUIで利用出来ます
? PostGISマニュアル日本語訳は日本特有の問題点、Tipsについても追記されているので
教科書として最適です(農研機構様に感謝)
ハンズオンは以上です
お疲れ様でした!
終わりに
FOSS4G 2014 Hokkaido PostGIS入門 50
![INSERT文で図形を作成してみます
? 文字列 'POINT(141.347 43.071)' がWKT
? 対になっているXとYはスペースで区切る
? [geom]の出力はバイナリ形式(普通の人には読めない)
? ST_asText関数により読みやすい形式にする
図形の作成
FOSS4G 2014 Hokkaido PostGIS入門 26
INSERT INTO sample_point (geom)
VALUES (
ST_GeomFromText('POINT(141.347 43.071)', 4612)
);
SELECT geom FROM sample_point;
SELECT ST_asText(geom) FROM sample_point;
空間参照系を指定するテキストをジオメトリ型に変換](https://image.slidesharecdn.com/foss4g-hokkaido-2014-handson-postgis-140630192604-phpapp01/85/FOSS4G-2014-Hokkaido-PostGIS-26-320.jpg)
![ジオメトリ出力関数で図形を確認してみます
図形を確認する(ジオメトリ出力)
FOSS4G 2014 Hokkaido PostGIS入門 29
SELECT id, ST_asText(geom) FROM sample_polygon;
→ 1, POLYGON((141.347 43.071,141.349 43.075, ...))
→ 2, POLYGON((141.347 43.071,...),(141.347 43.072,...))
SELECT ST_asKML(geom) FROM sample_line;
→ <LineString><coordinates>141.347,... </coordinates></LineString>
WKT表現(ポピュラー)
KML、GeoJSON表現(ジオアプリ向け)
SELECT ST_asGeoJSON(geom) FROM sample_line;
→"{"type":"LineString","coordinates":[141.347,43.071], ...[141.345,43.071]]}
KML、GeoJSONとも属性値は含められないのでogr2ogrや自作プログラムで対処する](https://image.slidesharecdn.com/foss4g-hokkaido-2014-handson-postgis-140630192604-phpapp01/85/FOSS4G-2014-Hokkaido-PostGIS-29-320.jpg)
