在 最近的一篇文章中，我们介绍了pg_eventserv和来自数据库操作的实时 Web 通知。
在这篇文章中，我们将深入研究一个实际用例：显示状态、计算事件和跟踪一组移动对象的历史位置。
该演示使用 pg_eventserv进行事件处理， 使用pg_featureserv作为外部 Web API，并使用OpenLayers作为地图 API，来构建一个展示移动对象系统的共同特征的小型示例应用程序。
基本功能
移动对象应用程序可能非常复杂或非常简单，但它们通常包含一些常见的基线功能：
- 对象状态的实时视图。
- 对象进入和离开一组“地理围栏”时的实时通知。
- 查询系统的历史，查看对象的位置，并总结它们的状态（例如，“卡车 513 将 50% 的时间花在院子里”）。
表
该模型具有三个表：
- 存储对象 当前位置的对象。
- 存储地理围栏 的地理围栏。
- objects_history存储所有对象位置的完整集合。
相关 SQL 可查看：此处
架构（简要）
从外部看，该系统具有以下架构：
对对象的更改通过由 pg_featureserv支持的 Web API 进行通信，这些更改会触发一堆触发器，这些触发器会生成 pg_eventserv通过 WebSockets 推送到侦听客户端的事件。
架构（详细）
-
用户界面通过每个对象的箭头按钮生成对象移动。这代替了现实世界中生成时间戳 GPS 轨迹的真实“移动物体”舰队。
-
UI 上的每次移动都会触发对 Web API 的调用，这只是通过 pg_featureserv发布的函数，
object_move(object_id, direction).
postgisftw.object_move（object_id，direction）
CREATE OR REPLACE FUNCTION postgisftw.object_move(
move_id integer, direction text)
RETURNS TABLE(id integer, geog geography)
AS $$
DECLARE
xoff real = 0.0;
yoff real = 0.0;
step real = 2.0;
BEGIN
yoff := CASE
WHEN direction = 'up' THEN 1 * step
WHEN direction = 'down' THEN -1 * step
ELSE 0.0 END;
xoff := CASE
WHEN direction = 'left' THEN -1 * step
WHEN direction = 'right' THEN 1 * step
ELSE 0.0 END;
RETURN QUERY UPDATE moving.objects mo
SET geog = ST_Translate(mo.geog::geometry, xoff, yoff)::geography,
ts = now()
WHERE mo.id = move_id
RETURNING mo.id, mo.geog;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
复制-
该
object_move(object_id, direction)函数只是将“方向”参数转换为运动矢量，以及表格UPDATES的相关行 。objects -
objects对表的更改会objects_geofence()触发触发器，该触发器会计算对象现在所在的栅栏。
objects_geofence()
CREATE FUNCTION objects_geofence() RETURNS trigger AS $$
DECLARE
fences_new integer[];
BEGIN
-- Add the current geofence state to the input
-- tuple every time.
SELECT coalesce(array_agg(id), ARRAY[]::integer[])
INTO fences_new
FROM moving.geofences
WHERE ST_Intersects(geofences.geog, new.geog);
RAISE DEBUG 'fences_new %', fences_new;
-- Ensure geofence state gets saved
NEW.fences := fences_new;
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';
复制- 然后
objects对表的更改触发 触发器，该触发器：objects_update()- 将当前的一组地理围栏与前一组进行比较，从而检测任何进入/离开事件。
- 将对象的新位置添加到
objects_history跟踪表。 - 将新位置和任何地理围栏事件组合到 JSON 对象中，并
NOTIFY使用pg_notify().
objects_update()
CREATE FUNCTION objects_update() RETURNS trigger AS
$$
DECLARE
channel text := 'objects';
fences_old integer[];
fences_entered integer[];
fences_left integer[];
events_json jsonb;
location_json jsonb;
payload_json jsonb;
BEGIN
-- Place a copy of the value into the history table
INSERT INTO moving.objects_history (id, geog, ts, props)
VALUES (NEW.id, NEW.geog, NEW.ts, NEW.props);
-- Clean up any nulls
fences_old := coalesce(OLD.fences, ARRAY[]::integer[]);
RAISE DEBUG 'fences_old %', fences_old;
-- Compare to previous fences state
fences_entered = NEW.fences - fences_old;
fences_left = fences_old - NEW.fences;
RAISE DEBUG 'fences_entered %', fences_entered;
RAISE DEBUG 'fences_left %', fences_left;
-- Form geofence events into JSON for notify payload
WITH r AS (
SELECT 'entered' AS action,
g.id AS geofence_id,
g.label AS geofence_label
FROM moving.geofences g
WHERE g.id = ANY(fences_entered)
UNION
SELECT 'left' AS action,
g.id AS geofence_id,
g.label AS geofence_label
FROM moving.geofences g
WHERE g.id = ANY(fences_left)
)
SELECT json_agg(row_to_json(r))
INTO events_json
FROM r;
-- Form notify payload
SELECT json_build_object(
'type', 'objectchange',
'object_id', NEW.id,
'events', events_json,
'location', json_build_object(
'longitude', ST_X(NEW.geog::geometry),
'latitude', ST_Y(NEW.geog::geometry)),
'ts', NEW.ts,
'color', NEW.color,
'props', NEW.props)
INTO payload_json;
RAISE DEBUG '%', payload_json;
-- Send the payload out on the channel
PERFORM (
SELECT pg_notify(channel, payload_json::text)
);
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';
复制-
pg_eventserv从队列中挑选事件
NOTIFY并通过 WebSocket 将其推送到所有监听客户端。 -
用户界面接收 JSON 有效负载，对其进行解析，并将新位置应用于适当的对象。如果地理围栏上有进入/离开事件，UI 也会相应地更改地理围栏轮廓颜色。
-
旁注，该
geofences表还有一个触发器，layer_change()它捕获插入/更新/删除事件并使用pg_notify(). 这也由 pg_eventserv发布，当 UI 接收到它时，它只是强制重新加载地理围栏数据。
layer_change()
CREATE FUNCTION layer_change() RETURNS trigger AS
$$
DECLARE
layer_change_json json;
channel text := 'objects';
BEGIN
-- Tell the client what layer changed and how
SELECT json_build_object(
'type', 'layerchange',
'layer', TG_TABLE_NAME::text,
'change', TG_OP)
INTO layer_change_json;
RAISE DEBUG 'layer_change %', layer_change_json;
PERFORM (
SELECT pg_notify(channel, layer_change_json::text)
);
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';
复制好的，全部完成。
所有代码和指令都可以在pg_eventserv 的 移动对象示例 中找到。您可以自己尝试。
结论
- 移动对象是“系统状态存储在数据库中”的经典案例。
- PostgreSQL 提供 LISTEN/NOTIFY 系统来更新客户端有关实时更改的信息。
- pg_eventserv 服务允许您将 LISTEN/NOTIFY 事件进一步推送到任何 WebSockets 客户端并生成移动对象映射。
- 因为状态是在数据库中管理的，所以存储系统的历史状态 非常容易。
原文标题：Moving Objects and Geofencing with Postgres & PostGIS
原文作者：Paul Ramsey
原文地址：https://www.crunchydata.com/blog/moving-objects-and-geofencing-with-postgres-postgis
