Task S4T02: Create stats.v_waypoints_source_union View¶

Sprint Context¶

• Sprint index: Sprint 4 Task Index
• Tags: database, database-refactor, sprint-4, task-index

Source¶

• DBA source: task-S4T02.dba.md
• Specification source: task-S4T02.specification.md

Purpose & Scope¶

Creates a diagnostic union view over the two existing waypoint source tables (geokrety.gk_waypoints_gc and geokrety.gk_waypoints_oc). This view is used by the seeding function in S4T03 to select from both sources in a single pass. It is also useful for ad-hoc diagnostics — e.g., verifying coverage gaps, identifying duplicate codes across sources, or reconciling coordinate discrepancies.

This is a view only — no data is moved. It does not replace stats.waypoints. It is a helper layer for seeding and maintenance.

Create a diagnostic union view stats.v_waypoints_source_union that merges both established waypoint source tables (geokrety.gk_waypoints_gc, geokrety.gk_waypoints_oc) into a single queryable surface. The primary consumer is the seeding function in S4T03. Secondary uses include ad-hoc diagnostics and source-coverage reconciliation.

Scope: Create one view. No tables, no triggers, no functions, no data.

Requirements¶

Acceptance Criteria¶

Migration File¶

20260310400100_create_waypoints_source_view.php

Full SQL DDL¶

CREATE OR REPLACE VIEW stats.v_waypoints_source_union AS
SELECT
  UPPER(waypoint)  AS waypoint_code,
  'GC'::CHAR(2)   AS source,
  lat::DOUBLE PRECISION,
  lon::DOUBLE PRECISION,
  UPPER(country)::CHAR(2) AS country
FROM geokrety.gk_waypoints_gc
WHERE waypoint IS NOT NULL

UNION ALL

SELECT
  UPPER(waypoint)  AS waypoint_code,
  'OC'::CHAR(2)   AS source,
  lat::DOUBLE PRECISION,
  lon::DOUBLE PRECISION,
  UPPER(country)::CHAR(2) AS country
FROM geokrety.gk_waypoints_oc
WHERE waypoint IS NOT NULL;

COMMENT ON VIEW stats.v_waypoints_source_union
  IS 'Union of GC and OC waypoint tables for seeding and diagnostics; does not deduplicate';

Full Phinx Migration PHP Code¶

<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CreateWaypointsSourceView extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<'SQL'
CREATE OR REPLACE VIEW stats.v_waypoints_source_union AS
SELECT
  UPPER(waypoint)  AS waypoint_code,
  'GC'::CHAR(2)   AS source,
  lat::DOUBLE PRECISION,
  lon::DOUBLE PRECISION,
  UPPER(country)::CHAR(2) AS country
FROM geokrety.gk_waypoints_gc
WHERE waypoint IS NOT NULL

UNION ALL

SELECT
  UPPER(waypoint)  AS waypoint_code,
  'OC'::CHAR(2)   AS source,
  lat::DOUBLE PRECISION,
  lon::DOUBLE PRECISION,
  UPPER(country)::CHAR(2) AS country
FROM geokrety.gk_waypoints_oc
WHERE waypoint IS NOT NULL;

COMMENT ON VIEW stats.v_waypoints_source_union
  IS 'Union of GC and OC waypoint tables for seeding and diagnostics; does not deduplicate';
SQL
        );
    }

    public function down(): void
    {
        $this->execute('DROP VIEW IF EXISTS stats.v_waypoints_source_union;');
    }
}

Data Contract¶

View: stats.v_waypoints_source_union

SQL Usage Examples¶

-- Total waypoints available from all sources
SELECT source, COUNT(*) AS total
FROM stats.v_waypoints_source_union
GROUP BY source
ORDER BY source;

-- Find waypoints present in both GC and OC (potential duplicates)
SELECT waypoint_code, COUNT(DISTINCT source) AS sources
FROM stats.v_waypoints_source_union
GROUP BY waypoint_code
HAVING COUNT(DISTINCT source) > 1
LIMIT 20;

-- Preview first 10 GC waypoints
SELECT * FROM stats.v_waypoints_source_union
WHERE source = 'GC'
LIMIT 10;

-- Waypoints without coordinates (will result in UK enrichment needed)
SELECT waypoint_code, source
FROM stats.v_waypoints_source_union
WHERE lat IS NULL OR lon IS NULL
LIMIT 20;

TimescaleDB Assessment¶

NOT applicable. This is a view over source tables, not a time-series. No TimescaleDB involvement.

pgTAP Unit Tests¶

Implementation Checklist¶

• 1. Create migration file 20260310400100_create_waypoints_source_view.php
• 2. Verify geokrety.gk_waypoints_gc and geokrety.gk_waypoints_oc exist with waypoint, lat, lon, country columns
• 3. Run phinx migrate — confirm no errors
• 4. SELECT source, COUNT(*) FROM stats.v_waypoints_source_union GROUP BY source — verify both GC and OC appear
• 5. Verify all waypoint_code values are uppercase
• 6. Run pgTAP tests T-4.2.001 through T-4.2.009

• 7. Verify phinx rollback drops the view cleanly

• • [DBA ] DBA file reviewed: task-S4T02.dba.md
• Migration 20260310400100_create_waypoints_source_view.php created
• Migration runs successfully
• Both sources visible in view
• All T-4.2.xxx pgTAP tests pass
• Migration rolled back successfully

View Column Reference¶

Note: This view uses UNION ALL (not UNION) to avoid the cost of deduplication at query time. The seeding function (S4T03) handles deduplication via ON CONFLICT DO NOTHING.