読者です 読者をやめる 読者になる 読者になる

bekkou68の日記

開発しているサービス, IT技術, 英語など。

PostgreSQL の幾何演算で「重なり, 含み, 距離」の GIS 位置情報を検索する - Search GIS Position Information Likes "overlap, contain, distance" by Geometric Operation of PostgreSQL

PostgreSQL

導入 - Introduction

目的 - Purpose

PostgreSQL の強力な幾何演算を紹介する。 - To introduce a powerful geometric operation of PostgreSQL.

用途 - Usage

地理的な検索ができる。例えば、家から 10 km 以内のコンビニを検索したいなど。 - You can do geometric search, for example, find some convenience stores they are located within 10 km from your house.

全容 - Outline

3 つの幾何演算を定義し、その幾何演算を実現する演算式を記述する。最後にテストを行い動作の検証を行う。なお、これらの演算式に用いる全ての幾何データ演算子は、あらかじめ PostgreSQL に用意されている。 - I'll define 3 geometric operations and write operational expressions which realize them. Finally, I'll test them and check their results. For your information, the geometric data operators which are used by the operational expressions are prepared by PostgreSQL.

幾何演算 - Geometric Operators

前提 - Precondition

今回は、オペランドは幾何データ型の一つである CIRCLE 型を用いる。オペランドとして BOX 型, POLYGON 型等、他の幾何データ型も使える。 - In this time, I'll use the CIRCLE type, which is one of the geometric data types, as an operand. Also BOX type, POLYGON type, and so on, can be used as operands.

重なっているか - overlap

CIRCLE(POINT(a.x, a.y), a.r) && CIRCLE(POINT(b.x, b.y), b.r)
含んでいるか - contain

CIRCLE(POINT(a.x, a.y), a.r) ~ CIRCLE(POINT(b.x, b.y), b.r)
距離 r 以内 - near distance r

CIRCLE(POINT(a.x, a.y), a.r) <-> CIRCLE(POINT(b.x, b.y), b.r) <= /*r*/
AND
CIRCLE(POINT(a.x, a.y), a.r) <-> CIRCLE(POINT(b.x, b.y), b.r) > 0
参考文献 - References

豊富な幾何データ型や幾何データ演算子。 - A lot of geometric data types and geometric data operators.

幾何関数と演算子

テスト準備 - Test Preparation

テストデータ - Test Data

以下のグラフの位置関係にある円のデータを用意する。各中心点は「名前((x, y), r)」という記述になっている。(x, y) は座標、r は半径を示す。 - Prepare the data of circles which are located on the following graph. Each center point is described as "Name((x, y), r)". (x, y) is a coordinate and r is a radius.

円 A を中心に考えてテストする。他の円は円 A と以下のような関係になっている。 - I'll test with thinking circle A as a center. The other circles have the following relationships with circle A.

  • 円 A と円 B は外接する - Circle A circumscribes circle B
  • 円 A と円 C は交わる - Circle A intersects circle C
  • 円 A は円 D を内包する - Circle A includes and not inscribes circle D
  • 円 A は円 E の外部にあり交わらない - Circle A externally locates outside of circle E and does not intersect
  • 円 A と円 F は内接する - Circle A inscribes circle F
クエリ - Query

円のデータを作成。 - Create the data of circle.

CREATE TABLE
  geometric_data
  (
    id INTEGER PRIMARY KEY
   ,circle CIRCLE
  )
;

INSERT INTO geometric_data(id, circle) VALUES(1, '((2, 2), 2)'); -- A
INSERT INTO geometric_data(id, circle) VALUES(2, '((6, 2), 2)'); -- B
INSERT INTO geometric_data(id, circle) VALUES(3, '((2, 5), 2)'); -- C
INSERT INTO geometric_data(id, circle) VALUES(4, '((2, 1), 0.5)'); -- D
INSERT INTO geometric_data(id, circle) VALUES(5, '((-4, 2), 2)'); -- E
INSERT INTO geometric_data(id, circle) VALUES(6, '((0.5, 2), 0.5)'); -- F
参考文献 - References

2 つの円の位置関係。 - A position relationship of two circles.

円 (数学) - Wikipedia

テスト - Test

重なっているか - overlap

/* A overlap B is TRUE */
SELECT
  a.circle && b.circle
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 2 /* B */
;

/* A overlap C is TRUE */
SELECT
  a.circle && b.circle
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 3 /* C */
;

/* A overlap D is TRUE */
SELECT
  a.circle && b.circle
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 4 /* D */
;

/* A overlap E is FALSE */
SELECT
  a.circle && b.circle
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 5 /* E */
;

/* A overlap F is TRUE */
SELECT
  a.circle && b.circle
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 6 /* F */
;
含んでいるか - contain

/* A contain B is FALSE */
SELECT
  a.circle ~ b.circle
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 2 /* B */
;

/* A contain C is FALSE */
SELECT
  a.circle ~ b.circle
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 3 /* C */
;

/* A contain D is TRUE */
SELECT
  a.circle ~ b.circle
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 4 /* D */
;

/* A contain E is FALSE */
SELECT
  a.circle ~ b.circle
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 5 /* E */
;

/* A contain F is TRUE */
SELECT
  a.circle ~ b.circle
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 6 /* F */
;
距離 r 以内 - near distance r

/* ==================== Let r is 2 ==================== */
/* A near B distance r is FALSE */
SELECT
  a.circle <-> b.circle <= 2 AND a.circle <-> b.circle > 0
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 2 /* B */
;

/* A near C distance r is FALSE */
SELECT
  a.circle <-> b.circle <= 2 AND a.circle <-> b.circle > 0
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 3 /* C */
;

/* A near D distance r is FALSE */
SELECT
  a.circle <-> b.circle <= 2 AND a.circle <-> b.circle > 0
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 4 /* D */
;

/* A near E distance r is TRUE (0 < R <= r) */
SELECT
  a.circle <-> b.circle <= 2 AND a.circle <-> b.circle > 0
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 5 /* E */
;

/* A near F distance r is FALSE */
SELECT
  a.circle <-> b.circle <= 2 AND a.circle <-> b.circle > 0
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 6 /* F */
;

/* ==================== Let r is 2.1 ==================== */
/* A near E distance r is TRUE (0 < R <= r) */
SELECT
  a.circle <-> b.circle <= 2.1 AND a.circle <-> b.circle > 0
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 5 /* E */
;

/* ==================== Let r is 1.9 ==================== */
/* A near E distance r is FALSE (r < R) */
SELECT
  a.circle <-> b.circle <= 1.9 AND a.circle <-> b.circle > 0
FROM
  geometric_data AS a, geometric_data AS b
WHERE
  a.id = 1 /* A */ AND b.id = 5 /* E */
;


−ω− 他にも月面検索で近くのクレーターを調べる等、色々なことに使えて楽しそう!