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

緯度経度を使って住所を検索する必要があったが、 SQL Server の地理空間データ型(geography)を使うことで手軽に実現することができたのでメモしておく。

-- 緯度経度と住所を格納するテーブルを作成
CREATE TABLE addresses
(
  id bigint IDENTITY(1,1) NOT NULL,
  address nvarchar(100) NOT NULL,
  location geography NOT NULL,
  CONSTRAINT pk_addresses PRIMARY KEY CLUSTERED 
  (
    id ASC
  )
)
GO

-- geography 型の location にインデックスを作成する
CREATE SPATIAL INDEX spatial_addresses ON addresses
(
  location
)USING  GEOGRAPHY_GRID 
GO

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

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

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

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

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

-- 天神二丁目の2の緯度経度に一番近い住所を取得
DECLARE @g GEOGRAPHY;
SET @g=GEOGRAPHY::STGeomFromText('POINT(130.398821 33.589085)', 4326);
SELECT
  TOP 1
  id
  , address
  , @g.STDistance(location) AS distance
FROM
  addresses
ORDER BY
  distance