Fix: when re-importing data into existing tables truncate needs cascade due to foreig...

[postgresql/geographic_data] / paf_postgresql_import_postgis.plpgsql

diff --git a/paf_postgresql_import_postgis.plpgsql b/paf_postgresql_import_postgis.plpgsql
index 36c764c4e9e4a1de2db9cdff050303b1741a4176..746ca4215ca5f024aa03a0e422f6c5306685e453 100644 (file)
--- 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)),''),
@@ -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,
@@ -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();