postGIS(Windows版)をインストールして町内を表示して、ダイクストラで最短距離計算をして、QGISで表示する

- 2018-09-30(日) 17:05:38
- 2018-10-03(水) 18:11:14
- 2018-10-04(木) 13:12:25
- 2018-10-21(日) 23:10:20
1.背景
- インストールしたもの
  - PostgreSQL-9.6.10-1-win64-bigsql.exe
    - これがDB本体
  - postgis-bundle-pg96x64-setup-2.4.4-1.exe
    - GIS DBとして使えるように拡張するもの
  - どっちか(忘れた)
    - QGIS-OSGeo4W-3.2.3-1-Setup-x86_64.exe
      - 地図のビューアー
    - osgeo4w-setup-x86_64.exe
      - 地図のビューアー
  - osm2pgrouting-pg94-2.0w64gcc48.zip
    - ダイクストラ計算をするソルバ(？)
2.前提
- PostgreSQL
  - "D:\PostgreSQL"にインストールする
  - で、こちらは問題なく動く
- postGISが上手く動かない
3.作業内容
- "Create spatial database"を選ぶ
  D:\PostgreSQL\pg96\ をインストール先にする
  postgresのパスワードを入力する
  Spatial Database Informationの名前はデフォルトで
  この辺は適当に"はい"で
  この警告が出てインストールに失敗する
  ここで、C:\Users\ebata\AppData\Local\Temp\postgis_installer\create_spatial_db_error.txt を実際に見てみると、こんな感じ
  "DROP DATABASE postgis_24_sample"でデータベースを強制的に消す
  再度インストール
  - - 失敗
    - 再び、データベースを強制的に消す
  面倒なので、D:\PostgreSQL\pg96\share\extensionの内容を、 "D:/PostgreSQL/pg96/../pg96/share/postgresql/extension/にまるごとコピーしてみる
  - - また失敗
    - postgis-2.4の場所を探す
    - D:\PostgreSQL\pg96\share\contrib に発見
  次に、D:\PostgreSQL\pg96\libのファイルを、D:\PostgreSQL\pg96\lib\postgresql にコピー
  - - 成功したらしい
(続行)
4.QGISのインストール
- インストールの場所
  - "D:\Program Files\QGIS 3.2\bin\qgis-bin.exe"
  - インストーラの言われるがまま(C:→D:にしたくらい)
5. osm2pgrouting-pg94-2.0w64gcc48の解凍
- D:\osm2pgrouting-pg94-2.0w64gcc48
- この中に、こんなものが入っている
  - libexpat-1.dll
  - mapconfig.xml
  - mapconfig_for_cars.xml
  - osm2pgrouting.exe
- 上記の「こんなもの」をD:\PostgreSQL\pg96\binにコピペする
6.町内会のデータ(osmファイル)をダウンロードする
- 適当なファイル名をつけて、omsファイルを保存する。
  - 私の場合、"hirohakama.osm"
7.osmファイルを、DBに放り込む
- osm2pgroutingでのPostgreSQLデータベースへのインポート
- 参考ページ
  - ~ https://live.osgeo.org/archive/11.0/ja/quickstart/pgrouting_quickstart.html
  - ~ https://qiita.com/sanak/items/f50e8c5bb97bf9619958
- psql のプロンプトで、以下を入力
  - CREATE DATABASE city_routing;
  - \c city_routing;
  - CREATE EXTENSION postgis;
  - CREATE EXTENSION pgrouting;
- インストールの確認
  - SELECT * FROM pgr_version();
- インポートの実行
  - "mapconfig_for_cars.xml"は出力ファイルではないので注意のこと(これで嵌った)
- 実行結果
  - 成功すればこうなるはず
8. QGISに表示してみる
- QGISを立ち上げる
- QGISに地図を表示
9.ダイクストラによるルート作成
- T.B.D.
- 説明は後
  - ■
    - SELECT seq, node, edge, cost FROM pgr_dijkstra('SELECT gid as id, source, target,length as cost FROM ways',100, 600, false);
      - SELECT
        seq
        通番
        
        node
        始点id
        
        edge
        ラインid
        
        cost
        距離
      - FROM pgr_dijkstra(
        'SELECT gid as id,
        
        source,
        
        target,
        
        length as cost
        
        FROM ways',
        100,
        始点id
        
        600,
        終点id
        
        false);
        逆向きの場合にreverse_costを使用するかどうか
      - 実行結果はこんな感じ
        seq | node | edge |         cost
        
        -----+------+------+----------------------
        
        (23 行)
        
        1 | 100 | 111 | 2.5622255967423e-005
        
        2 |   78 |   50 | 0.000173102888494406
        
        3 |   34 |   51 |   0.0012628511474394
        
        4 |   30 |   43 | 0.00072685875121704
        
        5 | 356 | 486 | 0.000241341190025051
        
        6 | 504 | 693 | 0.000836981097749484
        
        7 | 513 | 387 | 0.000858989125951372
        
        8 | 286 | 322 |   0.0029401035390535
        
        9 | 242 | 450 | 0.000484973769706383
        
        10 | 331 | 358 | 0.000806971747810262
        
        11 | 269 | 401 | 0.00061139492356515
        
        12 | 295 | 400 | 0.000530960977837258
        
        13 | 466 | 837 | 0.000817950860397281
        
        14 | 610 | 413 | 0.00127087139395644
        
        15 | 306 | 371 | 0.000846798689172664
        
        16 | 276 | 451 | 0.00120463826937263
        
        17 | 332 | 460 | 0.00177824302901349
        
        18 | 338 | 609 | 0.000728549058048487
        
        19 | 442 | 643 | 0.000918891778180173
        
        20 | 468 | 219 | 0.000129469417237481
        
        21 | 167 | 505 | 0.000859296908007581
        
        22 | 369 | 823 | 0.000348603671806087
        
        23 | 600 |   -1 |                    0
      - ちなみに、SELECT gid as id, source, target,length as cost FROM ways; を実行すると、これが出てくる
        id | source | target |         cost
        
        ----+--------+--------+-----------------------
        
        1 |    467 |    186 |   0.00140792103472266
        
        2 |    431 |     26 | 0.000974249177559836
        
        3 |    263 |      1 |   0.00232477105976142
        
        4 |    239 |      1 | 0.000463179587207689
        
        5 |     76 |      1 | 0.000390141064235121
        
        6 |    251 |      2 |   0.00465925674291399
        
        7 |    561 |      3 | 0.000448674938013377
        
        8 |    362 |      4 | 0.000353376470646882
        
        871 |    562 |    637 |   0.00056461233601709
        
        872 |    416 |    637 | 0.000543607330700107
        
        873 |    215 |    638 | 0.000477581291512472
        
        874 |    581 |    639 | 8.19983536340744e-005
        
        875 |    373 |    639 | 0.000517690264552089
        
        876 |     80 |    640 |   0.00185377474161293
        
        877 |     37 |    640 | 0.000390821967149387
        
        878 |    195 |    641 |   0.00152200941763791
        
        (878 行)
        
        なんで座標がないんだろう？と思ったけど、よく考えれば、ダイクストラ法はコスト値さえあれば、座標はいらない
    ■
    - SELECT id, osm_id FROM ways_vertices_pgr where id IN( 100, 600);
      - 100 | 1590338369
      - 600 | 1806691363
    ■
    - SELECT seq, node, edge, cost FROM pgr_dijkstra('SELECT gid as id, source_osm AS source, target_osm AS target,length as cost FROM ways', 1590338369, 1806691363, false);
    ■
    - SELECT seq, edge, rpad(b.the_geom::text,60,' ') AS "the_geom (truncated)" FROM pgr_dijkstra('SELECT gid as id, source, target, length as cost FROM ways', 100, 600, false ) a INNER JOIN ways b ON (a.edge = b.gid) ORDER BY seq;
      - 出力値はこんな感じ
        seq | edge |                     the_geom (truncated)
        
        -----+------+--------------------------------------------------------------
        
        1 | 111 | 0102000020E6100000020000004007A74F7C6F6140FD0EEAC7CBCC414097
        
        2 |   50 | 0102000020E6100000020000009741107B7C6F6140330E3F49CBCC414067
        
        3 |   51 | 0102000020E610000005000000A76CA2E0736F614012C946D6D0CC4140A7
        
        4 |   43 | 0102000020E610000006000000773469AE6E6F614036DE67A6C5CC414074
        
        5 | 486 | 0102000020E61000000200000054A9D9036D6F6140FCBDB964C1CC414077
        
        6 | 693 | 0102000020E6100000050000000702092F666F6140AF0C05C7C0CC414060
        
        7 | 387 | 0102000020E6100000070000000702092F666F6140AF0C05C7C0CC4140ED
        
        8 | 322 | 0102000020E61000001000000049E47107606F61403F462F59CBCC41401A
        
        9 | 450 | 0102000020E6100000060000001FE0A4C4496F6140E9F2E670ADCC414066
        
        10 | 358 | 0102000020E6100000060000003BF07BE54B6F61400D3C52D8A0CC414045
        
        11 | 401 | 0102000020E610000006000000B652639D456F6140CA65ED0099CC4140C2
        
        12 | 400 | 0102000020E61000000200000012F8C3CF3F6F61400DD4731C89CC4140BC
        
        13 | 837 | 0102000020E61000000200000012F8C3CF3F6F61400DD4731C89CC414083
        
        14 | 413 | 0102000020E61000000200000083D49233396F61401DA386808DCC4140D3
        
        15 | 371 | 0102000020E610000002000000D3A1D3F32E6F61405BBCB3D194CC4140BC
        
        16 | 451 | 0102000020E610000002000000BC9A971E286F6140C318479B99CC414079
        
        17 | 460 | 0102000020E61000000600000079573D601E6F6140377980DD9FCC41404E
        
        18 | 609 | 0102000020E6100000020000006E64C33F116F6140F54DF5BFB7CC414083
        
        19 | 643 | 0102000020E61000000200000083401D450C6F6140667CA9E9C4CC41400C
        
        20 | 219 | 0102000020E6100000020000000CD2E7FE056F61401CEF8E8CD5CC41404E
        
        21 | 505 | 0102000020E6100000030000004EAD0848056F6140B8BFC469D2CC414051
        
        22 | 823 | 0102000020E61000000200000060F76750FE6E6140769CECB0D3CC414032
        
        (22 行)
      - これがQGIS上に表示できない
        2018-10-04(木) 13:12:25
    SELECT seq, node, edge, cost FROM pgr_dijkstra('SELECT gid as id, source, target, cost_s As cost, reverse_cost_s AS reverse_cost FROM ways', 100, 600, true );
    ■
10.その他
- 同心円
  - select id, ST_Buffer(ST_GeomFromText('POINT(139.4746429 35.5988350)'), id*0.0001456) geom from unnest(ARRAY[1,2,3,4]) id;
    - これは上手くいくんだけどな
  ルート表示
  - SELECT seq, edge, rpad(b.the_geom::text,60,' ') AS "the_geom (truncated)" FROM pgr_dijkstra('SELECT gid as id, source, target, length as cost FROM ways', 100, 600, false ) a INNER JOIN ways b ON (a.edge = b.gid) ORDER BY seq;
    - これが上手く動かん
    やっと動きました
    - 犯人は、「rpad(b.the_geom::text,60,' ') 」でした
      SELECT seq, edge, b.the_geom AS "the_geom" FROM pgr_dijkstra('SELECT gid as id, source, target, length as cost FROM ways', 100, 600, false ) a INNER JOIN ways b ON (a.edge = b.gid) ORDER BY seq;
4.メモ(訳が分からなくていい)
- qGISとの連携
- 不明点
  - the_geomって何だ？
    - 全テーブルのジオメトリカラム名は the_geom (shp2pgsql のデフォルト) という記載がある
  - shp2pgsqlって何だ？
  - ST_Centroid
    - ジオメトリの幾何学的重心を返します．
    - geometry ST_Centroid(geometry g1);
      - ELECT ST_AsText(ST_Centroid('MULTIPOINT ( -1 0, -1 2, -1 3, -1 4, -1 7, 0 1, 0 3, 1 1, 2 0, 6 0, 7 8, 9 8, 10 6 )'));
        st_astext
- ~ d.hatena.ne.jp > EulerDijkstra > 20131008 > 1381190780
  - PostGISについて
    - 地理情報に関する機能を集めたPosgreSQLの拡張
    - メリット
      - 複雑な集計操作を豊富な関数で処理でき、GIS基盤のミドルウェアとして利用可能。
      - データベースに格納できるので既存のデータとの結合やデータの共有が容易。
      - 毎回必要に応じて集計すればよいので、粒度や集計単位の異なるshpファイルをたくさん持たなくてもよくなる。
      - QGIS等とも相性がよく、手軽にSQLで集計して主題図を記述可能。
    - デメリット
      - 毎回集計するのでメモリに乗るデータなら普通にGISで処理した方が早い
      - データベースへのロードやエクスポート作業が必要。
    - shp2pgsqlというコマンド
      - shpファイルをインポート
      - SQLを出力します
  - 型
    - Geometry型
      - データ型のカラム
      - ジオメトリー操作用の関数(ST_XXXXXX)でこれらを操作
      - 地理的な検索や集計を行ったり、地図として表示させるポリゴンを取得
      - postgresなら
        CREATE TABLE geo (
        
        geo_id INT NOT NULL,
        
        name VARCHAR(255) NOT NULL,
        
        geo GEOMETRY NOT NULL,
        
        PRIMARY KEY (geo_id),
        
        SPATIAL KEY (geo)
        
        )ENGINE=MyISAM;
      - ３つのジオメトリー型
        点（位置、ランドマーク）を表すPOINT
        INSERT INTO geo (geo_id , name, geo) values (1, 'はてな京都本社', geomFromText('POINT(135.761919 35.011141)'));
        INSERT INTO geo (geo_id , name , geo) values (2, '地下鉄烏丸御池', geomFromText('POINT(135.759666 35.010745)'));
        
        線（道路や川）を表すLINESTRING
        INSERT INTO geo (geo_id , name , geo) values (3, '東洞院通り上る', geomFromText('LINESTRING(135.761050 35.012165,135.761050 35.011040)'));
        
        面（領域、町丁目）を表すPOLYGON
        INSERT INTO geo (geo_id , name , geo) values (4, 'ハートンホテル', polygonFromText('POLYGON((135.760497 35.012033,135.760497 35.011655, 135.760970 35.011655, 135.760970 35.012033,135.760497 35.012033))'));
        
        それらの複合型、集合型もある。
    - Geography型
      - T.B.D.
- もしかしてthe_geomって、GeoHashのこと？
  - ~ GeoHashのdecodeのアルゴリズムの解説します & ScalaのGeoHashライブラリを作ってみました(仮) - ( ꒪⌓꒪) ゆるよろ日記.url