postgresql计算两点距离的2种方法小结
项目招商找A5 快速获取精准代理名单
这篇文章主要介绍了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种方法小结有什么看法呢？互联网品牌制作专家愿与您共同探讨！版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请加微信号oem365 举报，一经查实，本站将立刻删除。
