FCC Database schema

create table ULS
(
license_id numeric(10),
source_system varchar(10),
callsign char(10),
facility_id numeric,
frn varchar(200),
lic_name varchar(255),
common_name varchar(200),
radio_service_code char(2),
radio_service_desc char(255),
rollup_category_code varchar(100),
rollup_category_desc varchar(100),
grant_date varchar(25),
expired_date varchar(25),
cancellation_date varchar(25),
last_action_date varchar(25),
lic_status_code char,
lic_status_desc varchar(40),
rollup_status_code varchar(50),
rollup_status_desc varchar(100),
entity_type_code varchar(10),
entity_type_desc varchar(100),
rollup_entity_code varchar(5),
rollup_entity_desc varchar(100),
lic_address varchar(60),
lic_city varchar(40),
lic_state varchar(2),
lic_zip_code char(9),
lic_attention_line varchar(35),
contact_company varchar(100),
contact_name varchar(200),
contact_title varchar(25),
contact_address1 varchar(60),
contact_address2 varchar(60),
contact_city varchar(20),
contact_state varchar(2),
contact_zip varchar(9),
contact_country varchar(30),
contact_phone varchar(30),
contact_fax varchar(30),
contact_email varchar(100),
market_code varchar(10),
market_desc varchar(50),
channel_block varchar(6),
loc_type_code varchar(10),
loc_type_desc varchar(50),
loc_city varchar(20),
loc_county_code char(5),
loc_county_name varchar(60),
loc_state char(2),
loc_radius_op numeric(5,1),
loc_seq_id numeric,
loc_lat_deg numeric,
loc_lat_min numeric,
loc_lat_sec numeric(3,1),
loc_lat_dir char,
loc_long_deg numeric,
loc_long_min numeric,
loc_long_sec numeric(3,1),
loc_long_dir char,
hgt_structure numeric(7,1),
asr_num varchar(15),
antenna_id numeric(10),
ant_seq_id numeric,
ant_model varchar(25),
ant_type_code varchar(1),
ant_type_desc varchar(50),
azimuth numeric(4,1),
beamwidth numeric(4,1),
polarization_code varchar(15),
frequency_id numeric(10),
freq_seq_id numeric,
freq_class_station_code char(4),
freq_class_station_desc varchar(50),
power_erp numeric(15,3),
power_output numeric(15,3),
frequency_assigned numeric(16,8),
frequency_upper_band numeric(16,8),
unit_of_measure varchar(5),
tolerance numeric(6,5),
emission_id numeric(10),
emission_seq_id numeric,
emission_code char(10),
ground_elevation numeric(15,7),
primary key (license_id)
);


load data local infile '/storage/bobd/hams.csv'
into table ULS
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
;

update ULS set grant_date = str_to_date( grant_date, '%m/%d/%Y  %H:%i:%s');
update ULS set expired_date = str_to_date( expired_date, '%m/%d/%Y  %H:%i:%s');
update ULS set cancellation_date = str_to_date( cancellation_date, '%m/%d/%Y  %H:%i:%s');
update ULS set last_action_date = str_to_date( last_action_date, '%m/%d/%Y  %H:%i:%s');

alter table ULS modify grant_date datetime;
alter table ULS modify expired_date datetime;
alter table ULS modify cancellation_date datetime;
alter table ULS modify last_action_date datetime;