Create Geographic Addresses View

PL/pgSQL

CREATE OR REPLACE VIEW addressbaseplus.geographic_addresses AS
 SELECT formatted_address.uprn, formatted_address.rm_udprn, formatted_address.parent_uprn, formatted_address.rpc, formatted_address.multi_occ_count,(initcap(((ltrim(formatted_address.saon || formatted_address.paon) || formatted_address.street_description) || ', '::text) || formatted_address.localitytown) || ' '::text) || formatted_address.postcode::text AS full_address, formatted_address.postcode, formatted_address.geom
   FROM ( SELECT addressbaseplus.geom, addressbaseplus.uprn, addressbaseplus.rm_udprn, addressbaseplus.parent_uprn, addressbaseplus.rpc, addressbaseplus.multi_occ_count,
   btrim(((((
                CASE
                    WHEN addressbaseplus.organisation_name IS NOT NULL THEN addressbaseplus.organisation_name::text || ', '::text
                    ELSE ''::text
                END || 
                CASE
                    WHEN addressbaseplus.sao_start_number IS NOT NULL THEN addressbaseplus.sao_start_number::text
                    ELSE ''::text
                END) || 
                CASE
                    WHEN addressbaseplus.sao_start_suffix IS NOT NULL THEN addressbaseplus.sao_start_suffix::text
                    ELSE ''::text
                END) || 
                CASE
                    WHEN addressbaseplus.sao_end_number IS NOT NULL THEN '-'::text || addressbaseplus.sao_end_number::text
                    ELSE ''::text
                END) || 
                CASE
                    WHEN addressbaseplus.sao_end_suffix IS NOT NULL THEN addressbaseplus.sao_end_suffix::text
                    ELSE ''::text
                END) || 
                CASE
                    WHEN addressbaseplus.sao_text IS NOT NULL THEN (' '::text || addressbaseplus.sao_text::text) || ', '::text
                    ELSE ''::text
                END) || ' '::text AS saon, ltrim((((
                CASE
                    WHEN addressbaseplus.pao_start_number IS NOT NULL THEN addressbaseplus.pao_start_number::text
                    ELSE ''::text
                END || 
                CASE
                    WHEN addressbaseplus.pao_start_suffix IS NOT NULL THEN addressbaseplus.pao_start_suffix::text
                    ELSE ''::text
                END) || 
                CASE
                    WHEN addressbaseplus.pao_end_number IS NOT NULL THEN '-'::text || addressbaseplus.pao_end_number::text
                    ELSE ''::text
                END) || 
                CASE
                    WHEN addressbaseplus.pao_end_suffix IS NOT NULL THEN addressbaseplus.pao_end_suffix::text
                    ELSE ''::text
                END) || 
                CASE
                    WHEN addressbaseplus.pao_text IS NOT NULL THEN (' '::text || addressbaseplus.pao_text::text) || ', '::text
                    ELSE ' '::text
                END) AS paon, 
                CASE
                    WHEN addressbaseplus.street_description IS NOT NULL THEN addressbaseplus.street_description::text
                    ELSE ''::text
                END AS street_description, 
                CASE
                    WHEN addressbaseplus.locality_name IS NOT NULL THEN addressbaseplus.locality_name::text || ', '::text
                    ELSE ''::text
                END || 
                CASE
                    WHEN addressbaseplus.town_name IS NOT NULL THEN addressbaseplus.town_name::text
                    ELSE ''::text
                END AS localitytown, addressbaseplus.postcode_locator AS postcode
           FROM addressbaseplus.addressbaseplus) formatted_address;