PostGIS PostgreSQLの地理空間情報を扱う拡張

PostGISとはなんぞや

PostgreSQLで場所や範囲を地理空間データ（具体的には、点、線分、ポリゴン）として扱えるようになる拡張モジュール。
地図上の場所や範囲の情報をリレーショナルデータベースに格納して、日本地図の情報と掛けあわせてSQLで処理したい！などが簡単にできちゃいます。

PostGISのインストール

まず、PostgreSQLが必要です。その後PostGISをインストールする手順です。
Ubuntuなら両方ともaptで入ります。

$ apt-get install postgresql

次にpostgisをインストールします。

$ apt-get install postgis

最新版をインストールする場合、公式サイトの手順を参照ください。

PostGISの設定

PostGISは拡張のため、地理データを扱うデータベースを作成し、拡張を有効化する必要があります。
まず、データベースを作成します。

$ su - postgres
createdb <データベース名>

作成したデータベースに対して、拡張を有効化します。

$ psql -d <データベース名>
<データベース名>=# CREATE EXTENSION postgis;

\dfで関数がいっぱいできていたら問題ないです。

FF

地理空間データの入力

環境が整ったところで、地理空間データをデータベースに入力します。
世の中にいろいろなデータが公開されていますが、まずは自分で作ってみます。
作るテーブルは、呼称カラム、緯度カラム、経度カラムの3つを持つ名所テーブルとします。

CREATE TABLE location (
    name varchar(20),
    latitude double presicion,
    longitude double presicion
);

作成したテーブルに入力するレコードは、東京タワーとスカイツリーにします。
緯度経度は、度分秒ではなく、度を単位としています。

INSERT INTO location VALUES (
    "東京タワー",
    35.65859972222222,
    139.74544277777778
);
INSERT INTO location VALUES (
    "スカイツリー",
    35.710067,
    139.8085117
);

座標点が緯度経度で表されたままだと、PostGISのいろんな便利関数を使えないため、PostGISが提供しているデータ型に変換します。
geometryと呼ばれるデータ型がPostGISでは用意されており、座標点、線分、矩形、多角形などの幾何データを表現できます。
geometryデータ型のカラムを追加しましょう。

SELECT public.AddGeometryColumn('location', 'coordinate', 4326, 'POINT', 2);

緯度経度のカラムの値を入力とし、新たに追加したカラムにgeometryデータを代入します。

UPDATE location SET coordinate = public.ST_SetSRID(public.ST_MakePoint(longitude, latitude),4326);

注意点として、ST_MakePointの引数は、経度、緯度の順番になります。

地理空間データへのクエリ

地理空間データが準備できたので、クエリを発行してみましょう。
求めるものは、東京タワーとスカイツリーの直線距離です。

PostGISでは、2座標点間の距離を求める関数があります。

float ST_Distance(geometry g1, geometry g2);

この関数の返り値は、メートルではなく度になります。
1度は赤道に近いほどメートル換算で大きくなり、極に近いほどメートル換算で小さくなります。
厳密な解ではなく、ざっくり東京では1度=90000メートル(1秒=25メートル)と換算します。

SELECT public.ST_Distance(
    (SELECT coordinate FROM location WHERE name = '東京タワー'), 
    (SELECT coordinate FROM location WHERE name = 'スカイツリー')
)*90000;