postGIS環境作り直し方(バラバラに記載して、訳が分からなくなってエライ目にあった)

2020年6月28日

■全部のDockerを止める
C:\Users\ebata>docker ps -aq | xargs docker stop

■全部のDockerを消す
C:\Users\ebata>docker ps -aq | xargs docker rm (これやると、これまでの設定も全滅するので注意)

(ちなみに2つを纏めてやる方法は、>docker prune と教えて貰いました )

■作りなおす
C:\Users\ebata>docker run -d --name postgres122 -e POSTGRES_PASSWORD=test -p 5432:5432 postgres:12.2

■確認(スキップしてもいい)
C:\Users\ebata>docker container ls

■確認(シェルに入れることを確認する(しなくてもいい))
C:\Users\ebata>docker exec -ti postgres122 bash

■ファイルコピー(やらなくてもいいが、やらないと後の話に繋がらない)
C:\Users\ebata>docker cp c:\routing-20200221.sql postgres122:/tmp/

■コンテナに入る(やらなくてもいい)
C:\Users\ebata>docker exec -i -t postgres122 /bin/bash

■コピーされているかを確認する
root@839317f0bb70:/# cd tmp
root@839317f0bb70:/tmp# ls
で、
routing-20200221.sql
が表示されたので成功

■ローカル(例えば C:\Users\ebata)に以下のdocker-compose.ymlを作成する

version: '3.7'

services:
db:
image: pgrouting/pgrouting:v3.0.0-dev-postgresql_12
expose:
- 5432
ports:
- 15432:5432
volumes:
- db_data
- ./shared:/shared
db_data:
image: busybox
volumes:
- /data/db

■次のコマンドを実施する
C:\Users\ebata>docker-compose up -d

■DBの作成
C:\Users\ebata>docker-compose exec db createdb -h db -U postgres routing

■postgisのインストール(これが失敗するみたい)
C:\Users\ebata>docker-compose exec db psql -h db -U postgres -d routing -c 'CREATE EXTENSION pgrouting CASCADE; CREATE EXTENSION hstore;'

■ダンプしたsqlの取り込み
C:\Users\ebata>docker-compose exec db psql -h db -U postgres routing -f /shared/routing-20200221.sql

■作られたDockerの確認
C:\Users\ebata>docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
64101530c199 pgrouting/pgrouting:v3.0.0-dev-postgresql_12 "docker-entrypoint.s…" About a minute ago Up About a minute 0.0.0.0:15432->5432/tcp ebata_db_1
f5ae64d47096 busybox "sh" About a minute ago Exited (0) About a minute ago ebata_db_data_1
d7838161c2f9 postgres:12.2 "docker-entrypoint.s…" 2 minutes ago Up 2 minutes 0.0.0.0:5432->5432/tcp postgres122

----- ここから次のフェーズに入る ------

■ログインする
C:\Users\ebata>docker container exec -it ebata_db_1 bash

■psqlに入る
C:\Users\ebata>docker container exec -it ebata_db_1 bash
root@64101530c199:/#
root@64101530c199:/#
root@64101530c199:/# psql -U postgres
psql (12.0 (Debian 12.0-2.pgdg100+1))
Type "help" for help.

postgres=#

■d1というDBを作る
postgres=# CREATE DATABASE d1;

■d1に接続
postgres=# \c d1
You are now connected to database "d1" as user "postgres".

■postGISのインストール
d1=# CREATE EXTENSION postgis;
CREATE EXTENSION
d1=# CREATE EXTENSION pgrouting;
CREATE EXTENSION

■テーブルを作成する
d1=# CREATE TABLE edges (id serial, source int, target int, cost int);
CREATE TABLE

■値を入力する
d1=# INSERT INTO edges (source, target, cost) VALUES (1, 2, 7), (1, 3, 9), (1, 6, 14), (2, 3, 10), (2, 4, 15), (3, 4, 11), (3, 6, 2), (4, 5, 6), (5, 6, 9); ← 端点ノード1、端点ノード2、コスト
INSERT 0 9

■最短コスト計算をする
d1=# SELECT seq, node, edge, cost FROM pgr_dijkstra('SELECT id, source, target, cost FROM edges', 1, 5, directed:=false); ← ノード1からノード5までの最短コストを算出する
seq | node | edge | cost
-----+------+------+------
1 | 1 | 2 | 9
2 | 3 | 7 | 2
3 | 6 | 9 | 9
4 | 5 | -1 | 0
(4 rows)
1->3->6->5の最短コストが出てくる。

--------- Dockerで作ったpgRoutingにosmファイルをインポートする ---------

(一回psqlをログアウトしてから、再度 psql -U postgres でログインした)

■データベース新規作成
psqlでデータベースを新規作成する（以下、データベース名をca_simとする）。
postgres=#CREATE DATABASE ca_sim;

■データベース拡張
psqlに入って次のコマンドを実行する。

postgres=# \c ca_sim
postgres=# create extension postgis;
postgres=# create extension pgrouting;

■kashiwanoha.osmを読み込む
C:\Users\ebata\Desktop\20190327_casim_final\environment>docker cp kashiwanoha.osm ebata_db_1:/db_data

■osm2pgrouting をインストール(が、できると思わなかったので、かなり驚いた)

root@70fa5aa3a11d:/# apt-get update
root@70fa5aa3a11d:/# apt-get install
root@70fa5aa3a11d:/# apt-get install osm2pgrouting

■kashiwanoha.osmをインポート

root@70fa5aa3a11d:/# osm2pgrouting -f /db_data/kashiwanoha.osm -c /usr/share/osm2pgrouting/mapconfig_for_cars.xml -d ca_sim -U postgres

■sqlファイルをシェルから実施する(これ実施中→うまくいなかいが、後の処理で回収できる(はず))

root@70fa5aa3a11d:# psql -f /db_data/make_bus_route_table.sql -d ca_sim -U postgres

--------- QGISでの表示の為のパスワードの設定 --------

■root@19e323b3f7c1:/# psql -U postgres

とした状態で、

postgres=# alter role postgres with password 'c-anemone';

■と打ち込んだ後、psqlからログアウトして、別のコンソールから、

C:\Users\ebata>docker stop postgres122

C:\Users\ebata>docker start -a postgres122

この後、間違っても、"docker ps -aq | xargs docker rm"なんぞやらんように。ここまでの設定が吹き飛ぶ

===== このままPCを落して寝ると、次の日、DBが落ちているので、立ち上げ直す

C:\Users\ebata>docker start -a postgres122

C:\Users\ebata>docker container exec -it postgres122 bash

root@19e323b3f7c1:/# psql -U postgres

とした状態で、

postgres=# alter role postgres with password 'c-anemone';

と打ち込んだ後、psqlからログアウトして、別のコンソールから、

C:\Users\ebata>docker stop postgres122

C:\Users\ebata>docker start -a postgres122

次は、ebata_db_1の方を使えるようにする。

C:\Users\ebata>docker start -a ebata_db_1 ← pgRoutingで作ったDockerのDB
C:\Users\ebata>docker container exec -it ebata_db_1 bash ← ログインできる

root@19e323b3f7c1:/# psql -U postgres

postgres=# alter role postgres with password 'c-anemone';

C:\Users\ebata>docker stop ebata_db_1

C:\Users\ebata>docker start -a ebata_db_1

これで、QGIS3 や pgAdmin4からアクセスできるようになる(というか、"なった")

------- PostgresのpostGISに、鉄道情報も入れる --------

omsのデータには、鉄道情報入っていません(と思う osm2pgrouting にtrainを取り出すオプションがなかったので) ―― ので、国土地理院からダウンロードする

https://nlftp.mlit.go.jp/ksj/index.html から鉄道時系列（ライン）（ポイント）を探して、ここからファイル(N02-08.zip)をPCにダウンロードして解凍する。中身はKS-META-N02-08.xmlと、N02-08.xml が入っている。

ところが、xmlファイルは、postgresにインポートできないのでshpファイルに変換する必要がある。

で、https://nlftp.mlit.go.jp/ksj/jpgis/jpgis_tool.html にある、「国土数値情報XML-シェープ変換ツール」をダウンロードしてインストールする。私の場合、64Bit版OSに対応した変換ツールはこちらからダウンロードできます。(ksjtool_64_v1.8.zip 約18MB）をダウンロードした。

で、先程のN02-08.xmlを変換すると、N02-08_EB02.dbf、N02-08_EB02.shp、N02-08_EB02.shx、N02-08_EB03.dbf、N02-08_EB03.shp、N02-08_EB03.shx が生成される。

(どういう訳か、shp2pgsqlがなくて、apt-get install shp2pgsql にも失敗するので)、QGIS3を使って、PostgresのDBにつっこむ。

N02-08_EB02 を選んで、OKを押下して、さらにN02-08_EB03を選んで、OKを押下する(2つインポートする)

以下を見ると、N02-08_EB02は路線情報が、N02-08_EB03は駅の情報が入っているみたい

ただ、selectしようとすると、変なエラーがでるので、テーブルの名前を変更した方がいい(が、今日は疲れたので、ここまで)

selectでは、テーブルの名前は小文字で、"-"も使えないようである。QGISから、以下のように変更した。

2020年6月28日2020/06,江端さんの技術メモ

Posted by ebata