目次 1 内容 2 関数定義 3 使い方 4 例 5 問題点

内容

緯度経度で示される２点間の距離を返す、PostgreSQL用の関数について

関数定義

create function distance(numeric, numeric, numeric, numeric)
	returns numeric as '
declare
	v_lat1 numeric := $1;
	v_lon1 numeric := $2;
	v_lat2 numeric := $3;
	v_lon2 numeric := $4;
	v_pi numeric := 3.14159265358979;
	v_rd numeric := 0.0174532925199433;
	v_dp numeric;
	v_dr numeric;
	v_p numeric;
	v_m numeric;
	v_n numeric;
begin
	v_lat1 := v_lat1 * v_rd;
	v_lon1 := v_lon1 * v_rd;
	v_lat2 := v_lat2 * v_rd;
	v_lon2 := v_lon2 * v_rd;

	v_dp = abs(v_lat1 - v_lat2);
	v_dr = abs(v_lon1 - v_lon2);

	v_p = v_lat1 + ((v_lat2 - v_lat1) / 2);	
	
	v_m = 6335439 / sqrt(pow(1 - 0.006694 * pow(sin(v_p), 2), 3));
	v_n = 6378137 / sqrt(1 - 0.006694 * pow(sin(v_p), 2));

	return sqrt(pow(v_m * v_dp, 2) + pow(v_n * cos(v_p) * v_dr, 2));
end
' language plpgsql;

使い方

A点からB点の距離を調べると仮定し、

distance(A点緯度,A点経度,B点緯度,B点経度)

と呼び出す。

例

GPSの情報を元に、200m以内の店舗を近い順に最大10件取得

select
	*
from
	(
		select
			*,
			distance(
				shop.latilude,
				shop.longitude, 
				34.29452296537488,
				132.6221466064453) as distance
		from
 		   shop
	)
where
	distance <= 200.0
order by
	distance
limit 10;

問題点

若干遅い。まともに大量件数を扱うならば、PostGISを使用するべきであるが、数千件レベルの対象レコードであれば十分実用的に使える。