postgresql 计算两点距离的2种方法小结

 

这篇文章主要介绍了postgresql 计算两点距离的2种方法小结,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧。

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;

v_distance; end;$body$ language ‘plpgsql’ volatile;

文章

①本站所有CMS源码、杰奇CMS模板、PTCMS源码模板、YGBOOK源码模板、帝国CMS源码模板等仅用于学习和交流,勿用于商业。
②本站资源有安装及使用文档,安装使用请自行探索,如您对购买的程序或是模板无法胜任安装工作,请点击付费安装。
③本站资源来源网络或者用户投稿,切勿私自传播于网络,否则将追究法律责任。且仅供学习交流之用,如有侵权请联系删除。
④如果资源失效或下载链接错误请联系站长。
悟空资源网 » postgresql 计算两点距离的2种方法小结

发表评论