PostgreSQLを使って数値データの絶対値、二乗平均平方根(RMS)を計算して２つの値の距離を計算する

１次元の距離のテストデータテーブルの作成

まずテストデータテーブルを作成します。

DROP TABLE IF EXISTS distance;

CREATE TABLE distance (x1 int, x2 int);

INSERT INTO
  distance (x1, x2)
VALUES
  (5, 10),
  (11, 5),
  (2, 3),
  (-2, 4),
  (3, 7),
  (0, 5);

作成されたテーブルはこんな感じ

% psql -d db -c "select * from distance;"
 x1 | x2 
----+----
  5 | 10
 11 |  5
  2 |  3
 -2 |  4
  3 |  7
  0 |  5
(6 rows)

１次元の位置情報になります。

１次元のデータに対して絶対値と二乗平均平方根を計算するクエリ

SELECT
  abs(x1 - x2) AS abs,
  sqrt(power(x1 - x2, 2)) AS rms
FROM
  distance;

abs関数で絶対値

sqrt関数で平方根の計算

power関数で二乗の計算

ができます。

 abs | rms 
-----+-----
   5 |   5
   6 |   6
   1 |   1
   6 |   6
   4 |   4
   5 |   5
(6 rows)

２次元の距離のテストデータテーブルの作成

DROP TABLE IF EXISTS distance_2d;

CREATE TABLE distance_2d (x1 int, y1 int, x2 int, y2 int);

INSERT INTO
  distance_2d (x1, y1, x2, y2)
VALUES
  (0, 0, 2, 2),
  (11, 5, 3, 4),
  (1, 1, 2, 3),
  (-2, 4, 4, 3),
  (3, 7, 1, 1),
  (0, 1, 5, 5);

distance_2dというテーブルを作成します。

(x1, y1), (x2, y2)という座標のセットです。

２次元のデータに対して絶対値と二乗平均平方根を計算するクエリ

SELECT
  round(
    CAST(
      sqrt(power(x1 - x2, 2) + power(y1 - y2, 2)) AS numeric
    ),
    4
  ) AS dist
FROM
  distance_2d;

power関数で二乗しsqrt関数で平方根を求めています。

round関数で小数点４桁で四捨五入するためにCAST( ..... AS numeric ) とnumeric型に明示的に型変換を行っています。

  dist  
--------
 2.8284
 8.0623
 2.2361
 6.0828
 6.3246
 6.4031
(6 rows)

距離の計算は類似度計算などで意外と使えます。

POINT型を用いた距離計算の方法

PostgreSQLにはPOINT型と呼ばれる座標を扱うデータ構造があります。

ポイント型に変換すると距離演算子<->が使えます。

SELECT
  point(x1, y1) <-> point(x2, y2) AS dist
FROM
  distance_2d;

        dist        
--------------------
 2.8284271247461903
   8.06225774829855
   2.23606797749979
 6.0827625302982185
  6.324555320336759
 6.4031242374328485
(6 rows)