电子围栏预警 - cytggit/Map-openlayers GitHub Wiki

判断定位点是否在电子围栏内

sql函数

-- Function: pgr_pointinpolygon(character, double precision, double precision, character)

-- DROP FUNCTION pgr_pointinpolygon(character, double precision, double precision, character);

CREATE OR REPLACE FUNCTION pgr_pointinpolygon(tbl character, locatex double precision, locatey double precision, locatefloor character)
RETURNS character AS
$BODY$    declare     
v_fid character varying;--电子围栏的fid
begin       
 
execute	'SELECT fid FROM ' ||tbl||
	' WHERE floor_id = '''||locatefloor||''''
	' AND ST_Within (ST_Geometryfromtext(''point('||locatex||' '||locatey||')'',4326),geom) = ''t'''
	into v_fid;

    return v_fid;          
        
end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION pgr_pointinpolygon(character, double precision, double precision, character)
OWNER TO postgres;

sql视图

SELECT el. NAME,el.type_id,el.geom
FROM electronic_fence AS el,
	(SELECT fid FROM pgr_pointinpolygon (
		'electronic_fence',
		%locatex%,
		%locatey%,
		'%locatefloor%'
		) AS fid
	) AS lo
WHERE el.fid = lo.fid
输入locate参数的默认值为0