AddressBase Premium Lookup table SQL

AddressBase Premium Lookup table SQL

#-------------------------------------------------------------------------------
# Name:        `AddressBase Premium Lookup Table SQL
# Purpose:     `This SQL code can be used to create a new lookup table which contains
#		the core attributes of AddressBase Premium. This code will work if
#		the user has the same table names/structure as described in the Getting
#		Started Guide found on the AddressBase Premium product page.
# Created:     `12/08/2014
# Copyright:   `Crown Copyright (c) Ordnance Survey 2014
# Licence:     `THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
#               "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
#               LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
#               FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
#               COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
#               INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
#               BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
#               OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED
#               AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
#               OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF
#               THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE
#-------------------------------------------------------------------------------


CREATE TABLE addressbasepremium.abp_lookup AS SELECT
        b.uprn,
        b.parent_uprn,
        b.rpc,
        b.postal_address,

        --STATE CODE (BLPU)
        /*
        Definition ? describes the physical nature of the property or land object.
        These are used to represent the physical state or the feature for example, the BLPU or the LPI.*/
        b.blpu_state,
        case
        when (b.blpu_state = 1) then 'Under Construction'
        when (b.blpu_state = 2) then 'In use'
        when (b.blpu_state = 3) then 'Unoccupied'
        when (b.blpu_state = 4) then 'No longer existing'
        when (b.blpu_state = 6) then 'Planning permission granted'
        else 'Unknown/Not applicable'
        end AS blpu_state_desc,

        --LOGICAL STATUS (BLPU & LPI)
        /*
        Definition ? the Logical Status reflects where the BLPU/LPI has reached in its life cycle.
        Logical status is important in identification of the addresses? requirements, for example, whether it is an alternative address or an historic address.*/

        b.logical_status AS blpu_logical_status,
        case
        when (b.logical_status = 1) then 'Approved'
        when (b.logical_status = 3) then 'Alternative'
        when (b.logical_status = 6) then 'Provisional'
        when (b.logical_status = 8) then 'Historical'
        end AS blpu_logical_status_desc,

        l.logical_status AS lpi_logical_status,
        case
        when (l.logical_status = 1) then 'Approved'
        when (l.logical_status = 3) then 'Alternative'
        when (l.logical_status = 6) then 'Provisional'
        when (l.logical_status = 8) then 'Historical'
        end AS lpi_logical_status_desc,

        c.classification_code,
        b.local_custodian_code,
        d.rm_udprn,

        --Concatenate single Royal Mail Delivery Point address label
        case when d.department_name != '' then d.department_name || ', ' else '' end
        ||case when d.organisation_name != '' then d.organisation_name || ', ' else '' end
        ||case when d.sub_building_name != '' then d.sub_building_name || ', ' else '' end
        ||case when d.building_name != '' then d.building_name || ', ' else '' end
        ||case when d.building_number is not null then d.building_number::varchar(4) || ', ' else '' end
        ||case when d.po_box_number != '' then 'PO BOX '||d.po_box_number::varchar(4)||', ' else '' end
        ||case when d.dependent_thoroughfare_name != '' then d.dependent_thoroughfare_name || ', ' else '' end
        ||case when d.throughfare_name != '' then d.throughfare_name || ', ' else '' end
        ||case when d.double_dependent_locality != '' then d.double_dependent_locality || ', ' else '' end
        ||case when d.dependent_locality != '' then d.dependent_locality || ', ' else '' end
        ||case when d.post_town != '' then d.post_town || ', ' else '' end
        ||d.postcode AS dpa,

        --Concatenate a single GEOGRAPHIC address line label
        case when o.organisation != '' then o.organisation||', ' else '' end
        --Secondary Addressable Information-------------------------------------------------------------------------------------------------------

        ||case when l.sao_text != '' then l.sao_text||', ' else '' end
        --case statement for different combinations of the sao start numbers (e.g. if no sao start suffix)
        ||case when l.sao_start_number is not null and l.sao_start_suffix = '' and l.sao_end_number is null then l.sao_start_number::varchar(4)||', '

        when l.sao_start_number is null then '' else l.sao_start_number::varchar(4)||' ' end
        --case statement for different combinations of the sao start suffixes (e.g. if no sao end number)
        ||case when l.sao_start_suffix != '' and l.sao_end_number is null then l.sao_start_suffix||', '
        when l.sao_start_suffix != '' and l.sao_end_number is not null then l.sao_start_suffix else '' end

        --Add a '-' between the start and end of the secondary address (e.g. only when sao start and sao end)
        ||case when l.sao_end_suffix != '' and l.sao_end_number is not null then '-'
        when l.sao_start_number is not null and l.sao_end_number is not null then '-' else '' end
        --case statement for different combinations of the sao end numbers and sao end suffixes
        ||case when l.sao_end_number is not null and l.sao_end_suffix = '' then l.sao_end_number::varchar(4)||', '
        when l.sao_end_number is null then '' else l.sao_end_number::varchar(4) end
        --pao end suffix

        ||case when l.sao_end_suffix != '' then l.sao_end_suffix||', ' else '' end
        --Primary Addressable Information----------------------------------------------------------------------------------------------------------

        ||case when l.pao_text != '' then l.pao_text||', ' else '' end
        --case statement for different combinations of the pao start numbers (e.g. if no pao start suffix)
        ||case when l.pao_start_number is not null and l.pao_start_suffix = '' and l.pao_end_number is null then l.pao_start_number::varchar(4)||', '

        when l.pao_start_number is null then '' else l.pao_start_number::varchar(4)||' ' end
        --case statement for different combinations of the pao start suffixes (e.g. if no pao end number)
        ||case when l.pao_start_suffix != '' and l.pao_end_number is null then l.pao_start_suffix||', '
        when l.pao_start_suffix != '' and l.pao_end_number is not null then l.pao_start_suffix else '' end

        --Add a '-' between the start and end of the primary address (e.g. only when pao start and pao end)
        ||case when l.pao_end_suffix != '' and l.pao_end_number is not null then '-'
        when l.pao_start_number is not null and l.pao_end_number is not null then '-' else '' end
        --case statement for different combinations of the pao end numbers and pao end suffixes
        ||case when l.pao_end_number is not null and l.pao_end_suffix = '' then l.pao_end_number::varchar(4)||', '
        when l.pao_end_number is null then '' else l.pao_end_number::varchar(4) end
        --pao end suffix
        ||case when l.pao_end_suffix != '' then l.pao_end_suffix||', ' else '' end
        --Street Information----------------------------------------------------------------------------------------------------------------------------

        ||case when s.street_descriptor != '' then s.street_descriptor||', ' else '' end
        --Locality------------------------------------------------------------------------------------------------------------------------------------------

        ||case when s.locality_name != '' then s.locality_name||', ' else '' end
        --Town---------------------------------------------------------------------------------------------------------------------------------------------

        ||case when s.town_name != '' then s.town_name||', ' else '' end
        --Postcode----------------------------------------------------------------------------------------------------------------------------------------

        ||case when b.postcode_locator != '' then b.postcode_locator else '' end
        AS lpi,
        l.usrn,
        b.x_coordinate,
        b.y_coordinate

        FROM
        addressbasepremium.abp_blpu_record AS b,
        addressbasepremium.abp_classification AS c,
        addressbasepremium.abp_street_descriptor AS s,
        addressbasepremium.abp_lpi AS l full outer join addressbasepremium.abp_organisation AS o on (l.uprn = o.uprn)
        full outer join addressbasepremium.abp_delivery_point AS d on (l.uprn = d.uprn)

        WHERE b.uprn = l.uprn
        AND b.uprn = c.uprn
        AND l.usrn = s.usrn
        AND l.language = s.language;
        COMMIT;