快捷導(dǎo)航

postgresql 計(jì)算兩點(diǎn)距離的2種方法小結(jié)

更新時(shí)間：2021年01月26日 10:25:50 作者：溫水煮螞蟻

這篇文章主要介紹了postgresql 計(jì)算兩點(diǎn)距離的2種方法小結(jié)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧

postgresql計(jì)算兩點(diǎn)距離

下面兩種方法：

select 
ST_Distance(
 ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326)::geography,
 ST_SetSRID(ST_MakePoint(106.00231199774656,29.719258550486572),4326)::geography
),
ST_Length(
 ST_MakeLine(
 ST_MakePoint(115.97166453999147,28.716493914230423),
 ST_MakePoint(106.00231199774656,29.719258550486572)
 )::geography
)

備注：

ST_GeomFromText('LINESTRING(115.97166453999147 28.716493914230423,106.00231199774656 29.719258550486572)')與
ST_MakeLine(
	ST_MakePoint(115.97166453999147,28.716493914230423),
	ST_MakePoint(106.00231199774656,29.719258550486572)
)等價(jià)
 
ST_GeomFromText('POINT(115.97166453999147 28.716493914230423)',4326)與
ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326)等價(jià)
 
ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326)::geography與
Geography(ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326))、
ST_GeographyFromText('SRID=4326;POINT(115.97166453999147 28.716493914230423)')等價(jià)
(::geography是postgis中的轉(zhuǎn)換類型語(yǔ)法,把geometry轉(zhuǎn)成geography)

補(bǔ)充：postgresql計(jì)算兩點(diǎn)歐式距離（經(jīng)緯度地理位置）

我就廢話不多說(shuō)了，大家還是直接看代碼吧~

create or replace function getdistance
( 
 lon1 numeric,
 lat1 numeric, 
 lon2 numeric, 
 lat2 numeric 
) 
returns int 
as 
$body$ 
declare 
v_distance numeric;
v_earth_radius numeric;
radLat1 numeric;
radLat2 numeric;
v_radlatdiff numeric;
v_radlngdiff numeric;
begin 
 --地球半徑
 v_earth_radius:=6378137;
  
 radLat1 := lat1 * pi()/180.0;
 radLat2 := lat2 * pi()/180.0;
 v_radlatdiff := radLat1 - radLat2;
 v_radlngdiff := lon1 * pi()/180.0 - lon2 * pi()/180.0; 
 v_distance := 2 * asin(sqrt(power(sin(v_radlatdiff / 2), 2) + cos(radLat1) * cos(radLat2) * power(sin(v_radlngdiff/2),2)));
 v_distance := round(v_distance * v_earth_radius);
 return v_distance; 
end;
$body$
language 'plpgsql' volatile;

create or replace function getdistance
( 
 i_lngbegin real,
 i_latbegin real, 
 i_lngend real, 
 i_latend real 
) 
returns float 
as 
$body$
/*
 * 
 * select getdistance_bygispoint(116.281524,39.957202,117.648673,38.42584) as distance;
 * */ 
declare 
v_distance real;
v_earth_radius real;
v_radlatbegin real;
v_radlatend real;
v_radlatdiff real;
v_radlngdiff real;
begin 
 --地球半徑
 v_earth_radius:=6378.137;
  
 v_radlatbegin := i_latbegin * pi()/180.0;
 v_radlatend := i_latend * pi()/180.0;
 v_radlatdiff := v_radlatbegin - v_radlatend;
 v_radlngdiff := i_lngbegin * pi()/180.0 - i_lngend * pi()/180.0; 
 v_distance := 2 * asin(sqrt(power(sin(v_radlatdiff / 2), 2) + cos(v_radlatbegin) * cos(v_radlatend) * power(sin(v_radlngdiff/2),2)));
 v_distance := v_distance * v_earth_radius*1000; 
 return v_distance; 
end;
$body$ 
language 'plpgsql' volatile;

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。

您可能感興趣的文章: