乘风原创程序

  • postgresql 计算两点距离的2种方法小结
  • 2021/1/26 13:07:10
  • 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;

    以上为个人经验,希望能给大家一个参考,也希望大家多多支持本教程网。如有错误或未考虑完全的地方,望不吝赐教。