Cleaned and Augmented Logs (including RBN data) for CQ WW CW and SSB Contests, 2005 to 2020

 

Cleaned and augmented versions of the logs for the CQ WW CW and SSB contests are now available for the period 2005 to 2020.

Links to the cleaned and augmented logs may be followed here.

The cleaned logs are the result of processing the QSO: lines from the entrants' submitted Cabrillo files to ensure that all fields contain valid values and all the data match the format required in the rules. Any line containing illegal data in a field (for example, a zone number greater than 40, or a date/time stamp that is outside the contest period) has simply been removed. Also, only the QSO: lines are retained, so that each line in the file can be processed easily. All zones are rendered with two digits, so as to further simplify processing by scripts or programs.

The augmented logs contain the same information as the cleaned logs, but with the addition of some useful (derived) information on each line. In addition to the actual logs, two additional sources of information are used when appropriate:

1. AD1C has recently made accessible historical cty.dat and associated files. A copy of the cty,dat files is here. These allow us to use callsign-based multiplier lists as they would have existed at the time of each contest.
   
   
2. From 2009 onwards, the Reverse Beacon Network (RBN) has been available for the CW contests. This allows us to include the time since a station was last posted by the RBN (see below for details).

The information added to each line of the augmented logs comprises:

1. A sequence of four characters that are the same for each entry in a particular log:
•  a. letter "A" or "U" indicating "assisted" or "unassisted"
•  b. letter "Q", "L", "H" or "U", indicating respectively QRP, low power, high power or unknown power level
•  c. letter "S", "M", "C" or "U", indicating respectively a single-operator, multi-operator, checklog or unknown operator category [ the contest organisers have stated that checklogs are not made public, but in fact at least some of them from the early years have been, hence the need for the "C" category ]
•  d. character "1", "2", "+" or "U", indicating respectively that the number of transmitters is one, two, unlimited or unknown
2. A four-digit number representing the time if the contact in minutes measured from the start of the contest. (I realise that this can be calculated from the other information on the line, but it saves subsequent script-based processors of the file considerable time to have the number readily available in the file without having to calculate it for each QSO.)
3. Band
4. A set of fourteen flags, each -- apart from column k and column n -- encoded as T/F: 
   • a. QSO is confirmed by a log from the second party 
   • b. QSO is a reverse bust (i.e., the second party appears to have bust the call of the first party) 
   • c. QSO is an ordinary bust (i.e., the first party appears to have bust the call of the second party) 
   • d. the call of the second party is unique 
   • e. QSO appears to be a NIL 
   • f. QSO is with a station that did not send in a log, but who did make 20 or more QSOs in the contest 
   • g. QSO appears to be a country mult 
   • h. QSO appears to be a zone mult 
   • i. QSO is a zone bust (i.e., the received zone appears to be a bust)
   • j. QSO is a reverse zone bust (i.e. the second party appears to have bust the zone of the first party)
   • k. This entry has three possible values rather than just T/F:
     • T: QSO appears to be made during a run by the first party
     • F: QSO appears not to be made during a run by the first party
     • U: the run status is unknown because insufficient frequency information is available in the first party's log
   • l. QSO is a dupe
   • m. QSO is a dupe in the second party's log
   • n. RBN information (see below)
5. If the QSO is a reverse bust, the call logged by the second party; otherwise, the placeholder "-"
6. If the QSO is an ordinary bust, the correct call that should have been logged by the first party; otherwise, the placeholder "-"
7. If the QSO is a reverse zone bust, the zone logged by the second party; otherwise, the placeholder "-"
8.  If the QSO is an ordinary zone bust, the correct zone that should have been logged by the first party; otherwise, the placeholder "-" 

RBN Information

In the CW contests from 2009 onwards, the RBN was active, automatically spotting the frequency at which any station calling CQ was transmitting. To reflect possible use of RBN information, the augmented files now include a fourteenth flag. For the sake of uniformity, this column is present in all the augmented files, regardless of whether the RBN actually contributed useful information to a particular contest.

Each QSO has one of several characters in the fourteenth column of flags. These characters should be interpreted as follows:

'-'
  No useful RBN-derived information is available for this QSO.

'0'
  The worked station (i.e., the second call on the log line) appears to have begun to CQ on this frequency within (roughly) 60 seconds prior to the QSO.

'A' to 'Z'
  For the nth letter of the alphabet: the worked station appears to have been CQing on this frequency for (roughly) n minutes prior to the QSO.

'+'
  The worked station appears to have been CQing for more than 26 minutes on this frequency.

'<'
  Because the the RBN is distributed, and because each contest entrant station has its own clock, there is generally a skew between the reading of the clock of the station making the QSO and the timestamp from the RBN at which it believes a posting was made (indeed, it's unclear from the RBN's [lack of] documentation exactly how the timestamp on an individual RBN posting is to be interpreted). If the character '<' appears in the the RBN column, it indicates that the raw values of the clocks suggest that the QSO took place up to two minutes before the RBN reported the worked station commencing to CQ at this frequency. When this occurs, the most likely interpretation is that there is non-negligible skew between the two clocks, and the station was actually worked almost as soon as a CQ was posted by the RBN. This character also appears if the RBN erroneously posts the worked station as CQing at this frequency shortly after the QSO. But it might also mean that the entrant was simply lucky and found the CQing station just as it fired up on a new frequency.

Notes:

• The encoding of some of the flags requires subjective decisions to be made as to whether the flag should be true or false; consequently, and because CQ has yet to understand the importance of making their scoring code public, the value of a flag for a specific QSO line in some circumstances might not match the value that CQ would assign. (Also, CQ has more data available in the form of check logs, which are generally not made public.)
• I made no attempt to deduce or infer the run status of a QSO in the second party's log (if such exists), regardless of the status in the first party's log. This allows one cleanly to perform correct statistical analyses anent the number of QSOs made by running stations merely by excluding QSOs marked with a U in column k.
• No attempt is made to detect the case in which both participants of a QSO bust the other station's call. This is a problematic situation because of the relatively high probability of a false positive unless both stations accurately log the frequency as opposed to merely the band. (Also, on bands on which split-frequency QSOs are common, the absence of both transmit and receive frequency is a problem; I confess that I have never understood why Cabrillo was not designed to report both transmit and receive frequencies -- or even to define clearly which frequency is to be reported. I digress.) Because of the likelihood of false positives, it seems better, given the presumed rarity of double-bust QSOs, that no attempt be made to mark them.
• The entries for the zones in the case of zone or reverse zone busts are normalised to two-digit values.

Reverse Beacon Network Actvity: 2009-2020

I here show various plots of the G(15, 100) grid-based scatter metric, G(15, 100), for the Reverse Beacon Network (RBN), using data from the inception of the RBN up to the end of 2020.

As in the past I note that a reasonable a priori case can be made on the basis of propagation characteristics that somewhat different metrics in the G(Δ, n) series might be better representations of RBN coverage on some of the bands. However, rather than make this into a full-scale research project, I shall here simply continue to use the G(15, 100) metric on the basis that it seems "good enough" on all bands.

RBN Posting Stations as a Function of Time

We begin by looking simply at how the number of per-band posters to the RBN has varied since the RBN's inception. (NB Throughout this post, we ignore posters for which the location is not recorded by the RBN; plots for which the abscissa is time show one datum per month.)

First, a plot of the total number of posters as a function of time:

This can be more compactly represented, along with similar per-band data for 160m through 10m (excluding 60m):

G(15, 100) as a Function of Time

Turning now to the geographical distribution of the posting stations, we can display the mensal values of G(15, 100) in a similar manner:

These figures seem to make rather clearly the rather depressing point that, with the exception of 2020, which by definition was an exceptional year because of the prevalence of COVID-19, since early 2017 there has been no substantive or sustained increase in either the number or geographical distribution of the stations posting to the RBN. It will be interesting to see what happens in 2021.

G(15, 100) as a Function of the Number of Posters

Finally, we can combine the mensal values of G(15, 100) and the number of posters. Firstly, including all bands:

The summary plot for these data is slightly different, as the ordinate is multi-valued for some values of the abscissa. So, in this summary plot, we take the mean value of G(15, 100) in bins of width equivalent to ten posters, and plot rectangles in the equivalent colours:

All in all, a rather unhappy picture emerges, in which the RBN, after expanding and increasing coverage rather nicely for the better part of a decade, became essentially static in early 2017 and has effectively failed to expand numerically or in geographical coverage until he emergence of the pandemic in 2020. It will be interesting to see whether 2021 brings a return to stasis or whether the renewed slight improvement in coverage will continue.

2020 RBN Data

 

All the postings to the Reverse Beacon Network in 2020, along with the postings from prior years, are now available in this directory.

Some simple annual statistics for the period 2009 to 2020 follow (the 2009 numbers cover only part of that year, as the RBN was instantiated partway through that year).

Total posts:

2009:   5,007,040
2010:  25,116,810
2011:  49,705,539
2012:  71,584,195
2013:  92,875,152
2014:  108,862,505
2015:  116,385,762
2016:  111,027,068
2017:  117,973,111
2018:  131,930,432
2019:  135,558,461
2020:  173,655,453 

  Total posting stations:

2009: 151
2010: 265
2011: 320
2012: 420
2013: 473
2014: 515
2015: 511
2016: 590
2017: 625
2018: 550
2019: 583
2020: 616

 Total posted distinct callsigns:

2009: 143,724
2010: 266,189
2011: 271,133
2012: 308,010
2013: 353,952
2014: 398,293
2015: 433,197
2016: 375,613
2017: 356,461
2018: 361,058
2019: 337,246
2020: 369,580

Obviously, statistics that are considerably more comprehensive may be derived rather easily from the files in the directory.

Note that if you intend to use the databaseߴs reported signal strengths in an analysis, you should be sure that you understand the ramifications of what the RBN means by SNR.

Creating Local FCC Database From Version 5 Data

 

This is a followup to this post, required because the FCC has changed the format of their files from version 4 to version 5.

The contents of the eight FCC files are now (as described in the version 5 document):

Amateur
[AM] -- unchanged from version 4
1   Record Type [AM]            char(2)
2   Unique System Identifier    numeric(9,0)
3   ULS File Number             char(14)
4   EBF Number                  varchar(30)
5   Call Sign                   char(10)
6   Operator Class              char(1)
7   Group Code                  char(1)
8   Region Code                 tinyint
9   Trustee Call Sign           char(10)
10  Trustee Indicator           char(1)
11  Physician Certification     char(1)
12  VE Signature                char(1)
13  Systematic Call Sign Change char(1)
14  Vanity Call Sign Change     char(1)
15  Vanity Relationship         char(12)
16  Previous Call Sign          char(10)
17  Previous Operator Class     char(1)
18  Trustee Name                varchar(50)

Comments
[CO] -- unchanged from version 4
1   Record Type [CO]            char(2)
2   Unique System Identifier    numeric(9,0)
3   ULS File Number             char(14)
4   Call Sign                   char(10)
5   Comment Date                mm/dd/yyyy
6   Description                 varchar(255)
7   Status Code                 char(1)
8   Status Date                 mm/dd/yyyy

Entity
[EN]
1   Record Type [EN]                char(2)
2   Unique System Identifier        numeric(9,0)
3   ULS File Number                 char(14)
4   EBF Number                      varchar(30)
5   Call Sign                       char(10)
6   Entity Type                     char(2)
7   Licensee ID                     char(9)
8   Entity Name                     varchar(200)
9   First Name                      varchar(20)
10  MI                              char(1)
11  Last Name                       varchar(20)
12  Suffix                          char(3)
13  Phone                           char(10)
14  Fax                             char(10)
15  Email                           varchar(50)
16  Street Address                  varchar(60)
17  City                            varchar(20)
18  State                           char(2)
19  Zip Code                        char(9)
20  PO Box                          varchar(20)
21  Attention Line                  varchar(35)
22  SGIN                            char(3)
23  FCC Registration Number (FRN)   char(10)
24  Applicant Type Code             char(1)
25  Applicant Type Code Other       char(40)
26  Status Code                     char(1)
27  Status Date                     mm/dd/yyyy
28  3.7 GHz License Type            char(1)
29  Linked Unique System Identifier numeric(9,0)
30  Linked Call Sign                 char(10)

 

Application/License Header -- unchanged from version 4
[HD]
1   Record Type [HD]                            char(2)
2   Unique System Identifier                    numeric(9,0)
3   ULS File Number                             char(14)
4   EBF Number                                  varchar(30)
5   Call Sign                                   char(10)
6   License Status                              char(1)
7   Radio Service Code                          char(2)
8   Grant Date                                  mm/dd/yyyy
9   Expired Date                                mm/dd/yyyy
10  Cancellation Date                           mm/dd/yyyy
11  Eligibility Rule Num                        char(10)
12  Reserved                                    char(1)
13  Alien                                       char(1)
14  Alien Government                            char(1)
15  Alien Corporation                           char(1)
16  Alien Officer                               char(1)
17  Alien Control                               char(1)
18  Revoked                                     char(1)
19  Convicted                                   char(1)
20  Adjudged                                    char(1)
21  Reserved                                    char(1)
22  Common Carrier                              char(1)
23  Non Common Carrier                          char(1)
24  Private Comm                                char(1)
25  Fixed                                       char(1)
26  Mobile                                      char(1)
27  Radiolocation                               char(1)
28  Satellite                                   char(1)
29  Developmental or STA or Demonstration       char(1)
30  InterconnectedService                       char(1)
31  Certifier First Name                        varchar(20)
32  Certifier MI                                char(1)
33  Certifier Last Name                         varchar(20)
34  Certifier Suffix                            char(3)
35  Certifier Title                             char(40)
36  Female                                      char(1)
37  Black or African-American                   char(1)
38  Native American                             char(1)
39  Hawaiian                                    char(1)
40  Asian                                       char(1)
41  White                                       char(1)
42  Hispanic                                    char(1)
43  Effective Date                              mm/dd/yyyy
44  Last Action Date                            mm/dd/yyyy
45  Auction ID                                  integer
46  Broadcast Services - Regulatory Status      char(1)
47  Band Manager - Regulatory Status            char(1)
48  Broadcast Services - Type of Radio Service  char(1)
49  Alien Ruling                                char(1)
50  Licensee Name Change                        char(1)
51  Whitespace Indicator                        char(1)
52  Operation/Performance Requirement Choice    char(1)
53  Operation/Performance Requirement Answer    char(1)
54  Discontinuation of Service                  char(1)
55  Regulatory Compliance                       char(1)

History
[HS] -- unchanged from version 4
1   Record Type [HS]            char(2)
2   Unique System Identifier    numeric(9,0)
3   ULS File Number             char(14)
4   Call Sign                   char(10)
5   Log Date                    mm/dd/yyyy
6   Code                        char(6)

License Attachment
[LA] -- unchanged from version 4
1   Record Type [LA]            char(2)
2   Unique System Identifier    numeric(9,0)
3   Call Sign                   char(10)
4   Attachment Code             char(1)
5   Attachment Description      varchar(60)
6   Attachment Date             mm/dd/yyyy
7   Attachment File Name        varchar(60)
8   Action Performed            char(1)

Special Condition
[SC] -- unchanged from version 4
1   Record Type [SC]            char(2)
2   Unique System Identifier    numeric(9,0)
3   ULS File Number             char(14)
4   EBF Number                  varchar(30)
5   Call Sign                   char(10)
6   Special Condition Type      char(1)
7   Special Condition Code      int
8   Status Code                 char(1)
9   Status Date                 mm/dd/yyyy

License Free Form Special Condition
Position Data Element Definition
[SF] -- unchanged from version 4
1   Record Type [SF]                    char(2)
2   Unique System Identifier            numeric(9,0)
3   ULS File Number                     char(14)
4   EBF Number                          varchar(30)
5   Call Sign                           char(10)
6   License Free Form Type              char(1)
7   Unique License Free Form Identifier numeric(9,0)
8   Sequence Number                     integer
9   License Free Form Condition         varchar(255)
10  Status Code                         char(1)
11  Status Date                         mm/dd/yyyy

The following extract from the code that creates the output database maps these on a one-to-one basis to internal identifiers (the four new fields in the [HD] records don't seem to be important -- at least for now -- so there is no change in the output as compared to the processing of version 3 files, although the new fields are processed):

[AM]

RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
OPERATOR_CLASS,
GROUP_CODE,
REGION_CODE,
TRUSTEE_CALLSIGN,
TRUSTEE_INDICATOR,
PHYSICIAN_CERTIFICATION,
VE_SIGNATURE,
SYSTEMATIC_CALLSIGN_CHANGE,
VANITY_CALLSIGN_CHANGE,
VANITY_RELATIONSHIP,
PREVIOUS_CALLSIGN,
PREVIOUS_OPERATOR_CLASS,
TRUSTEE_NAME

 [CO]

RECORD_TYPE,
ID,
ULS_NUMBER,
CALLSIGN,
COMMENT_DATE,
DESCRIPTION,
STATUS_CODE,
STATUS_DATE

 [EN]

RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
ENTITY_TYPE,
LICENSE_ID,
ENTITY_NAME,
FIRST_NAME,
MIDDLE_INITIAL,
LAST_NAME,
SUFFIX,
PHONE,
FAX,
EMAIL,
STREET_ADDRESS,
CITY,
STATE,
ZIP_CODE,
PO_BOX,
ATTENTION_LINE,
SGIN,
FRN,
APPLICANT_TYPE_CODE,
APPLICANT_TYPE_CODE_OTHER,
STATUS_CODE,
LICENSE_TYPE_37,
LINKED_ID,
LINKED_CALLSIGN, 

  [HD]

RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
LICENSE_STATUS,
RADIO_SERVICE_CODE,
GRANT_DATE,
EXPIRED_DATE,
CANCELLATION_DATE,
ELIGIBILITY_RULE_NUM,
RESERVED_1,
ALIEN,
ALIEN_GOVERNMENT,
ALIEN_CORPORATION,
ALIEN_OFFICER,
ALIEN_CONTROL,
REVOKED,
CONVICTED,
ADJUDGED,
RESERVED_2,
COMMON_CARRIER,
NON_COMMON_CARRIER,
PRIVATE_COMM,
FIXED,
MOBILE,
RADIOLOCATION,
SATELLITE,
DEVELOPMENTAL_STA_DEMONSTRATION,
INTERCONNECTED_SERVICE,
CERTIFIER_FIRST_NAME,
CERTIFIER_MIDDLE_INITIAL,
CERTIFIER_LAST_NAME,
CERTIFIER_SUFFIX,
CERTIFIER_TITLE,
FEMALE,
BLACK_AFRICAN_AMERICAN,
NATIVE_AMERICAN,
HAWAIIAN,
ASIAN,
WHITE,
HISPANIC,
EFFECTIVE_DATE,
LAST_ACTION_DATE,
AUCTION_ID,
BROADCAST_SERVICES_REGULATORY_STATUS,
BAND_MANAGER_REGULATORY_STATUS,
BROADCAST_SERVICES_SERVICE_TYPE,
ALIEN_RULING,
LICENSEE_NAME_CHANGE,
WHITESPACE_INDICATOR

 [HS]

RECORD_TYPE,
ID,
ULS_NUMBER,
CALLSIGN,
LOG_DATE,
CODE

 [LA]

RECORD_TYPE,
ID,
CALLSIGN,
ATTACHMENT_CODE,
ATTACHMENT_DESCRIPTION,
ATTACHMENT_DATE,
ATTACHMENT_FILENAME,
ACTION_PERFORMED

 [SC]

RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
SPECIAL_CONDITION_TYPE,
SPECIAL_CONDITION_CODE,
STATUS_CODE,
STATUS_DATE

 [SF]

RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
LICENSE_FREEFORM_TYPE,
UNIQUE_LICENSE_FREEFORM_ID,
SEQUENCE_NUMBER,
LICENSE_FREEFORM_CONDITION,
STATUS_CODE,
STATUS_DATE

The 50 output fields selected from the above lists are (arranged in groups of ten for easy counting):

ID,
CALLSIGN,
OPERATOR_CLASS,
GROUP_CODE,
REGION_CODE,
TRUSTEE_CALLSIGN,
TRUSTEE_INDICATOR,
SYSTEMATIC_CALLSIGN_CHANGE,
VANITY_CALLSIGN_CHANGE,
VANITY_RELATIONSHIP,

PREVIOUS_CALLSIGN,
PREVIOUS_OPERATOR_CLASS,
TRUSTEE_NAME,
COMMENT_DATE,
DESCRIPTION,
CO_STATUS_CODE, (i.e., STATUS_CODE from [CO])
CO_STATUS_DATE, (i.e., STATUS_DATE from [CO])
ENTITY_NAME,
FIRST_NAME,
MIDDLE_INITIAL,

LAST_NAME,
SUFFIX,
PHONE,
FAX,
EMAIL,
STREET_ADDRESS,
CITY,
STATE,
ZIP_CODE,
PO_BOX,

ATTENTION_LINE,
FRN,
APPLICANT_TYPE_CODE,
APPLICANT_TYPE_CODE_OTHER,
EN_STATUS_CODE, (i.e., STATUS_CODE from [EN])
EN_STATUS_DATE, (i.e., STATUS_DATE from [EN])
LICENSE_STATUS,
RADIO_SERVICE_CODE,
GRANT_DATE,
EXPIRED_DATE,

CANCELLATION_DATE,
ELIGIBILITY_RULE_NUM,
REVOKED,
CONVICTED,
ADJUDGED,
EFFECTIVE_DATE,
LAST_ACTION_DATE,
LICENSEE_NAME_CHANGE,
LINKED_ID,
LINKED_CALLSIGN

The contents of these fields are based on the original equivalent entries in the original data files. The entries for the fields are subject to the following transformations before being written to the output file:

• The entry is converted to upper case;
• Any line feeds (yes, the FCC allows line feeds within a field) are converted to the four-character sequence: <LF>;
• Leading and trailing spaces are removed;
• If the field is a date, it is converted from FCC format (mm/dd/yyyy) to ISO 8601 extended format: YYYY-MM-DD.

The latest output file created in this manner (and its MD5 checksum) may be downloaded from this directory.

The full source code to generate the output file may be downloaded here.

To create the binary from the source code, go to the directory that contains the makefile and type:

make fcc-db

This should generate the executable program as: bin/fcc-db. The program may be executed from within the bin directory as:

fcc-db [directory]

where [directory] is the name of the directory that contains the input FCC AM.dat, CO.dat, EN.dat and HD.dat files. Those files should be processed and the output written to stdout.

For what it's worth, it takes somewhat less than 15 seconds for the program to execute to completion on my desktop computer if stdout is redirected to an output file.

Creating Local FCC Database From Version 4 Data

This is a followup to this post, required because the FCC has changed the format of their files from version 3 to version 4.

The contents of the eight FCC files are now (as described in the version 4 document):

Amateur
[AM] -- unchanged from version 3
1   Record Type [AM]            char(2)
2   Unique System Identifier    numeric(9,0)
3   ULS File Number             char(14)
4   EBF Number                  varchar(30)
5   Call Sign                   char(10)
6   Operator Class              char(1)
7   Group Code                  char(1)
8   Region Code                 tinyint
9   Trustee Call Sign           char(10)
10  Trustee Indicator           char(1)
11  Physician Certification     char(1)
12  VE Signature                char(1)
13  Systematic Call Sign Change char(1)
14  Vanity Call Sign Change     char(1)
15  Vanity Relationship         char(12)
16  Previous Call Sign          char(10)
17  Previous Operator Class     char(1)
18  Trustee Name                varchar(50)

Comments
[CO] -- unchanged from version 3
1   Record Type [CO]            char(2)
2   Unique System Identifier    numeric(9,0)
3   ULS File Number             char(14)
4   Call Sign                   char(10)
5   Comment Date                mm/dd/yyyy
6   Description                 varchar(255)
7   Status Code                 char(1)
8   Status Date                 mm/dd/yyyy

Entity
[EN] -- unchanged from version 3
1   Record Type [EN]                char(2)
2   Unique System Identifier        numeric(9,0)
3   ULS File Number                 char(14)
4   EBF Number                      varchar(30)
5   Call Sign                       char(10)
6   Entity Type                     char(2)
7   Licensee ID                     char(9)
8   Entity Name                     varchar(200)
9   First Name                      varchar(20)
10  MI                              char(1)
11  Last Name                       varchar(20)
12  Suffix                          char(3)
13  Phone                           char(10)
14  Fax                             char(10)
15  Email                           varchar(50)
16  Street Address                  varchar(60)
17  City                            varchar(20)
18  State                           char(2)
19  Zip Code                        char(9)
20  PO Box                          varchar(20)
21  Attention Line                  varchar(35)
22  SGIN                            char(3)
23  FCC Registration Number (FRN)   char(10)
24  Applicant Type Code             char(1)
25  Applicant Type Code Other       char(40)
26  Status Code                     char(1)
27  Status Date                     mm/dd/yyyy

 

Application/License Header
[HD]
1   Record Type [HD]                            char(2)
2   Unique System Identifier                    numeric(9,0)
3   ULS File Number                             char(14)
4   EBF Number                                  varchar(30)
5   Call Sign                                   char(10)
6   License Status                              char(1)
7   Radio Service Code                          char(2)
8   Grant Date                                  mm/dd/yyyy
9   Expired Date                                mm/dd/yyyy
10  Cancellation Date                           mm/dd/yyyy
11  Eligibility Rule Num                        char(10)
12  Reserved                                    char(1)
13  Alien                                       char(1)
14  Alien Government                            char(1)
15  Alien Corporation                           char(1)
16  Alien Officer                               char(1)
17  Alien Control                               char(1)
18  Revoked                                     char(1)
19  Convicted                                   char(1)
20  Adjudged                                    char(1)
21  Reserved                                    char(1)
22  Common Carrier                              char(1)
23  Non Common Carrier                          char(1)
24  Private Comm                                char(1)
25  Fixed                                       char(1)
26  Mobile                                      char(1)
27  Radiolocation                               char(1)
28  Satellite                                   char(1)
29  Developmental or STA or Demonstration       char(1)
30  InterconnectedService                       char(1)
31  Certifier First Name                        varchar(20)
32  Certifier MI                                char(1)
33  Certifier Last Name                         varchar(20)
34  Certifier Suffix                            char(3)
35  Certifier Title                             char(40)
36  Female                                      char(1)
37  Black or African-American                   char(1)
38  Native American                             char(1)
39  Hawaiian                                    char(1)
40  Asian                                       char(1)
41  White                                       char(1)
42  Hispanic                                    char(1)
43  Effective Date                              mm/dd/yyyy
44  Last Action Date                            mm/dd/yyyy
45  Auction ID                                  integer
46  Broadcast Services - Regulatory Status      char(1)
47  Band Manager - Regulatory Status            char(1)
48  Broadcast Services - Type of Radio Service  char(1)
49  Alien Ruling                                char(1)
50  Licensee Name Change                        char(1)
51  Whitespace Indicator                        char(1)
52  Operation/Performance Requirement Choice    char(1)
53  Operation/Performance Requirement Answer    char(1)
54  Discontinuation of Service                  char(1)
55  Regulatory Compliance                       char(1)

History
[HS] -- unchanged from version 3
1   Record Type [HS]            char(2)
2   Unique System Identifier    numeric(9,0)
3   ULS File Number             char(14)
4   Call Sign                   char(10)
5   Log Date                    mm/dd/yyyy
6   Code                        char(6)

License Attachment
[LA] -- unchanged from version 3
1   Record Type [LA]            char(2)
2   Unique System Identifier    numeric(9,0)
3   Call Sign                   char(10)
4   Attachment Code             char(1)
5   Attachment Description      varchar(60)
6   Attachment Date             mm/dd/yyyy
7   Attachment File Name        varchar(60)
8   Action Performed            char(1)

Special Condition
[SC] -- unchanged from version 3
1   Record Type [SC]            char(2)
2   Unique System Identifier    numeric(9,0)
3   ULS File Number             char(14)
4   EBF Number                  varchar(30)
5   Call Sign                   char(10)
6   Special Condition Type      char(1)
7   Special Condition Code      int
8   Status Code                 char(1)
9   Status Date                 mm/dd/yyyy

License Free Form Special Condition
Position Data Element Definition
[SF] -- unchanged from version 3
1   Record Type [SF]                    char(2)
2   Unique System Identifier            numeric(9,0)
3   ULS File Number                     char(14)
4   EBF Number                          varchar(30)
5   Call Sign                           char(10)
6   License Free Form Type              char(1)
7   Unique License Free Form Identifier numeric(9,0)
8   Sequence Number                     integer
9   License Free Form Condition         varchar(255)
10  Status Code                         char(1)
11  Status Date                         mm/dd/yyyy

The following extract from the code that creates the output database maps these on a one-to-one basis to internal identifiers (the four new fields in the [HD] records don't seem to be important -- at least for now -- so there is no change in the output as compared to the processing of version 3 files, although the new fields are processed):

[AM]

RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
OPERATOR_CLASS,
GROUP_CODE,
REGION_CODE,
TRUSTEE_CALLSIGN,
TRUSTEE_INDICATOR,
PHYSICIAN_CERTIFICATION,
VE_SIGNATURE,
SYSTEMATIC_CALLSIGN_CHANGE,
VANITY_CALLSIGN_CHANGE,
VANITY_RELATIONSHIP,
PREVIOUS_CALLSIGN,
PREVIOUS_OPERATOR_CLASS,
TRUSTEE_NAME

 [CO]

RECORD_TYPE,
ID,
ULS_NUMBER,
CALLSIGN,
COMMENT_DATE,
DESCRIPTION,
STATUS_CODE,
STATUS_DATE

 [EN]

RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
ENTITY_TYPE,
LICENSE_ID,
ENTITY_NAME,
FIRST_NAME,
MIDDLE_INITIAL,
LAST_NAME,
SUFFIX,
PHONE,
FAX,
EMAIL,
STREET_ADDRESS,
CITY,
STATE,
ZIP_CODE,
PO_BOX,
ATTENTION_LINE,
SGIN,
FRN,
APPLICANT_TYPE_CODE,
APPLICANT_TYPE_CODE_OTHER,
STATUS_CODE,
STATUS_DATE

 [HD]

RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
LICENSE_STATUS,
RADIO_SERVICE_CODE,
GRANT_DATE,
EXPIRED_DATE,
CANCELLATION_DATE,
ELIGIBILITY_RULE_NUM,
RESERVED_1,
ALIEN,
ALIEN_GOVERNMENT,
ALIEN_CORPORATION,
ALIEN_OFFICER,
ALIEN_CONTROL,
REVOKED,
CONVICTED,
ADJUDGED,
RESERVED_2,
COMMON_CARRIER,
NON_COMMON_CARRIER,
PRIVATE_COMM,
FIXED,
MOBILE,
RADIOLOCATION,
SATELLITE,
DEVELOPMENTAL_STA_DEMONSTRATION,
INTERCONNECTED_SERVICE,
CERTIFIER_FIRST_NAME,
CERTIFIER_MIDDLE_INITIAL,
CERTIFIER_LAST_NAME,
CERTIFIER_SUFFIX,
CERTIFIER_TITLE,
FEMALE,
BLACK_AFRICAN_AMERICAN,
NATIVE_AMERICAN,
HAWAIIAN,
ASIAN,
WHITE,
HISPANIC,
EFFECTIVE_DATE,
LAST_ACTION_DATE,
AUCTION_ID,
BROADCAST_SERVICES_REGULATORY_STATUS,
BAND_MANAGER_REGULATORY_STATUS,
BROADCAST_SERVICES_SERVICE_TYPE,
ALIEN_RULING,
LICENSEE_NAME_CHANGE,
WHITESPACE_INDICATOR

 [HS]

RECORD_TYPE,
ID,
ULS_NUMBER,
CALLSIGN,
LOG_DATE,
CODE

 [LA]

RECORD_TYPE,
ID,
CALLSIGN,
ATTACHMENT_CODE,
ATTACHMENT_DESCRIPTION,
ATTACHMENT_DATE,
ATTACHMENT_FILENAME,
ACTION_PERFORMED

 [SC]

RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
SPECIAL_CONDITION_TYPE,
SPECIAL_CONDITION_CODE,
STATUS_CODE,
STATUS_DATE

 [SF]

RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
LICENSE_FREEFORM_TYPE,
UNIQUE_LICENSE_FREEFORM_ID,
SEQUENCE_NUMBER,
LICENSE_FREEFORM_CONDITION,
STATUS_CODE,
STATUS_DATE

The 48 output fields selected from the above lists are (arranged in groups of ten for easy counting):

ID,
CALLSIGN,
OPERATOR_CLASS,
GROUP_CODE,
REGION_CODE,
TRUSTEE_CALLSIGN,
TRUSTEE_INDICATOR,
SYSTEMATIC_CALLSIGN_CHANGE,
VANITY_CALLSIGN_CHANGE,
VANITY_RELATIONSHIP,

PREVIOUS_CALLSIGN,
PREVIOUS_OPERATOR_CLASS,
TRUSTEE_NAME,
COMMENT_DATE,
DESCRIPTION,
CO_STATUS_CODE, (i.e., STATUS_CODE from [CO])
CO_STATUS_DATE, (i.e., STATUS_DATE from [CO])
ENTITY_NAME,
FIRST_NAME,
MIDDLE_INITIAL,

LAST_NAME,
SUFFIX,
PHONE,
FAX,
EMAIL,
STREET_ADDRESS,
CITY,
STATE,
ZIP_CODE,
PO_BOX,

ATTENTION_LINE,
FRN,
APPLICANT_TYPE_CODE,
APPLICANT_TYPE_CODE_OTHER,
EN_STATUS_CODE, (i.e., STATUS_CODE from [EN])
EN_STATUS_DATE, (i.e., STATUS_DATE from [EN])
LICENSE_STATUS,
RADIO_SERVICE_CODE,
GRANT_DATE,
EXPIRED_DATE,

CANCELLATION_DATE,
ELIGIBILITY_RULE_NUM,
REVOKED,
CONVICTED,
ADJUDGED,
EFFECTIVE_DATE,
LAST_ACTION_DATE,
LICENSEE_NAME_CHANGE

The contents of these fields are based on the original equivalent entries in the original data files. The entries for the fields are subject to the following transformations before being written to the output file:

• The entry is converted to upper case;
• Any line feeds (yes, the FCC allows line feeds within a field) are converted to the four-character sequence: <LF>;
• Leading and trailing spaces are removed;
• If the field is a date, it is converted from FCC format (mm/dd/yyyy) to ISO 8601 extended format: YYYY-MM-DD.

The latest output file created in this manner (and its MD5 checksum) may be downloaded from this directory.

The full source code to generate the output file may be downloaded here.

To create the binary from the source code, go to the directory that contains the makefile and type:

make fcc-db

This should generate the executable program as: bin/fcc-db. The program may be executed from within the bin directory as:

fcc-db [directory]

where [directory] is the name of the directory that contains the input FCC AM.dat, CO.dat, EN.dat and HD.dat files. Those files should be processed and the output written to stdout.

For what it's worth, it takes somewhat less than 15 seconds for the program to execute to completion on my desktop computer if stdout is redirected to an output file.

Running RigExpert AntScope2 on Debian Stable (buster)

RigExpert have instructions as to how to load their AntScope2 software on a Raspberry Pi.

As the Raspberry Pi runs a system based on debian, it shouldn't be too difficult to get the code running on a debian stable (buster) system.

[I apologise for some of the formatting in this post. blogger.com has foisted a predictably putrid new interface on me. It seems to have no consistent concept of what to do when one hits the ENTER key, nor how to paste text properly. Things that used to be a single click on a text button are now a process of figuring out which meaningless icon causes the menu I want to appear, and then selecting the correct text from the menu -- this is somehow supposed to be an improvement; Yet Another Reason to hate applications that don't run on the desktop, and are controlled by another entity -- they can resist the temptation to break things for users for only so long. To make matters even worse, they falsely claim that they provide a way to continue to use the old interface -- making that selection still forces one into the new atrocity. Yes, I'm ranting.]

1. Download and install the necessary packages.

sudo apt-get install qt5-default qt5-qmake libegl1-mesa libgles2-mesa libqt5serialport5-dev libusb-1.0.0-dev

The output I received (after wasting gobs of time trying to get it to format somewhat sensibly in the new interface, which scattered vast numbers of non-breaking spaces seemingly at random throughout the text):

[ZB:rbn] sudo apt-get install qt5-default qt5-qmake libegl1-mesa libgles2-mesa libqt5serialport5-dev libusb-1.0.0-dev
[sudo] password for n7dr:
Reading package lists... Done
Building dependency tree
Reading state information... Done
Note, selecting 'libusb-1.0-0-dev' for regex 'libusb-1.0.0-dev'
libegl1-mesa is already the newest version (18.3.6-2+deb10u1).
libegl1-mesa set to manually installed.
The following packages were automatically installed and are no longer required:
  linux-headers-4.19.0-6-amd64 linux-headers-4.19.0-6-common linux-headers-4.19.0-8-amd64 linux-headers-4.19.0-8-common linux-image-4.19.0-6-amd64 linux-image-4.19.0-8-amd64
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
  libqt5opengl5-dev libqt5serialport5 libusb-1.0-doc libvulkan-dev qt5-qmake-bin qtbase5-dev qtbase5-dev-tools
Suggested packages:
  default-libmysqlclient-dev firebird-dev libegl1-mesa-dev libpq-dev libsqlite3-dev unixodbc-dev
The following NEW packages will be installed:
  libgles2-mesa libqt5opengl5-dev libqt5serialport5 libqt5serialport5-dev libusb-1.0-0-dev libusb-1.0-doc libvulkan-dev qt5-default qt5-qmake qt5-qmake-bin qtbase5-dev qtbase5-dev-tools
0 upgraded, 12 newly installed, 0 to remove and 0 not upgraded.
Need to get 3,854 kB of archives.
After this operation, 29.9 MB of additional disk space will be used.
Do you want to continue? [Y/n]
Get:1 http://ftp.us.debian.org/debian buster/main amd64 libgles2-mesa amd64 18.3.6-2+deb10u1 [47.4 kB]
Get:2 http://ftp.us.debian.org/debian buster/main amd64 libvulkan-dev amd64 1.1.97-2 [390 kB]
Get:3 http://ftp.us.debian.org/debian buster/main amd64 qt5-qmake-bin amd64 5.11.3+dfsg1-1+deb10u3 [1,004 kB]
Get:4 http://ftp.us.debian.org/debian buster/main amd64 qt5-qmake amd64 5.11.3+dfsg1-1+deb10u3 [213 kB]
Get:5 http://ftp.us.debian.org/debian buster/main amd64 qtbase5-dev-tools amd64 5.11.3+dfsg1-1+deb10u3 [770 kB]
Get:6 http://ftp.us.debian.org/debian buster/main amd64 qtbase5-dev amd64 5.11.3+dfsg1-1+deb10u3 [1,014 kB]
Get:7 http://ftp.us.debian.org/debian buster/main amd64 libqt5opengl5-dev amd64 5.11.3+dfsg1-1+deb10u3 [63.8 kB]
Get:8 http://ftp.us.debian.org/debian buster/main amd64 libqt5serialport5 amd64 5.11.3-2 [35.0 kB]
Get:9 http://ftp.us.debian.org/debian buster/main amd64 libqt5serialport5-dev amd64 5.11.3-2 [12.8 kB]
Get:10 http://ftp.us.debian.org/debian buster/main amd64 libusb-1.0-0-dev amd64 2:1.0.22-2 [74.5 kB]
Get:11 http://ftp.us.debian.org/debian buster/main amd64 libusb-1.0-doc all 2:1.0.22-2 [182 kB]
Get:12 http://ftp.us.debian.org/debian buster/main amd64 qt5-default amd64 5.11.3+dfsg1-1+deb10u3 [48.2 kB]
Fetched 3,854 kB in 4s (946 kB/s)
Selecting previously unselected package libgles2-mesa:amd64.
(Reading database ... 371509 files and directories currently installed.)
Preparing to unpack .../00-libgles2-mesa_18.3.6-2+deb10u1_amd64.deb ...
Unpacking libgles2-mesa:amd64 (18.3.6-2+deb10u1) ...
Selecting previously unselected package libvulkan-dev:amd64.
Preparing to unpack .../01-libvulkan-dev_1.1.97-2_amd64.deb ...
Unpacking libvulkan-dev:amd64 (1.1.97-2) ...
Selecting previously unselected package qt5-qmake-bin.
Preparing to unpack .../02-qt5-qmake-bin_5.11.3+dfsg1-1+deb10u3_amd64.deb ...
Unpacking qt5-qmake-bin (5.11.3+dfsg1-1+deb10u3) ...
Selecting previously unselected package qt5-qmake:amd64.
Preparing to unpack .../03-qt5-qmake_5.11.3+dfsg1-1+deb10u3_amd64.deb ...
Unpacking qt5-qmake:amd64 (5.11.3+dfsg1-1+deb10u3) ...
Selecting previously unselected package qtbase5-dev-tools.
Preparing to unpack .../04-qtbase5-dev-tools_5.11.3+dfsg1-1+deb10u3_amd64.deb ...
Unpacking qtbase5-dev-tools (5.11.3+dfsg1-1+deb10u3) ...
Selecting previously unselected package qtbase5-dev:amd64.
Preparing to unpack .../05-qtbase5-dev_5.11.3+dfsg1-1+deb10u3_amd64.deb ...
Unpacking qtbase5-dev:amd64 (5.11.3+dfsg1-1+deb10u3) ...
Selecting previously unselected package libqt5opengl5-dev:amd64.
Preparing to unpack .../06-libqt5opengl5-dev_5.11.3+dfsg1-1+deb10u3_amd64.deb ...
Unpacking libqt5opengl5-dev:amd64 (5.11.3+dfsg1-1+deb10u3) ...
Selecting previously unselected package libqt5serialport5:amd64.
Preparing to unpack .../07-libqt5serialport5_5.11.3-2_amd64.deb ...
Unpacking libqt5serialport5:amd64 (5.11.3-2) ...
Selecting previously unselected package libqt5serialport5-dev:amd64.
Preparing to unpack .../08-libqt5serialport5-dev_5.11.3-2_amd64.deb ...
Unpacking libqt5serialport5-dev:amd64 (5.11.3-2) ...
Selecting previously unselected package libusb-1.0-0-dev:amd64.
Preparing to unpack .../09-libusb-1.0-0-dev_2%3a1.0.22-2_amd64.deb ...
Unpacking libusb-1.0-0-dev:amd64 (2:1.0.22-2) ...
Selecting previously unselected package libusb-1.0-doc.
Preparing to unpack .../10-libusb-1.0-doc_2%3a1.0.22-2_all.deb ...
Unpacking libusb-1.0-doc (2:1.0.22-2) ...
Selecting previously unselected package qt5-default:amd64.
Preparing to unpack .../11-qt5-default_5.11.3+dfsg1-1+deb10u3_amd64.deb ...
Unpacking qt5-default:amd64 (5.11.3+dfsg1-1+deb10u3) ...
Setting up libvulkan-dev:amd64 (1.1.97-2) ...
Setting up libgles2-mesa:amd64 (18.3.6-2+deb10u1) ...
Setting up libusb-1.0-doc (2:1.0.22-2) ...
Setting up libusb-1.0-0-dev:amd64 (2:1.0.22-2) ...
Setting up libqt5serialport5:amd64 (5.11.3-2) ...
Setting up qtbase5-dev-tools (5.11.3+dfsg1-1+deb10u3) ...
Setting up qt5-qmake-bin (5.11.3+dfsg1-1+deb10u3) ...
Setting up qt5-qmake:amd64 (5.11.3+dfsg1-1+deb10u3) ...
Setting up qtbase5-dev:amd64 (5.11.3+dfsg1-1+deb10u3) ...
Setting up qt5-default:amd64 (5.11.3+dfsg1-1+deb10u3) ...
Setting up libqt5opengl5-dev:amd64 (5.11.3+dfsg1-1+deb10u3) ...
Setting up libqt5serialport5-dev:amd64 (5.11.3-2) ...
Processing triggers for man-db (2.8.5-2) ...
Processing triggers for libc-bin (2.28-10) ...
[ZB:rbn]

2. The official RigExpert repository for the software is here. After downloading the software, set the working directory to Antscope2-master in the downloaded hierarchy and then:

[ZB:AntScope2-master] qmake AntScope.pro
Info: creating stash file /tmp/antscope/AntScope2-master/.qmake.stash
Project MESSAGE: AntScope2:
Project MESSAGE:        [/tmp/antscope/AntScope2-master/build/release]
Project MESSAGE:        [/tmp/antscope/AntScope2-master/build/release/.obj]
Project MESSAGE:        [/tmp/antscope/AntScope2-master/build/release/.moc]
Project MESSAGE:        [/tmp/antscope/AntScope2-master/build/release/.ui]
Project MESSAGE:        [/tmp/antscope/AntScope2-master/build/release/.rcc]
[ZB:AntScope2-master]

3. Assuming that you receive similar results, then you can make the executable:

[ZB:AntScope2-master] make                                 

This should generate many pages of output, including not a few warnings, but no errors. RigExpert say about this step: "This process will take considerable time. Caution! Warning messages may appear during compilation. This is not scary."

4. Unusually, this is not the end of the process; RigExpert requires a few more steps. After the make command completes, you should have files in a .../build/release directory. Now you must:

5. Create (manually) the directory .../build/release/Resources.

6. Download this ZIP file.

7. Put the file cables.txt from the ZIP archive in the .../Resources directory.

8. Put the remaining files from the ZIP archive in the .../release directory.

9. Allow yourself as an ordinary user to use the USB ports:

  sudo adduser $USER dialout

10. In order for step 9 to take effect, you must log out and log back in.

11. Download this file and place it in the directory /etc/udev/rules.d:

  sudo cp rigexpert-usb.rules /etc/udev/rules.d

12. Finally, you can execute the software, which is in the .../build/release directory:

  ./AntScope2

13. On my systems, this causes the following to appear:

Note the large black rectangle, which flashes into and out of existence as one moves the cursor, and also the menu window at bottom left that extends beyond border of window. These appear to be bugs in the software.