---
title: "Geofencing & Driver Behavior Analytics: Database Guide"
description: "Geofence events and driver-behavior events are both time-series data. See the schema and PostGIS queries for storing both in one database."
section: "Postgres for IoT"
published: 2026-09-29T16:27:23.969Z
updated: 2026-09-29T00:00:00.000Z
---

*Updated at Sep 29, 2026*

> **TimescaleDB is now Tiger Data.**

## Introduction

A fleet platform needs to answer two different questions about the same vehicle: where is it relative to a boundary, and how is it being driven. The first question is geofencing: did the vehicle enter or exit a defined zone. The second is driver behavior analytics: did it brake hard, accelerate sharply, speed, or sit idle too long. Most stacks handle these as two unrelated problems: a spatial layer bolted onto a time-series layer, or a fleet-telematics platform's built-in scoring that never exposes the data underneath it. Both questions draw on the same telematics data a connected vehicle already produces.

The core argument, for fleet geofencing and driver behavior alike, is that both are time-series data. A geofence event is spatial time-series data: a vehicle's position crossing a boundary. A driver-behavior event is temporal time-series data: a threshold crossing derived from continuous telemetry signals. PostGIS, hypertables, and continuous aggregates handle both in one PostgreSQL instance, so there's no second database for the spatial half of the workload.

Tiger Data builds [<u>Tiger Cloud</u>](https://www.tigerdata.com/cloud), a database compared in this piece, so read the technical claims here with that in mind. The goal is an honest answer, including where a fleet-telematics platform or a different architecture fits better than a database you manage yourself.

This page covers what geofence and driver-behavior events look like as raw data, why most stacks split them into two systems, the schema for both, the PostGIS queries that detect geofence entry and exit, a worked continuous-aggregate driver-scorecard example, compression and retention for the history that piles up, and a decision framework for when this architecture fits and when it doesn't.

One scope note: this page covers the data-architecture layer underneath geofencing and driver-behavior analytics, not fleet-management software, compliance reporting, or driver-coaching workflows. Those are application-layer concerns built on top of the data described here.

## What Geofence and Driver-Behavior Events Actually Are, as Data

A geofence event is precise and mechanical: a vehicle's GPS position crosses the boundary of a defined polygon or radius, generating a discrete entry or exit event with a timestamp, a vehicle ID, and a geofence ID. Nothing about it requires interpretation. The position either falls inside the boundary or it doesn't.

A driver-behavior event is a threshold-crossing flag, not a raw reading. Harsh braking detection, rapid-acceleration flags, speeding, and excessive idling are all derived from continuous telemetry signals, accelerometer data, OBD-II speed and RPM, or GPS-derived speed, compared against a defined threshold.

Despite looking like different problems, one spatial, one behavioral, both are append-only, timestamped event streams keyed on vehicle ID. That's the same structural shape as the GPS and OBD-II data covered in Tiger Data's [<u>fleet telemetry database guide</u>](https://www.tigerdata.com/learn/fleet-telemetry-database): a high-cardinality stream of readings that accumulates continuously and gets queried almost exclusively by time range and vehicle.

If you landed here searching "geofencing data" broadly: that term is dominated by adtech and location-analytics use cases, retail foot-traffic measurement, ad targeting, audience segmentation. This page is specifically about the fleet and vehicle use case, geofencing as part of a connected-vehicle telemetry pipeline, not a marketing-attribution tool.

### Why Fleet-Telematics Platforms Treat These as Separate Problems

Fleet-telematics platforms like Samsara, Geotab, Motive, and Azuga handle geofencing and driver-behavior scoring at the application layer, and they do it well. But their public content and documentation tend to stop at "what is a geofence" or "how driver scoring works." None of them document or expose the storage and schema layer underneath their features.

That's not a guess. [<u>Azuga's geofencing guide</u>](https://azuga.com/blog/geofencing-fleet-tracking-guide) runs to roughly 3,800 words across more than a dozen sections, covering setup, use cases, and business benefits, and never once addresses storage, scale, or schema. [<u>FatigueScience's driver-behavior-analytics guide</u>](https://fatiguescience.com/blog/driver-behavior-analytics-fleet) explains how event history rolls up into a weighted coaching score, but it stops at that analytics layer. There's no data-architecture content: no mention of storage, schema, or the database underneath the score.

That gap exists because fleet-telematics vendors sell the application layer, the dashboards, the alerts, the scoring UI, not the database underneath it. It's not a gap because the storage layer doesn't matter. It's a gap because it's not what those vendors are selling. This page fills the space they leave open.

## The Two-Workload Argument: One Database, Not Two

This is the spine of the piece, so it gets stated plainly rather than as a passing claim. Geofence events are spatial time-series data: PostGIS `GEOGRAPHY` types running on a hypertable. Driver-behavior events are temporal time-series data: hypertables plus continuous aggregates. Most stacks solve these as two separate problems, a spatial database bolted to a time-series database, or a telematics platform's opaque scoring engine, because nobody's made the case that they're the same underlying workload. They are.

Three architectural options sit side by side here, named plainly. Purpose-built fleet-telematics stacks handle geofencing and driver scoring at the application layer, but they don't expose a storage layer at all, so if you need to query the raw event stream yourself, there's nothing to query against. Generic cloud data warehouses handle scale well, but they typically lack native geospatial types and the sub-second query performance a live fleet dashboard needs, which usually means bolting on a separate spatial database anyway (see the [<u>broader IoT database comparison</u>](https://www.tigerdata.com/learn/how-to-choose-an-iot-database) for how that tradeoff plays out across other options). Tiger Data's case is PostGIS plus hypertables plus continuous aggregates in one Postgres instance, so there's no second database for the spatial half of the workload and no ETL between systems.

That comparison deserves softer framing than a head-to-head. Tiger Data isn't a fleet-telematics platform, and framing it as a replacement for Samsara, Geotab, Motive, or Azuga would overstate the case. It's the storage layer that tends to sit underneath one of those platforms, or underneath a team's own fleet application, when raw event access matters.

And this argument has a real edge where it stops applying: if a team is fully committed to a fleet-telematics platform's built-in scoring and geofencing, and never needs to query the raw event stream directly, there's no reason to stand up a separate database at all. The two-workload argument only matters once you need access to the data underneath the platform's UI.

## Schema Design for Geofence and Driver-Behavior Events

Three tables cover this workload: a geofence table for stored boundaries, a vehicle-location table for raw position pings, and a driver-behavior event table for threshold-crossing flags. The two get conflated in casual usage, so disambiguate up front: a **geofence** is a stored spatial boundary, defined once and rarely updated. A **geofence event** is a generated time-series row, produced when a vehicle's position crosses that boundary. The schema below reflects that split.

Geofence boundaries change rarely, so a standard relational table fits:

`-- Geofence boundaries (standard relational table, defined once)
CREATE TABLE geofences (
    geofence_id    TEXT PRIMARY KEY,
    name           TEXT NOT NULL,
    boundary       GEOGRAPHY(POLYGON, 4326),   -- for polygon-shaped zones
    center_point   GEOGRAPHY(POINT, 4326),     -- for radius-based zones
    radius_meters  DOUBLE PRECISION,
    fleet_group    TEXT
);`

Vehicle position pings are the opposite: high-volume, append-only, and a natural hypertable. One type decision matters here. Tiger Data's own PostGIS documentation models GPS coordinates as `GEOGRAPHY(POINT, 4326)`, and that's the type used below, because `GEOGRAPHY` calculates distance and containment on a spheroid, which is the physically correct model for real-world latitude and longitude. `GEOMETRY` assumes a flat plane and only gives accurate results with a correctly chosen projected SRID for the specific region you're covering. For a fleet that might operate anywhere, `GEOGRAPHY` is the safer default:

`-- Vehicle position pings (time-series, spatial)
CREATE TABLE vehicle_location (
    time         TIMESTAMPTZ NOT NULL,
    vehicle_id   TEXT NOT NULL,
    position     GEOGRAPHY(POINT, 4326)   -- WGS84 lat/lng
) WITH (
    tsdb.hypertable,
    tsdb.segmentby = 'vehicle_id',
    tsdb.orderby   = 'time DESC'
);

CREATE INDEX ON vehicle_location USING GIST (position);`

Driver-behavior events use the narrow-row pattern: one row per `(time, vehicle_id, event_type, value)`, rather than a fixed column for every possible event type. Adding a new event type later, say a new harsh-cornering detector, means inserting a new value, not running a schema migration:

`-- Driver-behavior events (time-series, narrow-row)
CREATE TABLE driver_behavior_events (
    time         TIMESTAMPTZ NOT NULL,
    vehicle_id   TEXT NOT NULL,
    event_type   TEXT NOT NULL,          -- 'harsh_brake', 'rapid_accel', 'speeding', 'idle'
    value        DOUBLE PRECISION        -- g-force for harsh_brake/rapid_accel; duration in minutes for speeding/idle 
) WITH (
    tsdb.hypertable,
    tsdb.segmentby = 'vehicle_id, event_type',
    tsdb.orderby   = 'time DESC'
);`

Both tables use the current `CREATE TABLE ... WITH (tsdb.hypertable)` syntax rather than a separate `create_hypertable()` call. Note for teams comparing this to [<u>Tiger Data's fleet telemetry database guide</u>](https://www.tigerdata.com/learn/fleet-telemetry-database): that page's existing GPS example uses `GEOMETRY(Point, 4326)` rather than `GEOGRAPHY`. Both types work, but `GEOGRAPHY` is the more broadly correct default and the one Tiger Data's own PostGIS documentation demonstrates, which is why it's used here.

## Geospatial Queries: Detecting Geofence Entry and Exit with PostGIS

With the schema in place, geofence detection is a spatial containment check. ST_Within tests whether a vehicle's current position falls inside a geofence's boundary. Because ST_Within operates on `geometry`, the `geography` columns get cast for the comparison:

`-- Vehicles inside a specific geofence polygon in the last 5 minutes 
SELECT DISTINCT vl.vehicle_id
FROM vehicle_location vl
JOIN geofences gf ON gf.geofence_id = 'chicago_depot'
WHERE vl.time > now() - INTERVAL '5 minutes'
  AND ST_Within(vl.position::geometry, gf.boundary::geometry);`

Radius-based proximity alerting, "notify me when a vehicle is within 500 meters of a depot," uses ST_DWithin instead. This matters for more than convenience: `ST_DWithin` is index-aware, so it uses the spatial index to check only vehicles near the point, where a raw distance comparison would compute the distance for every row in the table:

`-- Alert when a vehicle is within 500 meters of a depot
SELECT vehicle_id
FROM vehicle_location
WHERE time > now() - INTERVAL '5 minutes'
  AND ST_DWithin(
        position,
        ST_SetSRID(ST_MakePoint(-87.6298, 41.8781), 4326)::geography,
        500
      );`

Both queries run against the same PostgreSQL instance as the driver-behavior hypertable. A single query can filter by time range and spatial boundary at once, joining geofence results to driver-behavior events or vehicle metadata with no ETL between a spatial database and a time-series database.

One edge case to flag: a vehicle that pings near a geofence's edge without truly entering it can produce a false positive from a single point-in-polygon check. `ST_MakeLine`, applied to a vehicle's ordered position pings, reconstructs its trajectory between readings, so you can test whether the path actually crosses the boundary rather than trusting one ambiguous point.

## Code Walkthrough: Building a Driver Scorecard with Continuous Aggregates

A fleet manager's daily driver scorecard needs harsh-brake counts, speeding minutes, and idle time per driver per day. Recomputing that from raw event rows on every dashboard load works fine for a handful of vehicles and falls apart well before a fleet reaches meaningful scale, since every concurrent dashboard viewer triggers its own full scan of the raw event table.

A continuous aggregate solves this by rolling up` driver_behavior_events` into daily per-vehicle metrics:

`CREATE MATERIALIZED VIEW driver_scorecard_daily
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 day', time)                             AS day,
    vehicle_id,
    COUNT(*) FILTER (WHERE event_type = 'harsh_brake')      AS harsh_brake_count,
    COUNT(*) FILTER (WHERE event_type = 'rapid_accel')      AS rapid_accel_count,
    SUM(value) FILTER (WHERE event_type = 'speeding')       AS speeding_minutes,
    SUM(value) FILTER (WHERE event_type = 'idle')           AS idle_minutes
FROM driver_behavior_events
GROUP BY day, vehicle_id
WITH NO DATA;

SELECT add_continuous_aggregate_policy('driver_scorecard_daily',
    start_offset      => INTERVAL '3 days',
    end_offset         => INTERVAL '1 hour',
    schedule_interval  => INTERVAL '1 hour'
);`

A continuous aggregate isn't a scheduled batch job that re-scans everything on a timer. It's a materialized hypertable that updates incrementally, reprocessing only the chunks that changed since the last refresh. For a fleet dashboard queried by dozens of concurrent users, that's the difference between every page load rescanning months of raw events and every page load reading a handful of pre-computed rows.

The same mechanism extends to geofence events. A continuous aggregate can roll up zone-dwell-time or visit-count per geofence per day from the `vehicle_location` table using a similar pattern to `driver_scorecard_daily: time_bucket` and `FILTER` against a different source table. 

That's the concrete proof behind the two-workload argument. Geofence events and driver-behavior events aren't just conceptually similar, spatial versus temporal. They're operationally handled by the same database primitive: one continuous aggregate mechanism, applied to two event streams.

## Compression and Retention for Geofence and Driver-Behavior History

Geofence and driver-behavior event history is append-only and rarely updated, exactly the profile Hypercore, Tiger Data's columnstore engine, is built to compress. [<u>Compression ratios of 90 to 98%</u>](https://www.tigerdata.com/docs/build/how-to/basic-compression) are typical on this kind of data, consistent with what Tiger Data sees across its other fleet-telemetry content.

A fleet logging geofence and driver-behavior events at scale accumulates history fast, and the practical question is how much of it needs to stay query-fast versus get archived or dropped. 

Because both tables were created with tsdb.segmentby and tsdb.orderby, columnstore compression is already enabled: TimescaleDB automatically adds a columnstore policy that converts each chunk to the columnstore after one chunk interval, so older history compresses on its own with no separate policy call. 

`add_retention_policy()` handles the second part, dropping entire chunks once they age past a defined window, which is close to instantaneous since it drops whole chunks rather than deleting rows one at a time:

`SELECT add_retention_policy('vehicle_location', drop_after => INTERVAL '18 months');
SELECT add_retention_policy('driver_behavior_events', drop_after => INTERVAL '18 months');`

If a continuous aggregate depends on either table, keep the retention window longer than the aggregate's refresh window. Otherwise the refresh runs against data that's already been dropped and removes the aggregate along with it.

## Decision Framework: Which Approach Fits Your Fleet

None of this is a universal answer. Compare it against [<u>the broader IoT database comparison</u>](https://www.tigerdata.com/learn/how-to-choose-an-iot-database) if geofencing and driver behavior are only part of a larger evaluation. What follows is a genuine attempt to cover where each approach fits, including where Tiger Data isn't the right call.

### Choose a database like Tiger Data if:

- You need to query raw geofence and driver-behavior event data directly, for custom dashboards, ad hoc analysis, or building your own scoring logic instead of relying on a vendor's black-box score
- You want geospatial queries (geofencing, proximity, trajectory) and time-series queries (driver-behavior aggregation) running in the same database, without maintaining a separate spatial database alongside a separate time-series database
- You're already running fleet telemetry (GPS, OBD-II, CAN bus) in a time-series database and want geofencing and driver-behavior scoring to live alongside it rather than in a third system

### Choose a fleet-telematics platform's built-in tools if:

- Your team doesn't need to query the raw event stream directly, and a platform like Samsara, Geotab, Motive, or Azuga's built-in geofencing and driver-scoring features already answer the operational questions you have
- You don't have engineering resources to own a database layer, and the platform's dashboards and alerts cover the use case end to end

### Choose a separate spatial database alongside your time-series stack if:

- You have a highly specialized, high-volume GIS workload, large-scale routing or complex multi-layer spatial analysis, that goes well beyond geofencing and needs a dedicated GIS engine's full feature set. PostGIS doesn't cover every possible spatial use case, and this is a fair case where it shouldn't try to. See [<u>the full PostGIS feature set for geospatial time-series queries</u>](https://www.tigerdata.com/learn/postgresql-extensions-postgis) for what PostGIS covers beyond geofencing before assuming you need a second system.

### Related guides

This page stays focused on geofencing and driver-behavior data specifically. For adjacent parts of the fleet stack:

- **Fleet Telemetry Database:** the broader picture of everything a connected fleet produces (GPS, OBD-II, CAN bus, driver behavior, EV battery data) at a conceptual level, rather than this page's focused deep dive. See [<u>Tiger Data's guide to the four core data types a connected fleet produces</u>](https://www.tigerdata.com/learn/fleet-telemetry-database).
- **CAN Bus Data Logger:** use this if the driver-behavior signals you care about (harsh braking, hard acceleration) are derived from [<u>raw CAN or J1939 signals</u>](https://www.tigerdata.com/learn/can-bus-data-logger) rather than GPS or accelerometer data, and you need the signal-decode layer underneath them.
- **Robot Fleet Telemetry:** use this if you're [<u>logging robot or AMR telemetry rather than vehicle geofencing and driver behavior</u>](https://www.tigerdata.com/learn/robot-fleet-telemetry). The schema pattern is similar, but robots typically report local map-frame position instead of GPS, and add mission and task status as a first-class event type.
- **EV Charging Management System:** use this if your fleet is electric and you're also tracking [<u>EV fleet battery and charging data as an adjacent fleet workload</u>](https://www.tigerdata.com/learn/ev-charging-station-data-management), on top of geofencing and driver behavior.

## Migrating an Existing Geofencing or Driver-Behavior Pipeline to Tiger Data

Teams tend to arrive at this architecture from one of three starting points.

**From a fleet-telematics platform's webhook or export feed.** Geofence entry and exit events and driver-behavior flags typically arrive as JSON via a webhook or API export, often after [<u>a Telematics Control Unit buffers them at the edge before transmission</u>](https://www.tigerdata.com/learn/edge-database) rather than streaming every reading in real time. The migration is the same event source, a different destination: parse the payload, or consume it over [<u>MQTT as the transport layer from vehicle to central database</u>](https://www.tigerdata.com/learn/mqtt-to-postgresql) if that's how your pipeline already moves data, and insert into the `vehicle_location` and `driver_behavior_events` tables above instead of, or alongside, the vendor's own storage.

**From a separate spatial database plus a separate time-series database.** Teams running a standalone PostGIS instance or a GIS-only tool next to a separate time-series database can consolidate into one Tiger Data instance, with PostGIS running natively alongside the hypertables and no ETL step connecting the two systems anymore.

**From ad hoc CSV exports.** Geofence boundaries and driver-behavior events exported as flat files bulk-load into the schema above with a standard `COPY` or bulk-insert path. For a large historical backlog, load first and let the columnstore policy compress it on the normal schedule, or convert older chunks to the columnstore manually to reclaim storage sooner.

Ready to put this into practice? Start a [<u>Tiger Cloud free trial</u>](https://www.tigerdata.com/cloud/), and see the [<u>PostGIS documentation</u>](https://www.tigerdata.com/docs/deploy/tiger-cloud/tiger-cloud-aws/tiger-cloud-extensions/postgis) and [<u>continuous aggregates documentation</u>](https://www.tigerdata.com/docs/reference/timescaledb/continuous-aggregates/create_materialized_view) for implementation details.