Geospatial Patterns - PostGIS for laneweaverTMS

When to Use This Skill

Use when:

• Creating geofence boundaries around facilities

• Calculating distances between points (truck to facility, origin to destination)

• Detecting geofence entry/exit events for tracking

• Building ETA calculations or routing features

• Querying fleet positions and historical tracks

• Implementing spatial indexes for location queries

• Integrating with mapping frontends (Mapbox, Leaflet)

PostGIS Fundamentals

Geography vs Geometry Types

Use geography for real-world distance calculations:

Type Use Case Distance Unit Earth Curvature

geography

GPS coordinates, long distances Meters Accounts for curvature

geometry

Local/planar operations, contains checks Projection units Ignores curvature

-- Geography: accurate distances in meters for GPS data SELECT ST_Distance( ST_MakePoint(-87.6298, 41.8781)::geography, -- Chicago ST_MakePoint(-122.4194, 37.7749)::geography -- San Francisco ) / 1609.34 AS distance_miles; -- Returns: ~1856 miles (accurate)

-- Geometry: faster but less accurate for large distances SELECT ST_Distance( ST_SetSRID(ST_MakePoint(-87.6298, 41.8781), 4326), ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326) ) AS distance_degrees; -- Returns: degrees (must convert, less accurate for long distances)

SRID 4326 (WGS84)

All GPS coordinates use SRID 4326 (World Geodetic System 1984):

-- Creating a point from GPS coordinates ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)

-- Creating a geography point (preferred for distance calculations) ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography

-- Example: Create point for a facility location ST_SetSRID(ST_MakePoint(-87.6298, 41.8781), 4326)::geography

Important: PostGIS uses (longitude, latitude) order, NOT (latitude, longitude).

Geofence Table Design

Table Structure

-- Migration: Create geofences table

CREATE TABLE public.geofences ( -- Primary key id UUID DEFAULT gen_random_uuid() NOT NULL,

-- Identification
name TEXT NOT NULL,
description TEXT,

-- Relationship to facility (optional - standalone geofences allowed)
facility_id UUID REFERENCES facilities(id) ON DELETE SET NULL,

-- Spatial boundary - geography type for accurate distance calculations
boundary geography(Polygon, 4326) NOT NULL,

-- For circular geofences, store radius for reference
radius_miles NUMERIC(10,2),

-- Geofence type for business logic
geofence_type TEXT NOT NULL DEFAULT 'facility',

-- Active flag for enabling/disabling
is_active BOOLEAN NOT NULL DEFAULT true,

-- Standard audit columns
created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
created_by INT4,
updated_by INT4,
deleted_at TIMESTAMPTZ,
deleted_by INT4,

CONSTRAINT geofences_pkey PRIMARY KEY (id),
CONSTRAINT chk_geofences_type CHECK (
    geofence_type IN ('facility', 'city', 'region', 'custom')
)

);

COMMENT ON TABLE public.geofences IS 'Geographic boundaries for tracking events (arrival, departure, dwell time)';

COMMENT ON COLUMN public.geofences.boundary IS 'Polygon boundary in WGS84 (SRID 4326). Use geography type for accurate distance calculations';

COMMENT ON COLUMN public.geofences.radius_miles IS 'For circular geofences, the original radius used to generate the boundary polygon';

Creating Circular Geofences

-- Create a circular geofence around a facility INSERT INTO geofences (name, facility_id, boundary, radius_miles, geofence_type) SELECT f.name || ' Geofence', f.id, -- ST_Buffer creates a circle; convert miles to meters (1 mile = 1609.34 meters) ST_Buffer( ST_SetSRID(ST_MakePoint(f.longitude, f.latitude), 4326)::geography, 0.5 * 1609.34 -- 0.5 mile radius in meters ), 0.5, 'facility' FROM facilities f WHERE f.id = $1;

Creating Polygon Geofences

-- Create a custom polygon geofence (e.g., terminal yard) INSERT INTO geofences (name, boundary, geofence_type) VALUES ( 'Terminal Yard A', ST_GeomFromText( 'POLYGON((-87.630 41.878, -87.628 41.878, -87.628 41.876, -87.630 41.876, -87.630 41.878))', 4326 )::geography, 'custom' );

Spatial Indexes

Critical: Index All Spatial Columns

-- Migration: Add spatial indexes to geofences table

-- GIST index on geofence boundaries (required for spatial queries) CREATE INDEX idx_geofences_boundary ON public.geofences USING GIST(boundary);

-- Standard indexes CREATE INDEX idx_geofences_facility_id ON public.geofences(facility_id) WHERE facility_id IS NOT NULL;

CREATE INDEX idx_geofences_deleted_at ON public.geofences(deleted_at) WHERE deleted_at IS NULL;

CREATE INDEX idx_geofences_is_active ON public.geofences(is_active) WHERE is_active = true;

Index for load_cognition Location Queries

-- Create index on load_cognition for location-based queries -- Note: Creates expression index since location is stored as lat/lon columns

CREATE INDEX idx_load_cognition_location ON public.load_cognition USING GIST( ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography );

-- Alternative: Partial index for valid coordinates only CREATE INDEX idx_load_cognition_location_valid ON public.load_cognition USING GIST( ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography ) WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND latitude BETWEEN -90 AND 90 AND longitude BETWEEN -180 AND 180;

Index for facilities Table

-- Create spatial index on facilities for proximity queries CREATE INDEX idx_facilities_location ON public.facilities USING GIST( ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography ) WHERE latitude IS NOT NULL AND longitude IS NOT NULL;

Common Spatial Queries

ST_Distance: Calculate Distance Between Points

-- Distance from truck to destination facility (in miles) SELECT lc.load_id, ST_Distance( ST_SetSRID(ST_MakePoint(lc.longitude, lc.latitude), 4326)::geography, ST_SetSRID(ST_MakePoint(f.longitude, f.latitude), 4326)::geography ) / 1609.34 AS distance_miles FROM load_cognition lc JOIN loads l ON lc.load_id = l.id JOIN stops s ON l.id = s.load_id AND s.stop_type = 'destination' JOIN facilities f ON s.facility_id = f.id WHERE lc.load_id = $1 ORDER BY lc.cognition_time DESC LIMIT 1;

ST_DWithin: Find Points Within Radius

-- Find all trucks within 50 miles of a facility SELECT lc.load_id, l.load_number, lc.driver_name, ST_Distance( ST_SetSRID(ST_MakePoint(lc.longitude, lc.latitude), 4326)::geography, ST_SetSRID(ST_MakePoint(f.longitude, f.latitude), 4326)::geography ) / 1609.34 AS distance_miles FROM load_cognition lc JOIN loads l ON lc.load_id = l.id CROSS JOIN ( SELECT longitude, latitude FROM facilities WHERE id = $1 ) f WHERE ST_DWithin( ST_SetSRID(ST_MakePoint(lc.longitude, lc.latitude), 4326)::geography, ST_SetSRID(ST_MakePoint(f.longitude, f.latitude), 4326)::geography, 50 * 1609.34 -- 50 miles in meters ) AND lc.cognition_time > now() - INTERVAL '1 hour' ORDER BY distance_miles;

ST_Contains: Check if Point is Inside Polygon

-- Check if truck is inside any active geofence SELECT g.id, g.name, g.facility_id FROM geofences g WHERE ST_Contains( g.boundary::geometry, ST_SetSRID(ST_MakePoint($longitude, $latitude), 4326) ) AND g.is_active = true AND g.deleted_at IS NULL;

ST_Buffer: Create Radius Around Point

-- Create a 10-mile buffer zone around current truck position SELECT ST_Buffer( ST_SetSRID(ST_MakePoint($longitude, $latitude), 4326)::geography, 10 * 1609.34 -- 10 miles in meters ) AS buffer_zone;

-- Find facilities within buffer SELECT f.id, f.name, f.city, f.state FROM facilities f WHERE ST_DWithin( ST_SetSRID(ST_MakePoint(f.longitude, f.latitude), 4326)::geography, ST_SetSRID(ST_MakePoint($longitude, $latitude), 4326)::geography, 10 * 1609.34 ) AND f.deleted_at IS NULL;

Geofence Event Detection

Check Geofence Entry

-- Function to check if a position is inside any geofence CREATE OR REPLACE FUNCTION public.check_geofence_entry( p_longitude NUMERIC, p_latitude NUMERIC ) RETURNS TABLE ( geofence_id UUID, geofence_name TEXT, facility_id UUID, geofence_type TEXT ) LANGUAGE plpgsql SECURITY INVOKER SET search_path = 'public' AS $$ BEGIN RETURN QUERY SELECT g.id, g.name, g.facility_id, g.geofence_type FROM geofences g WHERE ST_Contains( g.boundary::geometry, ST_SetSRID(ST_MakePoint(p_longitude, p_latitude), 4326) ) AND g.is_active = true AND g.deleted_at IS NULL; END; $$;

COMMENT ON FUNCTION public.check_geofence_entry(NUMERIC, NUMERIC) IS 'Returns all active geofences containing the given GPS coordinates';

Geofence Event Table

-- Table to track geofence entry/exit events CREATE TABLE public.geofence_events ( id UUID DEFAULT gen_random_uuid() NOT NULL,

load_id UUID NOT NULL REFERENCES loads(id) ON DELETE CASCADE,
geofence_id UUID NOT NULL REFERENCES geofences(id) ON DELETE CASCADE,

event_type TEXT NOT NULL,  -- 'entry', 'exit', 'dwell'
event_time TIMESTAMPTZ NOT NULL DEFAULT now(),

-- Position at time of event
latitude NUMERIC(10,7),
longitude NUMERIC(11,7),

-- Dwell time tracking (for exit events)
entry_time TIMESTAMPTZ,
dwell_minutes INTEGER,

-- Standard audit columns
created_at TIMESTAMPTZ DEFAULT now() NOT NULL,

CONSTRAINT geofence_events_pkey PRIMARY KEY (id),
CONSTRAINT chk_geofence_events_type CHECK (
    event_type IN ('entry', 'exit', 'dwell')
)

);

CREATE INDEX idx_geofence_events_load_id ON geofence_events(load_id); CREATE INDEX idx_geofence_events_geofence_id ON geofence_events(geofence_id); CREATE INDEX idx_geofence_events_event_time ON geofence_events(event_time);

Detect Entry/Exit Pattern

-- Function to process location update and detect geofence events CREATE OR REPLACE FUNCTION public.process_location_geofence( p_load_id UUID, p_longitude NUMERIC, p_latitude NUMERIC, p_timestamp TIMESTAMPTZ DEFAULT now() ) RETURNS SETOF geofence_events LANGUAGE plpgsql SECURITY INVOKER SET search_path = 'public' AS $$ DECLARE v_current_geofences UUID[]; v_previous_geofences UUID[]; v_entered_geofence UUID; v_exited_geofence UUID; v_entry_time TIMESTAMPTZ; v_event geofence_events; BEGIN -- Get current geofences containing this position SELECT ARRAY_AGG(g.id) INTO v_current_geofences FROM geofences g WHERE ST_Contains( g.boundary::geometry, ST_SetSRID(ST_MakePoint(p_longitude, p_latitude), 4326) ) AND g.is_active = true AND g.deleted_at IS NULL;

-- Get previously active geofences for this load
SELECT ARRAY_AGG(DISTINCT ge.geofence_id) INTO v_previous_geofences
FROM geofence_events ge
WHERE ge.load_id = p_load_id
  AND ge.event_type = 'entry'
  AND NOT EXISTS (
      SELECT 1 FROM geofence_events ge2
      WHERE ge2.load_id = p_load_id
        AND ge2.geofence_id = ge.geofence_id
        AND ge2.event_type = 'exit'
        AND ge2.event_time > ge.event_time
  );

-- Handle NULL arrays
v_current_geofences := COALESCE(v_current_geofences, ARRAY[]::UUID[]);
v_previous_geofences := COALESCE(v_previous_geofences, ARRAY[]::UUID[]);

-- Detect entries (in current but not in previous)
FOR v_entered_geofence IN
    SELECT UNNEST(v_current_geofences)
    EXCEPT
    SELECT UNNEST(v_previous_geofences)
LOOP
    INSERT INTO geofence_events (load_id, geofence_id, event_type, event_time, latitude, longitude)
    VALUES (p_load_id, v_entered_geofence, 'entry', p_timestamp, p_latitude, p_longitude)
    RETURNING * INTO v_event;
    RETURN NEXT v_event;
END LOOP;

-- Detect exits (in previous but not in current)
FOR v_exited_geofence IN
    SELECT UNNEST(v_previous_geofences)
    EXCEPT
    SELECT UNNEST(v_current_geofences)
LOOP
    -- Get entry time for dwell calculation
    SELECT ge.event_time INTO v_entry_time
    FROM geofence_events ge
    WHERE ge.load_id = p_load_id
      AND ge.geofence_id = v_exited_geofence
      AND ge.event_type = 'entry'
    ORDER BY ge.event_time DESC
    LIMIT 1;

    INSERT INTO geofence_events (
        load_id, geofence_id, event_type, event_time,
        latitude, longitude, entry_time, dwell_minutes
    )
    VALUES (
        p_load_id, v_exited_geofence, 'exit', p_timestamp,
        p_latitude, p_longitude, v_entry_time,
        EXTRACT(EPOCH FROM (p_timestamp - v_entry_time)) / 60
    )
    RETURNING * INTO v_event;
    RETURN NEXT v_event;
END LOOP;

RETURN;

END; $$;

COMMENT ON FUNCTION public.process_location_geofence(UUID, NUMERIC, NUMERIC, TIMESTAMPTZ) IS 'Processes location update and generates geofence entry/exit events';

ETA Calculations

Basic Distance-Based ETA

-- Calculate simple ETA based on distance and average speed CREATE OR REPLACE FUNCTION public.calculate_eta( p_current_lon NUMERIC, p_current_lat NUMERIC, p_dest_lon NUMERIC, p_dest_lat NUMERIC, p_avg_speed_mph NUMERIC DEFAULT 50 ) RETURNS TIMESTAMPTZ LANGUAGE plpgsql SECURITY INVOKER SET search_path = 'public' AS $$ DECLARE v_distance_miles NUMERIC; v_hours NUMERIC; BEGIN -- Calculate distance in miles v_distance_miles := ST_Distance( ST_SetSRID(ST_MakePoint(p_current_lon, p_current_lat), 4326)::geography, ST_SetSRID(ST_MakePoint(p_dest_lon, p_dest_lat), 4326)::geography ) / 1609.34;

-- Calculate travel time
v_hours := v_distance_miles / p_avg_speed_mph;

RETURN now() + (v_hours || ' hours')::INTERVAL;

END; $$;

COMMENT ON FUNCTION public.calculate_eta(NUMERIC, NUMERIC, NUMERIC, NUMERIC, NUMERIC) IS 'Calculates ETA based on straight-line distance and average speed. For accurate routing, integrate with external routing API';

ETA for Load with Stops

-- Calculate ETA to next stop for a load SELECT l.load_number, s.stop_sequence, f.name AS facility_name, ST_Distance( ST_SetSRID(ST_MakePoint(lc.longitude, lc.latitude), 4326)::geography, ST_SetSRID(ST_MakePoint(f.longitude, f.latitude), 4326)::geography ) / 1609.34 AS distance_miles, now() + ( (ST_Distance( ST_SetSRID(ST_MakePoint(lc.longitude, lc.latitude), 4326)::geography, ST_SetSRID(ST_MakePoint(f.longitude, f.latitude), 4326)::geography ) / 1609.34) / 50 -- Assume 50 mph average || ' hours' )::INTERVAL AS estimated_arrival FROM loads l JOIN stops s ON l.id = s.load_id JOIN facilities f ON s.facility_id = f.id JOIN LATERAL ( SELECT longitude, latitude FROM load_cognition WHERE load_id = l.id ORDER BY cognition_time DESC LIMIT 1 ) lc ON true WHERE l.id = $1 AND s.actual_arrival IS NULL -- Not yet arrived ORDER BY s.stop_sequence LIMIT 1;

PGRouting Integration Points

For accurate routing with road networks:

-- PGRouting requires a road network table -- This is typically populated from OpenStreetMap data

-- Example: Calculate route distance using Dijkstra algorithm -- Note: Requires pgr_createTopology and road network data

-- SELECT -- sum(cost) AS total_distance, -- ST_Union(geom) AS route_geometry -- FROM pgr_dijkstra( -- 'SELECT id, source, target, cost FROM roads', -- $start_node, $end_node, -- directed := false -- ) AS route -- JOIN roads ON route.edge = roads.id;

-- For production ETA calculations, consider: -- 1. External routing APIs (OSRM, Google Directions, Here) -- 2. Pre-calculated route distances in lanes table -- 3. Mileage tables from PC*MILER or similar

Real-Time Tracking Patterns

load_cognition Table Structure

The load_cognition table stores GPS tracking data:

-- Key columns in load_cognition -- id UUID -- load_id UUID (FK to loads) -- driver_name TEXT -- latitude NUMERIC(10,7) -- longitude NUMERIC(11,7) -- cognition_time TIMESTAMPTZ -- speed_mph NUMERIC -- heading NUMERIC -- source TEXT (e.g., 'eld', 'mobile', 'manual')

Query Current Fleet Positions

-- Get current position of all active loads SELECT DISTINCT ON (l.id) l.id AS load_id, l.load_number, l.load_status, lc.driver_name, lc.latitude, lc.longitude, lc.speed_mph, lc.cognition_time, -- Calculate time since last update EXTRACT(EPOCH FROM (now() - lc.cognition_time)) / 60 AS minutes_since_update FROM loads l JOIN load_cognition lc ON l.id = lc.load_id WHERE l.load_status IN ('dispatched', 'at_origin', 'in_transit', 'at_destination') AND l.deleted_at IS NULL AND lc.latitude IS NOT NULL AND lc.longitude IS NOT NULL ORDER BY l.id, lc.cognition_time DESC;

Historical Track Query

-- Get tracking history for a load (for route visualization) SELECT lc.latitude, lc.longitude, lc.cognition_time, lc.speed_mph, lc.heading FROM load_cognition lc WHERE lc.load_id = $1 AND lc.latitude IS NOT NULL AND lc.longitude IS NOT NULL ORDER BY lc.cognition_time ASC;

Track as GeoJSON LineString

-- Get tracking history as GeoJSON for map display SELECT json_build_object( 'type', 'Feature', 'properties', json_build_object( 'load_id', $1::text, 'point_count', count(*) ), 'geometry', json_build_object( 'type', 'LineString', 'coordinates', json_agg( json_build_array(longitude, latitude) ORDER BY cognition_time ) ) ) AS geojson FROM load_cognition WHERE load_id = $1 AND latitude IS NOT NULL AND longitude IS NOT NULL;

Frontend Mapping Integration

GeoJSON Output for Mapbox/Leaflet

-- Facilities as GeoJSON FeatureCollection SELECT json_build_object( 'type', 'FeatureCollection', 'features', json_agg( json_build_object( 'type', 'Feature', 'id', f.id, 'properties', json_build_object( 'name', f.name, 'city', f.city, 'state', f.state, 'facility_type', f.facility_type ), 'geometry', json_build_object( 'type', 'Point', 'coordinates', json_build_array(f.longitude, f.latitude) ) ) ) ) AS geojson FROM facilities f WHERE f.latitude IS NOT NULL AND f.longitude IS NOT NULL AND f.deleted_at IS NULL;

Geofence Boundaries as GeoJSON

-- Export geofences as GeoJSON for map overlay SELECT json_build_object( 'type', 'FeatureCollection', 'features', json_agg( json_build_object( 'type', 'Feature', 'id', g.id, 'properties', json_build_object( 'name', g.name, 'geofence_type', g.geofence_type, 'facility_id', g.facility_id ), 'geometry', ST_AsGeoJSON(g.boundary::geometry)::json ) ) ) AS geojson FROM geofences g WHERE g.is_active = true AND g.deleted_at IS NULL;

Fleet Positions as GeoJSON

-- Current fleet positions for real-time map SELECT json_build_object( 'type', 'FeatureCollection', 'features', ( SELECT json_agg( json_build_object( 'type', 'Feature', 'id', t.load_id, 'properties', json_build_object( 'load_number', t.load_number, 'load_status', t.load_status, 'driver_name', t.driver_name, 'speed_mph', t.speed_mph, 'last_update', t.cognition_time ), 'geometry', json_build_object( 'type', 'Point', 'coordinates', json_build_array(t.longitude, t.latitude) ) ) ) FROM ( SELECT DISTINCT ON (l.id) l.id AS load_id, l.load_number, l.load_status, lc.driver_name, lc.latitude, lc.longitude, lc.speed_mph, lc.cognition_time FROM loads l JOIN load_cognition lc ON l.id = lc.load_id WHERE l.load_status IN ('dispatched', 'at_origin', 'in_transit', 'at_destination') AND l.deleted_at IS NULL AND lc.latitude IS NOT NULL ORDER BY l.id, lc.cognition_time DESC ) t ) ) AS geojson;

LayerChart Integration

For geographic visualizations in the frontend, use the layerchart plugin:

• Choropleth: State/region heat maps for load volume or revenue

• Bubble Map: Facility markers sized by load count

• GeoPath: Route visualization with tracking data

• GeoTile: Background map tiles for context

Reference the layerchart skill for component patterns and data transformation utilities.

PostGIS Functions Quick Reference

Function Purpose Example

ST_MakePoint(lon, lat)

Create point from coordinates ST_MakePoint(-87.6298, 41.8781)

ST_SetSRID(geom, srid)

Assign coordinate system ST_SetSRID(point, 4326)

ST_Distance(a, b)

Distance between geometries (meters for geography) ST_Distance(a::geography, b::geography)

ST_DWithin(a, b, dist)

True if within distance ST_DWithin(a, b, 80467) (50 miles)

ST_Contains(poly, point)

True if polygon contains point ST_Contains(geofence, truck_pos)

ST_Buffer(geom, dist)

Create buffer zone ST_Buffer(point::geography, 1609.34) (1 mile)

ST_AsGeoJSON(geom)

Export as GeoJSON ST_AsGeoJSON(boundary)

ST_GeomFromGeoJSON(json)

Import from GeoJSON ST_GeomFromGeoJSON($1)

ST_Union(geom)

Merge geometries ST_Union(route_segments)

ST_Centroid(geom)

Center point of geometry ST_Centroid(state_boundary)

Unit Conversion Reference

From To Multiply By

Miles Meters 1609.34

Meters Miles 0.000621371

Kilometers Miles 0.621371

Miles Kilometers 1.60934

Implementation Checklist

PostGIS Setup: [ ] PostGIS extension enabled (CREATE EXTENSION postgis) [ ] PGRouting extension enabled if routing needed (CREATE EXTENSION pgrouting) [ ] SRID 4326 used for all GPS coordinate data [ ] Geography type used for distance calculations

Geofences: [ ] Geofences table with geography(Polygon, 4326) boundary column [ ] GIST index on boundary column [ ] Functions for geofence entry/exit detection [ ] Event table for tracking geofence transitions

Spatial Indexes: [ ] GIST index on all geography/geometry columns [ ] Expression index on load_cognition for location queries [ ] Partial indexes for valid coordinate rows only

Tracking: [ ] load_cognition captures lat/lon with each update [ ] Efficient queries for current fleet position [ ] Historical track queries return ordered points [ ] GeoJSON output for frontend consumption

Frontend Integration: [ ] GeoJSON endpoints for facilities, geofences, fleet positions [ ] LayerChart components for geographic visualizations [ ] Real-time position updates via Supabase realtime subscriptions

Related Skills

• supabase:laneweaver-database-design : Table conventions, audit columns, migrations

• layerchart : Geographic visualization components for frontend

• load-lifecycle-patterns : Load tracking status transitions

Remember: Use geography type for real-world distance calculations with GPS data. Always use SRID 4326 for GPS coordinates. Index all spatial columns with GIST indexes for query performance.