【PostGIS】实时坐标-电子围栏-判断-(参考遥想公瑾当年postgres+socket.io+nodejs实时地图应用实践)
2023-09-11 14:17:09 时间
CREATE OR REPLACE FUNCTION "public"."process_t_gps"() RETURNS "pg_catalog"."trigger" AS $BODY$ DECLARE rec record; ewith record; BEGIN IF (TG_OP = 'DELETE') THEN --插入的GPS都是4326的经纬度,我们将在3857的谷歌底图上显示数据,发送转换后的3857出去 --select TG_OP TG_OP,OLD.id,ST_AsGeojson(ST_AsText(ST_Transform(OLD.geom,3857))) geom,gid as null into rec; --perform pg_notify('gps',row_to_json(rec)::text); select TG_OP TG_OP,OLD.id,ST_AsGeojson(ST_AsText(OLD.geom)) geom into rec; perform pg_notify('gps',row_to_json(rec)::text); --SELECT gid,ST_AsGeojson(ST_AsText(s.geom)) as epg,OLD.id,ST_AsGeojson(ST_AsText(NEW.geom)) as gps FROM eploygon s where st_within( OLD.geom , s.geom) into ewith; --raise notice 'data:%',row_to_json(ewith)::text; --perform pg_notify('gpswith',row_to_json(ewith)::text); RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN SELECT gid,ST_AsGeojson(ST_AsText(s.geom)) as epg,NEW.id,ST_AsGeojson(ST_AsText(NEW.geom)) as gps FROM eploygon s where st_within( NEW.geom , s.geom) into ewith; --raise notice 'data:%',row_to_json(ewith)::text; --perform pg_notify('gpswith',row_to_json(ewith)::text); select TG_OP TG_OP,NEW.id,ST_AsGeojson(ST_AsText(NEW.geom)) geom,null as gid into rec; rec.gid = ewith.gid; raise notice 'data:%',row_to_json(rec)::text; perform pg_notify('gps',row_to_json(rec)::text); RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN SELECT gid,ST_AsGeojson(ST_AsText(s.geom)) as epg,NEW.id,ST_AsGeojson(ST_AsText(NEW.geom)) as gps FROM eploygon s where st_within( NEW.geom , s.geom) into ewith; --raise notice 'data:%',row_to_json(ewith)::text; --raise notice 'data:%',ewith.gid; --perform pg_notify('gpswith',row_to_json(ewith)::text); select TG_OP TG_OP,NEW.id,ST_AsGeojson(ST_AsText(NEW.geom)) geom ,null as gid into rec; rec.gid = ewith.gid; raise notice 'data:%',row_to_json(rec)::text; perform pg_notify('gps',row_to_json(rec)::text); RETURN NEW; END IF; RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100
参考来源:https://www.jianshu.com/p/4c075a69ca13?mType=Group
备注:pg触发的事件名称需要小写,否则不会发送,如:perform pg_notify('gps',row_to_json(rec)::text);
相关文章
- nodejs express template (模版)的使用 (ejs + express)
- koa 基础(二十一)nodejs 操作mongodb数据库 --- 查询数据
- CSDN学霸课表——学习NodeJS第一天
- NodeJs - 100
- nodejs原码
- NodeJs
- WebSocket 学习--用nodejs搭建服务器
- NodeJs中process.cwd()与__dirname的区别
- nodejs基础 用http模块 搭建一个简单的web服务器 响应JSON、html
- NodeJS环境配置NPM&CNPM&YARN①
- nodejs 模板引擎ejs的使用
- CommonJS、AMD、CMD、NodeJs、RequireJS到底有什么联系?
- nodejs mysql 创建连接池
- nodejs express的基本用法
- nodejs进阶:密码加盐:随机盐值