PostgreSQL で緯度経度から住所を検索する

先日、SQL Server で緯度経度から住所を検索する方法を試したが、 PostgreSQL でも拡張の PostGIS を使えば同じようなことが可能だった。

-- 住所を格納するテーブルを作成
CREATE TABLE addresses (
  address_id BIGSERIAL PRIMARY KEY,
  address TEXT,
  location GEOMETRY(POINT, 4326)
);

-- 空間インデックスを作成
CREATE INDEX ON addresses USING gist (ST_Transform(location, 32654));

-- テストデータを投入
INSERT INTO addresses
(
  address,
  location
)
VALUES (
  '福岡県福岡市中央区天神一丁目',
  ST_GeomFromText('POINT(130.401396 33.590878)', 4326)
);

INSERT INTO addresses
(
  address,
  location
)
VALUES
(
  '福岡県福岡市中央区天神二丁目',
  ST_GeomFromText('POINT(130.398043 33.589390)', 4326)
);

INSERT INTO addresses
(
  address,
  location
)
VALUES
(
  '福岡県福岡市中央区天神三丁目',
  ST_GeomFromText('POINT(130.396224 33.593515)', 4326)
);

INSERT INTO addresses
(
  address,
  location
)
VALUES
(
  '福岡県福岡市中央区天神四丁目',
  ST_GeomFromText('POINT(130.399850 33.594580)', 4326)
);

INSERT INTO addresses
(
  address,
  location
)
VALUES
(
  '福岡県福岡市中央区天神五丁目',
  ST_GeomFromText('POINT(130.398918 33.596781)', 4326)
);

-- 天神二丁目の2の緯度経度に一番近い住所を取得
SELECT
  address_id,
  address,
  ST_Transform(location, 32654) <-> ST_Transform(ST_GeomFromText('POINT(130.398821 33.589085)', 4326), 32654) AS distance
FROM
  addresses 
ORDER BY
  distance
LIMIT 1;

Amazon RDS for PostgreSQL だと、最初から PostGIS が有効になっているのですぐ使えた。