
create table os_gaz50l (
  os_seq varchar(10) primary key,
  km_ref varchar(6),
  def_nam varchar(60),
  title_ref varchar(4),
  lat_deg int,
  lat_min numeric(3,1),
  lon_deg int,
  lon_min numeric(3,1),
  northing numeric(10),
  easting numeric(10),
  gmt char(1),
  co_code varchar(2),
  county varchar(20),
  full_county varchar(60),
  f_code varchar(3),
  e_date varchar(11),
  update_co varchar(1),
  sheet_1 numeric(3),
  sheet_2 numeric(3),
  sheet_3 numeric(3)
);

load data local infile '/home/ibbo/Desktop/data/gaz50k_gb/1 50 000 Scale Gazetteer/data/50kgaz2009.txt' into table os_gaz50l fields terminated by ':' lines terminated by '\r\n';


create table paf_counties as select distinct county, country_code from paf_ptn;

create table os_counties as select distinct(full_county) from os_gaz50l;

create table os_county_lookup (
  os_county_name varchar(40),
  country varchar(40) );

# insert into os_county_lookup (os_county_name, country)
# select os_counties.full_county, paf_counties.country_code
# from os_counties left outer join paf_counties on  os_counties.full_county = paf_counties.county;

load data local infile '/home/ibbo/Desktop/data/paf/paf/counties_RG.csv' into table os_county_lookup fields terminated by ',' lines terminated by '\r\n';

delete from paf_unified_gaz where auth = 'OSGAZ50L';

# Process Localities
insert into paf_unified_gaz ( auth, auth_key, type, place_name, fqn, fqnidx, aliases, status, northing, easting, lat, lng )
select 'OSGAZ50L',
       os_seq,
       '3. Locality',
       os_gaz50l.def_nam,
       CONCAT_WS(", ",def_nam, full_county, paf_country.name, 'UK'),
       CONCAT_WS(", ",def_nam, full_county, paf_country.name, 'UK'),
       null,
       'imp',
       northing,
       easting,
       (lat_deg+(lat_min/60)),
       (lon_deg+(lon_min/60)) * ( case gmt when 'W' then -1 else 1 end)
from os_gaz50l, os_county_lookup, paf_country
where f_code = 'O'
and os_county_lookup.os_county_name = full_county
and paf_country.code = os_county_lookup.country;


# Towns and cities

insert into paf_unified_gaz ( auth, auth_key, type, place_name, fqn, fqnidx, aliases, status, northing, easting, lat, lng )
select 'OSGAZ50L',
       os_seq,
       '4. PostTown',
       os_gaz50l.def_nam,
       CONCAT_WS(", ",def_nam, full_county, paf_country.name, 'UK'),
       CONCAT_WS(", ",def_nam, full_county, paf_country.name, 'UK'),
       null,
       'imp',
       northing,
       easting,
       (lat_deg+(lat_min/60)),
       (lon_deg+(lon_min/60)) * ( case gmt when 'W' then -1 else 1 end)
from os_gaz50l, os_county_lookup, paf_country
where ( f_code = 'T' or f_code = 'C' )
and os_county_lookup.os_county_name = full_county
and paf_country.code = os_county_lookup.country;
