drop table if exists paf_ptn;


create table paf_ptn ( 
  ptn_key varchar(10) primary key, 
  postal_area varchar(20), 
  town_name varchar(50), 
  county varchar(50), 
  county_flag varchar(1), 
  country_code varchar(1) 
); 

create table geo_outcodes ( 
  oc_key varchar(10) primary key,
  outcode varchar(10),
  lat numeric(20,10), 
  lng numeric(20,10), 
  ptn_fk varchar(10)
);

load data local infile 'PTN_0909.CSV' into table paf_ptn fields terminated by ',' enclosed by '"' lines terminated by '\r\n'; 

load data local infile 'postcodes.csv' into table geo_outcodes fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n'; 

 This dont work.. dunno why
#
# load data local infile 'postcodes.csv' 
# into table geo_outcodes (oc_key,outcode,lat,lng) 
# fields terminated by ',' 
# optionally enclosed by '"' 
# lines terminated by '\r\n'; 

delete from paf_ptn where town_name is null;

# select outcode, SUBSTRING(outcode,2,1), SUBSTRING(outcode,3,1) from geo_outcodes;
# select outcode, SUBSTRING(outcode,2,1) REGEXP '[0-9]', SUBSTRING(outcode,3,1) REGEXP'[0-9]' from geo_outcodes;
# select outcode, CASE when SUBSTRING(outcode,2,1) REGEXP '[0-9]' THEN SUBSTRING(outcode,0,1) ELSE SUBSTRING(outcode,0,2) from geo_outcodes
# select outcode, CASE when SUBSTRING(outcode,2,1) REGEXP '[0-9]' THEN 'A' ELSE 'B' from geo_outcodes
# select outcode, CASE when SUBSTRING(outcode,2,1) REGEXP '[0-9]' THEN LEFT(outcode,1) ELSE LEFT(outcode,2) END from geo_outcodes
# 

update geo_outcodes set ptn_fk = CASE when SUBSTRING(outcode,2,1) REGEXP '[0-9]' THEN LEFT(outcode,1) ELSE LEFT(outcode,2) END;

# authority_id,id,type,place_name,fqn,centroid_lat,centroid_lon
# select 'oc-dataset', CONCAT('oc-dataset-',go.outcode), '3.locality', go.outcode, CONCAT(go.outcode,', ',ptn.town_name, ', UK'), go.lat, go.lng 
# from geo_outcodes go, paf_ptn ptn
# where go.ptn_fk = ptn.postal_area;


# authority_id,id,type,place_name,fqn,fqnidx centroid_lat,centroid_lon
select 'oc-dataset', CONCAT('oc-dataset-',go.outcode), '3.locality', go.outcode, CONCAT(go.outcode, ', UK'), CONCAT('qzyf ', go.outcode, ', UK')go.lat, go.lng into outfile '/tmp/outcodes_for_solr.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' from outcodepostcodes go;


