SOTA Data

Desire to be able to produce quick activation statistics and detect new chasers and activators for monthly summaries.

Previously I would have used VB6 to write a code around database. No not this time.  Want to stay on MAC so will build database in SQLlite3  as have the files on computer for MLDX and allready using SQL to gather the entries and present in a format suitable for CSV import ,

Current SQL used to extract data from MLDX for import into Sota Database,

.mode csv
.output getSOTA-022013-output.csv
.headers OFF
select “VK3HRA/p”,strftime(“%d/%m/%Y”,qso_start,’unixepoch’),strftime( “%H%M”,qso_start,’unixepoch’),trim(substr(comments,5,11)),rx_frequency,mode,call,first_name from qso_table_v003 where comments LIKE ‘SOTA%’ and strftime(“%m-%Y”,qso_start,’unixepoch’) = ’02-2013′;
.output stdout

Have been trying to narrow the data to < 2 days without joy. The SQL above gives me monthly data. Just delete the early entries and import. Anyway back to statistics. 

Will be using the exported data from SOTA database as source – http://moosedata.com/SOTA/AM_Reports/. Thanks Andy, MM0FMF

My SQL is bit rusty but Google will and  the SQLite website is well documented so a quick search directed me to the import command. The data comes as csv so let SQLit3 know that you intend to use a CSV. Create the tables to suit the data then import data into each table.

So need three tables Activator, Chaser and Summit. Will only show activator here as the table are simple string mapping to available data.

sqlite> CREATE TABLE activator (date STRING,activator STRING, summit_code STRING, summit_name STRING, QSO INTEGER);

sqlite> .mode csv

sqlite> .import VK3-activator.csv activator

Check to see it went it,

sqlite> select * from activator;”

Date “,” Activator “,” Summit Code “,”Summit Name “,” QSO “
1-Feb-13,VK3AFW/P,VK3/VC-025,”Mt Dandenong”,16
1-Feb-13,VK3HRA/P,VK3/VT-006,”Mt Saint Phillack”,12
1-Feb-13,VK3KAN,VK3/VC-025,”Mt Dandenong”,12

Bingo data ! 

SQL Commands,

Chasers who chased VK1 summits,

select * from chaser where summit_code LIKE ‘%VK1%’;

select count(distinct chaser) from chaser where summit_code LIKE ‘%VK1%’;

VK1 Activators,

select distinct activator from activator where date like ‘%Feb-13%’ and activator like ‘%VK1%’;

select count(*) from summit where Summit_code like ‘%VK1%’;

select count(distinct summit_code) from summit where Summit_code like ‘%VK1%’;

 

Notes:

Save the CSV file as “Windows Comma Separated” from excel to adresses the CRLF issues.

set .mode to CSV else import will fail with no error message.

Advertisements

Tags:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: