PostgreSQL PostGIS 拡張機能

はじめに

この実験では、PostgreSQL データベースに空間機能を追加するために PostGIS 拡張機能を使用する方法を学びます。PostGIS はデータベースを空間データベースに変え、幾何学的な空間で定義されたデータを格納およびクエリできるようになります。

まず、新しいデータベースを作成し、PostGIS 拡張機能を有効にします。次に、位置データを格納するための GEOMETRY 列を持つテーブルを作成します。最後に、いくつかの都市のデータを挿入し、空間クエリを実行して距離を計算したり、特定のエリア内のデータを見つけたりします。

空間データベースの作成と PostGIS の有効化

このステップでは、新しいデータベースを作成し、その中に PostGIS 拡張機能を有効にします。空間データを整理するために、専用のデータベースを使用することは良い習慣です。

まず、ターミナルを開き、psql 対話型ターミナルを使用して PostgreSQL サーバーに接続します。デフォルトの postgres スーパーユーザーとして接続します。

sudo -u postgres psql

psql プロンプトが表示され、postgres=# のようになります。

次に、spatial_db という名前の新しいデータベースを作成します。psql でのすべての SQL コマンドは、セミコロン (;) で終わる必要があります。

CREATE DATABASE spatial_db;

以下の確認メッセージが表示されるはずです。

CREATE DATABASE

これで、新しく作成したデータベースに接続します。\c コマンドは、別のデータベースに接続するために使用されます。

\c spatial_db

プロンプトが spatial_db に接続されたことを示すように変わります。

You are now connected to database "spatial_db" as user "postgres".
spatial_db=#

データベースの準備ができたので、PostGIS 拡張機能を有効にします。このコマンドは、すべての PostGIS 空間タイプ、関数、およびテーブルをデータベースにロードします。

CREATE EXTENSION postgis;

出力は、拡張機能が作成されたことを確認します。

CREATE EXTENSION

PostGIS が正しくインストールされていることを確認するには、そのバージョンを確認できます。

SELECT PostGIS_full_version();

これにより、PostGIS および関連ライブラリの詳細なバージョン情報が表示され、セットアップが成功したことが確認されます。これで次のステップに進むことができます。psql シェルを終了しないでください。

空間テーブルの作成とデータの挿入

これで空間データベースが準備できたので、地理データを格納するテーブルを作成できます。このステップでは、cities テーブルを作成し、いくつかの主要都市の座標を挿入します。

まだ psql シェルに接続しており、spatial_db データベースに接続しているはずです。

まず、cities という名前のテーブルを作成します。このテーブルには、標準の id と name に加えて、GEOMETRY 型の location 列があります。

GEOMETRY(Point, 4326) の理解

GEOMETRY: これは、空間フィーチャを格納するための PostGIS の基本的なデータ型です。
Point: これは、ジオメトリ列がポイントのみを格納することを指定します。他のタイプには LINESTRING や POLYGON があります。
4326: これは空間参照系識別子 (SRID) です。SRID 4326 は、GPS や Google マップで使用される標準の座標系である WGS 84 に対応します。

以下のコマンドを実行してテーブルを作成します。

CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    location GEOMETRY(Point, 4326)
);

確認メッセージが表示されます。

CREATE TABLE

次に、cities テーブルにデータを挿入します。Well-Known Text (WKT) 形式のポイント表現をジオメトリオブジェクトに変換するために ST_GeomFromText 関数を使用します。フォーマットは POINT(経度緯度) です。

INSERT INTO cities (name, location) VALUES
    ('New York', ST_GeomFromText('POINT(-74.0060 40.7128)', 4326)),
    ('London', ST_GeomFromText('POINT(-0.1278 51.5074)', 4326)),
    ('Tokyo', ST_GeomFromText('POINT(139.6917 35.6895)', 4326));

出力は、3 行が挿入されたことを示します。

INSERT 0 3

データが正しく挿入されたことを確認するには、テーブルをクエリできます。ST_AsText 関数を使用して、ジオメトリオブジェクトを人間が読めるテキスト形式に戻します。

SELECT id, name, ST_AsText(location) FROM cities;

出力には、挿入したデータが表示されるはずです。

 id |   name   |          st_astext
----+----------+-----------------------------
  1 | New York | POINT(-74.006 40.7128)
  2 | London   | POINT(-0.1278 51.5074)
  3 | Tokyo    | POINT(139.6917 35.6895)
(3 rows)

空間テーブルの作成とデータ投入に成功しました。

空間クエリと分析の実行

PostGIS の真の力は、空間クエリを実行できる能力にあります。このステップでは、PostGIS 関数を使用して 2 点間の距離を計算し、特定の領域内にどのポイントが含まれるかを見つけます。

まだ psql シェルに接続しており、spatial_db データベースに接続しているはずです。

まず、ニューヨークとロンドンの間の距離を計算しましょう。地球の表面での正確な距離計算のためには、geometry 型を geography 型にキャストするのが最善です。geography 型は地球の湾曲を考慮しており、ST_Distance は結果をメートル単位で返します。

SELECT ST_Distance(
    (SELECT location::geography FROM cities WHERE name = 'New York'),
    (SELECT location::geography FROM cities WHERE name = 'London')
);

このクエリは、メートル単位の測地線距離を返します。結果は、約 557 万メートルという大きな数値になります。

   st_distance
-----------------
 5570299.7888681
(1 row)

次に、ロンドンから 200 キロメートル以内にある都市を見つけるための分析を実行しましょう。これを行うには、まずロンドンの位置を中心に円形のバッファを作成し、次にどの都市の位置がそのバッファと交差するかを確認します。

ST_Buffer: ソースジオメトリからの距離が指定された距離以下であるすべてのポイントを表すジオメトリを作成します。geography 型で使用する場合、距離はメートル単位です。
ST_Intersects: 2 つのジオメトリが「空間的に交差する」場合、つまり共通の空間を持つ場合に true を返します。

WITH london_buffer AS (
  SELECT ST_Buffer(location::geography, 200000) AS geom FROM cities WHERE name = 'London'
)
SELECT c.name
FROM cities c, london_buffer lb
WHERE ST_Intersects(c.location::geography, lb.geom);

このクエリは、Common Table Expression (CTE) を使用して、ロンドンの周りに 200,000 メートル (200 km) のバッファを作成します。次に、このバッファと交差するすべての都市を見つけます。予想どおり、この領域内にあるのはロンドン自体のみです。

  name
--------
 London
(1 row)

これで基本的な空間分析を実行しました。\q と入力して psql シェルを終了できます。

\q

データベースのクリーンアップ

作業完了後、環境をクリーンアップすることは良い習慣です。このステップでは、spatial_db データベースを削除します。データベースを削除すると、データベース自体と、テーブルや拡張機能などの含まれるすべてのオブジェクトが削除されます。

通常のターミナルプロンプト (labex@vm:~$) に戻っているはずです。

データベースを削除するには、別のデータベースに接続している必要があります。デフォルトの postgres データベースに接続します。

sudo -u postgres psql

次に、DROP DATABASE コマンドを実行します。

DROP DATABASE spatial_db;

確認メッセージが表示されます。

DROP DATABASE

\l コマンドを使用して利用可能なすべてのデータベースを一覧表示することで、データベースが削除されたことを確認できます。

\l

spatial_db がリストに表示されなくなっていることがわかります。

最後に、psql シェルを終了します。

\q

まとめ

この実験では、PostGIS 拡張機能を使用して PostgreSQL に空間データサポートを追加する方法を学びました。専用データベースの作成、PostGIS 拡張機能の有効化、ジオメトリ列を持つテーブルの定義、位置データの挿入、そして距離の計算や交差の検索を行う空間クエリの実行に成功しました。これらのスキルは、PostgreSQL を使用して位置情報を活用したアプリケーションを構築するための強固な基盤となります。

PostgreSQL における PostGIS 拡張機能の使用

はじめに

空間データベースの作成と PostGIS の有効化

空間テーブルの作成とデータの挿入

空間クエリと分析の実行

データベースのクリーンアップ

まとめ