PostGIS よく使うSQL文のメモ

このページでは自分がよく使うSQL文をメモしています。


SQL文をファイルから実行する

psql -U postgres -f ファイル名.sql

このコマンドを実行するためには環境変数の設定を済ませておく必要があります。


データベースの一覧表示

\l

PostGISのデータベースを作成する

CREATE DATABASE 〇〇;
\c sample
CREATE EXTENSION postgis;

1行目 〇〇というデータベースを作成しています。

2行目 〇〇に接続します。

3行目 現在のデータベースにPostGISの拡張をインストールしています。


SQL文の実行時間を計測する

\timing

このコマンドを実行した後、SQL文を実行すると、実行後に時間が出力されます。

現在接続しているデータベース名を取得する

SELECT current_database();

データベースを削除する

DROP DATABASE 〇〇;

テーブル一覧

\dt

テーブル作成

CREATE TABLE 〇〇
(
  geom GEOMETRY(オブジェクトタイプ, SRID),
  フィールド名 型
     :          :
     :          :
);

テーブル名を変更する

ALTER TABLE 〇〇 RENAME TO △△;

テーブルの行数を取得する

SELECT count(*) from 〇〇;

テーブルのカラム名一覧

\d 〇〇

テーブルを削除する

DROP TABLE 〇〇;

テーブルを初期化する

TRUNCATE 〇〇;

SRIDを変更する

SELECT UpdateGeometrySrID('〇〇', 'geom', SRID);

テーブル名とオブジェクトのフィールドには「'」を付けないとエラーになります。

インデックスを作成する

CREATE INDEX インデックス名 ON テーブル名 USING GIST (geom);

インデックスを削除する

DROP INDEX インデックス名;

点群のテキストを読み込む

スペースで区切られたx y 標高のテキストを読み込む場合...

CREATE TEMP TABLE tmp (
    x double precision,
    y double precision,
    z double precision
);

\COPY tmp FROM 'points.txt' WITH CSV DELIMITER ' ';

CREATE TABLE pnt (
    geom GEOMETRY(POINT, srid),
    elev double precision
);

INSERT INTO
    pnt (geom, elev)
SELECT
    CONCAT ('SRID=srid;POINT(', x, ' ', y, ')') AS concated,
    z
FROM
    tmp;

1行目 tmpという一時的に使用するデータベースを作成しています。接続が切れると自動的に削除されます。

7行目 クライアント側に存在するファイル「points.txt」をテーブル「tmp」にコピーしています。テキストはスペース区切りです。

9行目 オブジェクトタイプがポイントのpntというテーブルを作成しています。

14行目 テーブル「tmp」のフィールド「x」の値xxxxと、フィールド「y」の値yyyyでオブジェクトを作成し、フィールド「z」の値と共に、「pnt」に投入しています。


PostGISのデータディレクトリを変更する


PostGISのインストール途中で「Failed to load SQL modules into the database cluster.」や「Spatial database creation failed.」とエラーが出る場合があり、データディレクトリを変更する必要がありましたので、変更手順を紹介します。


PostgreSQLのサービスを削除する

f:id:Chiakikun:20190815233634p:plain

先に設定していたデータディレクトリを削除してから、PostgreSQLインストーラーが設定したサービスを削除します。コントロールパネルから「システムとセキュリティ」→「管理ツール」→「サービス」と移動して「Postgresql-x64-10」が確認できます。

 

f:id:Chiakikun:20190815234039p:plain

cmdを管理者権限で起動します。PowerShellではダメでした。

コマンドプロンプトで次のコマンドを実行します。

sc delete postgresql-x64-10

 


データディレクトリを作成する

f:id:Chiakikun:20190815234342p:plain

データディレクトリを手動で作成します。ここはPowerShellでも構いません。initdbコマンドを使うのでPath設定はしておいてください。次のコマンドを実行します。

initdb -D C:\users\〇〇\PostgreSQL -W -U postgres -A password --no-locale -E UTF8

〇〇の部分は自身のユーザー名を入れてください。データベースのパスワードの入力を求められるのでパスワードを2回入力すると、データディレクトリが作成されます。


PostgreSQLのサービスを登録する

f:id:Chiakikun:20190815235038p:plain

サービスを登録します。cmdを管理者権限で作成して、次のコマンドを実行します。

sc create postgresql-x64-10 binPath= "\"C:\Program Files\PostgreSQL\10\bin\pg_ctl.exe\" runservice -N \"postgresql-x64-10\" -D \"C:\Users\〇〇\PostgreSQL\"" start=auto password=************

NumbaでPythonプログラム高速化

幌延地域を対象とした10mグリッド数値標高モデルを用いた精密地形解析図の作成』を参考に、DEMから斜面方位を算出するためのプログラムをPythonで作成していたのですが、素のままのPythonでは実行速度はかなり遅かったです。
Pythonの実行速度改善方法はいくつかありますが、今回は『Numba』というライブラリを使用して、実際に高速化できたので紹介させていただきます。

インストール

f:id:Chiakikun:20190825152553p:plain

管理者権限でPowershellを起動して、次のコマンドを実行します。

pip install numba

インストールはこれで終わりです。

プログラムにNumbaを適用する

①例えば、下記のコードの赤字の関数を高速化するには...

        for y in range(1, nrow - 1) :
            for x in range(1, ncol - 1) :
                if dem_check4(self.__dem, x, y, self.__nodata) == False: continue
                if x == 1:
                    dx, dy = self.__dxdy(grs80, x, y)
                dem_hoi(self.__dem, x, y, dx, dy, result)

②numbaをインポートします

# -*- coding: utf-8 -*-
import numpy as np 
import numba
....

③各関数の先頭に『@numba.jit』を記述します

@numba.jit
def dem_check4(dem, x, y, nodata):
    if dem[y][x-1]   == nodata: return False
    if dem[y][x+1]   == nodata: return False
    ....
    
@numba.jit
def dem_hoi(dem, x, y, dx, dy, result):
    Hx = (dem[y-1][x-1] + dem[y][x-1] + dem[y+1][x-1] - (dem[y-1][x+1] + dem[y][x+1] + dem[y+1][x+1])) / (3 * dx)
    Hy = (dem[y+1][x-1] + dem[y+1][x] + dem[y+1][x+1] - (dem[y-1][x-1] + dem[y-1][x] + dem[y-1][x+1])) / (3 * dy)
    ....

numbaを使ってみた結果

f:id:Chiakikun:20190825163755p:plain

f:id:Chiakikun:20190825164007p:plain

①どのくらい速くなったのか、試しに富士山周辺の斜面方位図を作成しました。

numba適用前の実行時間は350秒でした。

numba適用後の実行時間は19秒でした。

仕方ないけど、やはりPythonは遅い

ものすごく速くなったなぁと感じたのですが...

f:id:Chiakikun:20190825164702p:plain

QGISの機能で傾斜方位を算出すると、なんと約1秒でした。

私のプログラムに何か問題があるのかもしれません...。

C# + gdal_csharpで同じプログラムを作ってみたのですが、やはり1秒程で処理が終わります。

どうしてもPythonを使いたい + Numbaをインストールできる環境の方は、Numbaを試してみてください。特に拘りが無い方は、他の言語の使用を検討した方が良いと思います。

 

最後までご覧頂き、ありがとうございました