X-Git-Url: https://git.8kb.co.uk/?p=postgresql%2Fgeographic_data;a=blobdiff_plain;f=paf_postgresql_import_postgis.plpgsql;h=746ca4215ca5f024aa03a0e422f6c5306685e453;hp=058c45fb476683d2841456b06c647687eedf2f9f;hb=HEAD;hpb=50dff4a45fc4d6c1456a35ed61c02d71c9e1f67d diff --git a/paf_postgresql_import_postgis.plpgsql b/paf_postgresql_import_postgis.plpgsql index 058c45f..746ca42 100644 --- a/paf_postgresql_import_postgis.plpgsql +++ b/paf_postgresql_import_postgis.plpgsql @@ -51,7 +51,7 @@ BEGIN 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)),''), @@ -91,7 +91,7 @@ BEGIN 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)),'') @@ -130,7 +130,7 @@ BEGIN 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)),''), @@ -169,7 +169,7 @@ BEGIN 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)),'') @@ -207,7 +207,7 @@ BEGIN 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)),'') @@ -247,7 +247,7 @@ BEGIN 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)),''), @@ -267,7 +267,7 @@ BEGIN -- 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; @@ -302,14 +302,14 @@ BEGIN 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, @@ -318,8 +318,8 @@ BEGIN 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; @@ -334,7 +334,7 @@ BEGIN 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 @@ -559,7 +559,7 @@ BEGIN 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, @@ -592,7 +592,7 @@ BEGIN 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, @@ -946,7 +946,7 @@ BEGIN 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(); @@ -1063,7 +1063,7 @@ BEGIN -- 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; @@ -1076,7 +1076,7 @@ BEGIN 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, @@ -1085,8 +1085,8 @@ BEGIN 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(); @@ -1236,7 +1236,7 @@ LEFT OUTER JOIN public.pc_paf_thoroughfare_descriptor td USING (thoroughfare_des 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; @@ -1272,7 +1272,7 @@ LEFT OUTER JOIN public.pc_paf_thoroughfare_descriptor td USING (thoroughfare_des 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;