WSPR Rocks SQL Query
An example SQL query to extract WSPR Rocks data. The “chosen_band” is the integer frequency band in MHz, and “callsign” is the amateur radio callsign of interest. This example query retrieves up to 100 entreis of data for the callsign “W1AW” on the 7 MHz band, looking at data from 12 to 14 years ago.
with( 'W1AW' as callsign, 7 as chosen_band )
select
rx_sign,
count(rx_sign) as uniqs,
any(rx_loc) as rx_loc,
any(rx_lat) as rx_lat,
any(rx_lon) as rx_lon,
round(avg(distance)) as avg_k,
avg(power) as avg_dBm,
round(avg(snr)) as avg_snr,
round(avg_k / avg_dBm) as avg_kpw,
round(avg_kpw / abs(plus(avg_snr,35.5)/35.5)) as avg_spotQ
from wspr.rx
where
time > subtractYears(now(), 14)
and time < subtractYears(now(), 12)
and tx_sign = callsign
and band = chosen_band
group by
rx_sign
order by
uniqs desc
limit 100