Migration 0011: Mobile Coverage Table

This migration creates the mobile_coverage table for storing mobile phone coverage maps from Australian carriers, enabling users to see network availability along their routes.

What This Migration Creates

`mobile_coverage` Table

Stores geographic coverage areas for mobile phone networks across Australia.

Fields:

carrier - Network provider (telstra, optus, vodafone, tpg)
network_type - Technology type (3g, 4g, 5g)
coverage_type - Coverage category (outdoor, indoor, ext_ant)
geometry - MultiPolygon geometry for coverage areas (PostGIS)
signal_strength - Optional signal strength indicator
frequency_band - Optional frequency band information
data_source - Data source identifier (default: 'accc')
source_year - Year of the coverage data
Timestamps (created_at, updated_at)

Indexes:

Spatial index (GIST) on geometry for fast geographic queries
Indexes on carrier, network_type, coverage_type for filtering
Unique constraint on carrier + network + coverage + band + year

Triggers:

Automatic updated_at timestamp on row updates

Data Source

ACCC Mobile Infrastructure Report

The Australian Competition and Consumer Commission (ACCC) publishes comprehensive mobile coverage data for all major carriers:

URL: https://data.gov.au/data/dataset/accc-mobile-infrastructure-report-data-release
Format: KML files (large, 100-500 MB per file)
Update Frequency: Annually
License: Open data - free to use
Coverage: All of Australia

Carriers Included:

Telstra (3G, 4G, 5G)
Optus (3G, 4G, 5G)
Vodafone (3G, 4G)
TPG (4G)

Coverage Types:

Outdoor: Standard mobile coverage
Indoor: Coverage within buildings
ext_ant: External antenna required

Running the Migration

Prerequisites

Supabase project set up
Database migrations 0001-0010 already applied
PostGIS extension enabled
Admin access to Supabase SQL Editor

Steps

Option 1: Supabase Web Interface (Recommended)

Go to your Supabase project SQL Editor:

https://app.supabase.com/project/YOUR_PROJECT_ID/sql/new

Open the migration file:

packages/database/supabase/migrations/0011_create_mobile_coverage_table.sql

Copy the entire contents and paste into the SQL Editor
Click Run or press Cmd/Ctrl + Enter
Verify success - you should see "Success. No rows returned"

Option 2: Supabase CLI

cd packages/database
supabase db push

Verification

Check that the table was created:

-- Verify table exists
SELECT
  column_name,
  data_type,
  is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'mobile_coverage'
ORDER BY ordinal_position;

Verify Indexes

-- Check spatial index
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'mobile_coverage';

You should see:

mobile_coverage_geometry_idx (GIST index for spatial queries)
mobile_coverage_carrier_idx
mobile_coverage_network_type_idx
mobile_coverage_coverage_type_idx

Importing Coverage Data

After running the migration, import mobile coverage data from ACCC:

Step 1: Download KML Files

Visit: https://data.gov.au/data/dataset/accc-mobile-infrastructure-report-data-release
Download coverage files:
- Telstra 3G/4G/5G coverage
- Optus 3G/4G/5G coverage
- Vodafone coverage
- TPG coverage
Save to a directory (e.g., ~/Downloads/mobile-coverage/)

File sizes: 100-500 MB each (total ~2-3 GB)

Step 2: Install Dependencies

cd packages/database
npm install @tmcw/togeojson @xmldom/xmldom --save-dev

These packages are required to parse KML files.

Step 3: Run Import Script

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

What the script does:

Parses KML files to GeoJSON
Extracts coverage polygons
Converts to PostGIS MultiPolygon format
Batch inserts to database (500 per batch)
Handles deduplication

Import time: 1-3 hours depending on file sizes and connection speed

Expected Results

After import, you should have:

Telstra: ~5,000-10,000 coverage areas
Optus: ~3,000-5,000 coverage areas
Vodafone: ~2,000-3,000 coverage areas
TPG: ~1,000-2,000 coverage areas

Total: 10,000-20,000 coverage polygons

Querying Coverage Data

Find Coverage at Location

-- Check mobile coverage at Sydney Opera House
SELECT
  carrier,
  network_type,
  coverage_type
FROM mobile_coverage
WHERE ST_Contains(
  geometry,
  ST_SetSRID(ST_MakePoint(151.2153, -33.8568), 4326)
)
ORDER BY carrier, network_type DESC;

Find Coverage Along Route

-- Check coverage along route from Sydney to Newcastle
SELECT DISTINCT
  carrier,
  network_type,
  COUNT(*) as segment_count
FROM mobile_coverage
WHERE ST_Intersects(
  geometry,
  ST_MakeLine(ARRAY[
    ST_SetSRID(ST_MakePoint(151.2093, -33.8688), 4326),
    ST_SetSRID(ST_MakePoint(151.7789, -32.9283), 4326)
  ])
)
GROUP BY carrier, network_type
ORDER BY carrier, network_type DESC;

Get Carriers with 4G Coverage

-- Find all carriers with 4G at location
SELECT carrier, coverage_type
FROM mobile_coverage
WHERE network_type = '4g'
  AND ST_Contains(
    geometry,
    ST_SetSRID(ST_MakePoint(lon, lat), 4326)
  );

Mobile App Integration

Check Coverage at User Location

import { supabase } from '@/lib/supabase';

// Check what coverage is available at user's location
const { data: coverage } = await supabase
  .rpc('get_coverage_at_point', {
    lat: userLat,
    lon: userLon
  });

// Returns: [
//   { carrier: 'telstra', network_type: '5g', coverage_type: 'outdoor' },
//   { carrier: 'optus', network_type: '4g', coverage_type: 'outdoor' },
//   ...
// ]

Check Coverage Along Route

// Get coverage gaps along planned route
const { data: gaps } = await supabase
  .rpc('find_coverage_gaps_on_route', {
    route_points: routeCoordinates,
    preferred_carrier: 'telstra'
  });

// Show warning if gaps detected
if (gaps && gaps.length > 0) {
  console.warn(`No coverage for ${gaps[0].distance_km} km`);
}

Helper Function (to create)

Add this function to your database:

-- Create helper function
CREATE OR REPLACE FUNCTION get_coverage_at_point(lat FLOAT, lon FLOAT)
RETURNS TABLE (
  carrier TEXT,
  network_type TEXT,
  coverage_type TEXT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    mc.carrier,
    mc.network_type,
    mc.coverage_type
  FROM mobile_coverage mc
  WHERE ST_Contains(
    mc.geometry,
    ST_SetSRID(ST_MakePoint(lon, lat), 4326)
  )
  ORDER BY
    mc.carrier,
    CASE mc.network_type
      WHEN '5g' THEN 1
      WHEN '4g' THEN 2
      WHEN '3g' THEN 3
      ELSE 4
    END;
END;
$$ LANGUAGE plpgsql;

Use Cases

1. Route Planning

Feature: "Show me areas with no mobile coverage"

const { data: route } = await planRoute(origin, destination);

// Check for coverage gaps
const gaps = await checkCoverageGaps(route.waypoints, 'telstra');

if (gaps.length > 0) {
  showWarning(
    `No mobile coverage for ${gaps[0].distance_km} km near ${gaps[0].location}`
  );
}

2. Emergency Planning

Feature: "When should I make last call before remote section"

// Find last point with coverage before remote area
const lastCoveragePoint = await findLastCoveragePoint(
  route,
  userCarrier
);

showNotification(
  `Last mobile coverage in 15km at ${lastCoveragePoint.name}`
);

3. Carrier Comparison

Feature: "Which carrier has best coverage on this route"

const carriers = ['telstra', 'optus', 'vodafone'];

const comparison = await Promise.all(
  carriers.map(async carrier => {
    const coverage = await calculateRouteCoverage(route, carrier);
    return { carrier, coverage_percent: coverage };
  })
);

// Sort by best coverage
comparison.sort((a, b) => b.coverage_percent - a.coverage_percent);

Performance Optimization

Indexes

The migration creates optimal indexes:

-- Spatial index for location queries (most important)
CREATE INDEX mobile_coverage_geometry_idx
  ON mobile_coverage USING GIST (geometry);

-- Filter indexes
CREATE INDEX mobile_coverage_carrier_idx ON mobile_coverage (carrier);
CREATE INDEX mobile_coverage_network_type_idx ON mobile_coverage (network_type);
CREATE INDEX mobile_coverage_coverage_type_idx ON mobile_coverage (coverage_type);

Query Optimization Tips

Always specify carrier when possible to reduce search space
Use bounding box queries before exact geometry checks
Cache coverage checks in mobile app (coverage doesn't change frequently)
Simplify route geometry before checking coverage (reduce waypoints)

Example Optimized Query

-- Optimized coverage check (uses bounding box first)
SELECT carrier, network_type
FROM mobile_coverage
WHERE carrier = 'telstra'  -- Filter by carrier first
  AND ST_Contains(
    geometry,
    ST_SetSRID(ST_MakePoint(?, ?), 4326)
  );

Troubleshooting

Import Script Fails

Error: "Module not found: @tmcw/togeojson"

Install dependencies:

npm install @tmcw/togeojson @xmldom/xmldom --save-dev

Error: "Cannot parse KML file"

Verify KML file is not corrupted
Check file size (should be 100-500 MB)
Try re-downloading from ACCC website

Error: "Database connection timeout"

Large inserts may timeout. The script batches insertions (500 per batch) to prevent this. If still timing out, reduce batch size in the script.

Queries Are Slow

Verify spatial index exists:

SELECT indexname FROM pg_indexes
WHERE tablename = 'mobile_coverage'
  AND indexname LIKE '%geometry%';

Check query plan:

EXPLAIN ANALYZE
SELECT * FROM mobile_coverage
WHERE ST_Contains(geometry, ST_Point(151.2, -33.8));

Look for "Index Scan using mobile_coverage_geometry_idx"

Simplify geometry if polygons are too complex:

UPDATE mobile_coverage
SET geometry = ST_SimplifyPreserveTopology(geometry, 0.01)
WHERE ST_NPoints(geometry) > 10000;

Coverage Data Seems Incorrect

Check data source year:

SELECT carrier, source_year, COUNT(*)
FROM mobile_coverage
GROUP BY carrier, source_year;

Verify coverage type: Mobile coverage maps sometimes show "theoretical" coverage vs "actual" coverage. ACCC data is based on network modeling, not actual signal testing.
Update data annually: Download latest ACCC coverage data and re-import.

Maintenance

Update Coverage Data

Mobile coverage data should be updated annually:

Check for new ACCC data: Visit https://data.gov.au/data/dataset/accc-mobile-infrastructure-report-data-release
Download new files

Clear old data:

DELETE FROM mobile_coverage WHERE source_year < EXTRACT(YEAR FROM NOW());

Re-run import script:

npm run db:import-mobile-coverage -- ~/Downloads/new-coverage/

Data Quality Notes

Accuracy

ACCC data is modeled, not measured - actual coverage may vary
Building penetration varies - indoor coverage may be less reliable
Terrain effects are accounted for in modeling
Network congestion not included in data

Recommendations for Users

Show disclaimers in your app:

"Coverage data is approximate and may vary"
"Last updated: [data source year]"
"Indoor coverage may be limited"
"Report coverage issues to improve data"

User Feedback

Consider adding user-reported coverage:

// Allow users to report actual coverage
await supabase.from('user_coverage_reports').insert({
  user_id: userId,
  location: `POINT(${lon} ${lat})`,
  carrier: 'telstra',
  network_type: '4g',
  actual_coverage: true,  // or false
  signal_bars: 3,
  timestamp: new Date(),
});

Next Steps

After this migration and import:

Test coverage queries - Verify data imported correctly
Create helper functions - Add SQL functions for common queries
Implement mobile app UI - Show coverage on route planning screen
Add carrier selection - Let users choose their carrier
Display coverage warnings - Alert users of upcoming coverage gaps
Monitor performance - Ensure spatial queries are fast

Remaining Data Sources - Other high-priority data
Database Overview - Main database documentation
Phase 3 Plan - Advanced features roadmap

Migration File Location

packages/database/supabase/migrations/0011_create_mobile_coverage_table.sql

What This Migration Creates​

mobile_coverage Table​

Data Source​

Running the Migration​

Prerequisites​

Steps​

Option 1: Supabase Web Interface (Recommended)​

Option 2: Supabase CLI​

Verification​

Verify Indexes​

Importing Coverage Data​

Step 1: Download KML Files​

Step 2: Install Dependencies​

Step 3: Run Import Script​

Expected Results​

Querying Coverage Data​

Find Coverage at Location​

Find Coverage Along Route​

Get Carriers with 4G Coverage​

Mobile App Integration​

Check Coverage at User Location​

Check Coverage Along Route​

Helper Function (to create)​

Use Cases​

1. Route Planning​

2. Emergency Planning​

3. Carrier Comparison​

Performance Optimization​

Indexes​

Query Optimization Tips​

Example Optimized Query​

Troubleshooting​

Import Script Fails​

Queries Are Slow​

Coverage Data Seems Incorrect​

Maintenance​

Update Coverage Data​

Data Quality Notes​

Accuracy​

Recommendations for Users​

User Feedback​

Next Steps​

Related Documentation​

Migration File Location​