Skip to main content

Things I Learned: The Datasette Haversine Plugin and the SKCC QRP Award

 The gang—the 12, 11, and 8 year-old kids whose dad I am—and I attended Simon Willison's Datasette office hours on Friday to talk about our ham radio Datasette projects and to start a bit of debug on a Datasette geocoding enrichment we're working on.

During our meeting, Simon asked what the longest QSO we'd made with the radio was, and mentioned that the datasette-haversine plugin can be used to calculate the distance between two locations on the Earth. Coincidentally, I'd already been looking into this in relation to the SKCC 1,000 Watts per Mile award. Consquently, I set out to apply the Haversine to our QSO logbook in Datasette to find the best set of QSOs to send in with our award application.

KO6BTY and I did an initial glance on Friday after speaking with Simon. Here's the query we used:

select rowid, id, tx_lng, tx_lat, rx_lng, rx_lat, haversine(tx_lat, tx_lng, rx_lat, rx_lng, 'mi') as haversine, floor((((haversine(tx_lat, tx_lng, rx_lat, rx_lng, 'mi'))/0.75)/500)-1) as endorsement, timestamp, dB, frequency, Spotter, Country, State, County, City, QSL_Sent, QSL_Rx, QSL_link, QSL_rx_link, tx_rst from rm_rnb_history_pres where (dB > 99) and (rx_lat > -90 and rx_lat < 90)  and (tx_lat > -90 and tx_lat < 90) and (timestamp > '2023-05-25') and (timestamp < '2023-06-20') and (haversine/0.75 > 1000) and (haversine/0.75 < 1500) order by haversine desc 

Where the distance returned by the haversine function is divided by 0.75 Watts (the power output by the Flying Rockmite) and then divided by 1000 miles.

For the award itself, we immediately get the following possibilities


But what I'd really like to know, given that there are different levels of the award:


is which QSOs over the summer when we were forced into straight key mode for a few weeks while out with the 750 mW Flying Rockmite qualify for each level of endorsment?

Using a slightly different query gets us the possible endorsements in 500 mile/Watt increments
select rowid, id, tx_lng, tx_lat, rx_lng, rx_lat, haversine(tx_lat, tx_lng, rx_lat, rx_lng, 'mi') as haversine, floor((((haversine(tx_lat, tx_lng, rx_lat, rx_lng, 'mi'))/0.75)/500)-1) as endorsement, timestamp, dB, frequency, Spotter, Country, State, County, City, QSL_Sent, QSL_Rx, QSL_link, QSL_rx_link, tx_rst from rm_rnb_history_pres where (dB > 99) and (rx_lat > -90 and rx_lat < 90)  and (tx_lat > -90 and tx_lat < 90) and (timestamp > '2023-05-25') and (timestamp < '2023-06-20') and (haversine/0.75 > 1500) order by haversine desc 

Second endorsement:
    

Third endorsement:

Fourth endorsement:


The next two projects will be to see if we can understand the kml mechanisms Google Earth uses for its new slide feature so we can have all the endorsement levels in a single kml map, and to load in the SKCC call vs. member number table to filter for SKCC members.










Labels:

Comments

Post a Comment

Please leave your comments on this topic:

Popular posts from this blog

The Valentine's Day Magnetic Monopole

There's an assymetry to the form of the two Maxwell's equations shown in picture 1.  While the divergence of the electric field is proportional to the electric charge density at a given point, the divergence of the magnetic field is equal to zero.  This is typically explained in the following way.  While we know that electrons, the fundamental electric charge carriers exist, evidence seems to indicate that magnetic monopoles, the particles that would carry magnetic 'charge', either don't exist, or, the energies required to create them are so high that they are exceedingly rare.  That doesn't stop us from looking for them though! Keeping with the theme of Fairbank[1] and his academic progeny over the semester break, today's post is about the discovery of a magnetic monopole candidate event by one of the Fairbank's graduate students, Blas Cabrera[2].  Cabrera was utilizing a loop type of magnetic monopole detector.  Its operation is in...
1 comment
Read more

Cool Math Tricks: Deriving the Divergence, (Del or Nabla) into New (Cylindrical) Coordinate Systems

Now available as a Kindle ebook for 99 cents ! Get a spiffy ebook, and fund more physics The following is a pretty lengthy procedure, but converting the divergence, (nabla, del) operator between coordinate systems comes up pretty often. While there are tables for converting between common coordinate systems , there seem to be fewer explanations of the procedure for deriving the conversion, so here goes! What do we actually want? To convert the Cartesian nabla to the nabla for another coordinate system, say… cylindrical coordinates. What we’ll need: 1. The Cartesian Nabla: 2. A set of equations relating the Cartesian coordinates to cylindrical coordinates: 3. A set of equations relating the Cartesian basis vectors to the basis vectors of the new coordinate system: How to do it: Use the chain rule for differentiation to convert the derivatives with respect to the Cartesian variables to derivatives with respect to the cylindrical variables. The chain ...
11 comments
Read more

More Cowbell! Record Production using Google Forms and Charts

First, the what : This article shows how to embed a new Google Form into any web page. To demonstrate ths, a chart and form that allow blog readers to control the recording levels of each instrument in Blue Oyster Cult's "(Don't Fear) The Reaper" is used. HTML code from the Google version of the form included on this page is shown and the parts that need to be modified are highlighted. Next, the why : Google recently released an e-mail form feature that allows users of Google Documents to create an e-mail a form that automatically places each user's input into an associated spreadsheet. As it turns out, with a little bit of work, the forms that are created by Google Docs can be embedded into any web page. Now, The Goods: Click on the instrument you want turned up, click the submit button and then refresh the page. Through the magic of Google Forms as soon as you click on submit and refresh this web page, the data chart will update immediately. Turn up the:...
Post a Comment
Read more