Open hcarter333 opened 9 months ago
Voila:
select rm_rnb_history_pres.rx_lng as longitude , rm_rnb_history_pres.rx_lat as latitude, rm_rnb_history_pres.timestamp, rm_rnb_history_pres.dB, rm_rnb_history_pres.Spotter, rm_rnb_history_pres.QSL_link, photo_path.path, photo_path.uuid, json_object(
'image',
'/-/media/thumbnail/' || uuid,
'title', Spotter || ' de KD0FNR',
'description', 'rx_rst: ' || dB || ' timestamp: ' || rm_rnb_history_pres.timestamp
) as popup
from rm_rnb_history_pres LEFT JOIN photo_path ON rm_rnb_history_pres.QSL_link = photo_path.uuid where db > 100 and rm_rnb_history_pres.timestamp > '2023-10-17' limit 101
This uses the csv construction:
csvs-to-sqlite -dt timestamp rm_rnb_history_pres.csv photo_path.csv rm_toucans_23_10_19_c.db
and the datasette command line
python -m datasette -m metadata.json rm_toucans_23_10_19_c.db
Map with all the QSL cards that are known:
select rm_rnb_history_pres.rx_lng as longitude , rm_rnb_history_pres.rx_lat as latitude, rm_rnb_history_pres.timestamp, rm_rnb_history_pres.dB, rm_rnb_history_pres.Spotter, rm_rnb_history_pres.QSL_link, photo_path.path, photo_path.uuid, json_object(
'image',
'/-/media/thumbnail/' || uuid,
'title', Spotter || ' de KD0FNR',
'description', 'rx_rst: ' || dB || ' timestamp: ' || rm_rnb_history_pres.timestamp
) as popup
from rm_rnb_history_pres LEFT JOIN photo_path ON rm_rnb_history_pres.QSL_link = photo_path.uuid where (db > 100 or db = 0)
At the moment, it looks like this might work if I added a popup column (at least in the SQL query) that added json with the path to the QSL card image.