Datalogging to an SQLite Database

[Dygear]

I started this in the https://github.com/robotastic/trunk-recorder/pull/482 where I had mentioned that I'm saving data for each recorded call into an SQLite database. Currently, I'm using the upload script to make a running version of this file as call are captured by the system. I think this is actually incredibly useful to have an SQLite database attached if for no other reason than running statistical analysis of the system over a much longer period of time. I was thinking that adding a config item for saving data to an sqlite database would be useful.

@rosecitytransit Thanks for the PR! I agree having some method for aggregating activity would be helpful. Instead of baking ing a single specific method for aggregating, I would like to have a more flexible option. I can see this creeping into having to add in support for hourly logs, and sending logs to splunk.

Something like a SQLite database that sits along side this might be a good idea. I'm currently saving all of the file information into an SQLite database to keep track of everything. It generally makes the query time for a page load much, much smaller.

DROP TABLE IF EXISTS sites;
DROP TABLE IF EXISTS p25;
DROP TABLE IF EXISTS p25_talkgroups;
DROP TABLE IF EXISTS p25_units;
DROP TABLE IF EXISTS audio;
DROP TABLE IF EXISTS audio_sources;
DROP TABLE IF EXISTS audio_frequencies;

CREATE TABLE sites (
    siteId INTEGER PRIMARY KEY,
    lat REAL,
    lng REAL,
    name TEXT,
    location TEXT
);
INSERT INTO sites (lat, lng, name, location) VALUES (40.909090, -73.115849, 'SBUH', '101 Nicolls Rd,
Stony Brook NY 11794');
INSERT INTO sites (lat, lng, name, location) VALUES (40.726321, -73.554070, 'NUMC', '2201 Hempstead Turnpike,
East Meadow, NY 11554');

CREATE TABLE p25 (
    p25Id INTEGER PRIMARY KEY,
    WACN TEXT NOT NULL,
    systemId TEXT NOT NULL,
    nameShort TEXT NOT NULL,
    nameLong TEXT NOT NULL
);
INSERT INTO p25 (WACN, systemId, nameShort, nameLong) VALUES ('BEE00','3CE','SCPD','Suffolk County Police Department');
INSERT INTO p25 (WACN, systemId, nameShort, nameLong) VALUES ('BEE00','1AE','NCPD','Nassau County Police Department');

CREATE TABLE p25_talkgroups (
    talkgroupId INTEGER PRIMARY KEY, 
    p25Id INTEGER NOT NULL,
    tgId INTEGER NOT NULL,
    mode TEXT NOT NULL,
    alphaTag TEXT NOT NULL,
    description TEXT NOT NULL,
    tag TEXT NOT NULL,
    'group' TEXT NOT NULL,
    priority INTEGER,
    FOREIGN KEY(p25Id) REFERENCES p25(p25Id)
);

INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (1, 3021, 'D', 'AVMEDVAC', 'Aviation Medevac', 'EMS', 'EMS', 1);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (1, 3111, 'D', 'SCPD 1st', '1st Pct', 'Police', 'SCPD', 4);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (1, 3211, 'D', 'SCPD 2nd', '2nd Pct', 'Police', 'SCPD', 4);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (1, 3311, 'D', 'SCPD 3rd', '3rd Pct', 'Police', 'SCPD', 4);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (1, 3411, 'D', 'SCPD 4th', '4th Pct', 'Police', 'SCPD', 4);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (1, 3511, 'D', 'SCPD 5th', '5th Pct', 'Police', 'SCPD', 4);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (1, 3611, 'D', 'SCPD 6th', '6th Pct', 'Police', 'SCPD', 4);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (1, 3711, 'D', 'SCPD 7th', '7th Pct', 'Police', 'SCPD', 4);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (1, 149, 'D', 'MEDCOM-C', 'MEDCOM Central', 'Ambulance', 'EMS', 2);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (1, 151, 'D', 'MEDCOM-E', 'MEDCOM East', 'Ambulance', 'EMS', 2);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (1, 153, 'D', 'MEDCOM-W', 'MEDCOM West', 'Ambulance', 'EMS', 2);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (1, 155, 'D', 'Hospital-N', 'Hospital North', 'Hospital', 'EMS', 3);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (1, 157, 'D', 'Hospital-S', 'Hospital South', 'Hospital', 'EMS', 3);

INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 1101, 'E', '1/7 Main', 'NCPD 1st & 7th Main', 'Police', 'NCPD', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 1201, 'E', '2/8 Main', 'NCPD 2nd & 8th Main', 'Police', 'NCPD', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 1301, 'E', '3/6 Main', 'NCPD 3rd & 6th Main', 'Police', 'NCPD', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 1401, 'E', '4/5 Main', 'NCPD 4th & 5th Main', 'Police', 'NCPD', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 1030, 'E', 'NCPS', 'NC Public Safety', 'Safety', 'Safety', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 2011, 'E', 'NCPD Detectives', 'NCPD Detectives', 'Police', 'NCPD', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 4001, 'D', 'NCFD Batt 1', 'NC Fire 1st Battalion', 'Fire-Tac', 'NCFD', 2);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 4002, 'D', 'NCFD Batt 2', 'NC Fire 2nd Battalion', 'Fire-Tac', 'NCFD', 2);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 4003, 'D', 'NCFD Batt 3', 'NC Fire 3rd Battalion', 'Fire-Tac', 'NCFD', 2);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 4004, 'D', 'NCFD Batt 4', 'NC Fire 4th Battalion', 'Fire-Tac', 'NCFD', 2);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 4005, 'D', 'NCFD Batt 5', 'NC Fire 5th Battalion', 'Fire-Tac', 'NCFD', 2);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 4006, 'D', 'NCFD Batt 6', 'NC Fire 6th Battalion', 'Fire-Tac', 'NCFD', 2);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 4007, 'D', 'NCFD Batt 7', 'NC Fire 7th Battalion', 'Fire-Tac', 'NCFD', 2);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 4008, 'D', 'NCFD Batt 8', 'NC Fire 8th Battalion', 'Fire-Tac', 'NCFD', 2);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 4009, 'D', 'NCFD Batt 9', 'NC Fire 9th Battalion', 'Fire-Tac', 'NCFD', 2);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 4021, 'D', 'FireMarshal', 'NC Fire Marshals', 'Fire-Tac', 'NCFD', 2);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 4099, 'D', 'NCFD InterOp', 'NC Fire InterOp', 'Fire-Talk', 'NCFD', 2);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 6001, 'D', 'MEDCOM 1', 'MEDCOM 1', 'Hospital', 'EMS', 1);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 6002, 'D', 'MEDCOM 2', 'MEDCOM 2', 'Hospital', 'EMS', 1);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 6003, 'D', 'MEDCOM 3', 'MEDCOM 3', 'Hospital', 'EMS', 1);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 6500, 'D', 'VEMS Main', 'Volunteer EMS Main', 'Ambulance', 'EMS', 1);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 20300, 'E', 'Floral Park PD', 'Floral Park Police', 'Police', 'Police', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 20500, 'E', 'Garden City PD', 'Garden City Police', 'Police', 'Police', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 20700, 'E', 'GNE/Kens PD', 'Great Neck Estates / Kensington PD', 'Police', 'Police', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 20800, 'E', 'Hempstead PD', 'Hempstead Police', 'Police', 'Police', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 21000, 'E', 'Lake Success PD', 'Lake Success Police', 'Police', 'Police', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 21100, 'E', 'Long Beach PD', 'Long Beach Police', 'Police', 'Police', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 21200, 'E', 'Lynbrook PD', 'Lynbrook Police', 'Police', 'Police', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 21300, 'E', 'Malverne PD', 'Malverne Police', 'Police', 'Police', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 21500, 'E', 'Old Westbruy PD', 'Old Westbury Police', 'Police', 'Police', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 21611, 'E', 'Town of Oyster Bay', 'Town of Oyster Bay Police', 'Police', 'Police', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 21700, 'E', 'PortWash PD', 'Port Washington Police', 'Police', 'Police', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 21800, 'E', 'Rockville Center PD', 'Rockville Centre Police', 'Police', 'Police', 9);
INSERT INTO p25_talkgroups (p25Id, tgId, mode, alphaTag, description, tag, group, priority) VALUES (2, 21900, 'E', 'Sands Point PD', 'Sands Point Police', 'Police', 'Police', 9);

CREATE TABLE p25_units (
    unitId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    p25Id INTEGER NOT NULL,
    src INTEGER NOT NULL,
    alias TEXT NOT NULL,
    startTime DATETIME NOT NULL,
    endTime DATETIME,
    FOREIGN KEY(p25Id) REFERENCES p25(p25Id)
);

CREATE TABLE audio (
    audioId INTEGER PRIMARY KEY,
    siteId INTEGER,
    p25Id INTEGER,
    tgId INTEGER,
    freq INTEGER,
    timeStart DATETIME,
    timeStop DATETIME,
    emergency BOOL,
    fileAudio TEXT,
    fileJSON TEXT,
    fileSize INTEGER,
    FOREIGN KEY(p25Id) REFERENCES p25(p25Id)
);

CREATE TABLE audio_sources (
    sourceId INTEGER PRIMARY KEY,
    audioId INTEGER NOT NULL,
    src INTEGER,
    time DATETIME,
    pos REAL,
    emergency BOOL,
    signal_system TEXT,
    tag TEXT,
    FOREIGN KEY(audioId) REFERENCES audio(audioId)
);

CREATE TABLE audio_frequencies (
    frequencyId INTEGER PRIMARY KEY,
    audioId INTEGER NOT NULL,
    freq INTEGER,
    time DATETIME,
    pos REAL,
    len INTEGER,
    error_count INTEGER,
    spike_count INTEGER,
    FOREIGN KEY(audioId) REFERENCES audio(audioId)
);

I save each audio file into the audio table, and if there is sources and frequencies inside of the json file I save these out here as well with each item in that array getting a row inside the database. You just have to be sure to save the audio table entry first, because you need that insert ID (audioId from the audio table) for the audio_sources and the audio_frequencies tables as they are foreign keys there. With all of this information in hand, you can track units (or radio IDs) across calls and return when a unit is talking. This is super useful for me when I'm dispatching as I can quickly go back and see what a unit has said when / if I miss it.

But if you wanted to get all of the calls so far in the day for a page load, you could simply do ...

SELECT * FROM audio WHERE timeStart > strftime('%s', date('now') || ' ' || time('now', 'start of day'));

You can also count those rows for page setup and then keep a running tally on the page with a WebSocket event that +1 to the count each time a new audio clip comes in.

SELECT COUNT(freq) AS COUNT, freq FROM audio_frequencies GROUP BY freq;

This is good to see where you should be focusing your antenna band, or putting your best software defined radio. There is a fairly big spike in the number of calls handle by a small number of frequencies as you can see by this table. 852850000, 852925000 & 853125000. The data was collected from January 1st 2020 to July 1st 2020 and only really captures the talkgroups that I'm intrestred in. We could see with a more detailed look over the data that there might be an affity for a talk group to always capture near the same frequencies. This whole thing allows for a lot of intresting questions to be asked and hopefully answered.

 COUNT | FREQ
 20931 | 851162500
 31693 | 852425000
 32451 | 852675000
 26916 | 852737500
308739 | 852850000
308789 | 852925000
309184 | 853125000
 11118 | 853225000
   366 | 853250000
   378 | 853375000
   368 | 853525000

This raises some interesting questions.

So far my dataset is around 700,000 calls over that 6 month time table. There was a small bug in the parsing script that I made to take all of this data and about 24 hours into its code execution, it crashed because I think I was missing a json file and I didn't handle that case in the code. I'm going to restart it once I get to work for it to continue crunching on the data but I'll probably do it in day batches with commit to the database because there is simply so much data and I'm flogging my NVMe drive right now.

---

It would probably also be useful to have the recorder device saved as well, so we make a table for all of the recorders we've seen.

CREATE TABLE sdr_recorders (
    sdrId INTEGER PRIMARY KEY,
    vendorId INTEGER NOT NULL,
    productId INTEGER NOT NULL,
    manufacturer TEXT,
    product TEXT,
    serial TEXT
);

This allows us to profile a SDR over time, correlate the information with temprature, and freqency. Are we getting more spikes with with higher temps outside? What about when it rains? How about this recorder has had an increasinly higher rolling 24 hour avarge for spikes ... Maybe it's about to die and we need to replace it soon. All of these questions would be answered. All we actually need to do is save the sdrId and make it a forigen key inside of the audio_frequencies table -- Maybe also the audio table as well for conventinal transmissions but it would require more manual look at the audio file. But at least it's possible now!

[rosecitytransit]

(copied from comment on the PR)

How about having the information passed on as an extra parameter (or parameters?) to the uploadScript? This way, you could do whatever you want with the data, from appending it to a log file to loading it into a database. As a plus, you could make sure the data is written once audio encoding is complete, and avoid cases of entries being consumed before the file is ready (the Web page won't link to it if it's not there at the moment).

As I said in my original comment, if I were creating the project, I'd have all data writing be external, and the default uploadScript be something like (<encode $1> && <write $2 ($3, $4, etc?) to daily log or JSON> && <curl -header="$2(,$3,$4,etc)" $1 uploadServer>) || <write to error log>

[robotastic]

This is great @Dygear - there is so much interesting data you can pull from being able to do this higher level analysis. I am fully on board with making it easier to direct all this metadata into good data stores.

[Dygear]

I moved over MimoSDR to use an SQLite database. I made a little script to convert the JSON files into a single database for me. It should be noted that I ended up writing about 1TB of data to my drive because of page write size on my computer, even tho the SQLite database it produced was only 800MB, I wrote to the same file about 8 million times with this script because it needs the index from the audio table to fill out the rest of the information in the other tables.

MimoSDR.db (800MB SQLite Database) January 1st 2020 - June 3rd 2021 ~ 4 Million Audio Records

I had a good idea from @tannewt of using the BTRFS file system for the flat json files as it implements compression. As my accidental production server for this right now as a Raspberry Pi 4 8GB is getting co-lo'ed at End Office in Boston MA, I think the best way to handle this is to add 2x 512GB SanDisk Ulta Fit into the USB3 ports and Linux Software RAID that into a 1TB drive then make that the BTRFS partition as /mnt/audio. That way I don't have to worry about trashing the Samsung Pro Endurance 128GB SD Card that's running the Web Server. That should maintain viability of the system even on very cheap hardware.

[WindyCitySDR]

Mark,

On Monday, June 28, 2021, Mark Tomlin @.***> wrote:

I moved over MimoSDR to use an SQLite database. I made a little script to convert the JSON files into a single database for me. https://gist.github.com/Dygear/2bc9fb54855f45b61a782b56cec5fbb2 It should be noted that I ended up writing about 1TB of data to my drive because of page write size on my computer, even tho the SQLite database it produced was only 800MB, I wrote to the same file about 8 million times with this script because it needs the index from the audio table to fill out the rest of the information in the other tables.

Waaaaay cool!

I had a good idea from @tannewt https://github.com/tannewt of using the BTRFS file system for the flat json files as it implements compression. As my accidental production server for this right now as a Raspberry Pi 4 8GB is getting co-lo'ed at End Office in Boston MA, I think the best way to handle this is to add 2x 512GB SanDisk Ulta Fit into the USB3 ports and Linux Software RAID that into a 1TB drive then make that the BTRFS partition as /mnt/audio. That way I don't have to worry about trashing the Samsung Pro Endurance 128GB SD Card that's running the Web Server. That should maintain viability of the system even on very cheap hardware.

Keep up the great work!

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/robotastic/trunk-recorder/issues/483#issuecomment-869995296, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABGHQJCB7O6NOUIAZYZU2RTTVDIQXANCNFSM45U7V5YA .

[JoeGilkey]

This could be handled as a plugin. You could load the talkgroups from SQLite using the setup_system or setupsystems methods, and store the data using the signal, call or unit_ methods. The Talkgroups class should be expanded to have add method that takes all the parameters (or a Talkgroup class instance).

[Dygear]

Sorry -- Apparently I fell of the end of the world around November.

Setting up Trunk-Recorder by using the SQLite database would be super cool. It would be better if I can tell it that it's This site and there for configures itself to only listen / track audio that is assigned to it. I am going to make an SQLite plugin for Trunk-Recorder. I think it's generally a good thing to have. The stats have been invaluable to me.

@robotastic Can you please assign this issue to me?