Things I Learned: Querying QSOs in Datasette Using the Leaflet Freedraw Plugin

Reading W6CSN's post on his recent POTA at Mt. Tamlpais got me thinking. How many QSOs had I made from the region, and had I sent out QSLs for all of them, and had I updated our log database to reflect those QSLs?

We have a mapable QSO log, so clearly, I could just zoom in and out on the map, but I wanted to do something that felt fancier. I remembered a Simon Willison blog posts about Datasette mentioning there was a plugin that allowed querying of databases based on regions drawn on a map. That's the thing I wanted.

Actually Doing the Thing

So, I wanted to be able to display a map, draw a region on the map, and then review the QSOs I'd made from that region. Armed with a map of QSOs, I also wanted to be able to click each QSO to see if I'd updated the QSL photo for the occasion in our QSO database.

After working though the issues I discuss below, I was delighted to be able to search for our QSOs from the Pantoll Campground Region like so:

Now that we've seen the end result, here are the issues I ran into getting everything set up.

Install the Plugin(s)

To do the things I'm about to talk about, you'll need Datasette and a few plugins.

Datasette

datasette-leaflet-freedraw

Spatialite

There was an issue using Datasette with Spatialite on Windows for me. I got around it though.

How DOES one get a geometry object?

When calling MakePoint be sure to cast your coordinates to floating point first, like so

MakePoint(cast(longitude as float), cast(latitude as float))

Otherwise, there's an error message for that.

Longitude and Latitude Work. Hierarchical Table References? Not so much.

In an early version of the SQL query, I'd tried to play nicely by using the full names of table fields. This did not work, (meaning 0 rows were returned for the query.) I do not know why yet. However, while working with Diaze—the 12 year-old here who's learning Python—I used the 'as' names of the columns instead, completely expecting them not to work either, but hoping for a more informative error message. Instead, the query just worked. So, this won't work for me:

select rm_rnb_history_pres.tx_lng as longitude , rm_rnb_history_pres.tx_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 
Intersects(GeomFromGeoJSON(:freedraw), MakePoint(cast(rm_rnb_history_pres.tx_lng as float), cast(rm_rnb_history_pres.tx_lat as float))) = 1  order by 
rm_rnb_history_pres.timestamp asc

but this totally did:

select rm_rnb_history_pres.tx_lng as longitude , rm_rnb_history_pres.tx_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 
Intersects(GeomFromGeoJSON(:freedraw), MakePoint(cast(longitude), cast(latitude))) = 1  order by 
rm_rnb_history_pres.timestamp asc

Do you have to have a column named geometry?

You do not have to have a column named geometry if you have a parameter named freedraw, that's enough to get the plugin to load the drawable map. The documentation mentions this, but I wasn't clear on the distinction at first, so I'd also added a column named geometry with the MakePoint call. I later removed the column and everything was fine.

You can only display images if you have the image query loaded in as metadata

On my first try, all the image links were broken. Then I remembered to load the metadata.json file from my project, and everything was just fine. My command line wound up being

python -m datasette rm_toucans_23_10_23.db --metadata metadata.json --load-extension=SpatiaLite

References:

W6CSN's POTA post that inspired all this

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:

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

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 concept very sim

Copasetic Flow

Search This Blog