PostGIS よく使うSQL文のメモ
- SQL文をファイルから実行する
- データベースの一覧表示
- PostGISのデータベースを作成する
- SQL文の実行時間を計測する
- 現在接続しているデータベース名を取得する
- データベースを削除する
- テーブル一覧
- テーブル作成
- テーブル名を変更する
- テーブルの行数を取得する
- テーブルのカラム名一覧
- テーブルを削除する
- テーブルを初期化する
- SRIDを変更する
- インデックスを作成する
- インデックスを削除する
- 点群のテキストを読み込む
このページでは自分がよく使う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のサービスを削除する
先に設定していたデータディレクトリを削除してから、PostgreSQLのインストーラーが設定したサービスを削除します。コントロールパネルから「システムとセキュリティ」→「管理ツール」→「サービス」と移動して「Postgresql-x64-10」が確認できます。
cmdを管理者権限で起動します。PowerShellではダメでした。
コマンドプロンプトで次のコマンドを実行します。
sc delete postgresql-x64-10
データディレクトリを作成する
データディレクトリを手動で作成します。ここはPowerShellでも構いません。initdbコマンドを使うのでPath設定はしておいてください。次のコマンドを実行します。
initdb -D C:\users\〇〇\PostgreSQL -W -U postgres -A password --no-locale -E UTF8
〇〇の部分は自身のユーザー名を入れてください。データベースのパスワードの入力を求められるのでパスワードを2回入力すると、データディレクトリが作成されます。
PostgreSQLのサービスを登録する
サービスを登録します。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』というライブラリを使用して、実際に高速化できたので紹介させていただきます。
インストール
管理者権限で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を使ってみた結果
①どのくらい速くなったのか、試しに富士山周辺の斜面方位図を作成しました。
numba適用前の実行時間は350秒でした。
numba適用後の実行時間は19秒でした。
仕方ないけど、やはりPythonは遅い
ものすごく速くなったなぁと感じたのですが...
②QGISの機能で傾斜方位を算出すると、なんと約1秒でした。
私のプログラムに何か問題があるのかもしれません...。
C# + gdal_csharpで同じプログラムを作ってみたのですが、やはり1秒程で処理が終わります。
どうしてもPythonを使いたい + Numbaをインストールできる環境の方は、Numbaを試してみてください。特に拘りが無い方は、他の言語の使用を検討した方が良いと思います。
最後までご覧頂き、ありがとうございました