postgres点线面缓冲查询sql

gis项目遇到需要查询 缓冲区分析问题，需要判断点线面之间相互包含的空间分析，总结了一下常用sql:

基于shp数据导入pg库的poi表
poitable表结构

--点缓冲分析 查出所选点的附近是否包含所需的各类poi点
select *, st_astext(geom) as wkt
from poitable
where ST_Covers(st_buffer(st_geomfromtext('POINT(105.422840233 0.981364336906)', 4326), 0.0032), geom)
  AND type in
      ('餐饮服务', '生活服务', '风景名胜', '金融保险服务', '住宿服务', '交通设施服务', '科教文化服务', '医疗保健服务',
       '购物服务');
       
--线缓冲分析  查出所选一条线周边 是否包含所需的各类poi点 
select *, st_astext(geom) as wkt
from poitable
where ST_Covers(st_buffer(ST_LineFromText(
                                  'linestring(105.4225685 0.9830948,105.4236208 0.9815797,105.4242180 0.9819105,105.4242187 0.9819137)',
                                  4326), 0.002), geom)
  AND type in ('餐饮服务', '金融保险服务', '生活服务');
  
--面缓冲分析 查出所选面是否包含所需的各类poi点  查询时，传入面的经纬度必须首尾相连
select *, st_astext(geom) as wkt
from poitable
where ST_Covers(ST_PolygonFromtext(
                        'polygon ((105.4212776 0.9836588,105.4218837 0.9806175,105.4240412 0.9805888,105.4251345 0.9839276,105.4251345 0.9839276,105.4212776 0.9836588))',
                        4326), geom)
  AND type in ('餐饮服务', '生活服务');


-- 路径分析 select st_astext(pgr_fromAtoB(${result.table},${result.lineListParam}));
-- select  ST_Line_Locate_Point(ST_GeometryN(v_res,1), v_statpoint) into v_perStart;
-- select  ST_Line_Locate_Point(ST_GeometryN(v_res,1), v_endpoint) into v_perEnd;
-- 4545
select st_astext(pgr_fromAtoB('jt_csdl_l', 215379.213, 108606.211, 212789.337, 109218.393));
select st_astext(pgr_fromAtoB('jt_csdl_l', 212151.442200001, 105658.544600002, 212307.0478, 105652.035000002));

--4326
select st_astext(pgr_fromAtoB_sq('sq_jhydl', 105.421333, 0.982946, 105.421333, 0.982946));
select st_astext(pgr_fromAtoB_sq('sq_jhydl', 105.4231509, 0.9842067, 105.4231998, 0.9817777));

select st_astext(pgr_fromAtoB_sq('sq_jhydl', 105.421333, 0.982946, 105.424259, 0.98104));
select st_astext(pgr_fromAtoB_sq('sq_jhydl', 105.421547, 0.983165, 105.426191, 0.98167));

select st_astext(pgr_fromAtoB_sq('sq_jhydl', 105.4148307, 0.9839017, 105.4208924, 0.9841309));
select st_astext(pgr_fromAtoB_sq('sq_jhydl', 105.4156078, 0.9805935, 105.4190242, 0.9842382));

6078, 0.9805935, 105.4190242, 0.9842382));