postgresql数据库自定义函数

1、计算管线是否在多边形范围内
参数：管线首尾坐标，多边形最小x，最大x，最小y，最大y

CREATE OR REPLACE  function fun_isinarea(x1 numeric,y1 numeric,x2 numeric,y2 numeric,xmin numeric,xmax numeric,ymin numeric,ymax numeric) returns BOOLEAN 
as 
$BODY$
declare bResult boolean=false;
begin
   if((x1>=xmin and x1<=xmax) and (y1>=ymin and y1<=ymax)) then bResult=true;
   end if;
   if((x2>=xmin and x2<=xmax) and (y2>=ymin and y2<=ymax)) then bResult=true;
   end if;
   return bResult ;
end;
$BODY$
LANGUAGE plpgsql VOLATILE

查询：

select * from (
    select *,fun_isinarea(x1,y1,x2,y2,118.6918,118.7918,37.41341,37.42341) as inArea from water
) t where inArea is true

2、管线是否在 某点的圆形范围内
管线头坐标x1,y1 管线尾坐标 x2,y2 查询点坐标 x3,y3 半径10m

CREATE OR REPLACE FUNCTION fun_calcdistance(x1 numeric, y1 numeric, x2 numeric, y2 numeric, x3 numeric, y3 numeric)
  RETURNS numeric AS $BODY$
DECLARE pResult1 numeric;
DECLARE pResult2 numeric;
declare a NUMERIC;
declare b NUMERIC;
declare c NUMERIC;
declare d NUMERIC;
declare s1 NUMERIC;
declare s2 NUMERIC;
declare pResult numeric;
begin
   --pResult=abs((x1-x3)*(y2-y3)-(x2-x3)*(y1-y3))/sqrt((x1-x2)^2+(y1-y2)^2);


    a=sin(abs((x1* pi()/180)-(x3*pi()/180))/2);
    b=sin(abs((y1* pi()/180)-(y3*pi()/180))/2);
  s1 = 2 * asin(sqrt(b^2 + cos(y1* pi()/180) * cos(y3* pi()/180) * a^2));
  s1 = s1 * 6378137;
  pResult1 = (s1 * 10000) / 10000;
  
  c=sin(abs((x2* pi()/180)-(x3*pi()/180))/2);
    d=sin(abs((y2* pi()/180)-(y3*pi()/180))/2);
  s2 = 2 * asin(sqrt(d^2 + cos(y2* pi()/180) * cos(y3* pi()/180) * c^2));
  s2 = s2 * 6378137;
  pResult2 = (s2 * 10000) / 10000;
    
 
  if(pResult1 > pResult2) then pResult=pResult2;
  else pResult=pResult1;
  end if;
  return pResult;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

查询(某个点的10米内的管线)：

select * from (
select *,fun_calcdistance(x1,y1,x2,y2,118.6904,37.41261) as dist from water
) t where dist<=10