PostgreSQLとOracleで緯度経度から半径nメートル内検索を実行してみる。

| コメントをどうぞ

昨今では各種データベースで地図情報を扱うジオメトリ型がサポートされています。
今回はその中からPostgreSQL(+ PostGIS)とOracle(+ Oracle Spatial)についてデータベースに保存された緯度経度から半径nメートル内にある対象を検索する方法についてご紹介します。

PostgreSQL編

まずPostgreSQL。PostgreSQLの場合、ジオメトリ型を扱う為にはPostgreSQLのオプション機能であるPostGISのインストールが必要になります。
未導入の場合はPostgreSQLおよびPostGISのインストールを行ってください。PostgreSQLおよびPostGISのインストールに関しては検索すれば多くの情報がありますのでここでは割愛します。あしからず。

なお、今回の紹介で利用しているPostgreSQLのバージョンは PostGISのバージョンは2.1となります。

テーブルの作成

まず最初に緯度経度の情報を持ったテーブルを作成します。
今回の例では以下のようなテーブルを作成します。

-- テーブルの作成
CREATE TABLE geotable (
    id   SERIAL NOT NULL PRIMARY KEY,
    name varchar (255) NOT NULL,
    geom geography(POINT, 4326) NOT NULL  -- 緯度経度を保持するカラム。「4326」は世界測地系を表すSRID。
);

データの挿入

次にデータを挿入します。

INSERT INTO geotable (name, geom) VALUES
('上野駅', ST_GeographyFromText('SRID=4326;POINT(139.777254 35.713768)')),
('西郷隆盛像', ST_GeographyFromText('SRID=4326;POINT(139.774029 35.711846)')),
('上野の森美術館', ST_GeographyFromText('SRID=4326;POINT(139.774744 35.712737)')),
('不忍池弁財天', ST_GeographyFromText('SRID=4326;POINT(139.770872 35.712351)')),
('野口英世博士像', ST_GeographyFromText('SRID=4326;POINT(139.775696 35.716293)')),
('国立西洋美術館', ST_GeographyFromText('SRID=4326;POINT(139.775803 35.71542)')),
('国立科学博物館', ST_GeographyFromText('SRID=4326;POINT(139.776544 35.716319)')),
('東京都美術館', ST_GeographyFromText('SRID=4326;POINT(139.772776 35.717186)')),
('東京国立博物館', ST_GeographyFromText('SRID=4326;POINT(139.776462 35.718883)')),
('花やしき', ST_GeographyFromText('SRID=4326;POINT(139.794547 35.71528)')),
('雷門', ST_GeographyFromText('SRID=4326;POINT(139.792692 35.710635)'));

上記データはこちらのブログから拝借しました。

なお、上記例ではテーブルの作成時およびデータ挿入の際に明示的にSRID(上記例の「4326」)を指定していますが、世界測地系を利用する場合は以下のように省略可能です。

-- テーブルの作成
CREATE TABLE geotable (
    id   SERIAL NOT NULL PRIMARY KEY,
    name varchar (255) NOT NULL,
    geom geography(POINT) NOT NULL  -- SRIDを省略
);

-- データの挿入
INSERT INTO geotable (name, geom) VALUES
('上野駅', ST_GeographyFromText('POINT(139.777254 35.713768)')), -- SRIDを省略
    :

空間インデックスの作成

ジオメトリ型のカラムに対するクエリの実行速度を向上させるために空間インデックスを作成します。

-- 空間インデックスの作成
CREATE INDEX gist_geotable on geotable USING GIST (geom);

なお、PostGISでは空間インデックスは作成していなくてもGEOMETRYオブジェクト用の各関数は利用可能なようです。

データの検索

作成されたgeotableから、「上野駅から半径300m以内の対象物」を検索する場合、以下のようなクエリとなります。

SELECT
    name, 
    ST_X(geom::geometry) as longitude, -- 経度(ST_X関数はGEOMETRY型用の関数なので明示的にキャスト)
    ST_Y(geom::geometry) as latitude,   -- 緯度(ST_Y関数はGEOMETRY型用の関数なので明示的にキャスト)
    ST_Distance('SRID=4326;POINT(139.777254 35.713768)', geom) as dist -- 基準点からの距離(m)
FROM geotable
WHERE ST_DWithin(geom, ST_GeographyFromText('SRID=4326;POINT(139.777254 35.713768)'), 300.0)
ORDER BY id;

上記クエリを実行すると以下のような結果が得られます。

name longitude latitude dist
上野駅 139.777254 35.713768 0
上野の森美術館 139.774744 35.712737 254.308127877
国立西洋美術館 139.775803 35.71542 225.468916585
国立科学博物館 139.776544 35.716319;290 290.24270722

Oracle編

次にPostgreSQLの場合と同じ検索をOracleで実行してみます。なお、今回の紹介で利用しているOracleのバージョンは 11g リリース2となります。
Oracleにおいてはジオメトリ型データを扱うためにOracle Spatialというオプション機能を使用します。

Oracle SpatialはEnterprise Editionに含まれるオプションで、11gでは自動構成したデータベースでは特に何もしなくてもインストールされていました。
詳細についてはOracle Spatialの各種マニュアルをご参照ください。

テーブルの作成

PostgreSQL編で説明したテーブルと同様の構成の緯度経度の情報を持ったテーブルを作成します。

-- テーブルの作成
CREATE TABLE geotable (
    id NUMBER(10) NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    geom SDO_GEOMETRY NOT NULL -- 緯度経度を保持するカラム。
);

データの挿入

次にデータを挿入します。

INSERT ALL
INTO geotable (id, name, geom) VALUES (1, '上野駅', SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(139.777254, 35.713768, NULL), NULL, NULL))
INTO geotable (id, name, geom) VALUES (2, '西郷隆盛像', SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(139.774029, 35.711846, NULL), NULL, NULL))
INTO geotable (id, name, geom) VALUES (3, '上野の森美術館', SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(139.774744, 35.712737, NULL), NULL, NULL))
INTO geotable (id, name, geom) VALUES (4, '不忍池弁財天', SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(139.770872, 35.712351, NULL), NULL, NULL))
INTO geotable (id, name, geom) VALUES (5, '野口英世博士像', SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(139.775696, 35.716293, NULL), NULL, NULL))
INTO geotable (id, name, geom) VALUES (6, '国立西洋美術館', SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(139.775803, 35.71542, NULL), NULL, NULL))
INTO geotable (id, name, geom) VALUES (7, '国立科学博物館', SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(139.776544, 35.716319, NULL), NULL, NULL))
INTO geotable (id, name, geom) VALUES (8, '東京都美術館', SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(139.772776, 35.717186, NULL), NULL, NULL))
INTO geotable (id, name, geom) VALUES (9, '東京国立博物館', SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(139.776462, 35.718883, NULL), NULL, NULL))
INTO geotable (id, name, geom) VALUES (10, '花やしき', SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(139.794547, 35.71528, NULL), NULL, NULL))
INTO geotable (id, name, geom) VALUES (11, '雷門', SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(139.792692, 35.710635, NULL), NULL, NULL))
SELECT * FROM DUAL;

PostgreSQLの場合と比べて緯度経度情報の扱いが複雑になっています。
SDO_GEOMETRYのコンストラクタの第1引数はジオメトリのタイプを示します。「2001」は2次元平面上の点を意味します。第2引数に指定されている「8307」は世界測地系を示すSRID(座標参照系ID)です。

空間メタデータの挿入

Oracle Spatialではジオメトリ型のカラムに対して空間演算関数を利用するためにUSER_SDO_GEOM_METADATA(パブリック・シノニム)に対象カラムのメタデータを挿入する必要があります。
今回の例の場合、geomカラムは緯度経度を表すので以下のようにメタデータを挿入します。

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) 
VALUES ('geotable', 'geom', 
   SDO_DIM_ARRAY 
     (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5), 
     SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)), 
   8307);

空間インデックスの作成

ジオメトリ型のカラムに対するクエリの実行速度を向上させるために空間インデックスを作成します。

-- 空間インデックスの作成
CREATE INDEX geom_idx ON geotable(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

なお、OracleではPostgreSQLの場合と異なり、GEOMETRYオブジェクト用の各関数を利用するためには空間インデックスの作成が必須となっています。
また、空間インデックスを作成するためには前述の空間メタデータの挿入が必須となりますので注意してください。

データの検索

作成されたgeotableから、「上野駅から半径300m以内の対象物」を検索する場合、Oracleでは以下のようなクエリとなります。

SELECT
    g.name, 
    g.geom.SDO_POINT.X as longitude,  -- 経度
    g.geom.SDO_POINT.Y as latitude,    -- 緯度
    SDO_GEOM.SDO_DISTANCE(g.geom, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(139.777254, 35.713768, NULL), NULL, NULL), 0.05) as dist -- 基準点からの距離(m)
FROM geotable g 
WHERE SDO_WITHIN_DISTANCE(geom, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(139.777254, 35.713768, NULL), NULL, NULL), 'distance=300')='TRUE'
ORDER BY g.id;

上記クエリを実行すると以下のような結果が得られます。

name longitude latitude dist
上野駅 139.777254 35.713768 0
上野の森美術館 139.774744 35.712737 254.308120382464
国立西洋美術館 139.775803 35.71542 225.46891352967
国立科学博物館 139.776544 35.716319;290 290.242701941376

なお、Oracleの場合、SELECT句の中でOracle SpatialのGEOMETRYオブジェクト(上記クエリ中のSDO_POINTなど)を参照する場合はテーブル別名が必須となります。
筆者はこれが分からずにずいぶんとハマりましたのでご注意を・・・

本稿では申し訳ありませんがPostGISやOracle Spatialの詳細については特に説明しておりません。
PostGISおよびOracle Spatialにつきましてはそれぞれのマニュアル等をご参照ください。

PostGIS 2.0.0マニュアル日本語訳
Oracle Spatial開発者ガイド 11gリリース2

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>