X-Git-Url: https://git.8kb.co.uk/?p=postgresql%2Fgeographic_data;a=blobdiff_plain;f=paf_postgresql_import.plpgsql;h=13f47cbbfebf752f7ed6964b9dd82446fc683476;hp=996ffa0dcb7f752a8e5aaea8fbebb5579c05dab5;hb=6bcf4ac235f27bc6a4c0e3f69aac134fda8e331b;hpb=befa09c0ea48bcacd6c457d188e6725a2a834ebe diff --git a/paf_postgresql_import.plpgsql b/paf_postgresql_import.plpgsql index 996ffa0..13f47cb 100644 --- a/paf_postgresql_import.plpgsql +++ b/paf_postgresql_import.plpgsql @@ -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 @@ -1032,7 +1032,7 @@ BEGIN -- If I did have a postzon changes file, it'd most likely be best to import the data directly and use postgis to do our conversion -- as the data is selected out of the data_stage table (rather than using the convert_paf.pl script). We could get those fields with -- something like: - -- CASE WHEN substring(northing,1,1) ~ '[\x4f-\x5a]' THEN '1' || translate(northing, 'POUTZY', '221100') ELSE northing END || '0' AS f_notrhing, + -- CASE WHEN substring(northing,1,1) ~ E'[\x4f-\x5a]' THEN '1' || translate(northing, 'POUTZY', '221100') ELSE northing END || '0' AS f_notrhing, -- ST_x(ST_transform(ST_GeomFromText('POINT('||easting||' '||f_northing||')',CASE WHEN postcode LIKE 'BT%' THEN 29903 ELSE 27700 END),4326)) AS longitude, -- ST_y(ST_transform(ST_GeomFromText('POINT('||easting||' '||f_northing||')',CASE WHEN postcode LIKE 'BT%' THEN 29903 ELSE 27700 END),4326)) AS latitude; @@ -1094,7 +1094,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; @@ -1130,7 +1130,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;