Phase 3 Implementation Plan

Date: October 14, 2025 Status: Research Complete - Ready for Implementation

Overview

Phase 3 adds critical safety and planning features to the Vanroute database:

Mobile Coverage Data - Carrier coverage maps for communication planning
Bridge Load Limits - Heavy vehicle and caravan restrictions
Weather & Flood Forecasting - Real-time weather data and warnings
Tourist Attractions & POIs - Destinations and points of interest

1. Mobile Coverage Data

Data Source: ACCC Mobile Infrastructure Report

Source URL: https://data.gov.au/data/dataset/accc-mobile-infrastructure-report-data-release

What's Included:

Coverage maps by carrier (Telstra, Optus, Vodafone, TPG)
Network types: 3G, 4G, 5G
Coverage types: Outdoor, Indoor, External Antenna
Data format: KML files
Update frequency: Annual (2024 data available)

Database Table: mobile_coverage (already created in Phase 2)

Implementation Status

✅ Database schema exists (migration 0008) ✅ Import script created: scripts/import-mobile-coverage.ts ⚠️ Requires dependencies:

npm install @tmcw/togeojson @xmldom/xmldom

Quick Start

1. Download Coverage Data:

# Visit data.gov.au and download KML files
# Create a directory for coverage files
mkdir -p ~/Downloads/coverage-data

2. Install Dependencies:

cd packages/database
npm install @tmcw/togeojson @xmldom/xmldom

3. Run Import:

npm run db:import-coverage -- ~/Downloads/coverage-data/

4. Download Links (2024 Data):

Telstra 5G Outdoor: https://data.gov.au/data/dataset/4b472a18-d0fa-409c-994a-ab17162bcb90/resource/c3194d30-ea7a-46fc-bebb-986875f30243
Telstra 4G Outdoor: https://data.gov.au/data/dataset/4b472a18-d0fa-409c-994a-ab17162bcb90/resource/a40e86d0-7cfe-45cf-89ad-6a0d4d140e89
Optus 5G: Look for similar resources on the same dataset page
TPG 4G: Available on the ACCC dataset

Note: KML files are large (hundreds of MB). Download only needed carriers/networks initially.

2. Bridge Load Limits & Restrictions

Data Sources

A. NHVR ArcGIS Services (National)

Service URLs:

Restricted Structures: https://gis.nhvr.gov.au/arcgis/rest/services/PROD/GazettedNetworks/MapServer
HML Networks: https://gis.nhvr.gov.au/arcgis/rest/services/NHVR/HML_Networks/MapServer
Spatial Open Data: https://spatialopendata-nhvr.hub.arcgis.com

Key Layers:

Layer 129: PBS Level 1 GML Restricted Structures
Layer 267: Restricted Structures - Level Crossings
Bridge restrictions and load limits

Data Format: ArcGIS REST API (GeoJSON export available)

Coverage: National network

B. Tasmania Load Limited Bridges

Source URL: https://data.gov.au/data/dataset/https-data-stategrowth-tas-gov-au-ags-rest-services-hvn-loadlimited_roadsbridges-mapserver

Data Format: ArcGIS REST API, WFS Coverage: Tasmania only Fields: Bridge locations, load limits, restriction types

C. State-Specific Sources

Victoria:

VicRoads bridge structures: https://discover.data.vic.gov.au/dataset/bridge-structures
Format: GeoJSON, API
Fields: Bridge condition, type, clearance, dimensions

South Australia:

Bridges and culverts: https://data.sa.gov.au/data/dataset/bridge-or-culvert
Format: GeoJSON
Fields: Bridge details, load ratings

Database Schema (Recommended)

-- New table for bridge restrictions
CREATE TABLE bridge_restrictions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Location
  latitude DOUBLE PRECISION NOT NULL,
  longitude DOUBLE PRECISION NOT NULL,
  geometry geometry(Point, 4326),

  -- Bridge details
  bridge_name TEXT,
  road_name TEXT,
  state TEXT,

  -- Restrictions
  max_weight_tonnes DOUBLE PRECISION,
  max_height_meters DOUBLE PRECISION,
  max_width_meters DOUBLE PRECISION,
  max_length_meters DOUBLE PRECISION,

  -- Vehicle types
  suitable_for_caravans BOOLEAN DEFAULT true,
  suitable_for_heavy_vehicles BOOLEAN DEFAULT true,
  restriction_type TEXT, -- 'load_limit', 'height', 'width', 'combination'

  -- Details
  restriction_reason TEXT,
  alternative_route TEXT,
  warning_signs BOOLEAN,

  -- Metadata
  data_source TEXT NOT NULL,
  source_id TEXT,
  last_updated TIMESTAMPTZ DEFAULT NOW(),

  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_bridge_restrictions_geom ON bridge_restrictions USING GIST (geometry);
CREATE INDEX idx_bridge_restrictions_state ON bridge_restrictions (state);
CREATE INDEX idx_bridge_restrictions_caravan ON bridge_restrictions (suitable_for_caravans);
CREATE INDEX idx_bridge_restrictions_weight ON bridge_restrictions (max_weight_tonnes);

Implementation Status

❌ Not yet implemented 📋 TODO: Create scripts/import-bridge-restrictions.ts

Implementation Steps:

Create migration file for bridge_restrictions table
Create import script to query NHVR ArcGIS REST API
Download state-specific datasets (VIC, SA, TAS)
Implement batch processing with pagination
Add npm script: db:import-bridge-restrictions

Estimated Time: 6-8 hours

3. Weather & Flood Forecasting

Data Source: Bureau of Meteorology (BOM)

Official API: Not publicly available (requires business agreement)

Alternative Sources:

A. BOM FTP Archive (Free)

URL: ftp://ftp.bom.gov.au/anon/gen/ Format: XML, JSON Coverage: National forecasts and warnings Update: Hourly

Data Feeds:

Product catalogue: http://www.bom.gov.au/catalogue/data-feeds.shtml
Forecasts: Town, district, and coastal forecasts
Warnings: Severe weather, flood, fire, marine

B. Weather API Services (Third-party)

Open-Meteo (Free)

URL: https://open-meteo.com/
Format: JSON REST API
Coverage: Global (including Australia)
Features: 7-day forecasts, historical data
License: Free for non-commercial (CC BY 4.0)
Rate limit: Generous for open source projects

ClimaCell/Tomorrow.io

URL: https://www.tomorrow.io/weather-api/
Free tier: 500 calls/day
Features: Real-time weather, forecasts, alerts

Database Tables (Already exist from Phase 2)

✅ weather_forecasts - 7-day forecasts by location ✅ weather_warnings - Active severe weather warnings

Implementation Status

🔄 Framework exists (migration 0008) ❌ Data collection not implemented

Recommended Approach:

Option 1: Open-Meteo API (Recommended)

# Create script: scripts/poll-weather-openmeteo.ts
npm run db:poll-weather

Option 2: BOM FTP Parsing

# Create script: scripts/poll-bom-ftp.ts
npm run db:poll-bom

Implementation Steps:

Choose API service (recommend Open-Meteo for simplicity)
Create polling script for major towns
Store forecasts with 7-day horizon
Parse warnings from BOM RSS feeds
Set up cron job: Run every 6 hours
Add npm scripts

Estimated Time: 4-6 hours (Open-Meteo), 8-10 hours (BOM FTP)

4. Tourist Attractions & Points of Interest

Top 5 Data Sources

1. OpenStreetMap (FREE - Recommended Start) ⭐

Coverage: National Data Format: JSON via Overpass API License: ODbL (Free with attribution)

OSM Tourism Tags:

tourism=attraction - Tourist attractions
tourism=viewpoint - Scenic lookouts
tourism=museum, gallery, zoo, theme_park
tourism=information - Visitor centers
tourism=camp_site, caravan_site - Camping

Implementation:

✅ Already have collection infrastructure!
Extend scripts/collect-hazards.ts pattern
Add tourism queries to Overpass API
Import to amenities table with new category

Estimated Time: 2-3 hours

2. CAPAD - Protected Areas (FREE - High Value) ⭐

Source: Australian Government Department of Environment URL: https://www.environment.gov.au/fed/catalog

Coverage: 14,575 national parks and protected areas Data Format: GeoJSON, WFS, Shapefile License: CC-BY (Free)

What's Included:

National Parks
Nature Reserves
Indigenous Protected Areas
Conservation Areas
Marine Protected Areas

Implementation:

Direct GeoJSON download (no rate limits)
Similar to boundary import pattern
Create protected_areas table or add to amenities
Single download, process once

Estimated Time: 1-2 hours

3. NSW Points of Interest (FREE - NSW Only)

Source: NSW Government Data Portal URL: https://data.nsw.gov.au/data/dataset/nsw-points-of-interest-poi

Coverage: New South Wales Data Format: ArcGIS REST API, WMS, GeoJSON License: CC-BY 3.0

Categories:

Recreation facilities
Education (universities, museums)
Transportation hubs
Community facilities

Implementation:

Query ArcGIS REST API
Filter for tourism/recreation categories
Similar to NHVR restrictions pattern

Estimated Time: 3-4 hours

4. Victorian Landmarks (FREE - Melbourne)

Source: City of Melbourne URL: https://data.melbourne.vic.gov.au/api/v2/catalog/datasets/landmarks-and-places-of-interest

Coverage: Melbourne metro area Data Format: Direct GeoJSON export License: CC-BY

What's Included:

Museums, galleries, theatres
Sports facilities
Educational institutions
Health services

Implementation:

Direct download - no API needed
Import like amenities

Estimated Time: 1 hour

5. ATDW API (PAID - Most Comprehensive)

Source: Australian Tourism Data Warehouse URL: https://www.atdw.com.au/distributors

Coverage: National (50,000+ tourism products) Data Format: XML via SOAP API License: Commercial (paid subscription)

Pricing:

30-day free trial
Regional Package: $165 setup (government/NFP)
Standard Package: $660 setup
Premium Package: Full access (most popular)

What's Included:

Accommodation, attractions, tours
Restaurants, events, transport
250,000+ images
Detailed descriptions and facilities
Operator contact information

Recommendation: Start with free trial to evaluate quality

Estimated Time: 8-12 hours (SOAP/XML complexity)

Database Schema Extension

-- Extend amenities table for tourism data
ALTER TABLE amenities ADD COLUMN IF NOT EXISTS attraction_type TEXT;
ALTER TABLE amenities ADD COLUMN IF NOT EXISTS images JSONB;
ALTER TABLE amenities ADD COLUMN IF NOT EXISTS facilities JSONB;
ALTER TABLE amenities ADD COLUMN IF NOT EXISTS opening_hours_detailed JSONB;
ALTER TABLE amenities ADD COLUMN IF NOT EXISTS entry_fee DECIMAL(10,2);
ALTER TABLE amenities ADD COLUMN IF NOT EXISTS website TEXT;
ALTER TABLE amenities ADD COLUMN IF NOT EXISTS description TEXT;
ALTER TABLE amenities ADD COLUMN IF NOT EXISTS accessibility JSONB;

-- Add tourism categories
ALTER TYPE amenity_category ADD VALUE IF NOT EXISTS 'tourism_attraction';
ALTER TYPE amenity_category ADD VALUE IF NOT EXISTS 'tourism_accommodation';
ALTER TYPE amenity_category ADD VALUE IF NOT EXISTS 'protected_area';
ALTER TYPE amenity_category ADD VALUE IF NOT EXISTS 'viewpoint';

Implementation Priority

Phase 3A: Free & Quick (Recommended Start)

✅ OSM Tourism data - Extend existing collection (2-3 hrs)
✅ CAPAD Protected Areas - Direct download (1-2 hrs)
✅ VIC Landmarks - Direct download (1 hr)
✅ NSW POI - ArcGIS API (3-4 hrs)

Total Time: 7-10 hours for comprehensive free coverage

Phase 3B: Enhanced (Optional) 5. 🔄 ATDW Free Trial - Evaluate commercial data (8-12 hrs) 6. 💰 ATDW Subscription - If trial proves valuable

Implementation Roadmap

Week 1: Mobile Coverage & Weather

Day 1-2: Mobile Coverage

Install KML dependencies: npm install @tmcw/togeojson @xmldom/xmldom
Download Telstra 4G/5G coverage KML files
Test import script with sample data
Import full coverage data (may take hours due to file size)

Day 3-4: Weather Integration

Choose weather API (recommend Open-Meteo)
Create scripts/poll-weather-openmeteo.ts
Implement forecast collection for major towns
Test BOM warning RSS feeds
Set up automation (cron/GitHub Actions)

Day 5: Testing & Documentation

Test mobile app queries for coverage gaps
Test weather forecast queries
Update README with new features

Week 2: Bridge Restrictions & Tourist POIs

Day 1-2: Bridge Restrictions

Create migration for bridge_restrictions table
Create scripts/import-bridge-restrictions.ts
Query NHVR ArcGIS services
Download Tasmania bridge data
Import and validate

Day 3-5: Tourist Attractions

Extend OSM collection for tourism tags
Download and import CAPAD protected areas
Import NSW POI data
Import Melbourne landmarks
Create scripts/collect-tourism-poi.ts

Day 6-7: Testing & Integration

Test all new queries
Update mobile app helper functions
Create data quality reports
Update documentation

NPM Scripts to Add

{
  "db:import-coverage": "tsx scripts/import-mobile-coverage.ts",
  "db:import-bridge-restrictions": "tsx scripts/import-bridge-restrictions.ts",
  "db:poll-weather": "tsx scripts/poll-weather-openmeteo.ts",
  "db:poll-bom": "tsx scripts/poll-bom-ftp.ts",
  "db:collect-tourism": "tsx scripts/collect-tourism-poi.ts",
  "db:import-protected-areas": "tsx scripts/import-protected-areas.ts",
  "db:setup-phase3": "tsx scripts/setup-phase3.ts"
}

Data Quality & Validation

Coverage Verification

After each import, verify:

# Check record counts
npm run db:summary

# Verify geographic distribution
npm run db:check-coverage

# Run data quality checks
npm run db:validate-phase3

Expected Record Counts

Dataset	Estimated Records	Source
Mobile Coverage	10-20 polygons	ACCC (per carrier/network)
Bridge Restrictions	500-1,000	NHVR + State data
Weather Forecasts	100-200 locations	Major towns
Weather Warnings	0-50 (active)	BOM
Tourist Attractions	10,000+	OSM
Protected Areas	14,575	CAPAD
NSW POI	5,000+	NSW Govt
Melbourne Landmarks	500+	City of Melbourne

Total New Records: ~30,000+

Mobile App Integration

Query Examples

Check Coverage Along Route:

const { data } = await supabase.rpc('mobile_coverage_gaps_along_route', {
  route_geom: routeLineString,
  sample_distance: 5000, // Check every 5km
  carrier: 'telstra',
  network_type: '4g'
});

// Show warning if gaps found
if (data && data.length > 0) {
  alert(`⚠️ ${data.length} coverage gaps on route`);
}

Check Bridge Restrictions:

const { data } = await supabase
  .from('bridge_restrictions')
  .select('*')
  .eq('suitable_for_caravans', false)
  .gte('latitude', minLat)
  .lte('latitude', maxLat)
  .gte('longitude', minLon)
  .lte('longitude', maxLon);

// Show bridge warnings
data?.forEach(bridge => {
  console.log(`⚠️ ${bridge.bridge_name}: ${bridge.restriction_reason}`);
});

Get Weather Forecast:

const { data } = await supabase.rpc('weather_forecast_near_point', {
  lat: -33.8688,
  lon: 151.2093,
  radius_meters: 50000,
  days_ahead: 7
});

// Show weather warnings
data?.forEach(forecast => {
  if (forecast.has_severe_weather_warning) {
    alert(`⚠️ ${forecast.warning_type}: ${forecast.warning_description}`);
  }
});

Find Nearby Attractions:

const { data } = await supabase
  .from('amenities')
  .select('*')
  .in('category', ['tourism_attraction', 'viewpoint', 'protected_area'])
  .rpc('nearby', { lat, lon, radius_meters: 20000 });

// Display on map
data?.forEach(attraction => {
  addMarkerToMap(attraction);
});

Cost Analysis

Free Data Sources

✅ OpenStreetMap: Free
✅ ACCC Coverage: Free
✅ NHVR Data: Free
✅ BOM Weather: Free (FTP)
✅ Open-Meteo: Free
✅ CAPAD: Free
✅ State POI Data: Free

Paid Options (Optional)

ATDW Tourism Data: $165-$660 setup + possible ongoing fees
Tomorrow.io Weather: Free tier (500 calls/day), paid tiers available

Estimated Total Cost for Phase 3: $0 (using free sources)

Success Metrics

Phase 3 will be considered complete when:

✅ Mobile coverage data imported for all major carriers
✅ Bridge restrictions data covering national network
✅ Weather polling operational with 6-hour updates
✅ 10,000+ tourist attractions in database
✅ Protected areas (national parks) imported
✅ Mobile app can query all new data types
✅ Helper functions tested and documented
✅ Data quality validated (>95% accuracy)
✅ Automation running (weather polling)

Current Status Summary

✅ Completed:

Research for all Phase 3 data sources
Mobile coverage import script (needs dependencies)
Database schema (Phase 2 migrations)
Helper functions for queries

🔄 In Progress:

Documentation (this file)

❌ Not Started:

Installing KML dependencies
Downloading coverage data
Bridge restrictions implementation
Weather polling implementation
Tourism POI collection
Testing and validation

Next Steps:

Install dependencies: npm install @tmcw/togeojson @xmldom/xmldom
Start with Quick Wins: OSM tourism + CAPAD protected areas
Download mobile coverage KML files
Implement weather polling with Open-Meteo

Resources

Documentation

ACCC Data: https://data.gov.au/data/dataset/accc-mobile-infrastructure-report-data-release
NHVR API: https://api-portal.nhvr.gov.au
BOM Data Feeds: http://www.bom.gov.au/catalogue/data-feeds.shtml
Open-Meteo API: https://open-meteo.com/en/docs
CAPAD: https://www.environment.gov.au/fed/catalog
OSM Tourism: https://wiki.openstreetmap.org/wiki/Tag:tourism=*

Support

GitHub Issues: https://github.com/haydenfarquhar/vanroute/issues
Phase 2 Reference: PHASE2.md, PHASE2_COMPLETION_REPORT.md

Last Updated: October 14, 2025 Status: Ready for Implementation Estimated Total Time: 30-40 hours for complete Phase 3

Overview​

1. Mobile Coverage Data​

Data Source: ACCC Mobile Infrastructure Report​

Implementation Status​

Quick Start​

2. Bridge Load Limits & Restrictions​

Data Sources​

A. NHVR ArcGIS Services (National)​

B. Tasmania Load Limited Bridges​

C. State-Specific Sources​

Database Schema (Recommended)​

Implementation Status​

3. Weather & Flood Forecasting​

Data Source: Bureau of Meteorology (BOM)​

A. BOM FTP Archive (Free)​

B. Weather API Services (Third-party)​

Database Tables (Already exist from Phase 2)​

Implementation Status​

4. Tourist Attractions & Points of Interest​

Top 5 Data Sources​

1. OpenStreetMap (FREE - Recommended Start) ⭐​

2. CAPAD - Protected Areas (FREE - High Value) ⭐​

3. NSW Points of Interest (FREE - NSW Only)​

4. Victorian Landmarks (FREE - Melbourne)​

5. ATDW API (PAID - Most Comprehensive)​

Database Schema Extension​

Implementation Priority​

Implementation Roadmap​

Week 1: Mobile Coverage & Weather​

Week 2: Bridge Restrictions & Tourist POIs​

NPM Scripts to Add​

Data Quality & Validation​

Coverage Verification​

Expected Record Counts​

Mobile App Integration​

Query Examples​

Cost Analysis​

Free Data Sources​

Paid Options (Optional)​

Success Metrics​

Current Status Summary​

Resources​

Documentation​

Support​

Overview

1. Mobile Coverage Data

Data Source: ACCC Mobile Infrastructure Report

Implementation Status

Quick Start

2. Bridge Load Limits & Restrictions

Data Sources

A. NHVR ArcGIS Services (National)

B. Tasmania Load Limited Bridges

C. State-Specific Sources

Database Schema (Recommended)

Implementation Status

3. Weather & Flood Forecasting

Data Source: Bureau of Meteorology (BOM)

A. BOM FTP Archive (Free)

B. Weather API Services (Third-party)

Database Tables (Already exist from Phase 2)

Implementation Status

4. Tourist Attractions & Points of Interest

Top 5 Data Sources

1. OpenStreetMap (FREE - Recommended Start) ⭐

2. CAPAD - Protected Areas (FREE - High Value) ⭐

3. NSW Points of Interest (FREE - NSW Only)

4. Victorian Landmarks (FREE - Melbourne)

5. ATDW API (PAID - Most Comprehensive)

Database Schema Extension

Implementation Priority

Implementation Roadmap

Week 1: Mobile Coverage & Weather

Week 2: Bridge Restrictions & Tourist POIs

NPM Scripts to Add

Data Quality & Validation

Coverage Verification

Expected Record Counts

Mobile App Integration

Query Examples

Cost Analysis

Free Data Sources

Paid Options (Optional)

Success Metrics

Current Status Summary

Resources

Documentation

Support