Frequency Database and RDS Log - SQLite comparison

What you need to run the software
User avatar
G0OFE
Posts: 435
Joined: Thu Aug 06, 2020 7:33 am
Location: Sunny Sunny Bournemouth IO90BR
Contact:

Frequency Database and RDS Log - SQLite comparison

#1

Unread post by G0OFE »

Not sure exactly where to put this, but as it's an SQLite specific question rather than Console, I've put it here.

I have two SQLite files:

1. The RDS log from a recent opening... generated by playing back an IQ recording. In this case a massive Poland (and nearby countries) opening of 11th July.
2. Console's Frequency Database.

The RDS log contains a lot of erroneous PI codes, that are not used by any station. I want to quickly weed these out, or alternatively, highlight the entries where a frequency/pi Code exists.

Does anyone know if there is a way of opening both databases and running a query where:

"does the PIText entry in the RDS database exist in the RDS_PI column in Frequency Database? if so, highlight the cell in RDS Log to show it does"

or perhaps

"Does the frequency/PI Code combination on a line in the RDS Log match with a Frequency/RDS_PI combination in the Frequency Database? If so, highlight the line in RDS Log"

I am using DB Browser for SQLite, I cant find such a utility in there, but I know sod all about SQlite.

Thanks for any pointers anyone can give me.
Jim, Bournemouth IO90BR
Image

pauldbnut
Posts: 115
Joined: Wed Aug 19, 2020 10:59 am
Location: Dinosaur Country (IoW)

Re: Frequency Database and RDS Log - SQLite comparison

#2

Unread post by pauldbnut »

Hello, Jim, this is not your answer but (seeing as no-one has replied) here is a suggestion.
Microsoft Access is famous for its ability to link multiple (and disparate) external data files via ODBC. These may then be queried (more-or-less) as local tables using user-defined table joins.
I've used this many times in the past, to great effect.
As for SQLlite, there are at least two driver options - by DevArt (expensive) and by Chris Werner (free).
I just tried the latter and failed to open SDRC Scheduler because of some driver incompatibility (MS Office 365 on Win 7).
With apologies, I'm not in a position to devote further time at present.
Regards, Paul.
Paul White (Grumpy Old Git)

pauldbnut
Posts: 115
Joined: Wed Aug 19, 2020 10:59 am
Location: Dinosaur Country (IoW)

Re: Frequency Database and RDS Log - SQLite comparison

#3

Unread post by pauldbnut »

P.S. @Simon
I'm way out-of-date with server-side databases but have some reservations about SQLlite which seems to be rather behind MyQSL in some respects?
For sure you have good reason to be using SQLlite even if the other would work just as well (or better?) - I'm thinking of stored procedures especially.
Just one point, though: why not put scheduler and RDS (plus no doubt other future possibilities like band plan) in the same DB?
That way, Jim and others would have an easier task with queries such as in this topic.
Paul White (Grumpy Old Git)

User avatar
G0OFE
Posts: 435
Joined: Thu Aug 06, 2020 7:33 am
Location: Sunny Sunny Bournemouth IO90BR
Contact:

Re: Frequency Database and RDS Log - SQLite comparison

#4

Unread post by G0OFE »

Paul,

Thanks.

Did you mean "scheduler" or "frequency database"?

I have actually now found a reasonable solution.. exported the frequency database to csv, and made a master spreadsheet.

I then export the rds log to a csv, load it into a spreadsheet, frig around with the columns a bit to match the order of those in the frequency database, highlight all the entries then copy and paste into a copy of the master streadsheet, and sort by RDS PI code then frequency.

I can then scroll down and check through the highlighted entries that have a matching frequency/PI code match which will be right next to it.

It's a bit of friggery, but it works.
Jim, Bournemouth IO90BR
Image

User avatar
Simon G4ELI
Posts: 2134
Joined: Thu Aug 06, 2020 7:27 am
Location: Mawnan Smith
Contact:

Re: Frequency Database and RDS Log - SQLite comparison

#5

Unread post by Simon G4ELI »

pauldbnut wrote: Sat Aug 07, 2021 7:53 pm P.S. @Simon
I'm way out-of-date with server-side databases but have some reservations about SQLlite which seems to be rather behind MyQSL in some respects?
For sure you have good reason to be using SQLlite even if the other would work just as well (or better?) - I'm thinking of stored procedures especially.
Just one point, though: why not put scheduler and RDS (plus no doubt other future possibilities like band plan) in the same DB?
That way, Jim and others would have an easier task with queries such as in this topic.
SQLite is excellent, ideal for what's needed here. No reason whatsoever to use MySQL.
Simon Brown :shock:
www.sdr-radio.com

Do not send me direct e-mail, thank-you!

DaveB
Posts: 78
Joined: Sat Sep 05, 2020 10:32 am

Re: Frequency Database and RDS Log - SQLite comparison

#6

Unread post by DaveB »

G0OFE wrote: Sat Aug 07, 2021 8:37 pm Paul,

Thanks.

Did you mean "scheduler" or "frequency database"?

I have actually now found a reasonable solution.. exported the frequency database to csv, and made a master spreadsheet.

I then export the rds log to a csv, load it into a spreadsheet, frig around with the columns a bit to match the order of those in the frequency database, highlight all the entries then copy and paste into a copy of the master streadsheet, and sort by RDS PI code then frequency.

I can then scroll down and check through the highlighted entries that have a matching frequency/PI code match which will be right next to it.

It's a bit of friggery, but it works.
Hi Jim,
The solution I use is to Export the frequency database to csv, open it and save as and ODS file in OpenOffice. Next I create a pivot table from the database for just the PI codes.

As a once only I make a spreadsheet with two tabs. Tab 1 is PI code lookup and I copy/paste special the PI code column from the pivot table in the frequency database. Tab 2 I name as RDS log. The first eight columns are to copy/paste the data from anRDSdatabase exported from SDRConsole. Column 9 contains a formula to lookup the PI code in column 6 against the pi code list in Tab1. (Tab3 is a summary of MHz+pi codes)

Then 'magically' if there is a matching pi code it will display in column 9 - if not #N/A appears. BUT that doesn't mean it is automatically a false code - there could be four reasons it is valid 1. you need to update your frequency database 2. FMList hasn't been updated 3. it is a regional pi code (not downloaded by FMscan userlist) 4. the station is more than 2700 km away.

The simplest way to show this is to include a link to an actual finished spreadsheet log which is in OpenOffice format

https://app.box.com/s/qwveb33o1arqj0ma6l52s0of4gi3jpvz

If you want to use it - all you have to do is delete the RDS log data - and Save as eg FreqDatabaseBlank the PI code list is from 14 Aug 2021 and imported via SDR console. I reckon the PI code list nees updating at least monthly

False pi codes are a bit of an issue - but no different to RDSSpy. I am not using DX mode btw. What I am finding is that PI codes showing 4 counts are false, and aren't reproducible when playing back with single VFO, but whether that is because it was a false trigger or it was valid but detection was on the threshold is a good question.

User avatar
G0OFE
Posts: 435
Joined: Thu Aug 06, 2020 7:33 am
Location: Sunny Sunny Bournemouth IO90BR
Contact:

Re: Frequency Database and RDS Log - SQLite comparison

#7

Unread post by G0OFE »

Thanks Dave,

I'll have a closer look at that. Need to learn about pivot tables!

Yes, a lot of false decodes but I think that is the price to pay for having a sensitive RDS decoder, working at the limit's of what is possible.
During the off-season, I'll run a complete scan on a file I've already done, but with DX Mode off, and see how many genuine decodes get missed.


"BUT that doesn't mean it is automatically a false code - there could be four reasons it is valid 1. you need to update your frequency database 2. FMList hasn't been updated 3. it is a regional pi code (not downloaded by FMscan userlist) 4. the station is more than 2700 km away."


1. I do, as frequently as I can
2. True, I'll go back and listen to it too see if I can gain any info
3. True. Any pi counts of 4 or more, I'll dig a bit deeper.
4. I got round this limitation with some friggery: downloaded FMList database based on several locations to cover areas further away than 2700km, then imported them into Console database. Only caveat is the distance/bearings will obviously be wrong, but the locator/grid tells me exactly where they are. So a 42E4 on 88.8 will return the Israeli stations there, distance and bearing shown will be wrong but the KM72 grid tells me exactly where they are. It's a cludge, but it works.

The resulting database can be exported and then imported into CSVUserlistBrowser.. I found I can recalculate distance/bearing for the more distant stations in there.
Jim, Bournemouth IO90BR
Image

User avatar
G0OFE
Posts: 435
Joined: Thu Aug 06, 2020 7:33 am
Location: Sunny Sunny Bournemouth IO90BR
Contact:

Re: Frequency Database and RDS Log - SQLite comparison

#8

Unread post by G0OFE »

So.. I've been frigging around a bit with this and have a working solution. Taking Dave B's idea on board and moving it a step further, I have come up with this:

Taking the Frequency Database and exporting it to an OpenOffice spreadsheet, I used the CATCON function to produce a combination of Freq/PI in a single cell, for example "88.5C202", in a new column. I then used this column to produce a pivot table of valid Frequency/PI combinations. This goes on a second sheet in the database file.
Then I use a third sheet into which I place the RDS Log from a Console session. Within that sheet, I again use CATCON to create the logged Freq/PI combined in a new column.

Once that is done, I can perform the same lookup function as per Dave's file, except instead of just the PI code, I am looking up the PI/frequency combination. If that combination does not exist (i.e. it's either a false decode or unlisted) then I get a #N/A. I then have the choice to dig deeper, or simply accept it's a falsie.

(Once the file is set up for the first time, it's a case of copy/paste the RDS Log of the next session into the first few columns of the third sheet, and OpenOffice/Excel will do the rest. Keeping the database up to date is a good idea, then one just needs to re-run the pivot table on the 2nd sheet after updating.)

The only thing is that one does not know if there is more than one transmitter on that frequency purely from this method, but I guess there are ways to automate that with further pivot tables and lookups.. but the experienced DXer will either know (BBC network or RAI network for example) or will find out if logging it in FMList. I can live with that, to be honest, as all the "hard labour" has been done up to that point!

So, again, it's a bit of friggery, especially to initially set it up, but far quicker then the previous method I use.

Thanks to DaveB for his help and advice.
Jim, Bournemouth IO90BR
Image

DaveB
Posts: 78
Joined: Sat Sep 05, 2020 10:32 am

Re: Frequency Database and RDS Log - SQLite comparison

#9

Unread post by DaveB »

Concatenating the frequency and pi code is something I'm going to have to explore. As an aside - this is where when sharing ideas there are differences between OpenOffice and LibreOffice, and indeed Excel. CATCON in OpenOffice is CONCATENATE.

Taking it one step further - I think that Concatenating Freq+PI+ITU columns is likely to give very few duplicates as it is not often I come across two different countries with the same pi code on the same frequency. So I will give that a try, but I've got to work out converting the frequency in Hz to MHz to do the matching.

User avatar
G0OFE
Posts: 435
Joined: Thu Aug 06, 2020 7:33 am
Location: Sunny Sunny Bournemouth IO90BR
Contact:

Re: Frequency Database and RDS Log - SQLite comparison

#10

Unread post by G0OFE »

DaveB wrote: Fri Aug 20, 2021 10:25 am Concatenating the frequency and pi code is something I'm going to have to explore. As an aside - this is where when sharing ideas there are differences between OpenOffice and LibreOffice, and indeed Excel. CATCON in OpenOffice is CONCATENATE.

Taking it one step further - I think that Concatenating Freq+PI+ITU columns is likely to give very few duplicates as it is not often I come across two different countries with the same pi code on the same frequency. So I will give that a try, but I've got to work out converting the frequency in Hz to MHz to do the matching.
Need a new column with a formula - e.g A2/1000000 - it's what I did.

I did of course mean CONCAT, not CATCON. But yes, there will be odd differences.
Jim, Bournemouth IO90BR
Image

Post Reply