PolarDB:GanosBase trajectory model: transport vehicle reachability analysis

Step 1: Install extensions

CREATE EXTENSION ganos_spatialref;
CREATE EXTENSION ganos_geometry;
CREATE EXTENSION ganos_trajectory;

Step 2: Create tables and indexes

-- Trajectory point table: stores raw GPS data as it arrives from vehicles
CREATE TABLE bill_point(id integer, longitude double precision, latitude double precision, sample_time timestamp);

-- Trajectory table: stores one trajectory object per transport order
CREATE TABLE bill_traj(id integer UNIQUE, traj trajectory);

-- Spatial index on the trajectory table for fast geofence intersection queries
CREATE INDEX ON bill_traj USING gist(traj);

-- Geofence table: stores the geometry and scheduled activation time for each geofence
CREATE TABLE fence(id integer, fence_time timestamp, area geometry);

CREATE INDEX ON fence USING btree(id);
CREATE INDEX ON fence USING btree(fence_time);

Three tables form the core schema. bill_point receives raw GPS records from vehicles. bill_traj holds one trajectory object per transport order, built incrementally as GPS points arrive. fence stores geofence geometries alongside the scheduled time each geofence should activate.

The GiST index on bill_traj enables spatial indexing for fast geofence intersection queries across large trajectory datasets.

Step 3: Create a trigger to build trajectories from raw points

Rather than building trajectory objects manually, use a trigger on bill_point to automatically construct and extend trajectory objects in bill_traj as GPS data arrives.

CREATE OR REPLACE FUNCTION trajectory_sync_point() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO bill_traj
    SELECT NEW.id,
           ST_MakeTrajectory(array_agg(ROW(NEW.sample_time, NEW.longitude, NEW.latitude)), false, '{}'::cstring[])
  ON CONFLICT(id) DO UPDATE
    SET traj = ST_Append(bill_traj.traj, excluded.traj);
  RETURN NULL;
END;
$$
LANGUAGE plpgsql STRICT PARALLEL SAFE;

CREATE TRIGGER point_trigger AFTER INSERT ON bill_point
    FOR EACH ROW EXECUTE PROCEDURE trajectory_sync_point();

Each row inserted into bill_point triggers this function. On the first insert for a given order ID, the function creates a new trajectory object using ST_MakeTrajectory. For subsequent inserts, it calls ST_Append to extend the existing trajectory — so bill_traj always reflects the vehicle's complete path up to the most recent GPS point.

Insert initial GPS points

-- Three points recorded before the geofence activation time
INSERT INTO bill_point VALUES
  (1, 2,   2, '2000-01-01 00:01:00'),
  (1, 2.1, 2, '2000-01-01 00:02:00'),
  (1, 2.2, 2, '2000-01-01 00:03:00');

After the insert, the trigger fires three times and builds a trajectory for order ID 1. Verify that the trajectory table was populated:

SELECT * FROM bill_traj;

SELECT f.* FROM (SELECT traj FROM bill_traj WHERE id = 1) a,
  ST_AsTable(a.traj) AS f(t timestamp, x double precision, y double precision);

Set up the geofence

-- Geofence: rectangular area around (2.3, 2), activates at 00:05:00
INSERT INTO fence VALUES(1, '2000-01-01 00:05:00', ST_MakeEnvelope(2.29, 1.99, 2.31, 2.01));

ST_MakeEnvelope(2.29, 1.99, 2.31, 2.01) creates a small rectangular area centered on longitude 2.3, latitude 2. The activation time 00:05:00 is the deadline by which the vehicle must pass through.

Run the geofence scan

The business system scans for triggered geofences every minute. A geofence is triggered when all three conditions are met:

• The geofence activation time falls within the current one-minute window

• The vehicle's trajectory has data up to or past the activation time

• The trajectory has not intersected the geofence area

-- Geofence scan query — returns geofence IDs for vehicles that are late
SELECT fence.id
FROM fence
JOIN bill_traj ON
  now() >= fence.fence_time
  AND now() - '60 s'::interval < fence.fence_time  -- only check geofences in the current minute
  AND fence.id = bill_traj.id
  AND ST_EndTime(bill_traj.traj) >= fence.fence_time  -- trajectory data reaches the activation time
  AND NOT ST_2DIntersects(bill_traj.traj, fence.area); -- vehicle has not passed through

At this point, the trajectory ends at longitude 2.2 — outside the geofence — but the activation time (00:05:00) hasn't been reached yet, so the query returns no rows. No geofence is triggered.

Simulate a late vehicle

Add a point at the activation time but still outside the geofence area:

INSERT INTO bill_point VALUES (1, 2.25, 2, '2000-01-01 00:05:00');

Run the scan again at 00:05:00. The vehicle's trajectory now has data at the activation time, and the trajectory has not entered the geofence. The scan returns geofence ID 1, indicating the vehicle is late:

 id
----
  1

Simulate the vehicle passing through

Insert a point inside the geofence:

INSERT INTO bill_point VALUES (1, 2.3, 2, '2000-01-01 00:06:00');

Run the scan again. The trajectory now intersects the geofence area, so ST_2DIntersects returns true and the NOT condition excludes this vehicle. The query returns no rows — all vehicles are on time:

 id
----
(0 rows)

Insert the remaining points to complete the trajectory (the vehicle leaves the geofence area and does not re-trigger):

INSERT INTO bill_point VALUES (1, 2.3, 2.1, '2000-01-01 00:07:00');
INSERT INTO bill_point VALUES (1, 2.2, 1.9, '2000-01-01 00:08:00');

View the final trajectory for order ID 1:

SELECT f.* FROM (SELECT traj FROM bill_traj WHERE id = 1) a,
  ST_AsTable(a.traj) AS f(t timestamp, x double precision, y double precision);

Insert trajectory data directly

For analysis scenarios where you have historical trajectory data, insert it directly into bill_traj without going through the point table:

INSERT INTO bill_traj SELECT
  2,
  ST_makeTrajectory(
    'STPOINT'::leaftype,
    'LINESTRING(0 0,1 1,2 2,2 2,2 2,2 3,3 4,2 4,2 3,2 3,2 3,2 2,2 1,2 0,1 0,0 0,-1 0)'::geometry,
    '{"2000-01-01 00:12:34","2000-01-01 00:23:37","2000-01-01 00:34:41","2000-01-01 00:45:45",
      "2000-01-01 00:56:48","2000-01-01 01:07:52","2000-01-01 01:18:56","2000-01-01 01:30:00",
      "2000-01-01 01:41:03","2000-01-01 01:52:07","2000-01-01 02:03:11","2000-01-01 02:14:14",
      "2000-01-01 02:25:18","2000-01-01 02:36:22","2000-01-01 02:47:25","2000-01-01 02:58:29",
      "2000-01-01 03:09:33"}'::timestamp[],
    NULL
  );

This creates a 17-point trajectory (order ID 2) spanning about three hours, with several segments where the vehicle stays at the same coordinates — the data that ST_StayPoint will identify as rest periods.

Extract stay points

-- Parameters: trajectory, resample interval, distance threshold, time threshold, minimum neighbor count
SELECT ST_StayPoint(traj, '5 minute', 0.1, '15 minute', 3)
FROM bill_traj
WHERE id = 2;

The parameters control how ST_StayPoint clusters nearby positions into rest periods:

The query returns two stay points for this trajectory:

                               st_staypoint
------------------------------------------------------------------------------------------
 (010100000000000000000000400000000000000040,"2000-01-01 00:34:41","2000-01-01 00:56:48")
 (010100000000000000000000400000000000000840,"2000-01-01 01:41:03","2000-01-01 02:03:11")

Each row contains the stay point's location (as WKB geometry), start time, and end time. The first rest period lasted about 22 minutes; the second lasted about 22 minutes as well.

Check if total rest time meets the minimum threshold

-- Get the geographic location of each stay point
SELECT ST_AsText((ST_StayPoint(traj, '5 minute', 0.1, '15 minute', 3)).point)
FROM bill_traj
WHERE id = 2;

-- Check whether total rest time exceeds 30 minutes
-- Returns true if adequate rest was detected, false if driver fatigue is a risk
SELECT SUM(duration) >= '30 minute'::interval
FROM (
  SELECT
    (ST_StayPoint(traj, '5 minute', 0.1, '15 minute', 3)).endt
    - (ST_StayPoint(traj, '5 minute', 0.1, '15 minute', 3)).startt AS duration
  FROM bill_traj
  WHERE id = 2
) staytime;

The aggregation query sums the duration of all detected rest periods and compares the total to the 30-minute threshold. If the result is false, the system can trigger a fatigue alert for that driver.