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.9716645399914728.716493914230423,106.0023119977465629.719258550486572)')与
ST_MakeLine(
ST_MakePoint(115.97166453999147,28.716493914230423),
ST_MakePoint(106.00231199774656,29.719258550486572)
)等价
ST_GeomFromText('POINT(115.9716645399914728.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.9716645399914728.716493914230423)')等价
(::geography是postgis中的转换类型语法,把geometry转成geography)
补充：postgresql计算两点欧式距离（经纬度地理位置）
我就废话不多说了，大家还是直接看代码吧~
createorreplacefunctiongetdistance ( lon1numeric, lat1numeric, lon2numeric, lat2numeric ) returnsint as $body$ declare v_distancenumeric; v_earth_radiusnumeric; radLat1numeric; radLat2numeric; v_radlatdiffnumeric; v_radlngdiffnumeric; 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); returnv_distance; end; $body$ language'plpgsql'volatile;
createorreplacefunctiongetdistance ( i_lngbeginreal, i_latbeginreal, i_lngendreal, i_latendreal ) returnsfloat as $body$ /* * *selectgetdistance_bygispoint(116.281524,39.957202,117.648673,38.42584)asdistance; **/ declare v_distancereal; v_earth_radiusreal; v_radlatbeginreal; v_radlatendreal; v_radlatdiffreal; v_radlngdiffreal; 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; returnv_distance; end; $body$ language'plpgsql'volatile;
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。