【PostGIS】ローカルのdockerでPostGIS+pgrouting環境を作ってみる

最近PostGISを使ったアプリの構築をしました。

地図の経路検索とかをSQLでできてPostGIS面白いです。

ローカルでDocker上でOSMのデータを使ってPostGiS+pgrouting環境を作ってみました。

但し書き

やっつけです。もっときれいなやり方あるかもしれません。
1から改めて検証したけど漏れがあったらすみません。

使ったDockerイメージ

pgrouting/pgrouting

PostGIS+pgroutingのイメージ。今回はこれをベースにosm2pgroutingも入れます。

openfirmware/osm2pgsql

OSMのデータをインポートするためのツール。

最終的なDocker-compose

https://github.com/panda-telecoms/postgis-osm

使い方

Git pullしてdocker-compose.ymlのあるところで下記コマンドを実行
docker-compose up -d

postgis、pgroutingの有効化

$ docker-compose run postgis sh -c 'exec psql -d"$POSTGRES_DB" -hpostgis -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres'
osm=# CREATE EXTENSION postgis;
CREATE EXTENSION
osm=# CREATE EXTENSION pgrouting;
CREATE EXTENSION
osm=#

OSMのデータを作る

こちらから最寄りのデータを取得します。
日本地図なんかは大きいデータになるのでローカルで遊ぶには向いてないと思います。
(近所の方がなじみがあって使いやすいと思いますし)

ファイル形式はpbfで落とします。
https://extract.bbbike.org/

pbfからosmデータにコンバートする

3で落としたデータだけでもPostGISを使うことはできます。
もしpgroutingを使うときはosm2pgroutingを使うときにpbfからosm形式に変換しないといけません。

Osmconvertというツールが公開されているのでこちらをつかって変換してみてください
https://wiki.openstreetmap.org/wiki/Osmconvert

データのインポート

```
# osm2pgsqlでインポート
$ docker-compose run osm2pgsql -c 'osm2pgsql --create --slim --cache 2000 --database $PG_ENV_OSM_DB --username $PG_ENV_OSM_USER --host pg --port $PG_PORT_5432_TCP_PORT /osm/○○.pbf -W'
# osm2pgroutingでpgrouting用データインポート
$ docker-compose exec postgis sh -c 'osm2pgrouting -f /osm/○○.osm -h postgis -Upostgres -d"$POSTGRES_DB" -p "$POSTGRES_PORT_5432_TCP_PORT" --conf /usr/local/share/osm2pgrouting/○○.xml -W"$POSTGRES_PASSWORD"'

使ってみる

psqlで入ってデータを確認します

docker-compose run postgis sh -c 'exec psql -d"$POSTGRES_DB" -hpostgis -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres'
Password for user postgres:
psql (12.3 (Debian 12.3-1.pgdg100+1))
Type "help" for help.

osm=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 osm       | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

osm=# \d
                    List of relations
 Schema |           Name           |   Type   |  Owner
--------+--------------------------+----------+----------
 public | configuration            | table    | postgres
 public | configuration_id_seq     | sequence | postgres
 public | geography_columns        | view     | postgres
 public | geometry_columns         | view     | postgres
 public | planet_osm_line          | table    | postgres
 public | planet_osm_nodes         | table    | postgres
 public | planet_osm_point         | table    | postgres
 public | planet_osm_polygon       | table    | postgres
 public | planet_osm_rels          | table    | postgres
 public | planet_osm_roads         | table    | postgres
 public | planet_osm_ways          | table    | postgres
 public | pointsofinterest         | table    | postgres
 public | pointsofinterest_pid_seq | sequence | postgres
 public | spatial_ref_sys          | table    | postgres
 public | ways                     | table    | postgres
 public | ways_gid_seq             | sequence | postgres
 public | ways_vertices_pgr        | table    | postgres
 public | ways_vertices_pgr_id_seq | sequence | postgres
(18 rows)

planet_osm_pointの中を見てみます。カラムwayをgeojsonとして出力することでPostGISの関数も使えることを確認してみます


osm=# select name, ST_AsGeoJSON(way) from planet_osm_point limit 10;
-[ RECORD 1 ]+------------------------------------------------------------------------------------------------------------------------
-----------
name         | 西澄寺
st_asgeojson | {"type":"Point","crs":{"type":"name","properties":{"name":"spatialreferencing.org:900913"}},"coordinates":[15548888.4,4251009.61]}
-[ RECORD 2 ]+------------------------------------------------------------------------------------------------------------------------
-----------
name         | 西澄寺前
st_asgeojson | {"type":"Point","crs":{"type":"name","properties":{"name":"spatialreferencing.org:900913"}},"coordinates":[15548980.2,4251038.51]}
-[ RECORD 3 ]+------------------------------------------------------------------------------------------------------------------------
-----------
name         |
st_asgeojson | {"type":"Point","crs":{"type":"name","properties":{"name":"spatialreferencing.org:900913"}},"coordinates":[15549093,4251074.99]}
-[ RECORD 4 ]+------------------------------------------------------------------------------------------------------------------------
-----------
name         | 駒繋神社 (Komatsunagi Shrine)
st_asgeojson | {"type":"Point","crs":{"type":"name","properties":{"name":"spatialreferencing.org:900913"}},"coordinates":[15549132.3,4250971.06]}
-[ RECORD 5 ]+------------------------------------------------------------------------------------------------------------------------
-----------
name         | Leap
st_asgeojson | {"type":"Point","crs":{"type":"name","properties":{"name":"spatialreferencing.org:900913"}},"coordinates":[15549187.6,4251096.29]}
-[ RECORD 6 ]+------------------------------------------------------------------------------------------------------------------------
-----------
name         | 駒繋小学校裏
st_asgeojson | {"type":"Point","crs":{"type":"name","properties":{"name":"spatialreferencing.org:900913"}},"coordinates":[15549176,4251103.24]}
-[ RECORD 7 ]+------------------------------------------------------------------------------------------------------------------------
-----------
name         | コインパーキング
st_asgeojson | {"type":"Point","crs":{"type":"name","properties":{"name":"spatialreferencing.org:900913"}},"coordinates":[15549308.4,4251016.69]}
-[ RECORD 8 ]+------------------------------------------------------------------------------------------------------------------------
-----------
name         | いづみ幼稚園
st_asgeojson | {"type":"Point","crs":{"type":"name","properties":{"name":"spatialreferencing.org:900913"}},"coordinates":[15549297.9,4251098.06]}
-[ RECORD 9 ]+------------------------------------------------------------------------------------------------------------------------
-----------
name         |
st_asgeojson | {"type":"Point","crs":{"type":"name","properties":{"name":"spatialreferencing.org:900913"}},"coordinates":[15549419.6,4251060.05]}
-[ RECORD 10 ]------------------------------------------------------------------------------------------------------------------------
-----------
name         |
st_asgeojson | {"type":"Point","crs":{"type":"name","properties":{"name":"spatialreferencing.org:900913"}},"coordinates":[15549424,4251069.51]}

osm=#

...

データは良さそうですし、PostGISの関数も使えてます。

QGISで出力するとこんな具合
QGISの結果
pgroutingも使ってみます。
https://live.osgeo.org/archive/10.5/ja/quickstart/pgrouting_quickstart.html

osm=# SELECT seq, edge, b.the_geom::text AS "the_geom (truncated)"
osm-#         FROM pgr_dijkstra('
osm'#                 SELECT gid as id, source, target,
osm'#                         length as cost FROM ways',
osm(#                 100, 600, false
osm(#         ) a INNER JOIN ways b ON (a.edge = b.gid) ORDER BY seq;
 seq |  edge  |                     the_geom (truncated)
-----+--------+--------------------------------------------------------------
   1 |  85803 | 0102000020E6100000020000009B2F40EC567861400FF6813F57D5414074
   2 |  82819 | 0102000020E610000002000000745C323358786140C23060C955D54140DE
   3 |  79695 | 0102000020E610000004000000DE0951195A786140F35E10EC53D5414048
   4 |  79947 | 0102000020E61000000200000026E6B4025E7861404D96A47C52D541405E
   5 |  82425 | 0102000020E6100000020000005E2F4D1160786140BE37E1B952D541405D
   6 |  84942 | 0102000020E6100000020000005D9896A266786140E7CAFB9353D5414063
   7 |  84941 | 0102000020E61000000300000063F60DA767786140462F59CB53D54140AA
   8 |  84940 | 0102000020E610000004000000E89326EF6D7861401B7629BF56D54140E3
   9 |  82601 | 0102000020E61000000200000003571E496E7861404A0E338F57D54140E8
  10 |  85215 | 0102000020E61000000200000080B6D5AC73786140D9FFA5B162D5414003
  11 |  63039 | 0102000020E61000000200000038EDCE7F747861401C57C80160D5414080
  12 |  65173 | 0102000020E610000002000000EDCA78A576786140CD2ECECE58D5414038
  13 |  61972 | 0102000020E610000002000000276728EE78786140772FF7C951D54140ED
  14 |  62747 | 0102000020E610000002000000889C63E57C7861409BA6745545D5414027
  15 |  65172 | 0102000020E6100000020000008754AC757D786140A888789043D5414088
  16 |  37996 | 0102000020E6100000020000008754AC757D786140A888789043D54140C2
  17 |  44007 | 0102000020E610000002000000C2C8810D7E7861405AE5F85745D5414072
  18 | 108968 | 0102000020E6100000020000008D2551D47E786140B316557545D5414072
  19 | 112462 | 0102000020E6100000020000005AAAC1EA7E786140A17D512745D541408D
  20 | 112461 | 0102000020E610000002000000E051BEFB7E786140D7D8CAF044D541405A
  21 | 112459 | 0102000020E610000002000000D310FA3E81786140BDAF253F3DD54140E0
  22 | 112473 | 0102000020E610000002000000B217659181786140FF00101C3CD54140D3
  23 | 108971 | 0102000020E61000000200000082D8E3CF81786140172F713F3BD54140B2
  24 | 112474 | 0102000020E610000002000000E3FFE9618378614085F70B2C36D5414082
  25 | 114593 | 0102000020E6100000020000000C31A88085786140D622FD512FD54140E3
  26 | 114592 | 0102000020E6100000040000001AB3DB1D887861406FEC7EBA26D54140D9
  27 | 113337 | 0102000020E610000002000000133823948878614046A11B5025D541401A
  28 | 108972 | 0102000020E610000004000000B0E02307917861403C212A430BD5414016
  29 | 112475 | 0102000020E61000000300000009821145927861405A3AD5100BD541401E
  30 | 123025 | 0102000020E61000000200000009821145927861405A3AD5100BD541407C
  31 | 123026 | 0102000020E6100000020000007CBB80F2927861402B604C4409D5414028
  32 |  91484 | 0102000020E6100000040000000C3AC6BA9D786140E424DE4B2BD54140E8
  33 |  95842 | 0102000020E610000004000000E4C2DC499F78614044F1CFB128D5414091
  34 | 105927 | 0102000020E610000004000000E4C2DC499F78614044F1CFB128D5414014
  35 |  97361 | 0102000020E610000002000000904B1C79A07861404419AA622AD5414048
  36 |  33111 | 0102000020E610000002000000F5F6E7A2A17861401B16FE6728D5414048
  37 |   4839 | 0102000020E610000002000000AC83CD4AA47861402082058B1ED54140F5
  38 |   3039 | 0102000020E610000002000000369A12FFA67861406753646314D54140AC
  39 |   9945 | 0102000020E610000002000000369A12FFA67861406753646314D54140AA
  40 | 141419 | 0102000020E61000000300000000F7E1C5A778614061DEE34C13D541403F
  41 | 112986 | 0102000020E610000007000000CCCB063EAB786140794B83EC06D5414037
  42 |  16588 | 0102000020E610000002000000CCCB063EAB786140794B83EC06D5414018
  43 |  16589 | 0102000020E610000002000000A348ADE6AF7861403E473F7507D5414018
  44 |  16604 | 0102000020E610000002000000A348ADE6AF7861403E473F7507D541405A
  45 |   9944 | 0102000020E6100000020000005A03A5B2B27861400A8BE5F10FD5414023
  46 |   4832 | 0102000020E61000000200000023884E2AB578614049AD41A907D54140C1
  47 |  84331 | 0102000020E610000002000000313894EBB77861408E475FF70CD54140C1
  48 | 109875 | 0102000020E6100000020000002BBDDB61B8786140891A96500BD5414031
  49 | 115356 | 0102000020E6100000020000001F0F22BEB87861406BA5C63A0BD541402B
  50 | 115355 | 0102000020E610000002000000264003A0C07861407A4501ECEDD441401F
  51 | 115353 | 0102000020E610000002000000089A852CC1786140AFF6668AEFD4414026
  52 |  90581 | 0102000020E610000002000000649126DEC17861405184D4EDECD4414008
  53 | 141125 | 0102000020E6100000020000009B3347FBC47861404BEC3598E1D4414064
  54 |  47697 | 0102000020E6100000020000000EB5125FD2786140571DDE18A7D441409B
  55 |  11558 | 0102000020E610000002000000F7F8CE79D7786140F2BB445090D441400E
  56 |  13537 | 0102000020E6100000020000002BD5AA02DA786140BC9F090385D44140F7
  57 |  33707 | 0102000020E610000002000000667792BEDA78614058422DBC81D441402B
  58 |  11667 | 0102000020E610000002000000634FF16EDB786140E331A8DB7ED4414066
  59 |  22050 | 0102000020E6100000030000004E14D7E7E07861405451611168D4414077
  60 |   8204 | 0102000020E610000002000000DD103EEFE17861404468A96164D441404E
  61 |  79720 | 0102000020E610000002000000564F9C92E278614003C70A2362D44140DD
(61 rows)

osm=#

QGISで見てみると。。。(緑の線がそれ)
QGISの結果

良さそうです。

今の検証結果をグラフィカルに見たいときはQGISなりを使います。
今回のdocker構成の場合は

ホスト名: localhost
ユーザ名: postgres
パスワード: password
DB名: osm
ポート: 5432

でいけるかなと思います。
(繋ぎ方もどこかで書くかもしれません)