Β Here's the query I'm using for Burr Trail
============================================
select
Β tx_lng,
Β tx_lat,
Β rx_lng,
Β rx_lat,
Β rm_rnb_history_pres.timestamp,
Β rm_rnb_history_pres.dB,
Β rm_rnb_history_pres.Spotter,
Β haversine(tx_lat, tx_lng, rx_lat, rx_lng) as total_path,
Β gis_partial_path_lat(tx_lat, tx_lng, rx_lat, rx_lng, 200) as el_lat,
Β gis_partial_path_lng(tx_lat, tx_lng, rx_lat, rx_lng, 200) as el_lng,
Β id,
Β strftime('%Y%m%d', timestamp) as date,
Β strftime('%H%M', timestamp) as time,
Β 'US-4399' as park,
Β 'KD0FNR' as call,
Β 1770.829467773438 as elev_tx
from
Β rm_rnb_history_pres
where
Β dB > 100
Β and timestamp > '2024-05-27'
Β and timestamp < '2024-05-28'
order by
Β rm_rnb_history_pres.timestamp desc
=======================================================
At some point I'll add an ionosonde field using this map for each QSO. For the Boulder, CO ionosonde, the query will look like
=========================================================
select
Β tx_lng,
Β tx_lat,
Β rx_lng,
Β rx_lat,
Β rm_rnb_history_pres.timestamp,
Β rm_rnb_history_pres.dB,
Β rm_rnb_history_pres.Spotter,
Β haversine(tx_lat, tx_lng, rx_lat, rx_lng) as total_path,
Β gis_partial_path_lat(tx_lat, tx_lng, rx_lat, rx_lng, 200) as el_lat,
Β gis_partial_path_lng(tx_lat, tx_lng, rx_lat, rx_lng, 200) as el_lng,
Β id,
Β strftime('%Y%m%d', timestamp) as date,
Β strftime('%H%M', timestamp) as time,
Β 'US-4399' as park,
Β 'KD0FNR' as call,
Β 'BC840' as ionosonde,
Β 1770.829467773438 as elev_tx
from
Β rm_rnb_history_pres
where
Β dB > 100
Β and timestamp > '2024-05-27'
Β and timestamp < '2024-05-28'
order by
Β rm_rnb_history_pres.timestamp desc
limit 1
====================================================================
The extra ionosonde field works!!!Β
References
Ionosonde Data
This was made possible with data collected byΒ
Reinisch, B. W., and I. A. Galkin, Global ionospheric radio observatory (GIRO), Earth, Planets, and Space, 63, 377-381, doi:10.5047/eps.2011.03.001, 2011.
http://spase.info/SMWG/Observatory/GIRO