Intelligent Routing Implementation Guide

This guide covers the complete implementation of VanRoute's intelligent routing system, from database setup to mobile app integration.

Quick Start

Prerequisites

• Supabase project with PostGIS enabled
• Node.js 18+ and TypeScript
• GDAL library (for elevation processing)
• ~20GB disk space for SRTM tiles

Installation

cd packages/database

# Install dependencies
npm install

# Run database migrations
npm run db:migrate 0015  # Routing tables
npm run db:migrate 0016  # Routing functions

# Build routing graph (2-4 hours)
NODE_OPTIONS="--max-old-space-size=8192" npx tsx scripts/build-routing-graph.ts

# (Optional) Install pgRouting
# Run in Supabase SQL Editor:
CREATE EXTENSION IF NOT EXISTS pgrouting;

Architecture Overview

Database Layer

Tables:

• route_graph_nodes - Junction points and endpoints (~8-10M rows)
• route_graph_edges - Road connections with costs (~4.3M rows)
• calculated_routes - Cached routes (1 hour TTL)
• route_waypoints - Fuel/rest stops along routes

Functions:

• find_nearest_node() - Locate graph node near coordinate
• get_vehicle_accessible_edges() - Filter edges by vehicle constraints
• calculate_route_stats() - Aggregate route metrics
• find_fuel_stations_near_route() - Locate fuel stops
• find_rest_areas_near_route() - Locate rest areas
• get_restrictions_on_segments() - Check route hazards
• get_elevation_for_segments() - Retrieve elevation data

Service Layer

Core Service:

// packages/database/api/routing-service.ts
export class RoutingService {
  async calculateRoute(request: RouteRequest): Promise<Route>
  private async getVehicle(vehicleId: string): Promise<Vehicle>
  private async findCachedRoute(...): Promise<Route | null>
  private async findNearestNode(coord: Coordinate): Promise<number>
  private async findPath(...): Promise<PathResult>
  private async analyzeRouteHazards(...): Promise<Warning[]>
  private async suggestFuelStops(...): Promise<FuelStop[]>
  private async suggestRestStops(...): Promise<RestStop[]>
  private async getElevationProfile(...): Promise<ElevationPoint[]>
}

Step-by-Step Implementation

Step 1: Database Setup

Create the routing tables by running migration 0015:

-- packages/database/supabase/migrations/0015_create_routing_tables.sql

-- Graph nodes (intersections, endpoints)
CREATE TABLE route_graph_nodes (
  id BIGSERIAL PRIMARY KEY,
  geom GEOMETRY(POINT, 4326) NOT NULL,
  road_name TEXT,
  is_junction BOOLEAN DEFAULT false,
  connected_segments_count INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_route_graph_nodes_geom
  ON route_graph_nodes USING GIST(geom);

-- Graph edges (road connections)
CREATE TABLE route_graph_edges (
  id BIGSERIAL PRIMARY KEY,
  source_node_id BIGINT REFERENCES route_graph_nodes(id),
  target_node_id BIGINT REFERENCES route_graph_nodes(id),
  segment_id UUID REFERENCES route_segments(id),

  distance_meters DOUBLE PRECISION NOT NULL,
  time_seconds INTEGER NOT NULL,

  -- Restrictions
  max_height_meters DOUBLE PRECISION,
  max_weight_kg INTEGER,
  max_grade_percent DOUBLE PRECISION,

  -- Road characteristics
  highway_type TEXT,
  surface_type TEXT,

  -- Flags
  has_height_restriction BOOLEAN DEFAULT false,
  has_weight_restriction BOOLEAN DEFAULT false,
  is_unsealed BOOLEAN DEFAULT false,

  geom GEOMETRY(LINESTRING, 4326) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_route_graph_edges_source
  ON route_graph_edges(source_node_id);
CREATE INDEX idx_route_graph_edges_target
  ON route_graph_edges(target_node_id);

Step 2: Helper Functions

Install routing helper functions from migration 0016:

-- packages/database/supabase/migrations/0016_create_routing_functions.sql

-- Find nearest node to coordinates
CREATE OR REPLACE FUNCTION find_nearest_node(
  target_lat DOUBLE PRECISION,
  target_lon DOUBLE PRECISION,
  max_distance_meters DOUBLE PRECISION DEFAULT 1000
) RETURNS BIGINT AS $$
  SELECT id
  FROM route_graph_nodes
  WHERE ST_DWithin(
    geom::geography,
    ST_SetSRID(ST_MakePoint(target_lon, target_lat), 4326)::geography,
    max_distance_meters
  )
  ORDER BY geom <-> ST_SetSRID(ST_MakePoint(target_lon, target_lat), 4326)
  LIMIT 1;
$$ LANGUAGE SQL STABLE;

-- Get accessible edges for vehicle
CREATE OR REPLACE FUNCTION get_vehicle_accessible_edges(
  vehicle_height DOUBLE PRECISION,
  vehicle_weight INTEGER,
  vehicle_length DOUBLE PRECISION,
  avoid_unsealed BOOLEAN DEFAULT true
) RETURNS TABLE(
  edge_id BIGINT,
  source_id BIGINT,
  target_id BIGINT,
  distance DOUBLE PRECISION,
  cost DOUBLE PRECISION
) AS $$
  SELECT
    e.id AS edge_id,
    e.source_node_id AS source_id,
    e.target_node_id AS target_id,
    e.distance_meters AS distance,
    -- Calculate cost with penalties
    e.distance_meters * CASE
      WHEN e.max_height_meters IS NOT NULL
        AND vehicle_height > e.max_height_meters THEN 100000.0
      WHEN e.max_weight_kg IS NOT NULL
        AND vehicle_weight > e.max_weight_kg THEN 100000.0
      WHEN e.max_grade_percent > 12 THEN 3.0
      WHEN e.max_grade_percent > 8 THEN 2.0
      WHEN avoid_unsealed AND e.is_unsealed THEN 1.8
      WHEN e.highway_type = 'motorway' THEN 0.9
      ELSE 1.0
    END AS cost
  FROM route_graph_edges e;
$$ LANGUAGE SQL STABLE;

Step 3: Build Routing Graph

Convert route_segments into a routable graph:

# Run the graph builder script
NODE_OPTIONS="--max-old-space-size=8192" npx tsx scripts/build-routing-graph.ts

What it does:

// Simplified logic from scripts/build-routing-graph.ts

async function buildRoutingGraph() {
  const nodeMap = new Map<string, number>(); // "lat,lon" -> node_id
  const nodes: Node[] = [];
  const edges: Edge[] = [];

  // Process all route segments
  for (const segment of allSegments) {
    const coords = segment.geometry_json.coordinates;

    // Create nodes at start and end
    const startNode = getOrCreateNode(coords[0]);
    const endNode = getOrCreateNode(coords[coords.length - 1]);

    // Create edge with enriched data
    edges.push({
      sourceNodeId: startNode,
      targetNodeId: endNode,
      segmentId: segment.id,
      distanceMeters: calculateLength(coords),
      maxHeightMeters: getRestriction(segment.id)?.height,
      maxGradePercent: getElevation(segment.id)?.grade,
      surfaceType: segment.surface,
    });
  }

  // Insert into database
  await insertNodes(nodes);
  await insertEdges(edges);
}

Progress tracking: The script saves progress to .graph-build-progress.json and is resumable if interrupted.

Step 4: Implement Routing Service

Use the TypeScript routing service:

// Import the service
import { RoutingService } from './api/routing-service';

// Initialize
const router = new RoutingService(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_SERVICE_KEY
);

// Calculate a route
const route = await router.calculateRoute({
  origin: { lat: -33.8688, lon: 151.2093 },
  destination: { lat: -37.8136, lon: 144.9631 },
  vehicleId: 'user-vehicle-uuid',
  preferences: {
    avoidUnsealedRoads: true,
    maxGradePercent: 10,
  },
});

console.log(`Route: ${route.distance_meters}m`);
console.log(`Warnings: ${route.warnings.length}`);
console.log(`Fuel stops: ${route.fuelStops.length}`);

Step 5: Integrate with Mobile App

Add routing to React Native app:

// Mobile app: services/routing.ts
import { supabase } from './supabase';

export async function calculateRoute(
  origin: Coordinate,
  destination: Coordinate,
  vehicleId: string
) {
  // Option A: Via Supabase Edge Function (recommended)
  const { data, error } = await supabase.functions.invoke('calculate-route', {
    body: { origin, destination, vehicleId },
  });

  if (error) throw error;
  return data;
}

// React Native component
function RouteScreen() {
  const [route, setRoute] = useState<Route | null>(null);

  async function planRoute() {
    const result = await calculateRoute(
      origin,
      destination,
      userVehicleId
    );
    setRoute(result);
  }

  return (
    <View>
      <MapView>
        {route && (
          <>
            <Polyline
              coordinates={route.geometry.coordinates}
              strokeColor="#007AFF"
              strokeWidth={3}
            />

            {route.warnings.map(w => (
              <Marker
                key={w.id}
                coordinate={w.location}
                pinColor="red"
              />
            ))}

            {route.fuelStops.map(f => (
              <Marker
                key={f.id}
                coordinate={f.location}
                pinColor="blue"
              />
            ))}
          </>
        )}
      </MapView>

      <RouteInfo route={route} />
      <WarningsList warnings={route?.warnings} />
    </View>
  );
}

Advanced Features

Alternative Routes

Generate multiple route options:

async function calculateAlternativeRoutes(
  request: RouteRequest
): Promise<Route[]> {
  const routes: Route[] = [];

  // Primary route (fastest)
  routes.push(await calculateRoute(request));

  // Scenic route (prefer coastal/mountain roads)
  routes.push(await calculateRoute({
    ...request,
    preferences: {
      ...request.preferences,
      preferScenic: true,
    },
  }));

  // Economy route (minimize distance)
  routes.push(await calculateRoute({
    ...request,
    preferences: {
      ...request.preferences,
      minimizeDistance: true,
    },
  }));

  return routes;
}

Real-Time Traffic Integration

Monitor and adjust routes based on live conditions:

class LiveRouteMonitor {
  async monitorRoute(route: Route): Promise<void> {
    setInterval(async () => {
      // Check for traffic incidents
      const incidents = await supabase
        .from('live_traffic_incidents')
        .select('*')
        .eq('is_active', true)
        .overlaps('affected_segments', route.segments);

      if (incidents.data?.length > 0) {
        // Calculate alternate route
        const alternate = await recalculateRoute(route);

        // Notify user
        await notifyUser({
          type: 'traffic_incident',
          message: `Traffic incident ahead. Alternate route available (+${formatTime(alternate.estimated_time_seconds - route.estimated_time_seconds)})`,
          alternate,
        });
      }
    }, 60000); // Check every minute
  }
}

Route Caching Strategy

Optimize performance with intelligent caching:

// Automatic caching in routing service
async calculateRoute(request: RouteRequest): Promise<Route> {
  // Check cache first
  const cached = await this.findCachedRoute(request);
  if (cached && cached.expires_at > Date.now()) {
    return cached;
  }

  // Calculate new route
  const route = await this.performRouteCalculation(request);

  // Cache result
  await this.cacheRoute(route, {
    expiresIn: 3600, // 1 hour
    tags: ['user:' + request.userId, 'vehicle:' + request.vehicleId],
  });

  return route;
}

// Invalidate cache on data changes
async onRoadRestrictionUpdate(restriction: RoadRestriction) {
  // Invalidate all routes using affected segments
  await this.invalidateCacheBySegments([restriction.road_segment_id]);
}

Performance Optimization

Database Indexes

Ensure optimal query performance:

-- Spatial indexes (already in migrations)
CREATE INDEX idx_route_graph_nodes_geom
  ON route_graph_nodes USING GIST(geom);

CREATE INDEX idx_route_graph_edges_geom
  ON route_graph_edges USING GIST(geom);

-- Lookup indexes
CREATE INDEX idx_route_graph_edges_source
  ON route_graph_edges(source_node_id);

CREATE INDEX idx_route_graph_edges_target
  ON route_graph_edges(target_node_id);

-- Filtering indexes
CREATE INDEX idx_route_graph_edges_height
  ON route_graph_edges(max_height_meters)
  WHERE max_height_meters IS NOT NULL;

CREATE INDEX idx_route_graph_edges_grade
  ON route_graph_edges(max_grade_percent)
  WHERE max_grade_percent > 8;

Query Optimization

Use prepared statements and connection pooling:

// Connection pool configuration
const supabase = createClient(url, key, {
  db: {
    schema: 'public',
  },
  auth: {
    autoRefreshToken: false,
    persistSession: false,
  },
  global: {
    headers: {
      'x-connection-pool-size': '20',
    },
  },
});

Memory Management

Handle large result sets efficiently:

async function processLargeGraph() {
  const BATCH_SIZE = 1000;
  let offset = 0;

  while (true) {
    const { data: edges } = await supabase
      .from('route_graph_edges')
      .select('*')
      .range(offset, offset + BATCH_SIZE - 1);

    if (!edges || edges.length === 0) break;

    await processEdgeBatch(edges);

    offset += BATCH_SIZE;

    // Free memory
    if (global.gc) global.gc();
  }
}

Testing

Unit Tests

describe('RoutingService', () => {
  it('should calculate basic route', async () => {
    const route = await router.calculateRoute({
      origin: { lat: -33.8688, lon: 151.2093 },
      destination: { lat: -33.7688, lon: 151.3093 },
      vehicleId: testVehicleId,
    });

    expect(route.segments.length).toBeGreaterThan(0);
    expect(route.distance_meters).toBeGreaterThan(0);
  });

  it('should avoid low bridges', async () => {
    const route = await router.calculateRoute({
      origin: pointA,
      destination: pointB,
      vehicleId: tallVehicleId, // 4.5m height
    });

    const restrictions = await getRestrictionsOnRoute(route);
    const tooLow = restrictions.filter(r => r.height < 4.6);

    expect(tooLow.length).toBe(0);
  });

  it('should suggest fuel stops', async () => {
    const route = await router.calculateRoute({
      origin: sydney,
      destination: brisbane, // ~900km
      vehicleId: smallTankVehicleId, // 400km range
    });

    expect(route.fuelStops.length).toBeGreaterThanOrEqual(2);
  });
});

Integration Tests

describe('End-to-end routing', () => {
  it('should handle Sydney to Melbourne route', async () => {
    const route = await router.calculateRoute({
      origin: SYDNEY_COORDS,
      destination: MELBOURNE_COORDS,
      vehicleId: standardCaravanId,
    });

    expect(route.distance_meters).toBeCloseTo(878000, -3); // ~878km
    expect(route.warnings.length).toBeGreaterThan(0);
    expect(route.fuelStops.length).toBeGreaterThan(0);
  });
});

Troubleshooting

Issue: "No route found"

Possible causes:

• Vehicle constraints too restrictive
• Origin/destination too far from graph nodes
• Graph not fully built

Solutions:

// Check if nodes exist near coordinates
const originNode = await findNearestNode(origin.lat, origin.lon, 10000); // 10km radius
if (!originNode) {
  console.error('No graph node near origin');
}

// Relax vehicle constraints
const route = await calculateRoute({
  ...request,
  preferences: {
    maxGradePercent: 15, // More lenient
    avoidUnsealedRoads: false,
  },
});

Issue: Route calculation is slow

Possible causes:

• Missing database indexes
• Large number of accessible edges
• Inefficient pathfinding algorithm

Solutions:

-- Verify indexes exist
SELECT * FROM pg_indexes
WHERE tablename IN ('route_graph_nodes', 'route_graph_edges');

-- Analyze query performance
EXPLAIN ANALYZE
SELECT * FROM get_vehicle_accessible_edges(4.2, 3500, 8.5, true);

Issue: Unexpected detours

Possible causes:

• Incorrect edge costs
• Missing road connections
• Elevation data gaps

Solutions:

// Inspect route edges
const edges = await supabase
  .from('route_graph_edges')
  .select('*')
  .in('segment_id', route.segments);

console.log('Edge costs:', edges.map(e => ({
  segment: e.segment_id,
  distance: e.distance_meters,
  grade: e.max_grade_percent,
})));

Next Steps

1. Complete graph building if not done
2. Test with real vehicle data and routes
3. Monitor performance metrics
4. Integrate with mobile app UI
5. Add alternative routes for user choice
6. Implement AI layer for natural language

Resources

• Overview - System architecture and features
• AI Agent Integration - Future AI capabilities
• Database Overview - Data sources and structure
• Code:
  • packages/database/api/routing-service.ts - Main service
  • packages/database/scripts/build-routing-graph.ts - Graph builder
  • packages/database/supabase/migrations/0015_*.sql - Routing schema
  • packages/database/supabase/migrations/0016_*.sql - Routing functions

---

Last Updated: October 2025 Status: Implementation guide complete