PostGIS 缓冲区分析（查询距离范围内要素）

说明：

遇到个需求：需要查询事发点周围100米内的人和车，这个通过PostGIS的ST_DWithin函数很容易实现。
但是在实现过程中，遇到了三个不同的问题，在此总结一下。

解决方案：

方案一：

这种方式适用于PostGIS库，在库里直接写SQL实现，入参直接可以用PostGIS的geometry格式。

--调用方式（参数：表名，坐标系id，表主键ID，缓冲区半径，中心点）
select * from enc_buffer_bygeom('fm',3857,'gid',100,'0101000020110F0000F2D24D3662CA6841480C02EB46545241');

函数如下：

-- FUNCTION: public.func_buffer_bygeom(character varying, integer, character varying, double precision, character varying)
-- DROP FUNCTION public.func_buffer_bygeom(character varying, integer, character varying, double precision, character varying);
CREATE OR REPLACE FUNCTION public.func_buffer_bygeom(
	tb character varying,
	qsrid integer,
	qid character varying,
	qbuffer double precision,
	qgeom character varying)
    RETURNS TABLE(v_gid integer, v_res geometry, v_dis double precision) 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE STRICT 
    ROWS 1000
    
AS $BODY$  

declare  
    v_gid integer; --主键
    v_res geometry; --要素
    v_dis double precision; --与目标距离
begin 
    RETURN QUERY EXECUTE 
    'select '||qId||',geom, ST_Distance(geom,'''||qGeom||''') from ' ||tb|| ' where ST_DWithin(geom,'''||qGeom||''','||qBuffer||')' ; 

end;  

$BODY$;

ALTER FUNCTION public.func_buffer_bygeom(character varying, integer, character varying, double precision, character varying)
    OWNER TO postgres;

方案二：

这种方式适用于PostGIS库里的查询（有geom字段的），就可以直接用此函数查询。
相较于方案一的话，他更适合发布到geoserver中供前端调用，因为前端获取WKT格式较方便。

--调用方式（参数：表名，坐标系id，表主键ID，缓冲区半径，中心点）
select * from func_buffer('layer_grid',3857,'id',10,'POINT(12988813.522 4798555.074)');

函数如下：

-- FUNCTION: public.func_buffer(character varying, integer, character varying, double precision, character varying)
-- DROP FUNCTION public.func_buffer(character varying, integer, character varying, double precision, character varying);
CREATE OR REPLACE FUNCTION public.func_buffer(
	tb character varying,
	qsrid integer,
	qid character varying,
	qbuffer double precision,
	qgeom character varying)
    RETURNS TABLE(v_gid integer, v_res geometry, v_dis double precision) 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE STRICT 
    ROWS 1000
    
AS $BODY$  

declare  
    v_gid integer; --主键
    v_res geometry; --要素
    v_dis double precision; --与目标距离
begin 
    RETURN QUERY EXECUTE 
    'select '||qId||',geom, ST_Distance(geom,ST_Geometryfromtext('''||qGeom||''','||qSrid||')) from ' ||tb|| ' where ST_DWithin(geom,ST_Geometryfromtext('''||qGeom||''','||qSrid||'),'||qBuffer||')' ; 

end;  

$BODY$;

ALTER FUNCTION public.func_buffer(character varying, integer, character varying, double precision, character varying)
    OWNER TO postgres;

方案三：

这种方式适用于对外部表做缓冲区分析（外部表只有x和y字段，没有geom字段）
这种比较常见于业务库和GIS空间数据库分开的情况下（我们是mysql+postgis），业务库只有存Xy字段，并不支持postgis的geometry类型
所以操作前需要通过外表关联，将mysql表关联到postgis，接着根据mysql中xy字段，构造geom字段
外表关联方法参见：https://www.cnblogs.com/giser-s/p/11208818.html

--调用方式（参数：表名，坐标系id，表主键ID，缓冲区半径，中心点）
select * from func_buffer_fetable('layer_grid',3857,'id','lng','lat',10,'POINT(12988813.522 4798555.074)');

函数如下：

-- FUNCTION: public.func_buffer_fetable(character varying, integer, character varying, character varying, character varying, double precision, character varying)
-- DROP FUNCTION public.func_buffer_fetable(character varying, integer, character varying, character varying, character varying, double precision, character varying);
CREATE OR REPLACE FUNCTION public.func_buffer_fetable(
	tb character varying,
	qsrid integer,
	qid character varying,
	qxfield character varying,
	qyfield character varying,
	qbuffer double precision,
	qgeom character varying)
    RETURNS TABLE(v_gid integer, v_res geometry, v_dis double precision) 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE STRICT 
    ROWS 1000
    
AS $BODY$  

declare  
    v_gid integer; --主键
    v_res geometry; --要素
    v_dis double precision; --与目标距离
	v_record record;
begin 
RETURN QUERY EXECUTE  
	'select '||qId||',
	ST_Geometryfromtext(''POINT( ''||'|| qxfield ||'||'' ''||'|| qyfield ||'||'')'','||qSrid||'),
	ST_Distance(ST_Geometryfromtext(''POINT( ''||'|| qxfield ||'||'' ''||'|| qyfield ||'||'')'','||qSrid||'),ST_Geometryfromtext('''||qGeom||''','||qSrid||')) 
	from ' ||tb|| 
	' where ST_DWithin(ST_Geometryfromtext(''POINT( ''||'|| qxfield ||'||'' ''||'|| qyfield ||'||'')'','||qSrid||'),ST_Geometryfromtext('''||qGeom||''','||qSrid||'),'||qBuffer||')';
end;  

$BODY$;

ALTER FUNCTION public.func_buffer_fetable(character varying, integer, character varying, character varying, character varying, double precision, character varying)
    OWNER TO postgres;

方案四：

这种方式提前将PostGIS库中geometry格式转成了WKT格式（geomtext字段），方便直接查询。
测试产物，不是很推荐这么做。

--调用方式（参数：表名，坐标系id，表主键ID，缓冲区半径，中心点）
select * from func_buffer_bytxt('layer_grid',3857,'id',10,'POINT(12988813.522 4798555.074)');

函数如下：

-- FUNCTION: public.func_buffer_bytxt(character varying, integer, character varying, double precision, character varying)
-- DROP FUNCTION public.func_buffer_bytxt(character varying, integer, character varying, double precision, character varying);
CREATE OR REPLACE FUNCTION public.func_buffer_bytxt(
	tb character varying,
	qsrid integer,
	qid character varying,
	qbuffer double precision,
	qgeom character varying)
    RETURNS TABLE(v_gid integer, v_res geometry, v_dis double precision) 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE STRICT 
    ROWS 1000
    
AS $BODY$  

declare  
    v_gid integer; --主键
    v_res geometry; --要素
    v_dis double precision; --与目标距离
begin 
    RETURN QUERY EXECUTE 
    'select '||qId||',geom, ST_Distance(st_geomfromtext(geomtext,'||qSrid||'),ST_Geometryfromtext('''||qGeom||''','||qSrid||')) from ' ||tb|| ' where ST_DWithin(geom,ST_Geometryfromtext('''||qGeom||''','||qSrid||'),'||qBuffer||')' ; 

end;  

$BODY$;

ALTER FUNCTION public.func_buffer_bytxt(character varying, integer, character varying, double precision, character varying)
    OWNER TO postgres;