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」に投入しています。

---