Archive | SOTA Data RSS for this section

SOTA Blogs

SOTA Blogs

Have updated ParksnPeaks.org to show only active blogs. Active means an update within the last month.

Here is my current list of all the blogs that has been feed into ParksnPeaks.

Any additions, updates or corrections please let me know. 

VK1ATP Paul http://blog.pwarren.id.au
VK1DA Andrew http://vk1da.net/blog
VK1EM Mark- http://gentoo.psyberhome.net/vk1em
VK1FAAS Simon- http://vk1faas.wordpress.com
VK1DI Ian http://vk1di.blogspot.com.au
VK1NAM Andrew http://vk1nam.wordpress.com
 
VK2DAG Matt- http://vk2dag.com/blog
VK2FMIA Doug Andy http://vk2fmia.com/
VK2IB Bernard http://vk2ib.wordpress.com/
VK2IO Gerard http://vk2io.wordpress.com/
VK2QR Rob http://vk2qr.wordpress.com/
VK2JI Ed http://vk2ji.wordpress.com
VK2TWR Rod http://vk2twr.wordpress.com/
VK2YW John http://vk2yw.wordpress.com/
 
VK3ARR Andrew http://vk3arr.wordpress.com
VK3BQ Andrew http://www.vk3bq.com/
VK3BYD Warren http://vk3byd.wordpress.com
VK3CAT Tony http://vk3cat.wordpress.com
VK3HRA Allen https://vk3hra.wordpress.com
VK3IL David http://vk3il.net/
VK3KAB Kevin http://vk3kab.wordpress.com
VK3MRG Marshell http://vk3mrg.weebly.com/index.html
VK3PF Peter http://vk3pf.wordpress.com
VK3WAM Wayne http://waynemerry.wordpress.com
VK3YY Glenn http://vk3yy.wordpress.com
VK3ZPF Peter http://vk3zpf.com
 
VK4MDX David http://vk4mdx.wordpress.com/
 
VK5AKH Andy http://vk5akh.wordpress.com
VK5BJE John http://vk5bje.wordpress.com
VK5FO Bob http://vk5fo.com
VK5HCF Colin http://vk5hcf.wordpress.com
VK5LA Andy http://vk5la.wordpress.com
VK5NQP David http://vk5nqpblog.wordpress.com
VK5PAS Paul http://vk5pas.wordpress.com
 
VK6FMTG Merton http://mertonglass.wordpress.com/
VK6MAC Anthony http://www.epicsota.com/
 
VK7IS Ian https://vk7is.wordpress.com/
VK7TW Justin http://vk7tw.wordpress.com/

 

Allen

VK3HRA

MacLoggerDX

Add SOTAWatch to the DX Cluster list.

Andy (MM0FMF) supports a DXCLuster for SOTA spots. Topic on the SOTA DXCluster  – http://www.sotawatch.org/reflector.php?topic=8696#79117

You need to add this to the default DX Cluster nodes as loaded into the pop up list in the DXClusters button. This list is maintained in /Documents/MLDX_Logs/data_files/dxclusters.dat.

To customize this list, edit the dxclusters.dat file. Make a backup first just in case.  Ensure you save as a text file

Each list item has the following format: [Call sign][URL, with an option port number preceded by a colon][description]

Example file contents;

MM0FMF elgur.dtdns.net:7300 SOTA Cluster

K5NA k5na.net CW Skimmer

ZL2AQY-10 zl2aqy.ath.cx:9000 Auckland, New Zealand

Once saved, restart MacLoggerDX then you can enable auto tune to follow the spots as they come through. 

 

Script to create files for import into SOTAWatch.

I have a script used to gather data from MacLoggerDX database into files suitable to import into SOTAWatch. Currently uses month-year to select the data – “(strftime(“%m-%Y”,qso_start,’unixepoch’) = ’04-2014’);”. Any pointers as how to limit to the last two days or so would be appreciated.

Example Script for gathering data for April 2014. Files named getSOTA-042014.txt in this case;

.headers ON

.mode column

.output stdout

select “v2″,”VK3HRA/p”,trim(substr(my_sota,1,10)),strftime(“%d/%m/%Y”,qso_start,’unixepoch’),strftime( “%H:%M”,qso_start,’unixepoch’),rx_frequency,mode,call,trim(substr(sota,1,10)),’s’ ||rst_sent ||’ r’||rst_received from qso_table_v005 where (my_sota <>”” OR sota <> “”) and (strftime(“%m-%Y”,qso_start,’unixepoch’) = ’04-2014′);

.mode csv

.headers OFF

.output 04-2014-activate.csv

select “VK3HRA/p”,strftime(“%d/%m/%Y”,qso_start,’unixepoch’),strftime( “%H:%M”,qso_start,’unixepoch’),trim(my_sota),rx_frequency,mode,call,’s’ ||rst_sent ||’ r’||rst_received from qso_table_v005 where my_sota <>”” and strftime(“%m-%Y”,qso_start,’unixepoch’) = ’04-2014′;

 

.mode csv

.headers OFF

.output 04-2014-chase.csv

select “v2″,”VK3HRA/p”,trim(substr(my_sota,1,10)),strftime(“%d/%m/%Y”,qso_start,’unixepoch’),strftime( “%H:%M”,qso_start,’unixepoch’),rx_frequency,mode,call,trim(substr(sota,1,10)),’s’ ||rst_sent ||’ r’||rst_received from qso_table_v005 where (my_sota <>”” OR sota <> “”) and (strftime(“%m-%Y”,qso_start,’unixepoch’) = ’04-2014′);

.output stdout 

 

To use, go to the directory containing the files,

cd Documents/MLDX_Logs/

sqlite3 MacLoggerDX.sql

 

Execute the script,

sqlite> .read getSOTA-042014.txt

sqlite> .exit

You will be left with two files,

allen:MLDX_Logs allen$ ls -al 04-2014*

-rw-r–r–  1 allen  staff   7705 25 Apr 18:30 04-2014-activate.csv

-rw-r–r–  1 allen  staff  11392 25 Apr 18:30 04-2014-chase.csv

May need to open with Excel and sort by date and time if you have added out of sequence as SOTAWatch import not very happy when the dates are not sorted.

ADIF SOTA and v2 Import

 

Update 112013

Logging software now supports my_sota and sota fields.

 

#SQL to update my_sota infer form comments field.

UPDATE qso_table_v005 SET my_sota=trim(substr(comments,5,11)) WHERE (comments LIKE ‘SOTA%’);

 

#SQL to create files for import into SotaWatch

.mode csv

.headers OFF

.output 11—2013-activate.csv
select “VK3HRA/p”,strftime(“%d/%m/%Y”,qso_start,’unixepoch’),strftime( “%H:%M”,qso_start,’unixepoch’),trim(my_sota),rx_frequency,mode,call,’s’ ||rst_sent ||’ r’||rst_received from qso_table_v005 where my_sota <>”” and strftime(“%m-%Y”,qso_start,’unixepoch’) = ’11-2013′;

.mode csv
.headers OFF
.output 11—2013_chase.csv
select “v2″,”VK3HRA/p”,trim(substr(my_sota,1,10)),strftime(“%d/%m/%Y”,qso_start,’unixepoch’),strftime( “%H:%M”,qso_start,’unixepoch’),rx_frequency,mode,call,trim(substr(sota,1,10)),’s’ ||rst_sent ||’ r’||rst_received from qso_table_v005 where (my_sota <>”” OR sota <> “”) and (strftime(“%m-%Y”,qso_start,’unixepoch’) = ’11-2013′);
.output stdout

 

Creates two files. One for chaser and and for activation.

 

 

 

ADIF – Amateur Data Interchange Format – is an open standard for exchange of data between amateur radio software, services and websites.

ADIF specifications are prepared by the ADIF Developers Group.

Current Released VersionVersion 3.0.4 

ADIF spec contains SOTA information:

MY_SOTA_REF the logging station’s International SOTA Reference.

SOTA_REF the contacted station’s International SOTA Reference.

Both are of type “SOTARef” which is defined as:

a sequence of Characters representing an International SOTA Reference. The sequence comprises:

an ITU prefix
if applicable, a SOTA subdivision
a / Character
a SOTA Reference Number

Examples:
W2/WE-003
G/LD-003


The latest beta of MacLoggerDX is supports  a new field named SOTA. http://dogparksoftware.com/MacLoggerDX.html (Don, VE3VRW)

The intent is to create files in a format to upload-file to SotaWatch

SOTA-Style comma-separated CSV-file:

v2,mycall,myreference,date,time,band,mode,contaced_station,worked_reference,notes

date-format: dd/mm/yyyy or dd/mm/yy
time-format: hh:mm or hhmm

Recording SOTA activation summit in Comments fields and using SOTA field to store worked summit.

Still need to work out how to extract only data from selected date so still using month.

The following script extracts three files from MacLoggerDX v4 database for import to Sota Database,

.headers ON
.mode column
.output stdout
select strftime(“%d/%m/%Y” ,qso_start,’unixepoch’), strftime( “%H:%M”,qso_start,’unixepoch’),sota,trim(substr(comments,5,11)),rx_frequency,mode,call,first_name,’s’ ||rst_sent ||’ r’||rst_received from qso_table_v004 where (comments LIKE ‘SOTA%’ or sota <>””) and strftime(“%m-%Y”,qso_start,’unixepoch’) = ’09-2013′;

.mode csv
.headers OFF
.output activate-output.csv
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,’s’ ||rst_sent ||’ r’||rst_received from qso_table_v004 where comments LIKE ‘SOTA%’ and strftime(“%m-%Y”,qso_start,’unixepoch’) = ’09-2013′;

.output chase-output.csv
select “VK3HRA”,strftime(“%d/%m/%Y”,qso_start,’unixepoch’),strftime( “%H:%M”,qso_start,’unixepoch’),trim(substr(sota,1,10)),rx_frequency,mode,call,’s’ ||rst_sent ||’ r’||rst_received from qso_table_v004 where sota <> “” and strftime(“%m-%Y”,qso_start,’unixepoch’) = ’09-2013′;

.output S2S-output.csv
select “v2″,”VK3HRA/p”,trim(substr(comments,5,11)),strftime(“%d/%m/%Y”,qso_start,’unixepoch’),strftime( “%H:%M”,qso_start,’unixepoch’),rx_frequency,mode,call,trim(substr(sota,1,10)),’s’ ||rst_sent ||’ r’||rst_received from qso_table_v004 where comments LIKE ‘SOTA%’ and sota <> “” and strftime(“%m-%Y”,qso_start,’unixepoch’) = ’09-2013′;

.output stdout

 

ADIF supporting SOTA

 

ADIF Version 3.0.4 release will have support for SOTA,

ADIF Version 3.0.4 – http://adif.org.uk/proposed/304/index.htm

SOTA SOTARef the contacted station’s International SOTA Reference.
MY_SOTA SOTARef the logging station’s International SOTA Reference.

SOTARef a sequence of Characters representing an International SOTA Reference.  The sequence comprises:
• an ITU prefix
• if applicable, a SOTA subdivision
• a / Character
• a SOTA Reference Number
Examples:
W2/WE-003
G/LD-003

No Award Enumeration of SOTA

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.