On 09-May-2000 Hamish Moffatt wrote:
> 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)
> );
I think it may be interesting to share with you the experiences I had trying
to create a reasonable database for DXSpider (http://www.dxcluster.org).
The major problem is that there is no one definition of everything in one
place that you can use. Even the CT site has various 'views' of the
information in the versions of the .dat files.
This is how I do it, I offer the information for what it worth and you can
get the latest version of the perl data files from the website. Currently
they get distributed as part of the source distribution + patches but I can
make them available as separate entities if anyone is remotely interested.
The file concerned is /spider/data/prefix_data.pl
I have two tables:-
a prefix table which maps a prefix to one or more a unique geographical
entities, e.g:-
'3B9' => '6',
'3C' => '7',
'3C0' => '8',
'3D2' => '9,338,11',
'3D2/C' => '9',
'3D2/F' => '10',
and a table of geographical entities, eg:-
5 => bless( { name => 'Mauritius-3B8', dxcc => 273, itu => 53, cq => 39,
utcoff => -4.0, lat => -20.3, long => 57.5 }, 'Prefix'), 6 => bless( { name
=> 'Rodriguez-Is-3B9', dxcc => 274, itu => 53, cq => 39, utcoff => -4.0, lat
=> -19.7, long => 63.4 }, 'Prefix'),
7 => bless( { name => 'Equatorial-Guinea-3C', dxcc => 275, itu => 47, cq
=> 36, utcoff => -1.0, lat => 1.8, long => 10 }, 'Prefix'),
8 => bless( { name => 'Annobon-3C0', dxcc => 276, itu => 52, cq => 36,
utcoff => 0.0, lat => -1.5, long => 5.6 }, 'Prefix'),
9 => bless( { name => 'Conway-Reef-3D2/C', dxcc => 321, itu => 56, cq =>
32, utcoff => -13.0, lat => -21.7333333333333, long => 174.633333333333 },
'Prefix'),
10 => bless( { name => 'Fiji-Islands-3D2/F', dxcc => 277, itu => 56, cq =>
32, utcoff => -12.0, lat => -17, long => 178 }, 'Prefix'),
11 => bless( { name => 'Rotuma-3D2/R', dxcc => 324, itu => 56, cq => 32,
utcoff => -12.0, lat => -12.5, long => 177 }, 'Prefix'),
These are generated from a corrected wpxloc.raw and rsgb.cty which I and some
other DXSpider sysops maintain. At some point I will merge in the callsigns
from CTY.dat - I just haven't got round to it.
To translate this into a schema, I would therefore suggest that you would be
better off always working with some kind of join on:-
drop table geog;
create table geog (
id int2 unique key autoincrement,
name varchar(30) not null,
dxcc int2 not null,
itu int2 not null,
cq int2 not null,
utcoffset float not null,
lat float not null,
long float not null
);
drop table prefix;
create table prefix (
call varchar(12) unique key,
geog_id int2 not null
);
I don't have continent, not had any call for it, but I could add it anytime.
You have to create it in more than one pass, adding information as you go (so
be careful with the nulls).
The point of this is that several of the prefixes have potentially many
geographical areas.
The _real_ fun starts when you start looking for prefixes from complete
callsigns eg: 9K2/KM5FY/P and HL5/JA6HW or G1TLH/W3/AM.
Dirk G1TLH
--
Dirk-Jan Koopman, Tobit Computer Co Ltd
At the source of every error which is blamed on the computer you will find
at least two human errors, including the error of blaming it on the computer.
