postgresql計算兩點距離
下面兩種方法：
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)
)等價
ST_GeomFromText('POINT(115.97166453999147 28.716493914230423)',4326)與
ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326)等價
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)')等價
(::geography是postgis中的轉換類型語法,把geometry轉成geography)
補充：postgresql計算兩點歐式距離（經緯度地理位置）
我就廢話不多說了，大家還是直接看代碼吧~
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;
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- PostgreSQL 慢查詢SQL跟蹤操作
- CentOS PostgreSQL 12 主從復制(主從切換)操作
- PostgreSQL 查看表的主外鍵等約束關系詳解
- PostgreSQL 修改視圖的操作
- PostgreSQL 更新視圖腳本的注意事項說明
- postgreSQL中的row_number() 與distinct用法說明
- Postgresql 動態統計某一列的某一值出現的次數實例
- 淺談pg_hint_plan定制執行計劃
