明月美好生活网明月美好生活网明月美好生活网

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;

文章来源:脚本之家

来源地址:https://www.jb51.net/article/204788.htm

未经允许不得转载:明月美好生活网 » postgresql 计算两点距离的2种方法小结