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