More breakdowns of Statistics?

[KN6TPK]

Hey all, I found the Facts and Figures page on the Database, and it’s cool! But I was wondering: is it possible to find or create more detailed breakdowns anywhere? For example, I’m curious to slice and dice the stats by time (e.g. “within the last year”), and place ("summits in W6/NC-*), and find out which combinations of band + mode were used successfully (by number of QSOs).

To make it more concrete, I want to answer questions like:
In the last 12 months, for activations in W6/NC-*, what were the top 20 {band + mode} pairs with the most logged SOTA QSOs?
For S2S QSOs in the above query, what were the most common Associations and Regions of the other summit?

If there’s a web frontend where I can explore the QSO data, that’d be really cool! But I’m not afraid to write my own SQL queries, if that’s what it takes… In another thread I found a link to the SOTA database JSON API, which would get me part of the way there, but it doesn’t appear to expose QSOs, just alerts and spots. Are there any other data sources or sites I should try?

Thanks!

[MM0FMF]

There isn’t any more detailed breakdown available at present. We did have more information available in the past but it needs rewriting to work with the changes that happened with switch to SSO in 2020.

[VK3ARR]

To be honest, the issue is typically more around indexing. With many millions of QSOs, slicing and dicing on unindexed columns ties up the DB for a while and slows everything down as it does a table scan. Indeed, the facts and figures page already is the page we use to test DB snappiness.

If you detail what you want we can often write endpoints for it, but it goes in the queue with all the other work we have on.

[G4VFL]

My personal view having worked on databases in the past and the endless queries that users can dream up is to sell a snapshot dump of the database in .csv format.

The buyer would get the same as the download QSOs or Chases. It is up to the buyer to build their own database and run their own queries. I would guess the dump of the QSOs would be about 650MBytes, the chase dump may be similar. It would not be unreasonable to sell it on the SOTA shop to help fund the database. How to price it I am not so certain but £20 a shot would not seem too unreasonable. A snapshot once a month would be adequate for trend analysis.

This would leave a question on copyright and preventing copying. I would address that by putting a few random spelling errors in the .csv file to make the copies unique. I wouldn’t imagine that copying would be a great issue.

73 de

Andrew G4VFL

[MM0FMF]

Not a bad guess! There’s about 1.16GB of all data and 1.74GB of indexes right now

[KN6TPK]

@VK3ARR

the issue is typically more around indexing.

Ok, that makes sense. I’m spoiled by my job, where we’ve got the resources to run ridiculously large queries; out here in the real world I guess compute resources aren’t actually infinite

I spent a little time thinking about this yesterday and trying to make up a wishlist for what I’d want, and I also came to more or less the same conclusion as Andrew: I think want access to the raw data, and if I can’t run my queries on the real database, I think the next logical step is that I want a copy. I’d be happy to pay a nominal fee to get the initial database dump.

But then my next question will be how to keep my database copy up-to-date. I don’t mind if the update frequency is limited (once a week would be more than enough; even once a month would be plenty), but I wouldn’t want updates to be a manual process every time. I didn’t have time to really dig into this yet, but I do think I’d probably want some kind of API for database updates, perhaps like “fetch all QSOs uploaded in the last 24 hours” or “the 10000 most recently uploaded QSOs” or something along those lines.

For my use case, I was planning to upload my copy to something like BigQuery, since even the free tier is likely enough and would let me build whatever visualizations my heart desires. BQ supports all manner of ways of getting data in, but I haven’t spent the time yet to figure out what would make sense for this.

@G4VFL

preventing copying

My perspective: IMHO the point of charging a nominal fee when providing a copy of the DB is to reduce the number of times the DB admins have to do the manual work of making the copy. If so, then if users are providing copies to each other instead of making the DB admin do it, that sounds like a success to me. (Besides, even if you did discover someone making unauthorized copies of the database, would you really want to put up the effort to try and stop them? I know I wouldn’t…) But that’s up to you of course.

[VK3ARR]

@KN6TPK

But then my next question will be how to keep my database copy up-to-date. I don’t mind if the update frequency is limited (once a week would be more than enough; even once a month would be plenty), but I wouldn’t want updates to be a manual process every time. I didn’t have time to really dig into this yet, but I do think I’d probably want some kind of API for database updates, perhaps like “fetch all QSOs uploaded in the last 24 hours” or “the 10000 most recently uploaded QSOs” or something along those lines.

You also have to track all the records that might have been deleted, etc. By the end of it, you’ve implemented rsync again.

I think there may be GDPR issues with us giving out a full copy of the database, and I don’t feel like the tables that have GDPR information in them could be easily separated out, so I can’t see us providing that at present.

[KN6TPK]

I can definitely understand why you wouldn’t want to tangle with GDPR! In that case, I think I’m limited to only asking for particular queries I’d like to see added to the existing stats web page. I won’t go too crazy asking for stuff, but I still think there’s potential value to adding a view like "what are people using now (say, in the last month or year), as opposed to all time. And maybe also “what modes are popular on what bands”. As a new user trying to figure out what equipment and skills I would need to get started, those were the first questions on my mind, and I found it pretty hard to answer them any other way today.

And maybe one more lower-priority one while I’m at it: are there any specific frequencies that people are commonly using? I’m guessing that one probably only has value if it can also be separated by region somehow, which I imagine could be tricky.

[M0LEP]

@KN6TPK

specific frequencies

The database doesn’t have that information. It only worries about bands. You’d have to watch the spots, and they’re only visible for a short while…

[VK3ARR]

I could potentially run up a query against that data though. Let me think about it.

[DM1CM]

I remember I made up a page a few years ago for the SMP, which had these kinds of queries/requests. Nobody seemed to be interested in the page, so it was removed.

I’ve fired it up again, and it looks something like this:

It takes 2.5 seconds to gather the data from the SOTA db and build the page.

I can extend the page to include other results - maybe it will appeal to somebody out there?

Rob

[KN6TPK]

@DM1CM

maybe it will appeal to somebody out there?

That third chart looks almost exactly like what I was looking for! In fact, I like your stacked bar chart even more than what I actually asked for So yes, I for one like that page, even if I’m the only weirdo who likes looking at graphs…

[DM1CM]

@KN6TPK

I for one like that page, even if I’m the only weirdo who likes looking at graphs…

Well, the SMP also has some graphs in the activations page, which others have admitted to liking, so there are perhaps more such fellow “weirdos” out there than you may imagine…

[DM1CM]

Having been away for some time, I haven’t used the mapping pages for a while, and so it had slipped my memory that a stacked barchart just like the 3rd one in the image above already exists in the SMP.

If you go to the main mapping page, choose a region, and click the “Region summaries” tab (upper left), a table of bands usage and charts of

• QSOs per mode by band, and
• Distribution of QSOs by band

will be displayed. I think you have to choose a summit first from the list of region summits to force the program to do the deep search by explicitly “asking” for it. Or something like that…

If you, or others, like the type of results page as in the first image above, and would like to have it or something similar available, you’d have to talk with the MT about that.

HTH,
Rob

[KN6TPK]

@DM1CM

a stacked barchart just like the 3rd one in the image above already exists in the SMP

Thanks Rob, that’s really helpful! I think this will satisfy my curiosity for now. Thanks for helping me find it!