v_table_created := true;
END IF;
- TRUNCATE TABLE public.pc_paf_localities;
+ TRUNCATE TABLE public.pc_paf_localities CASCADE;
INSERT INTO public.pc_paf_localities
SELECT substring(data,1,6)::integer,
nullif(trim(substring(data,52,30)),''),
v_table_created := true;
END IF;
- TRUNCATE TABLE public. pc_paf_thoroughfares;
+ TRUNCATE TABLE public. pc_paf_thoroughfares CASCADE;
INSERT INTO public. pc_paf_thoroughfares
SELECT substring(data,1,8)::integer,
nullif(trim(substring(data,9,60)),'')
v_table_created := true;
end if;
- TRUNCATE TABLE public.pc_paf_thoroughfare_descriptor;
+ TRUNCATE TABLE public.pc_paf_thoroughfare_descriptor CASCADE;
INSERT INTO public.pc_paf_thoroughfare_descriptor
SELECT substring(data,1,4)::integer,
nullif(trim(substring(data,5,20)),''),
v_table_created := true;
END IF;
- TRUNCATE TABLE public.pc_paf_building_names;
+ TRUNCATE TABLE public.pc_paf_building_names CASCADE;
INSERT INTO public.pc_paf_building_names
SELECT substring(data,1,8)::integer,
nullif(trim(substring(data,9,50)),'')
v_table_created := true;
END IF;
- TRUNCATE TABLE public.pc_paf_sub_building_names;
+ TRUNCATE TABLE public.pc_paf_sub_building_names CASCADE;
INSERT INTO public.pc_paf_sub_building_names
SELECT substring(data,1,8)::integer,
nullif(trim(substring(data,9,30)),'')
v_table_created := true;
END IF;
- TRUNCATE TABLE public.pc_paf_organisations;
+ TRUNCATE TABLE public.pc_paf_organisations CASCADE;
INSERT INTO public.pc_paf_organisations
SELECT substring(data,1,8)::integer,
nullif(trim(substring(data,9,1)),''),
-- 7) Postzon with latlon generated using postgis
TRUNCATE TABLE data_stage;
- RAISE NOTICE '%: Begin staging postzone', clock_timestamp();
+ RAISE NOTICE '%: Begin staging postzon', clock_timestamp();
v_sql := 'COPY data_stage FROM ' || quote_literal(v_data_postzon || 'pzone100.c01');
EXECUTE v_sql;
v_table_created := true;
END IF;
- TRUNCATE TABLE public.pc_paf_postzon_100m;
+ TRUNCATE TABLE public.pc_paf_postzon_100m CASCADE;
INSERT INTO public.pc_paf_postzon_100m(outward_code, inward_code, introduction_date, grid_reference_east, grid_reference_north,
country_code, area_code_county, area_code_district, ward_code, nhs_region, nhs_code, user_type, grid_status, latitude, longitude)
SELECT nullif(trim(substring(data,1,4)),'') AS outward_code,
nullif(trim(substring(data,5,3)),'') AS inward_code,
to_date(substring(data,8,6), 'YYYYMM') AS introduction_date,
(nullif(trim(substring(data,14,5)),'') || '0')::integer AS grid_reference_east,
- (CASE WHEN substring(nullif(trim(substring(data,19,5)),''),1,1) ~ '[\x4f-\x5a]' THEN '1' || translate(nullif(trim(substring(data,19,5)),''), 'POUTZY', '221100') ELSE nullif(trim(substring(data,19,5)),'') END || '0')::integer AS grid_reference_north,
+ (CASE WHEN substring(nullif(trim(substring(data,19,5)),''),1,1) ~ E'[\x4f-\x5a]' THEN '1' || translate(nullif(trim(substring(data,19,5)),''), 'POUTZY', '221100') ELSE nullif(trim(substring(data,19,5)),'') END || '0')::integer AS grid_reference_north,
nullif(trim(substring(data,24,9)),'') AS country_code,
nullif(trim(substring(data,33,9)),'') AS area_code_county,
nullif(trim(substring(data,42,9)),'') AS area_code_district,
nullif(trim(substring(data,69,9)),'') AS nhs_code,
nullif(trim(substring(data,78,1)),'')::smallint AS user_type,
nullif(trim(substring(data,79,1)),'')::smallint AS grid_status,
- ST_y(ST_transform(ST_GeomFromText('POINT('||(nullif(trim(substring(data,14,5)),'') || '0')||' '||(CASE WHEN substring(nullif(trim(substring(data,19,5)),''),1,1) ~ '[\x4f-\x5a]' THEN '1' || translate(nullif(trim(substring(data,19,5)),''), 'POUTZY', '221100') ELSE nullif(trim(substring(data,19,5)),'') END || '0')||')',CASE WHEN nullif(trim(substring(data,1,4)),'') LIKE 'BT%' THEN 29903 ELSE 27700 END),4326))::numeric(8,6) AS latitude,
- ST_x(ST_transform(ST_GeomFromText('POINT('||(nullif(trim(substring(data,14,5)),'') || '0')||' '||(CASE WHEN substring(nullif(trim(substring(data,19,5)),''),1,1) ~ '[\x4f-\x5a]' THEN '1' || translate(nullif(trim(substring(data,19,5)),''), 'POUTZY', '221100') ELSE nullif(trim(substring(data,19,5)),'') END || '0')||')',CASE WHEN nullif(trim(substring(data,1,4)),'') LIKE 'BT%' THEN 29903 ELSE 27700 END),4326))::numeric(8,6) AS longitude
+ ST_y(ST_transform(ST_GeomFromText('POINT('||(nullif(trim(substring(data,14,5)),'') || '0')||' '||(CASE WHEN substring(nullif(trim(substring(data,19,5)),''),1,1) ~ E'[\x4f-\x5a]' THEN '1' || translate(nullif(trim(substring(data,19,5)),''), 'POUTZY', '221100') ELSE nullif(trim(substring(data,19,5)),'') END || '0')||')',CASE WHEN nullif(trim(substring(data,1,4)),'') LIKE 'BT%' THEN 29903 ELSE 27700 END),4326))::numeric(8,6) AS latitude,
+ ST_x(ST_transform(ST_GeomFromText('POINT('||(nullif(trim(substring(data,14,5)),'') || '0')||' '||(CASE WHEN substring(nullif(trim(substring(data,19,5)),''),1,1) ~ E'[\x4f-\x5a]' THEN '1' || translate(nullif(trim(substring(data,19,5)),''), 'POUTZY', '221100') ELSE nullif(trim(substring(data,19,5)),'') END || '0')||')',CASE WHEN nullif(trim(substring(data,1,4)),'') LIKE 'BT%' THEN 29903 ELSE 27700 END),4326))::numeric(8,6) AS longitude
FROM data_stage;
GET DIAGNOSTICS v_processed = ROW_COUNT;
COMMENT ON COLUMN public.pc_paf_postzon_100m.grid_reference_east IS 'BT postcodes reflect the Irish Grid public (different origin and scale); subtract 17000 to approximate onto OS National Grid. For latlong do not approximate but do proper conversion';
COMMENT ON COLUMN public.pc_paf_postzon_100m.grid_reference_north IS 'BT postcodes reflect the Irish Grid public (different origin and scale); add 13000 to approximate onto OS National Grid. For latlong do not approximate but do proper conversion';
COMMENT ON COLUMN public.pc_paf_postzon_100m.user_type IS '0 Small User 1 Large User';
- COMMENT ON COLUMN public.pc_paf_postzon_100m.grid_status IS '0 Status not supplied by OS \n
+ COMMENT ON COLUMN public.pc_paf_postzon_100m.grid_status IS E'0 Status not supplied by OS \n
1 Within the building of the matched address closest to the Postcode mean. \n
2 Co-ordinates allocated by GROS during Postcode boundary creation to the building nearest the centre of the populated part of the Postcode (Scotland only) \n
3 Approximate to within 50m of true position \n
v_table_created := true;
END IF;
- TRUNCATE TABLE public.pc_paf_counties;
+ TRUNCATE TABLE public.pc_paf_counties CASCADE;
INSERT INTO public.pc_paf_counties
SELECT substring(data,2,4)::integer AS county_key,
trim(substring(data,6,30)) AS county_name,
v_table_created := true;
END IF;
- TRUNCATE TABLE public.pc_paf_county_alias;
+ TRUNCATE TABLE public.pc_paf_county_alias CASCADE;
INSERT INTO public.pc_paf_county_alias (postcode, former_postal_county, traditional_county, administrative_county)
SELECT trim(substring(data,2,7)) AS postcode,
nullif(substring(data,9,4)::integer,0) AS former_postal_county,
EXECUTE v_sql;
DELETE FROM data_stage WHERE data like '%WCHANGES' || in_edition || '%' OR data like v_main_footer;
- RAISE NOTICE '%: Done staging WChanges2 file',clock_timestamp();
+ RAISE NOTICE '%: Done staging WChanges file',clock_timestamp();
RAISE NOTICE '%: Preparing to update welsh mainfile', clock_timestamp();
-- It would appear postzon is supplied as a full refresh rather than an update file
TRUNCATE TABLE data_stage;
- RAISE NOTICE '%: Begin staging postzone', clock_timestamp();
+ RAISE NOTICE '%: Begin staging postzon', clock_timestamp();
v_sql := 'COPY data_stage FROM ' || quote_literal(v_data_postzon || 'pzone100.c01');
EXECUTE v_sql;
nullif(trim(substring(data,5,3)),'') AS inward_code,
to_date(substring(data,8,6), 'YYYYMM') AS introduction_date,
(nullif(trim(substring(data,14,5)),'') || '0')::integer AS grid_reference_east,
- (CASE WHEN substring(nullif(trim(substring(data,19,5)),''),1,1) ~ '[\x4f-\x5a]' THEN '1' || translate(nullif(trim(substring(data,19,5)),''), 'POUTZY', '221100') ELSE nullif(trim(substring(data,19,5)),'') END || '0')::integer AS grid_reference_north,
+ (CASE WHEN substring(nullif(trim(substring(data,19,5)),''),1,1) ~ E'[\x4f-\x5a]' THEN '1' || translate(nullif(trim(substring(data,19,5)),''), 'POUTZY', '221100') ELSE nullif(trim(substring(data,19,5)),'') END || '0')::integer AS grid_reference_north,
nullif(trim(substring(data,24,9)),'') AS country_code,
nullif(trim(substring(data,33,9)),'') AS area_code_county,
nullif(trim(substring(data,42,9)),'') AS area_code_district,
nullif(trim(substring(data,69,9)),'') AS nhs_code,
nullif(trim(substring(data,78,1)),'')::smallint AS user_type,
nullif(trim(substring(data,79,1)),'')::smallint AS grid_status,
- ST_y(ST_transform(ST_GeomFromText('POINT('||(nullif(trim(substring(data,14,5)),'') || '0')||' '||(CASE WHEN substring(nullif(trim(substring(data,19,5)),''),1,1) ~ '[\x4f-\x5a]' THEN '1' || translate(nullif(trim(substring(data,19,5)),''), 'POUTZY', '221100') ELSE nullif(trim(substring(data,19,5)),'') END || '0')||')',CASE WHEN nullif(trim(substring(data,1,4)),'') LIKE 'BT%' THEN 29903 ELSE 27700 END),4326))::numeric(8,6) AS latitude,
- ST_x(ST_transform(ST_GeomFromText('POINT('||(nullif(trim(substring(data,14,5)),'') || '0')||' '||(CASE WHEN substring(nullif(trim(substring(data,19,5)),''),1,1) ~ '[\x4f-\x5a]' THEN '1' || translate(nullif(trim(substring(data,19,5)),''), 'POUTZY', '221100') ELSE nullif(trim(substring(data,19,5)),'') END || '0')||')',CASE WHEN nullif(trim(substring(data,1,4)),'') LIKE 'BT%' THEN 29903 ELSE 27700 END),4326))::numeric(8,6) AS longitude
+ ST_y(ST_transform(ST_GeomFromText('POINT('||(nullif(trim(substring(data,14,5)),'') || '0')||' '||(CASE WHEN substring(nullif(trim(substring(data,19,5)),''),1,1) ~ E'[\x4f-\x5a]' THEN '1' || translate(nullif(trim(substring(data,19,5)),''), 'POUTZY', '221100') ELSE nullif(trim(substring(data,19,5)),'') END || '0')||')',CASE WHEN nullif(trim(substring(data,1,4)),'') LIKE 'BT%' THEN 29903 ELSE 27700 END),4326))::numeric(8,6) AS latitude,
+ ST_x(ST_transform(ST_GeomFromText('POINT('||(nullif(trim(substring(data,14,5)),'') || '0')||' '||(CASE WHEN substring(nullif(trim(substring(data,19,5)),''),1,1) ~ E'[\x4f-\x5a]' THEN '1' || translate(nullif(trim(substring(data,19,5)),''), 'POUTZY', '221100') ELSE nullif(trim(substring(data,19,5)),'') END || '0')||')',CASE WHEN nullif(trim(substring(data,1,4)),'') LIKE 'BT%' THEN 29903 ELSE 27700 END),4326))::numeric(8,6) AS longitude
FROM data_stage;
RAISE NOTICE '%: Done staging postzon with lat long data, updating', clock_timestamp();
LEFT OUTER JOIN public.pc_paf_building_names b USING (building_name_key)
LEFT OUTER JOIN public.pc_paf_sub_building_names sb USING (sub_building_name_key)
LEFT OUTER JOIN public.pc_paf_organisations o USING (organisation_key, postcode_type)
-LEFT OUTER JOIN public.pc_paf_organisations lo ON m.address_key = lo.organisation_key AND m.postcode_type = lo.postcode_type
+LEFT OUTER JOIN public.pc_paf_organisations lo ON m.address_key = lo.organisation_key AND m.postcode_type = lo.postcode_type AND m.postcode_type = 'L'
LEFT OUTER JOIN public.pc_paf_postzon_100m p USING (outward_code, inward_code)
LEFT OUTER JOIN public.pc_paf_county_alias a ON a.postcode = (rpad(m.outward_code,4) || m.inward_code)
LEFT OUTER JOIN public.pc_paf_counties apc ON apc.county_key = a.former_postal_county;
LEFT OUTER JOIN public.pc_paf_building_names b USING (building_name_key)
LEFT OUTER JOIN public.pc_paf_sub_building_names sb USING (sub_building_name_key)
LEFT OUTER JOIN public.pc_paf_organisations o USING (organisation_key, postcode_type)
-LEFT OUTER JOIN public.pc_paf_organisations lo ON mw.address_key = lo.organisation_key AND mw.postcode_type = lo.postcode_type
+LEFT OUTER JOIN public.pc_paf_organisations lo ON mw.address_key = lo.organisation_key AND mw.postcode_type = lo.postcode_type AND mw.postcode_type = 'L'
LEFT OUTER JOIN public.pc_paf_postzon_100m p USING (outward_code, inward_code)
LEFT OUTER JOIN public.pc_paf_county_alias a ON a.postcode = (rpad(mw.outward_code,4) || mw.inward_code)
-LEFT OUTER JOIN public.pc_paf_counties apc ON apc.county_key = a.former_postal_county;
\ No newline at end of file
+LEFT OUTER JOIN public.pc_paf_counties apc ON apc.county_key = a.former_postal_county;