⇧ 夏の怪談じゃないけども、怖いですね...
PostgreSQLで地理情報を扱ってみる
前回、
⇧ PostgreSQLの拡張機能「PostGIS」というものを導入し、PostgreSQL で地理情報に関するデータを扱えるように準備しました。
今回は、実際に、PostgreSQL で地理情報を扱ってみたいと思います。
日本PostgreSQLユーザ会の公開している情報によると、
⇧ PostgreSQLのデータ型としては、上記のようなものがあるらしく、
- 数値型
- 貨幣型
- 文字データ型
- 日付/時刻型
- ブール値型
- 地理データ型
- インターネットプロトコル バージョン4 上の ネットワークアドレスとホストアドレス
⇧ の7つに分類されるらしい。
で、拡張機能「PostGIS」をインストールすることで利用できるようになるのが、「6.地理データ型」ということらしい。
ちなみに、
⇧ 公式のドキュメントによると、
- 8.1. Numeric Types
- 8.2. Monetary Types
- 8.3. Character Types
- 8.4. Binary Data Types
- 8.5. Date/Time Types
- 8.6. Boolean Type
- 8.7. Enumerated Types
- 8.8. Geometric Types
- 8.9. Network Address Types
- 8.10. Bit String Types
- 8.11. Text Search Types
- 8.12. UUID Type
- 8.13. XML Type
- 8.14. JSON Types
- 8.15. Arrays
- 8.16. Composite Types
- 8.17. Range Types
- 8.18. Domain Types
- 8.19. Object Identifier Types
- 8.20. pg_lsn Type
- 8.21. Pseudo-Types
⇧ の21つに分類されるように見えて、ちょっと何が正解なのかが分からない...
とりあえず、「地理情報」は、「6.地理データ型」ないし「8. 8.8.Geometric Type」のデータ型を指定したカラムを含めたテーブルを作成する必要があるという認識で良いんかな？
拡張機能「PostGIS」のドキュメントによると、「Examples of creating tables with geometry columns:」で5つほど、geometry型のカラムを含めたテーブル作成の例が載っているのだけど、
The geometry type supports two optional type modifiers:
-
the spatial type modifier restricts the kind of shapes and dimensions allowed in the column. The value can be any of the supported geometry subtypes (e.g. POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION, etc). The modifier supports coordinate dimensionality restrictions by adding suffixes: Z, M and ZM. For example, a modifier of 'LINESTRINGM' allows only linestrings with three dimensions, and treats the third dimension as a measure. Similarly, 'POINTZM' requires four dimensional (XYZM) data.
-
the SRID modifier restricts the spatial reference system SRID to a particular number. If omitted, the SRID defaults to 0.
https://postgis.net/docs/using_postgis_dbmanagement.html#idm2063
⇧「空間参照系識別コード（SRID：Spatial Reference System IDentifier）」なるものを指定してあげる必要がありそうですと。
SRIDとSRS=CRS=空間参照系
SRIDは多数あるSRS (Spatial Reference System)の一つ一つを識別するコードです。
CRS (Coordinate Reference System)とSRSは同じと考えてください。また、空間参照系はCRSの訳語です。以上より、CRSとSRSと空間参照系は同じで、SRIDはSRS=CRS=空間参照系を識別するためのコードである、と言えます。
⇧ とあるように、「SRS（Spatial Reference System）」を識別するための「ID（Identifier）」が「空間参照系識別コード（SRID：Spatial Reference System IDentifier）」ということらしい。
「SRS（Spatial Reference System）」はと言うと、
A spatial reference system (SRS) or coordinate reference system (CRS) is a framework used to precisely measure locations on the surface of the Earth as coordinates. It is thus the application of the abstract mathematics of coordinate systems and analytic geometry to geographic space.
⇧ 正確な「座標」を算出してくれるフレームワークらしい。
「空間参照系識別コード（SRID：Spatial Reference System IDentifier）」は、
コンピュータで地図を扱おうとする際に、空間参照系（Coordinate system）、測定単位（Unit）、測地CRS（Geodetic CRS）、基礎データ（Datum）、地球楕円体（Ellipsoid）、子午線（Prime meridian）など、たくさんの設定値を扱う必要があります。個別に扱うのは大変なので、これらをセットとして、IDを発番し、多くの地理情報システム（GIS）ではこれを使用しています。
概念的には、空間参照ID、英語ではSRID（Spatial Reference System Identifiers）と呼ばれるもので、事実上の標準として、EPSGコードが使用されています。EPSGは、the European Petroleum Survey Group（欧州石油調査グループ）の略称です。
⇧「EPSGコード」が利用されてるらしい。
⇧ 上記サイト様で紹介されてる、日本でよく利用される「EPSGコード」の数が、
| 座標系の名前 | EPSGコードの数 | |
|---|---|---|
| 日本測地系2011（JGD2011） | 平面直角座標系 ※1 | 19 |
| UTM（ユニバーサル横メルカトル）座標系 | 5 | |
| 地理座標系（緯度経度）※2 | 1 | |
| 日本測地系2000（JGD2000） | 平面直角座標系 ※3 | 19 |
| UTM（ユニバーサル横メルカトル）座標系 | 5 | |
| 地理座標系（緯度経度） ※4 | 1 | |
| WGS84 | 地理座標系（緯度経度） | 1 |
| Webメルカトル（Pseudo-Mercator） | 1 | |
※1 <表記例（Ⅶ系の場合）>
- JGD2011/7(X,Y)
- JGD2011平面直角座標系第Ⅶ
- 日本測地系2011における平面直角座標系第Ⅶ系
※2 <表記例>
※3 <表記例（Ⅶ系の場合）>
- JGD2000/7(X,Y)
- JGD2000平面直角座標系第Ⅶ
- 日本測地系2000における平面直角座標系第Ⅶ系
※4 <表記例>
となってるので、テーブルのカラムは扱う「EPSGコード」の数だけ必要になるってことかね？
作成済みのテーブルのカラムのデータ型で確認すれば、どの「EPSGコード」のデータを扱っているか分かるってことかしら？
だいぶ、脱線しましたが、
⇧ 上記サイト様によりますと、拡張機能「PostGIS」の「shp2pgsql」というコマンドを利用すると、「シェープファイル形式」のファイルからテーブルとか作成できるようです。
Wikipediaさんによりますと、
⇧ とのこと。
このサイトでは、地形、土地利用、公共施設などの国土に関する基礎的な情報をGISデータとして整備し、無償で提供しています。
⇧ GISデータを公開してくれてるので、ダウンロードしてみることに。「洪水浸水想定区域（1次メッシュ）データ」を選択しました。
⇧ 「座標系」が「JGD2011 / （B, L）」となってるので、
| 座標系の名前 | EPSGコード | |
|---|---|---|
| 日本測地系2011（JGD2011） | 地理座標系（緯度経度） | 6668 |
⇧ の「EPSGコード」を利用するということかと。
⇧ 対応してるファイルのフォーマットとしては、
- GML形式
- シェープ形式
- GEOJSON形式
の3つらしい。
「シェープ形式」をダウンロードしました。
「WSL 2（Windows SubSystem for Linux 2）」のUbuntuを起動し、WinSCPなどで、ダウンロードしたzipファイルをPostgreSQL（拡張機能「PostGIS」が有効になっている）がインストールされてるUbuntu環境へ配置します。
国土交通省のサイトからダウンロードした「シェープ形式」のzipファイルを配置。
そしたらば、新たにコマンドプロンプトを起ち上げて、UbuntuへSSHログイン。
配置したzipファイルの場所へ移動。
zipファイルを展開（解凍）します。
壮絶な文字化けが起こっているんだが...
分からんけど、文字化けが起こっていないフォルダに配置されてた「.shp」ファイルを利用してみたいと思います。
まずは、「CREATE TABLE」のSQL文を生成します。
shp2pgsql -p -s 6668 -W cp932 A31-40-22_10_5238.shp flood_assumed_area_shizuoka > flood_assumed_area_shizuoka.sql
生成されたSQLファイルの中身は、以下のようになっていました。
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE "flood_assumed_area_shizuoka" (gid serial,
"a31_401" int2);
ALTER TABLE "flood_assumed_area_shizuoka" ADD PRIMARY KEY (gid);
SELECT AddGeometryColumn('','flood_assumed_area_shizuoka','geom','6668','MULTIPOLYGON',2);
COMMIT;
ANALYZE "flood_assumed_area_shizuoka";
続いて、INSERTするSQL文を生成。
shp2pgsql -a -s 6668 -W cp932 A31-40-22_10_5238.shp flood_assumed_area_shizuoka > insert_flood_assumed_area_shizuoka.sql
ファイル中身は、以下のようになってました。行数が多過ぎたので、途中省略してます。
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
INSERT INTO "flood_assumed_area_shizuoka" ("a31_401",geom) VALUES ('1','01060000200C1A0000010000000103000000010000000C000000B3CF5E9F73576140EF3141417B92414034333333735761401CDF063A6D924140CCC10F607257614086D7DFB76B92414084B37124715761407802BC976B924140640D51C96F576140273FF6BC6B924140D00AE8E16E5761403AE24CE16B9241404AE17A146E5761401CDF063A6D92414023DD887F6D576140168E518C7C92414094A712546E57614023B2535A7C92414000D3806670576140369D1FEA7B92414042F697D9725761400D065E727B924140B3CF5E9F73576140EF3141417B924140');
...省略
INSERT INTO "flood_assumed_area_shizuoka" ("a31_401",geom) VALUES ('3','01060000200C1A0000010000000103000000010000000B0000003BDF4F8D174361407D3F355EBA6141403BDF4F8D174361400A647616BD6141403BDF4F8D1743614068041BD7BF6141406ABC74931843614068041BD7BF6141406ABC749318436140F6285C8FC26141409A99999919436140F6285C8FC26141409A9999991943614068041BD7BF6141409A999999194361400A647616BD6141406ABC7493184361400A647616BD6141406ABC7493184361407D3F355EBA6141403BDF4F8D174361407D3F355EBA614140');
COMMIT;
ANALYZE "flood_assumed_area_shizuoka";
そしたらば、テーブル作成のSQLファイルから実行。
sudo -u postgres psql -d sample < flood_assumed_area_shizuoka.sql
続いて、作成したテーブルにINSERTのSQLファイルを実行。
sudo -u postgres psql -d sample < insert_flood_assumed_area_shizuoka.sql
ついでに、PostgreSQLの作成していたユーザー権限を追加しておく。
sudo -u postgres psql -d sample
GRANT ALL ON ALL TABLES IN SCHEMA public TO dev_web;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO dev_web;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO dev_web;
とりあえず、geometryカラムのテーブルの作成ができて、
データも入ったっぽい。
「空間参照系識別コード（SRID：Spatial Reference System IDentifier）」が反映されているのかが分からん...
psqlで直にデータベースに接続してテーブルのカラムを確認したところ、
⇧「空間参照系識別コード（SRID：Spatial Reference System IDentifier）」反映されてるっぽい。自分の使っている「A5:SQL Nk-2」が旧くて、geometry型の細かい部分に対応してないだけなのか分からんけど、反映はされていたということで。
何やら、
⇧「spatial_ref_sys」テーブルにもデータがINSERTされてるけど、カラムsridの値が、「EPSGコード」の「6668」と関係ないのも入ってるんだけど...
The spatial_ref_sys table is a PostGIS included and OGC compliant database table that lists over 3000 known spatial reference systems and details needed to transform/reproject between them.
Although the PostGIS spatial_ref_sys table contains over 3000 of the more commonly used spatial reference system definitions that can be handled by the proj library, it does not contain all known to man and you can even define your own custom projection if you are familiar with proj4 constructs. Keep in mind that most spatial reference systems are regional and have no meaning when used outside of the bounds they were intended for.
An excellent resource for finding spatial reference systems not defined in the core set is http://spatialreference.org/
⇧「spatial_ref_sys」テーブルのレコードについては、拡張機能「PostGIS」がデフォルトで用意してるデータってことか...追加とかもできると...
一応、
⇧ 「EPSGコード」が6668のレコードも用意されてました。
う～む、地理情報、分からんことが多いっすな...
ちなみに、今回、利用したデータは、
⇧ 日本のオリジナルなんかね？
どういう仕様になっていて、どういう利用の仕方をすれば良いかがサッパリなので、余計に扱い方が分からん...
毎度モヤモヤ感が半端ない...
今回はこのへんで。
2023年8月2日（水）21:20 追記：↓ ここから
どうやら、階層構造のディレクトリを圧縮しているファイルの場合、
⇧ unzipを繰り返し実行する必要があったみたい。
あと、文字コードは、utf8にする必要があったみたい、国土交通省やってくれるやないかい...
find . -name "*.zip" | while read filename; do unzip -O utf8 -d "`basename "$filename" .zip`" "$filename"; done;
⇧ それぞれのフォルダに、「.shp」ファイルが配置されてるっぽいから、「40_家屋倒壊等氾濫想定区域/A31-40-22_10_5238.shp」で行ったのと同じ様にテーブル作って、データをINSERTする感じになるんかね。
2023年8月2日（水）21:20 追記：↑ ここまで
