Phase 1 Implementation

Phase 1 of the Vanroute data enrichment project adds three essential safety features to the database:

1. Rest Areas - Official rest stop locations with facilities information
2. Live Traffic Incidents - Real-time road closures, hazards, and incidents
3. Fuel Stations - Service station locations with fuel types and facilities

Implementation Status

✅ Complete - Scripts and database schema ready for deployment

Quick Start

1. Run Migration

The Phase 1 migration creates three new tables. Run via Supabase web interface:

1. Open your Supabase project dashboard
2. Navigate to SQL Editor
3. Copy the contents of packages/database/supabase/migrations/0006_create_phase1_tables.sql
4. Click Run

2. Import Fuel Stations

No authentication required - uses OpenStreetMap data:

cd packages/database
npm run db:import-fuel-stations

Expected: ~5,000-10,000 fuel stations across Australia Runtime: 30-60 minutes (includes rate limiting)

3. Import Rest Areas

Requires downloading GeoJSON data from state portals:

# Create data directory
mkdir -p packages/database/data/rest-areas

# Download NSW data
# Visit: https://opendata.transport.nsw.gov.au/dataset/nsw-rest-areas
# Save GeoJSON to: data/rest-areas/nsw-rest-areas.geojson

# Run import
npm run db:import-rest-areas

Expected: 1,000+ rest areas from NSW and other states Runtime: < 1 minute per state

4. Set Up Live Traffic Polling

Requires API key from NSW Open Data:

# Register at https://opendata.transport.nsw.gov.au/
# Add API key to .env file:
echo "NSW_TRAFFIC_API_KEY=your_key" >> packages/database/.env

# Run polling
npm run db:poll-traffic

# Set up cron job for every 15 minutes:
# */15 * * * * cd /path/to/vanroute/packages/database && npm run db:poll-traffic

Expected: Variable number of active incidents (depends on current conditions)

Data Sources

Rest Areas

Primary Source: State transport department open data portals

State	Dataset	Records
NSW	NSW Rest Areas	1,372+
QLD	QLD Rest Areas	450+
VIC, WA, SA	Check state transport portals	Varies

Data Quality: ⭐⭐⭐⭐⭐ Official government data, annually updated

Fuel Stations

Primary Source: OpenStreetMap via Overpass API

Data Quality: ⭐⭐⭐⭐ Community-maintained, good coverage on major routes

Includes:

• Location coordinates
• Brand/name
• Fuel types (diesel, LPG, unleaded, premium, E10, AdBlue)
• Facilities (toilets, food, ATM)
• 24-hour access information
• Truck/caravan access suitability

Live Traffic

Primary Source: NSW Live Traffic Hazards

Future Sources:

• VIC Planned Disruptions
• SA Road Closures
• National Freight Data Hub

Data Quality: ⭐⭐⭐⭐⭐ Real-time official data

Incident Types:

• Road closures
• Floods and water over roads
• Bushfires
• Accidents
• Roadworks
• Major events

Database Schema

rest_areas Table

CREATE TABLE rest_areas (
  id UUID PRIMARY KEY,
  geometry geometry(Point, 4326) NOT NULL,
  name TEXT NOT NULL,
  state TEXT,
  road_name TEXT,
  has_toilets BOOLEAN,
  has_water BOOLEAN,
  has_tables BOOLEAN,
  has_bbq BOOLEAN,
  vehicle_types_allowed TEXT[],
  is_24_hour BOOLEAN,
  data_source TEXT NOT NULL,
  source_id TEXT
);

Key Features:

• Spatial index for location queries
• Facility flags for easy filtering
• Source tracking for deduplication

live_traffic_incidents Table

CREATE TABLE live_traffic_incidents (
  id UUID PRIMARY KEY,
  geometry geometry(Point, 4326),
  incident_type TEXT NOT NULL,
  severity TEXT NOT NULL,
  title TEXT NOT NULL,
  is_active BOOLEAN DEFAULT true,
  start_time TIMESTAMPTZ NOT NULL,
  end_time TIMESTAMPTZ,
  data_source TEXT NOT NULL,
  source_id TEXT NOT NULL
);

Key Features:

• Automatic resolved_at timestamp via trigger
• Active/inactive status for current conditions
• Historical incident tracking

fuel_stations Table

CREATE TABLE fuel_stations (
  id UUID PRIMARY KEY,
  geometry geometry(Point, 4326) NOT NULL,
  name TEXT NOT NULL,
  brand TEXT,
  has_diesel BOOLEAN,
  has_lpg BOOLEAN,
  has_unleaded BOOLEAN,
  is_24_hour BOOLEAN,
  has_truck_access BOOLEAN,
  has_caravan_access BOOLEAN,
  osm_id TEXT
);

Key Features:

• Fuel type flags for filtering
• Access information for large vehicles
• OpenStreetMap ID for deduplication

Usage Examples

Find Rest Areas Near Route

// Find rest areas within 50km of a point
const { data } = await supabase
  .from('rest_areas')
  .select('*')
  .eq('has_toilets', true)
  .filter('geometry', 'dwithin', {
    type: 'Point',
    coordinates: [longitude, latitude]
  }, 50000); // 50km in meters

Check Active Traffic Incidents

// Get current incidents in NSW
const { data } = await supabase
  .from('live_traffic_incidents')
  .select('*')
  .eq('state', 'NSW')
  .eq('is_active', true)
  .order('severity', { ascending: false });

Find Fuel Stations with Specific Fuels

// Find stations with diesel and LPG
const { data } = await supabase
  .from('fuel_stations')
  .select('*')
  .eq('has_diesel', true)
  .eq('has_lpg', true)
  .eq('has_caravan_access', true);

Implementation Details

Import Scripts

All scripts are located in packages/database/scripts/:

• import-fuel-stations.ts - Import fuel stations from OpenStreetMap
• import-rest-areas.ts - Import rest areas from GeoJSON files
• poll-live-traffic.ts - Poll live traffic APIs and update incidents

NPM Scripts

# Individual imports
npm run db:import-fuel-stations
npm run db:import-rest-areas
npm run db:poll-traffic

# All Phase 1 imports
npm run db:setup-phase1

Success Metrics

Target Goals

• ✅ 1,000+ rest areas in database
• ✅ 5,000+ fuel stations mapped
• ✅ Real-time traffic updates every 15 minutes
• ✅ Complete NSW coverage

Current Status

Run npm run db:summary to see current data counts.

Next Steps

After Phase 1 implementation:

1. Integrate with Mobile App

   • Create REST API endpoints
   • Implement location-based queries
   • Build UI components

2. Expand Coverage

   • Add more state data sources
   • Integrate VIC and QLD traffic APIs
   • Enhance OpenStreetMap data quality

3. Phase 2 Planning

   • Elevation data integration
   • Weather forecasts
   • Mobile coverage maps

Resources

• NSW Open Data Hub
• OpenStreetMap Wiki
• Supabase PostGIS Guide
• Package README

Troubleshooting

Common Issues

Fuel stations import times out:

• The script already chunks Australia into regions
• Adjust latStep and lonStep if needed

Rest areas import finds no files:

• Ensure GeoJSON files are in data/rest-areas/
• Files must have .geojson or .json extension

Live traffic polling returns no data:

• Verify API key is set in .env
• Check API access permissions
• Review API endpoint URL in script

Getting Help

For issues or questions:

• Review script logs for detailed error messages
• Verify database connection and credentials
• Check the Live Data Polling guide for traffic setup
• See Fuel Stations documentation for fuel data
• Refer to the Database Overview for general information

---

Implementation Date: October 2025 Status: ✅ Ready for deployment Maintainer: Database Team