[info] Migrations already up
================================================================================
PROPAGATION ANALYSIS: QSO-HRRR Correlation Study
================================================================================
Started: 2026-04-01 13:59:49.016617Z

Building dataset (joining QSOs × HRRR at both endpoints)...
[debug] QUERY OK db=3011.3ms decode=8.3ms queue=3.2ms idle=49.0ms
SELECT
  q.id AS qso_id,
  q.band::float AS band,
  q.distance_km::float AS distance_km,
  q.qso_timestamp,
  EXTRACT(MONTH FROM q.qso_timestamp)::int AS month,
  EXTRACT(HOUR FROM q.qso_timestamp)::int AS utc_hour,

  -- Endpoint 1 atmospheric
  h1.surface_temp_c       AS h1_temp,
  h1.surface_dewpoint_c   AS h1_dewpoint,
  h1.surface_pressure_mb  AS h1_pressure,
  h1.surface_refractivity AS h1_refractivity,
  h1.min_refractivity_gradient AS h1_gradient,
  h1.hpbl_m               AS h1_hpbl,
  h1.pwat_mm              AS h1_pwat,
  h1.ducting_detected     AS h1_ducting,

  -- Endpoint 2 atmospheric
  h2.surface_temp_c       AS h2_temp,
  h2.surface_dewpoint_c   AS h2_dewpoint,
  h2.surface_pressure_mb  AS h2_pressure,
  h2.surface_refractivity AS h2_refractivity,
  h2.min_refractivity_gradient AS h2_gradient,
  h2.hpbl_m               AS h2_hpbl,
  h2.pwat_mm              AS h2_pwat,
  h2.ducting_detected     AS h2_ducting,

  -- Terrain
  tp.verdict AS terrain_verdict,

  -- Longitude for solar time
  ((q.pos1->>'lng')::float + (q.pos2->>'lng')::float) / 2.0 AS avg_longitude

FROM qsos q

INNER JOIN hrrr_profiles h1
  ON h1.lat = ROUND((q.pos1->>'lat')::numeric * 8) / 8
  AND h1.lon = ROUND((q.pos1->>'lng')::numeric * 8) / 8
  AND h1.valid_time = date_trunc('hour', q.qso_timestamp)

INNER JOIN hrrr_profiles h2
  ON h2.lat = ROUND((q.pos2->>'lat')::numeric * 8) / 8
  AND h2.lon = ROUND((q.pos2->>'lng')::numeric * 8) / 8
  AND h2.valid_time = date_trunc('hour', q.qso_timestamp)

LEFT JOIN terrain_profiles tp ON tp.qso_id = q.id

WHERE q.distance_km > 0
  AND q.distance_km < 3000
  AND q.qso_timestamp >= '2016-06-30'
  AND q.pos1 IS NOT NULL
  AND q.pos2 IS NOT NULL
 []
Dataset: 57248 matched QSO-HRRR rows

--------------------------------------------------------------------------------
DATASET SUMMARY
--------------------------------------------------------------------------------

QSOs per band:
  10 GHz: n=52846, median=191.8 km, p25=114.5 km, p75=283.9 km
  24 GHz: n=3621, median=93.1 km, p25=44.7 km, p75=135.5 km
  47 GHz: n=680, median=65.4 km, p25=22.3 km, p75=92.4 km
  75 GHz: n=94, median=23.7 km, p25=11.4 km, p75=85.8 km
  122000 MHz: n=7, median=80.2 km, p25=22.4 km, p75=139.0 km

Terrain verdicts:
  BLOCKED: 56430
  CLEAR: 480
  FRESNEL_PARTIAL: 338

--------------------------------------------------------------------------------
SPEARMAN RANK CORRELATION WITH DISTANCE (per band)
--------------------------------------------------------------------------------

10 GHz (n=52846):
  Variable                          rho  n_valid
  ----------------------------------------------
  Pressure (mb)                 -0.1804    52846
  Month                          0.1052    52846
  Dewpoint (°C)                 -0.0590    52846
  HPBL (m)                       0.0446    52846
  PWAT (mm)                     -0.0390    52846
  Refractivity Gradient         -0.0336    52347
  Temperature (°C)               0.0313    52846
  Surface Refractivity          -0.0239    52347
  Solar Hour                     0.0160    52846
  UTC Hour                       0.0067    52846

24 GHz (n=3621):
  Variable                          rho  n_valid
  ----------------------------------------------
  Dewpoint (°C)                 -0.3705     3621
  PWAT (mm)                     -0.3299     3621
  Surface Refractivity          -0.3169     3582
  Month                          0.2723     3621
  Solar Hour                     0.1876     3621
  Temperature (°C)              -0.1793     3621
  Pressure (mb)                 -0.1723     3621
  Refractivity Gradient         -0.0746     3582
  UTC Hour                       0.0557     3621
  HPBL (m)                      -0.0485     3621

47 GHz (n=680):
  Variable                          rho  n_valid
  ----------------------------------------------
  Pressure (mb)                 -0.2305      680
  PWAT (mm)                     -0.2265      680
  Dewpoint (°C)                 -0.1809      680
  Solar Hour                     0.1522      680
  Refractivity Gradient         -0.1391      678
  Month                          0.1105      680
  Surface Refractivity          -0.1088      678
  Temperature (°C)               0.0373      680
  UTC Hour                      -0.0238      680
  HPBL (m)                       0.0041      680

75 GHz (n=94):
  Variable                          rho  n_valid
  ----------------------------------------------
  Dewpoint (°C)                 -0.7033       94
  PWAT (mm)                     -0.6077       94
  Temperature (°C)              -0.5887       94
  Pressure (mb)                 -0.5695       94
  Surface Refractivity          -0.5257       94
  UTC Hour                      -0.3919       94
  Solar Hour                     0.2393       94
  HPBL (m)                       0.1496       94
  Month                          0.1444       94
  Refractivity Gradient         -0.0821       94

--------------------------------------------------------------------------------
BINNED FACTOR ANALYSIS (median distance per bin)
--------------------------------------------------------------------------------

10 GHz (n=52846):

  Refractivity Gradient:
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    < -500 (strong)                   0        -        -        -
    -500 to -300 (moderate)         202    213.7    180.3    284.5
    -300 to -200 (enhanced)        1910    176.1     99.5    271.0
    -200 to -100 (mild)           19345    193.5    116.7    297.0
    -100 to 0 (normal)            30890    192.4    112.4    278.0
    > 0                               0        -        -        -

  Temperature (°C):
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    < 0                               4      4.6      4.6      4.6
    0-10                            621    172.5     95.4    246.4
    10-20                         16764    184.4    112.1    276.4
    20-30                         33063    195.5    119.1    290.4
    > 30                           2394    178.9     98.8    299.6

  HPBL (m):
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    < 200                         10779    176.5    101.8    280.3
    200-500                       14670    186.6    110.1    296.4
    500-1000                      17409    199.2    125.8    281.4
    1000-2000                      9461    204.2    128.0    283.5
    > 2000                          527    121.1     62.3    190.0

  Pressure (mb):
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    < 1005                        47669    197.1    121.2    285.1
    1005-1013                      3447    151.3    100.7    289.2
    1013-1020                      1254    130.8     77.9    239.2
    > 1020                          476    103.4     76.8    191.3

  PWAT (mm):
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    < 10                           1834    160.6     96.8    265.0
    10-20                         15041    193.8    125.1    280.7
    20-30                         17788    218.8    129.9    295.6
    30-40                         13999    173.9    106.5    289.6
    > 40                           4184    155.2     84.5    256.1

  UTC Hour:
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    00-02 UTC                      4171    181.8    114.6    296.0
    03-05 UTC                       950    210.0    126.3    308.2
    06-08 UTC                       192    196.3     72.6    273.9
    09-11 UTC                      1271    171.4     83.9    278.4
    12-14 UTC                      9545    184.7    108.2    297.0
    15-17 UTC                     12591    203.1    112.2    285.2
    18-20 UTC                     13812    196.2    119.7    274.0
    21-23 UTC                     10314    188.4    129.5    281.4

  Solar Hour:
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    00-02 Solar (night)              71    218.9     75.3    274.0
    03-05 Solar (pre-dawn)         1675    183.2     83.6    273.7
    06-08 Solar (dawn)            10002    190.7    108.2    302.5
    09-11 Solar (morning)         14145    191.8    110.1    276.4
    12-14 Solar (midday)          13559    201.7    127.5    276.4
    15-17 Solar (afternoon)        9705    181.2    126.0    283.0
    18-20 Solar (evening)          3077    188.2    116.0    313.1
    21-23 Solar (night)             612    187.9    121.6    284.6

  Month:
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    Jan                               0        -        -        -
    Feb                               0        -        -        -
    Mar                               0        -        -        -
    Apr                               1    825.0    825.0    825.0
    May                               3    682.0    662.5    696.8
    Jun                               1    772.0    772.0    772.0
    Jul                               4    974.5    813.5   1098.0
    Aug                           26813    195.8    113.4    296.4
    Sep                           26024    188.4    116.2    274.0
    Oct                               0        -        -        -
    Nov                               0        -        -        -
    Dec                               0        -        -        -

  Terrain Verdict:
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    CLEAR                           407     71.5     30.9    142.1
    FRESNEL_PARTIAL                 284     38.6     37.9     96.1
    BLOCKED                       52155    194.6    116.9    285.8

24 GHz (n=3621):

  Refractivity Gradient:
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    < -500 (strong)                   0        -        -        -
    -500 to -300 (moderate)          15    189.0    158.4    196.7
    -300 to -200 (enhanced)          87    109.4     72.6    203.4
    -200 to -100 (mild)            1291     98.8     56.0    136.5
    -100 to 0 (normal)             2189     87.7     41.9    130.0
    > 0                               0        -        -        -

  Temperature (°C):
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    < 0                               2    259.0    259.0    259.0
    0-10                             32     93.1     61.9     93.1
    10-20                          1212    104.0     60.4    139.8
    20-30                          2142     86.5     41.9    131.2
    > 30                            233     47.0     24.4    129.8

  HPBL (m):
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    < 200                           775     85.3     47.8    134.6
    200-500                        1164    102.8     43.7    146.1
    500-1000                       1114     98.8     50.2    135.9
    1000-2000                       500     83.7     47.5    128.0
    > 2000                           68     27.9     15.9     47.7

  Pressure (mb):
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    < 1005                         3289     94.3     44.7    136.0
    1005-1013                       136    110.1     99.0    169.3
    1013-1020                       109     48.7     13.0     88.0
    > 1020                           87     65.4     17.1     89.6

  PWAT (mm):
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    < 10                            206    126.1     72.1    139.2
    10-20                          1031    126.1     65.5    171.8
    20-30                           803     98.0     52.4    142.0
    30-40                          1211     80.8     37.7    110.1
    > 40                            370     44.7     28.1     88.3

  UTC Hour:
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    00-02 UTC                       368    112.3     75.0    159.8
    03-05 UTC                        56    156.8     96.2    200.5
    06-08 UTC                        24    185.3     72.6    272.1
    09-11 UTC                       134     57.3     42.6     65.5
    12-14 UTC                       795     77.3     40.8    110.1
    15-17 UTC                       805     93.9     40.8    129.4
    18-20 UTC                       802     98.4     51.0    139.2
    21-23 UTC                       637    100.4     44.7    147.3

  Solar Hour:
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    00-02 Solar (night)               2    114.1     72.9    155.4
    03-05 Solar (pre-dawn)          159     57.3     41.9     65.9
    06-08 Solar (dawn)              705     72.5     37.1    108.2
    09-11 Solar (morning)           943     96.5     42.3    131.5
    12-14 Solar (midday)            810     99.9     61.8    131.9
    15-17 Solar (afternoon)         659    107.1     44.9    162.1
    18-20 Solar (evening)           302    104.8     53.9    159.6
    21-23 Solar (night)              41    140.4     76.9    204.2

  Month:
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    Jan                               0        -        -        -
    Feb                               2    259.0    259.0    259.0
    Mar                               0        -        -        -
    Apr                               1    220.0    220.0    220.0
    May                               2    242.0    224.0    260.0
    Jun                               0        -        -        -
    Jul                               0        -        -        -
    Aug                            1932     76.9     39.5    126.0
    Sep                            1684    104.3     61.9    142.0
    Oct                               0        -        -        -
    Nov                               0        -        -        -
    Dec                               0        -        -        -

  Terrain Verdict:
    Bin                               n   median      p25      p75
    ------------------------------------------------------------
    CLEAR                            43     37.2      9.3     37.2
    FRESNEL_PARTIAL                  36     68.4     43.7     93.1
    BLOCKED                        3542     94.8     44.9    135.7

--------------------------------------------------------------------------------
INTERACTION EFFECTS (10 GHz)
--------------------------------------------------------------------------------

Dataset: 52846 QSOs at 10 GHz

1. Refractivity Gradient × Time of Day
   (strong gradient = avg < -100, weak = avg >= -100)

   Time Block               Gradient        n  median km
   ------------------------------------------------------
   00-05 UTC (night)        strong       3073      204.2
   00-05 UTC (night)        weak         2044      167.8
   06-11 UTC (morning)      strong        700      185.6
   06-11 UTC (morning)      weak          763      155.6
   12-17 UTC (afternoon)    strong       9004      189.8
   12-17 UTC (afternoon)    weak        12873      197.5
   18-23 UTC (evening)      strong       8680      189.3
   18-23 UTC (evening)      weak        15210      194.6

2. HPBL × Season
   (shallow = avg < 500m, mid = 500-999m, deep = avg >= 1000m)

   Season                 HPBL            n  median km
   ----------------------------------------------------
   Winter (Dec-Feb)       shallow         0          -
   Winter (Dec-Feb)       mid             0          -
   Winter (Dec-Feb)       deep            0          -
   Spring (Mar-May)       shallow         0          -
   Spring (Mar-May)       mid             3      711.6
   Spring (Mar-May)       deep            1      643.0
   Summer (Jun-Aug)       shallow     12909      179.9
   Summer (Jun-Aug)       mid          9221      206.9
   Summer (Jun-Aug)       deep         4688      231.0
   Fall (Sep-Nov)         shallow     12540      184.1
   Fall (Sep-Nov)         mid          8185      194.3
   Fall (Sep-Nov)         deep         5299      187.6

3. Ducting Detection vs Distance

   Ducting YES: n=7979, median=189.0 km, p25=124.9 km, p75=296.4 km, max=986.2 km
   Ducting NO : n=44867, median=192.4 km, p25=112.4 km, p75=281.4 km, max=1264.5 km


================================================================================
Analysis complete: 2026-04-01 14:00:36.820405Z
