OK I've been working on the DXCC database again. The CTY.DAT format
is quite good and the web site seems to encourage other people to use
it, so I decided it would be OK.
Their format does allow lat/long, zones and continent to be overriden
on a per-prefix basis, with multiple prefixes per entity. This could
be used for the sub-entities we discussed, although we would have
to merge the data in some how, and also it doesn't allow you to override
the description for a sub-entity.
Here's the schema (SQL, more generic this time):
drop table country;
create table country (
prefix varchar(10) PRIMARY KEY,
name varchar(26),
timezone int2
);
drop table prefix;
create table prefix (
prefix varchar(10) PRIMARY KEY,
cqzone int2,
ituzone int2,
latitude float4,
longitude float4,
continent varchar(2)
);
Quite a bit simpler, really. The country table contains the primary
prefix, country name, and timezone (which can't be overriden on
a per-prefix basis). Then the prefix table contains the specifics, and
a link to the primary prefix.
The doco for CTY.DAT on the K1EA web site is wrong. It says that
prefixes may be at most 6 characters, but there are things like
'4U50VIC", "3D2AG/P" etc. I allowed 10 instead. It also says that
the lines of additional prefixes begin with &, but none of them do.
There's also one duplicate; 4U1VIC is listed twice, once for 4U1 and
once for OE.
I've attached: a script to convert CTY.DAT to SQL, and the output of
that script. I'll modify the script to use DBI and insert the data
directly into Postgres, probably.
Comments most welcome.
I would like to incorporate IOTA data. The challenge will be to
be able to import it into an already-populated database. Perhaps
it might be simpler to use a separate set of tables. More work
at lookup time, though.
Hamish
--
Hamish Moffatt VK3SB <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>
#!/usr/bin/perl
use strict;
use vars qw(@in @prefixes);
if (not defined $ARGV[0]) {
print "syntax: $0 <filename>\n";
exit;
}
open(IN, $ARGV[0]) or
die "$0: could not open $ARGV[0]\n";
while (my $in = <IN>) {
chomp($in);
$in =~ s/\x0D$//; # Remove carriage returns
push @in, $in;
}
close(IN);
my $i;
print "delete from country;\n";
print "delete from prefix;\n";
while ($i <= $#in) {
my ($name, $cqzone, $ituzone, $continent, $lat, $long, $tz, $primary);
my (@prefixes);
($name, $cqzone, $ituzone, $continent, $lat, $long, $tz, $primary)
= split(":", $in[$i]);
$i++;
$name =~ s/^\s+//;
$tz =~ s/^\s+//;
$cqzone =~ s/^\s+//;
$ituzone =~ s/^\s+//;
$lat =~ s/^\s+//;
$long =~ s/^\s+//;
$continent =~ s/^\s+//;
$primary =~ s/^\s+//;
print "insert into country values ('$primary', '$name', $tz);\n";
# Get additional prefixes
while ($in[$i] =~ m/^\s/) {
$in[$i] =~ s/^\s+//;
my $o_cqzone = $cqzone;
my $o_ituzone = $ituzone;
my $o_lat = $lat;
my $o_long = $long;
my $o_continent = $continent;
my $o_prefix;
my @aux = split(",", $in[$i]);
my $aux;
foreach $aux (@aux) {
$aux =~ s/;$//;
if ($aux =~ m#^([\w/]+)#) {
$o_prefix = $1;
}
if ($aux =~ m#\((\d+)\)#) {
$o_cqzone = $1;
}
if ($aux =~ m#\[(\d+)\]#) {
$o_ituzone = $1;
}
if ($aux =~ m#\{(\d+)\}#) {
$o_continent = $1;
}
if ($aux =~ m#<(\d+)/(\d+)\}#) {
$o_lat = $1;
$o_long = $2;
}
print "insert into prefix values ('$o_prefix', $o_cqzone, " .
"$o_ituzone, $o_lat, $o_long, '$o_continent');\n";
}
$i++;
}
}
data.sql.gz
