Alter master views to account for quirk in PAF schema where mainfile address_key...

[postgresql/geographic_data] / paf_postgresql_import_postgis.plpgsql

diff --git a/paf_postgresql_import_postgis.plpgsql b/paf_postgresql_import_postgis.plpgsql
index 058c45fb476683d2841456b06c647687eedf2f9f..36c764c4e9e4a1de2db9cdff050303b1741a4176 100644 (file)
--- a/paf_postgresql_import_postgis.plpgsql
+++ b/paf_postgresql_import_postgis.plpgsql
@@ -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;  
@@ -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;                  
@@ -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;