From dbe06233a9220029439c5baa17ebc83eb52d457e Mon Sep 17 00:00:00 2001 From: glyn Date: Mon, 11 Aug 2014 11:12:38 +0100 Subject: [PATCH] Alter master views to account for quirk in PAF schema where mainfile address_key relates to organisations instead of organisation_key when postcode_type is L --- paf_postgresql_import_postgis.plpgsql | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/paf_postgresql_import_postgis.plpgsql b/paf_postgresql_import_postgis.plpgsql index 5a16920..36c764c 100644 --- a/paf_postgresql_import_postgis.plpgsql +++ b/paf_postgresql_import_postgis.plpgsql @@ -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; -- 2.39.5