73 lines
2.8 KiB
Elixir
73 lines
2.8 KiB
Elixir
defmodule Microwaveprop.Pskr.SpotHourly do
|
|
@moduledoc """
|
|
Hourly aggregate of PSK Reporter spots between two Maidenhead
|
|
grid locators on a single band. Locator precision is preserved
|
|
from the source spot (4/6/8/10 chars), so two spotters in
|
|
different subsquares of the same field generate distinct rows
|
|
even though they share an `EM12` prefix.
|
|
|
|
A single QSO often produces 50+ reception reports (one per
|
|
monitoring station that decoded it). Storing each report verbatim
|
|
would dwarf the rest of the database — instead `Pskr.Aggregator`
|
|
collapses every spot inside `(hour_utc, band, sender_grid,
|
|
receiver_grid)` into one row, retaining `spot_count`, SNR
|
|
envelope, and the set of modes observed. Downstream weather
|
|
correlation cares about *did propagation occur in this cell during
|
|
this hour*, not about each individual reporter — so the lossy
|
|
aggregation is the desired shape, not just a space optimization.
|
|
|
|
`midpoint_lat/lon` is the great-circle midpoint of the path,
|
|
pre-computed at insert time so HRRR / IEMRE grid lookups are a
|
|
cheap `WHERE midpoint_lat BETWEEN …` rather than a per-row
|
|
recomputation.
|
|
"""
|
|
use Ecto.Schema
|
|
|
|
import Ecto.Changeset
|
|
|
|
@primary_key {:id, :binary_id, autogenerate: true}
|
|
@foreign_key_type :binary_id
|
|
|
|
schema "pskr_spots_hourly" do
|
|
field :hour_utc, :utc_datetime
|
|
field :band, :string
|
|
field :sender_grid, :string
|
|
field :receiver_grid, :string
|
|
field :sender_country, :integer
|
|
field :receiver_country, :integer
|
|
field :sender_lat, :float
|
|
field :sender_lon, :float
|
|
field :receiver_lat, :float
|
|
field :receiver_lon, :float
|
|
field :midpoint_lat, :float
|
|
field :midpoint_lon, :float
|
|
field :distance_km, :float
|
|
field :spot_count, :integer, default: 0
|
|
field :max_snr_db, :integer
|
|
field :min_snr_db, :integer
|
|
field :modes, {:array, :string}, default: []
|
|
field :sender_callsigns, {:array, :string}, default: []
|
|
field :receiver_callsigns, {:array, :string}, default: []
|
|
field :first_spot_at, :utc_datetime
|
|
field :last_spot_at, :utc_datetime
|
|
|
|
timestamps(type: :utc_datetime)
|
|
end
|
|
|
|
@type t :: %__MODULE__{}
|
|
|
|
@cast_fields ~w(hour_utc band sender_grid receiver_grid sender_country receiver_country
|
|
sender_lat sender_lon receiver_lat receiver_lon midpoint_lat midpoint_lon
|
|
distance_km spot_count max_snr_db min_snr_db modes sender_callsigns
|
|
receiver_callsigns first_spot_at last_spot_at)a
|
|
|
|
@required_fields ~w(hour_utc band sender_grid receiver_grid spot_count)a
|
|
|
|
@spec changeset(t(), map()) :: Ecto.Changeset.t()
|
|
def changeset(record, attrs) do
|
|
record
|
|
|> cast(attrs, @cast_fields)
|
|
|> validate_required(@required_fields)
|
|
|> unique_constraint([:hour_utc, :band, :sender_grid, :receiver_grid])
|
|
end
|
|
end
|