Building a Geofencing Alert System with Streaming SQL

A geofencing alert system triggers notifications the moment a vehicle enters or exits a defined geographic boundary — not five minutes later when a batch job runs. Building one on RisingWave means expressing boundary checks as SQL geometry predicates in a materialized view, with Kafka delivering alerts sub-second after each GPS event.

Why Real-Time Geofencing Alerts Matter

Geofencing has a wide range of operational applications in trucking and fleet management: confirming that a vehicle arrived at a customer delivery site, detecting unauthorized use outside business hours, triggering automated check-in messages at distribution centers, enforcing restricted zone compliance, and flagging vehicles that deviate from approved corridors.

The common thread is that these alerts are only useful when they are fast. A delivery confirmation that arrives two minutes after the truck pulled into the dock is fine. But an alert that a truck is entering a restricted port zone needs to arrive before the truck clears the security checkpoint — which may be less than a minute after the GPS ping that triggered it.

Traditional approaches poll a PostGIS database on a schedule — every 30 or 60 seconds — and compute containment checks. This introduces latency equal to the polling interval plus query execution time. It also means that a vehicle that enters and exits a geofence within one polling interval is never detected.

RisingWave solves this by computing the containment check as a continuous materialized view predicate. Each GPS event triggers an incremental evaluation. If the vehicle is inside the geofence, the view includes the row. If the vehicle exits, the row is retracted. Kafka sinks detect the state change and emit an alert.

The Streaming SQL Solution

GPS telemetry arrives in Kafka. Geofence definitions are loaded from a reference stream. RisingWave maintains a vehicles_in_geofences materialized view by continuously evaluating each GPS position against all active geofences. Entry and exit events are detected by comparing current state with previous state and emitting a Kafka message on change.

Tutorial: Building It Step by Step

Step 1: Set Up the Data Source

-- Vehicle GPS telemetry stream
CREATE SOURCE vehicle_gps (
    vin             VARCHAR,
    driver_id       VARCHAR,
    latitude        DOUBLE PRECISION,
    longitude       DOUBLE PRECISION,
    speed_mph       DOUBLE PRECISION,
    heading_deg     INT,
    odometer_km     DOUBLE PRECISION,
    ignition_on     BOOLEAN,
    event_ts        TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'fleet.gps',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

-- Geofence definitions: polygons or circle approximations
-- Stored as bounding box for efficient pre-filter + exact check
CREATE SOURCE geofence_definitions (
    geofence_id         VARCHAR,
    geofence_name       VARCHAR,
    geofence_type       VARCHAR,   -- CUSTOMER_SITE | DEPOT | RESTRICTED | CORRIDOR
    center_lat          DOUBLE PRECISION,
    center_lon          DOUBLE PRECISION,
    radius_km           DOUBLE PRECISION,   -- for circular geofences
    min_lat             DOUBLE PRECISION,   -- bounding box pre-filter
    max_lat             DOUBLE PRECISION,
    min_lon             DOUBLE PRECISION,
    max_lon             DOUBLE PRECISION,
    alert_on_enter      BOOLEAN,
    alert_on_exit       BOOLEAN,
    active              BOOLEAN,
    created_ts          TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'fleet.geofences',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

Step 2: Build Real-Time Aggregations

-- Current vehicle positions (latest per VIN)
CREATE MATERIALIZED VIEW vehicle_current_position AS
SELECT DISTINCT ON (vin)
    vin,
    driver_id,
    latitude,
    longitude,
    speed_mph,
    heading_deg,
    ignition_on,
    event_ts AS position_ts
FROM vehicle_gps
ORDER BY vin, event_ts DESC;

-- Geofence containment check: which vehicles are inside which geofences right now
-- Uses great-circle distance approximation for circular geofences
CREATE MATERIALIZED VIEW vehicles_in_geofences AS
SELECT
    v.vin,
    v.driver_id,
    v.latitude,
    v.longitude,
    v.speed_mph,
    v.position_ts,
    g.geofence_id,
    g.geofence_name,
    g.geofence_type,
    g.alert_on_enter,
    g.alert_on_exit,
    -- Approximate distance in km using equirectangular projection
    ROUND(
        SQRT(
            POWER((v.latitude  - g.center_lat) * 111.32, 2) +
            POWER((v.longitude - g.center_lon) * 111.32 * COS(RADIANS(g.center_lat)), 2)
        ),
        4
    ) AS distance_from_center_km
FROM vehicle_current_position v
CROSS JOIN geofence_definitions g
WHERE g.active = true
  -- Bounding box pre-filter (fast check before distance calculation)
  AND v.latitude  BETWEEN g.min_lat AND g.max_lat
  AND v.longitude BETWEEN g.min_lon AND g.max_lon
  -- Exact circular containment check
  AND SQRT(
      POWER((v.latitude  - g.center_lat) * 111.32, 2) +
      POWER((v.longitude - g.center_lon) * 111.32 * COS(RADIANS(g.center_lat)), 2)
  ) <= g.radius_km;

-- Dwell time: how long each vehicle has been inside a geofence
CREATE MATERIALIZED VIEW geofence_dwell_time AS
SELECT
    g.vin,
    g.driver_id,
    g.geofence_id,
    g.geofence_name,
    g.geofence_type,
    window_start,
    window_end,
    COUNT(*)                    AS position_samples,
    -- Estimated dwell time: sample count * approximate GPS interval (30s)
    COUNT(*) * 30 / 60.0        AS dwell_minutes_est,
    AVG(g.distance_from_center_km) AS avg_distance_from_center_km,
    MIN(g.distance_from_center_km) AS min_distance_from_center_km
FROM TUMBLE(
    (
        SELECT v.vin, v.driver_id, v.latitude, v.longitude, v.position_ts,
               g2.geofence_id, g2.geofence_name, g2.geofence_type,
               SQRT(
                   POWER((v.latitude  - g2.center_lat) * 111.32, 2) +
                   POWER((v.longitude - g2.center_lon) * 111.32 * COS(RADIANS(g2.center_lat)), 2)
               ) AS distance_from_center_km
        FROM vehicle_gps v
        CROSS JOIN geofence_definitions g2
        WHERE g2.active = true
          AND v.latitude  BETWEEN g2.min_lat AND g2.max_lat
          AND v.longitude BETWEEN g2.min_lon AND g2.max_lon
          AND SQRT(
              POWER((v.latitude  - g2.center_lat) * 111.32, 2) +
              POWER((v.longitude - g2.center_lon) * 111.32 * COS(RADIANS(g2.center_lat)), 2)
          ) <= g2.radius_km
    ) gps_in_fence,
    position_ts,
    INTERVAL '1 HOUR'
)
GROUP BY vin, driver_id, geofence_id, geofence_name, geofence_type, window_start, window_end;

Step 3: Detect Anomalies or Generate Alerts

-- Alert: vehicles inside RESTRICTED geofences
CREATE MATERIALIZED VIEW restricted_zone_alerts AS
SELECT
    vin,
    driver_id,
    latitude,
    longitude,
    geofence_id,
    geofence_name,
    distance_from_center_km,
    position_ts,
    'RESTRICTED_ZONE_ENTRY' AS alert_type
FROM vehicles_in_geofences
WHERE geofence_type = 'RESTRICTED'
  AND alert_on_enter = true;

-- Alert: vehicle inside CUSTOMER_SITE for longer than expected (2 hours)
CREATE MATERIALIZED VIEW long_dwell_alerts AS
SELECT
    vin,
    driver_id,
    geofence_id,
    geofence_name,
    dwell_minutes_est,
    window_end,
    'EXCESSIVE_DWELL' AS alert_type
FROM geofence_dwell_time
WHERE geofence_type = 'CUSTOMER_SITE'
  AND dwell_minutes_est > 120
  AND window_end = (
      SELECT MAX(window_end) FROM geofence_dwell_time dt2
      WHERE dt2.vin = geofence_dwell_time.vin
        AND dt2.geofence_id = geofence_dwell_time.geofence_id
  );

-- Sink: push geofence alerts to fleet operations
CREATE SINK geofence_alert_sink
FROM (
    SELECT vin, driver_id, geofence_id, geofence_name, position_ts AS alert_ts, alert_type
    FROM restricted_zone_alerts
    UNION ALL
    SELECT vin, driver_id, geofence_id, geofence_name, window_end AS alert_ts, alert_type
    FROM long_dwell_alerts
)
WITH (
    connector = 'kafka',
    topic = 'alerts.fleet.geofencing',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

Comparison Table

FAQ

How do I handle polygon-shaped geofences, not just circles? For polygons, store the boundary as a WKT (Well-Known Text) string in the geofence_definitions source. RisingWave supports PostGIS-compatible ST_Contains and ST_GeomFromText functions. Replace the distance formula in the view with ST_Contains(ST_GeomFromText(g.polygon_wkt), ST_Point(v.longitude, v.latitude)).

What is the maximum number of active geofences the system can handle? The CROSS JOIN between all GPS positions and all geofence definitions scales as O(vehicles × geofences) per event. For large fleets with many geofences, partition the geofence_definitions source by geographic region and filter the join with a coarser bounding box pre-filter. In practice, fleets with thousands of vehicles and hundreds of geofences have deployed this pattern successfully.

Can I trigger webhooks from geofence alerts instead of Kafka? RisingWave's native sinks include Kafka, PostgreSQL, and several others. For webhook delivery, consume the Kafka topic with a lightweight consumer (Kafka Streams, a Python consumer, or Kafka Connect HTTP Sink) that translates messages to HTTP POST requests.

How do I avoid duplicate alerts when a vehicle oscillates near a geofence boundary? Add a hysteresis buffer: define two radius values — entry_radius_km and exit_radius_km — where exit_radius_km > entry_radius_km. The vehicle must cross the outer radius to trigger exit and the inner radius to trigger entry, preventing oscillation-induced duplicates.

Key Takeaways

• RisingWave's CROSS JOIN between a GPS position stream and a geofence definition stream enables real-time containment evaluation without any application code.
• A bounding box pre-filter reduces the cost of the containment check before the more expensive great-circle distance formula is evaluated.
• The vehicles_in_geofences view is always current, reflecting the latest GPS position for each vehicle, enabling sub-second alert delivery.
• Kafka sinks route restricted-zone and dwell-time alerts to fleet operations tooling in real time, replacing polling-based geofence systems.

• RisingWave documentation: Geospatial functions
• RisingWave documentation: CROSS JOIN in streaming
• PostGIS ST_Contains documentation