Radio Station Integration Plan

Executive Summary

Integrate FCC radio station data from cbradio project into cbdistricts, enabling spatial analysis of radio coverage against congressional districts. This creates a powerful targeting tool: "Which radio stations reach voters in District X?"

---

Data Overview

Source: ../cbradio/

Asset	Location	Size	Count
Station DB	db/cbradio.db	54 MB	1,908 stations
Coverage Contours	fcc/data/contours/*.json	~23 KB each	Up to 1,563 files

Station Schema (Key Fields)

-- From cbradio.db station table
facility_id         -- FCC unique ID (PK)
callsign            -- e.g., "WBCH-AM"
service_type        -- "am" or "fm"
fcc_frequency       -- kHz (AM) or MHz (FM)
fcc_power_kw        -- Transmit power
fcc_station_class   -- A/B/C/D
fcc_hours_operation -- U/D/N
fcc_antenna_lat/lon -- Point location
fcc_service_contour -- Filename if contour exists
state               -- Two-letter code
nielsen_dma         -- Market name
owner               -- Licensee

Coverage Contour Schema (GeoJSON)

{
  "type": "Feature",
  "properties": {
    "callsign": "WBCH",
    "facility_id": 3990,
    "service_type": "am",
    "frequency": 1220.0,
    "power_kw": 0.25
  },
  "geometry": {
    "type": "Polygon",
    "coordinates": [[...361 points...]]
  }
}

---

Architecture Decision

Option Considered: Extend Layer System

The existing /api/v1/layers/* system is designed for tessellating political boundaries where: - Polygons don't overlap - Each point belongs to exactly one district - Features are stored in separate DuckDB files per layer

Radio coverage is fundamentally different: - Coverage polygons overlap (multiple stations cover same area) - Points (stations) + Polygons (coverage) hybrid data - Spatial relationships matter more than boundaries

Chosen Approach: Dedicated Radio Module

Create a separate radio domain with: 1. Dedicated tables in main cbdistricts.duckdb 2. Spatial join tables pre-computing district coverage 3. New API endpoints under /api/v1/radio/* 4. Cross-domain endpoints like /api/v1/districts/{geoid}/radio

This enables powerful queries while keeping the layer system clean.

---

Data Model

Tables in cbdistricts.duckdb

-- Radio stations (imported from cbradio.db)
CREATE TABLE radio_stations (
    facility_id VARCHAR PRIMARY KEY,
    callsign VARCHAR NOT NULL,
    service_type VARCHAR,           -- 'am' or 'fm'
    frequency DECIMAL(10,3),
    power_kw DECIMAL(10,3),
    station_class VARCHAR,
    hours_operation VARCHAR,
    antenna_mode VARCHAR,
    state VARCHAR,
    nielsen_dma VARCHAR,
    owner VARCHAR,
    contour_file VARCHAR,           -- Filename if exists
    point GEOMETRY,                 -- Point from lat/lon
    last_synced TIMESTAMP
);

-- Coverage contours (imported from GeoJSON files)
CREATE TABLE radio_contours (
    facility_id VARCHAR PRIMARY KEY
        REFERENCES radio_stations(facility_id),
    callsign VARCHAR NOT NULL,
    service_type VARCHAR,
    contour GEOMETRY NOT NULL       -- Polygon from GeoJSON
);

-- Spatial index for fast lookups
CREATE INDEX idx_stations_point ON radio_stations USING RTREE (point);
CREATE INDEX idx_contours_geom ON radio_contours USING RTREE (contour);

-- Pre-computed: which districts does each station cover?
CREATE TABLE radio_district_coverage (
    facility_id VARCHAR REFERENCES radio_stations(facility_id),
    geoid VARCHAR,                  -- Congressional district GEOID
    overlap_sq_km DECIMAL(10,2),    -- Area of intersection
    district_pct DECIMAL(5,2),      -- % of district covered
    PRIMARY KEY (facility_id, geoid)
);

-- View for easy station queries
CREATE VIEW v_radio_full AS
SELECT
    s.*,
    c.contour IS NOT NULL AS has_contour,
    ST_AsGeoJSON(s.point) AS point_geojson
FROM radio_stations s
LEFT JOIN radio_contours c ON s.facility_id = c.facility_id;

---

API Endpoints

Radio Endpoints (/api/v1/radio/)

Method	Endpoint	Description
GET	/radio/stations	List stations with filtering
GET	/radio/stations/{facility_id}	Station detail
GET	/radio/stations/{facility_id}/contour	Coverage GeoJSON
GET	/radio/stations/{facility_id}/districts	Districts this station covers
GET	/radio/stats	Aggregate statistics

Cross-Domain Endpoints

Method	Endpoint	Description
GET	/districts/{geoid}/radio	Radio stations covering this district
GET	/districts/{geoid}/radio/geojson	Coverage polygons as FeatureCollection

Query Parameters

GET /api/v1/radio/stations
  ?state=MI              # Filter by state
  ?service_type=fm       # AM or FM only
  ?min_power=10          # Minimum power (kW)
  ?has_contour=true      # Only stations with coverage data
  ?limit=50&offset=0     # Pagination

Response Examples

// GET /api/v1/radio/stations/55492
{
  "facility_id": "55492",
  "callsign": "WBCH-AM",
  "service_type": "am",
  "frequency": 1220.0,
  "power_kw": 0.25,
  "station_class": "D",
  "hours_operation": "D",
  "state": "MI",
  "nielsen_dma": "Grand Rapids-Kalamazoo-Battle Creek",
  "owner": "Midwest Communications Inc.",
  "has_contour": true,
  "location": {
    "lat": 42.626111,
    "lon": -85.278056
  },
  "districts_covered": 3
}

// GET /api/v1/districts/2603/radio
{
  "geoid": "2603",
  "district_name": "Michigan's 3rd Congressional District",
  "stations": [
    {
      "facility_id": "55492",
      "callsign": "WBCH-AM",
      "frequency": 1220.0,
      "power_kw": 0.25,
      "coverage_pct": 45.2
    },
    ...
  ],
  "total_stations": 12,
  "by_service_type": {"am": 5, "fm": 7}
}

---

Implementation Phases

Phase 1: Data Import Script

File: scripts/import_radio_data.py

1. Connect to ../cbradio/db/cbradio.db
2. Import station table → radio_stations
3. Convert lat/lon to Point geometry
4. Import contour GeoJSON files → radio_contours
5. Build spatial indexes

Usage:

python scripts/import_radio_data.py
  --cbradio-db ../cbradio/db/cbradio.db
  --contours-dir ../cbradio/fcc/data/contours/
  --output data/output/cbdistricts.duckdb

Phase 2: Spatial Join Computation

File: scripts/compute_radio_coverage.py

1. For each station with contour:
2. ST_Intersection with each congressional district
3. Calculate overlap area and percentage
4. Store in radio_district_coverage

SQL:

INSERT INTO radio_district_coverage
SELECT
    c.facility_id,
    d.geoid,
    ST_Area(ST_Intersection(c.contour, d.geometry)) / 1000000 AS overlap_sq_km,
    ST_Area(ST_Intersection(c.contour, d.geometry)) /
        ST_Area(d.geometry) * 100 AS district_pct
FROM radio_contours c
CROSS JOIN districts d
WHERE ST_Intersects(c.contour, d.geometry);

Phase 3: API Layer

Files: - api/models/radio.py - Pydantic models - api/services/radio_service.py - Business logic - api/routes/radio.py - Endpoints

Integration: - Add radio_router to api/main.py - Extend api/routes/districts.py with /districts/{geoid}/radio

Phase 4: Web Visualization

Updates to web/server.py:

1. Add radio layer toggle
2. Station markers (colored by service type)
3. Contour polygons on hover/click
4. Station info popup

---

File Summary

scripts/
├── import_radio_data.py      # NEW: Import from cbradio
└── compute_radio_coverage.py # NEW: Spatial join computation

api/
├── models/
│   └── radio.py              # NEW: RadioStation, RadioContour models
├── services/
│   └── radio_service.py      # NEW: Radio data access
├── routes/
│   ├── radio.py              # NEW: /radio/* endpoints
│   └── districts.py          # MODIFY: Add /districts/{geoid}/radio

web/
└── server.py                 # MODIFY: Add radio layer to map

data/
└── output/
    └── cbdistricts.duckdb    # MODIFY: Add radio_* tables

---

Sync Strategy

Initial Load

1. Full import from cbradio.db
2. Import all existing contour files
3. Compute spatial joins (may take 10-15 minutes for 1,563 × 441 intersections)

Ongoing Sync

• FCC data changes infrequently (weekly/monthly)
• Add scripts/sync_radio_data.py for incremental updates
• Track last_synced timestamp

Contour Generation

If contours don't exist yet:

cd ../cbradio
python -m fcc.sync --all --force  # ~30 min due to rate limiting

---

Dependencies

Already Available

• DuckDB with spatial extension ✓
• Pydantic models pattern ✓
• Redis caching infrastructure ✓
• MapLibre GL JS in web viewer ✓

May Need

• None - all dependencies already in place

---

Estimated Effort

Phase	Tasks	Estimate
Phase 1	Import script	2-3 hours
Phase 2	Spatial joins	1-2 hours
Phase 3	API layer	3-4 hours
Phase 4	Web visualization	2-3 hours
Total		8-12 hours

---

Open Questions

1. Contour availability: Only 1 contour file exists currently. Run FCC sync first?

2. Recommendation: Yes, run python -m fcc.sync --all in cbradio before import

3. Storage: Same DuckDB or separate radio.db?

4. Recommendation: Same file for easier spatial joins with districts

5. State scope: Import all 50 states or start with focus states?

6. Recommendation: Import all - it's only 1,908 stations

7. Power threshold: Filter out very low power stations?

8. Recommendation: Import all, filter in API queries

---

Success Metrics

• All 1,908 stations imported
• All available contours loaded
• Spatial joins computed for all contour×district pairs
• API returns stations for any district in <100ms
• Map displays station markers and coverage polygons
• Redis caching reduces repeat queries to <10ms

---

Plan created: 2025-12-24