PostGISメモ

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


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

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

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


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

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

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

2行目 sampleに接続します。

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

4行目 データベースの一覧を表示しています。


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

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, 4326),
elev double precision
);

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

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

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

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

15行目 テーブル「tmp」のフィールド「x」の値xxxxと、フィールド「y」の値yyyyを使って、文字列「SRID=4326;POINT(xxxx yyyy)」を作成し、concatedに入れています。

テーブル「pnt」のフィールド「geom」にconcatedの値を、フィールド「elev」にテーブル「tmp」のフィールド「z」の値を入れています。


ポリゴンに重なるポイントを取得する

CREATE TABLE intpnt (LIKE pnt);
INSERT INTO intpnt (geom, elev) (SELECT geom, elev  FROM pnt WHERE ST_Intersects(geom, (SELECT geom FROM mesh where コード='53381465')));

1行目 構造はpntと同じintpntというデータベースを作成しています。

2行目 テーブル「intpnt」に、テーブル「mesh」のフィールド「コード」の値が53381465のレコードのオブジェクトに重なるテーブル「pnt」のオブジェクトを追加します。


テーブル名を変更する

ALTER TABLE 〇〇〇 RENAME TO △△△;

間違えてテーブル名の先頭に数字を使ってしまったとき、〇〇〇はダブルクォーテーションで囲まないと、syntax errorになります。