導入 - 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
テスト準備 - 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
テスト - 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 */ ;
−ω− 他にも月面検索で近くのクレーターを調べる等、色々なことに使えて楽しそう！