RDBMS-GIS における小技

はじめに

皆さん位置情報を取り扱ってますか？位置情報データをどのように蓄えてますか？
elasticsearch？ひとつの選択肢ではありますが、RDBMS(MySQLやPostgreSQL)でやるのもいいですよ、ということで、RDBMSで位置情報を取り扱うときのtipsをご紹介。サンプルのクエリは PostgreSQL w/PostGIS 向けになっております。

都道府県

都道府県ごとのポリゴンデータを持つテーブルを用意して、任意の都道府県情報を持たない位置情報(Point なり Polygon なり)を都道府県ごとに取り出すことができます。データの正規化をきちんとやっていけるのが大きなメリットですね。

以下の
- id と name カラムを持つ prefectures テーブル
- prefecture_id と boundary(Polygon) カラムを持つ prefecture_zones テーブル
- boundary(Polygon) カラムを持つ hoges テーブル

の3テーブルがあるとすると

SELECT hoges.*
FROM hoges
INNER JOIN prefecture_zones ON ST_Intersects(hoges.boundary, prefecture_zones.boundary)
INNER JOIN prefectures ON prefecture_zones.prefecture_id = prefectures.id
WHERE prefectures.name = '東京都';

というクエリで、東京都の範囲を含む hoge を抽出できます
(記法は PostGIS)

おまけ・都道府県のポリゴンデータの作り方

ポリゴンデータは国土交通省から頂いて、QGISで適当にリダクションして使用すると良いでしょう。市区町村レベルまではこれで行けます。
それより細かいデータは総務省統計局にあります。

特定ジオグラフィーから一定距離内にあるジオグラフィーを得る

tips記事なので「ジオグラフィーとは？」という説明は省略します

ST_DWithin を使います。距離を測るのはST_Distanceですが、制約条件(WHEREとか)に ST_Distance を使ってもインデックスを利用しないので、インデックスを使う ST_DWithin を利用するべきです。

2回やればドーナツ状の検索もできますね。
↓サンプル
- name と boundary(Polygon) カラムを持つ hoges テーブル

SELECT
  hoges.name as name,
  ST_Distance(hoges.boundary, ST_GeographyFromText('POINT(139.707345 35.658634)')) as distance

FROM
  hoges
WHERE
  not ST_DWithin(hoges.boundary, ST_GeographyFromText('POINT(139.707345 35.658634)'), 0)
AND
  ST_DWithin(hoges.boundary, ST_GeographyFromText('POINT(139.707345 35.658634)'), 1000)
GROUP BY name, ph.user_id, distance
ORDER BY distance asc;

指定座標を含んでいないが boundary が指定座標からの距離が 1000m 以内の hoge の name と 距離を得ます。

終わりに

位置情報は楽しいのでみんな気軽に取り扱っていきましょう