Appendix 3 – Geographic Address View

4.3.1 Geographic Address View

This view combines a view of the address data (v_ecad_model_geographic) with the corresponding address line columns 1 to 9, which are constructed using a user-defined function (f_get_address_cols):

create view dbo.v_geographic_address_view as SELECT [ECAD_ID]

,[SECONDARY_THOROUGHFARE_ID]

,[PRIMARY_LOCALITY_ID]

,cols.ADDR_LINE_1, cols.ADDR_LINE_2, cols.ADDR_LINE_3,

cols.ADDR_LINE_4, cols.ADDR_LINE_5, cols.ADDR_LINE_6, cols.ADDR_LINE_7, cols.ADDR_LINE_8, cols.ADDR_LINE_9

,irish_cols.ADDR_LINE_1 IRISH_ADDR_LINE_1, irish_cols.ADDR_LINE_2 IRISH_ADDR_LINE_2, irish_cols.ADDR_LINE_3 IRISH_ADDR_LINE_3,

irish_cols.ADDR_LINE_4 IRISH_ADDR_LINE_4, irish_cols.ADDR_LINE_5 IRISH_ADDR_LINE_5, irish_cols.ADDR_LINE_6 IRISH_ADDR_LINE_6, irish_cols.ADDR_LINE_7 IRISH_ADDR_LINE_7, irish_cols.ADDR_LINE_8 IRISH_ADDR_LINE_8, irish_cols.ADDR_LINE_9 IRISH_ADDR_LINE_9 from dbo.V_ECAD_MODEL_GEOGRAPHIC em

cross apply dbo.f_get_address_cols(

EM.ECAD_ID ,EM.DEPARTMENT

,EM.DEPARTMENT_REQUIRED ,EM.ORGANISATION_NAME

,EM.ORGANISATION_NAME_REQUIRED ,EM.SUB_BUILDING_NAME

,EM.SUB_BUILDING_NAME_FULL_ADDRESS_LINE ,EM.SUB_BUILDING_NAME_REQUIRED ,EM.PRIMARY_THOROUGHFARE

,EM.PRIMARY_THOROUGHFARE_REQUIRED ,EM.SECONDARY_THOROUGHFARE

,EM.SECONDARY_THOROUGHFARE_REQUIRED ,EM.PRIMARY_LOCALITY

,EM.PRIMARY_LOCALITY_REQUIRED ,EM.SECONDARY_LOCALITY

,EM.SECONDARY_LOCALITY_REQUIRED ,EM.POST_TOWN

,EM.POST_TOWN_REQUIRED

,EM.POST_COUNTY

,EM.POST_COUNTY_REQUIRED ,EM.EIRCODE_DISPLAY ) cols cross apply dbo.f_get_address_cols(

EM.ECAD_ID

,EM.DEPARTMENT_IRISH ,EM.DEPARTMENT_REQUIRED ,EM.ORGANISATION_NAME_IRISH ,EM.ORGANISATION_NAME_REQUIRED ,EM.SUB_BUILDING_NAME_IRISH

,EM.SUB_BUILDING_NAME_FULL_ADDRESS_LINE ,EM.SUB_BUILDING_NAME_REQUIRED ,EM.PRIMARY_THOROUGHFARE_IRISH ,EM.PRIMARY_THOROUGHFARE_REQUIRED ,EM.SECONDARY_THOROUGHFARE_IRISH ,EM.SECONDARY_THOROUGHFARE_REQUIRED ,EM.PRIMARY_LOCALITY_IRISH

,EM.PRIMARY_LOCALITY_REQUIRED ,EM.SECONDARY_LOCALITY_IRISH ,EM.SECONDARY_LOCALITY_REQUIRED ,EM.POST_TOWN_IRISH

,EM.POST_TOWN_REQUIRED ,EM.POST_COUNTY_IRISH ,EM.POST_COUNTY_REQUIRED

,EM.EIRCODE_DISPLAY ) irish_cols

4.3.2 Geographic Address Model View

A model view is produced by joining together those ECAD model tables which are required to construct addresses. This query encapsulates the rules of the model:

CREATE VIEW dbo.v_ecad_model_geographic as

Select

b.ROUTING_KEY_ID

, rk.routing_key_name ROUTING_KEY

,IIF(NOT org.ORGANISATION_ID IS NULL, org.ORGANISATION_ID, ap.address_point_id) as ECAD_ID

-- The ECAD_ID identifies an ‘A’ddress Point or ‘O’rganisation

, IIF(NOT org.ORGANISATION_ID IS NULL, 'O', 'A') as ECAD_ID_IS_FROM , b.BUILDING_ID

, ap.ADDRESS_POINT_ID , org.ORGANISATION_ID

, org.NAME as ORGANISATION_NAME , org.DEPARTMENT

, org.NAME_IRISH as ORGANISATION_NAME_IRISH

, org.DEPARTMENT_IRISH

-- Determine SUB_BUILDING_NAME depending on single/multiple occupancy and whether residential or commercial

, COALESCE (org.sub_address_name, ap.name) as SUB_BUILDING_NAME

, COALESCE (org.SUB_ADDRESS_NAME_IRISH, ap.NAME_IRISH) as SUB_BUILDING_NAME_IRISH

-- Does the SUB_BUILDING_NAME appear on a line by itself or does it prefix the next address element ?

, IIF(NOT org.ORGANISATION_ID IS NULL and NOT org.SUB_ADDRESS_NAME IS NULL, org.FULL_ADDRESS_LINE,

ap.FULL_ADDRESS_LINE) as SUB_BUILDING_NAME_FULL_ADDRESS_LINE

, b.name as BUILDING_NAME

, b.name_irish as BUILDING_NAME_IRISH , b.number as BUILDING_NUMBER

, bg.name as BUILDING_GROUP_NAME

, bg.name_irish as BUILDING_GROUP_NAME_IRISH , th1.name as PRIMARY_THOROUGHFARE

, th1.name_irish as PRIMARY_THOROUGHFARE_IRISH , th2.name as SECONDARY_THOROUGHFARE

, th2.name_irish as SECONDARY_THOROUGHFARE_IRISH , l1.name as PRIMARY_LOCALITY

, l1.name_irish as PRIMARY_LOCALITY_IRISH , l2.name as SECONDARY_LOCALITY

, l2.name_irish as SECONDARY_LOCALITY_IRISH , pt.name as POST_TOWN

, pt.name_irish as POST_TOWN_IRISH , c.name as POST_COUNTY

, c.NAME_IRISH_CO as POST_COUNTY_IRISH , ap.EIRCODE as EIRCODE

-- The Eircode has 8 digits, 3 for routing key, a space, then the 4-digit unique identifier

, substring(ap.EIRCODE,1,3) + ' ' + substring(ap.EIRCODE,4,4) as EIRCODE_DISPLAY

-- flags determine if building address elements are required for individual address points

, org.ORGANISATION_NAME_REQUIRED

, IIF(NOT org.ORGANISATION_ID IS NULL, org.BUILDING_GROUP_REQUIRED, b.BUILDING_GROUP_REQUIRED) as BUILDING_GROUP_REQUIRED

, IIF(NOT org.ORGANISATION_ID IS NULL, org.BUILDING_NAME_REQUIRED, b.BUILDING_NAME_REQUIRED) as BUILDING_NAME_REQUIRED

, b.PRIMARY_THOROUGHFARE_REQUIRED , b.SECONDARY_THOROUGHFARE_REQUIRED , bgeo.PRIMARY_LOCALITY_REQUIRED , bgeo.SECONDARY_LOCALITY_REQUIRED , bgeo.POST_TOWN_REQUIRED

, bgeo.POST_COUNTY_REQUIRED , org.DEPARTMENT_REQUIRED

, IIF(NOT org.ORGANISATION_ID IS NULL and NOT org.SUB_ADDRESS_NAME IS NULL, org.SUB_BUILDING_NAME_REQUIRED,

ap.SUB_BUILDING_NAME_REQUIRED) SUB_BUILDING_NAME_REQUIRED , b.BUILDING_NUMBER_REQUIRED

, b.ADDRESS_TYPE_ID BUILDING_ADDRESS_TYPE_ID , b.BUILDING_GROUP_ID

, b.PRIMARY_THOROUGHFARE_ID , b.SECONDARY_THOROUGHFARE_ID , bgeo.PRIMARY_LOCALITY_ID , bgeo.SECONDARY_LOCALITY_ID , bgeo.POST_TOWN_ID

, bgeo.POST_COUNTY_ID

, ap.ADDRESS_TYPE_ID AP_ADDRESS_TYPE_ID , ap.building_id AP_BUILDING_ID

, ap.name AP_NAME

, org.address_type_id ORG_ADDRESS_TYPE_ID

from dbo.ADDRESS_POINT ap

inner join dbo.BUILDING b on b.building_id = ap.building_id inner join dbo.BUILDING_GEOGRAPHIC bgeo on b.building_id = bgeo.building_id

inner join dbo.ROUTING_KEY rk on rk.routing_key_id = b.routing_key_id left outer join dbo.ORGANISATION org on org.ADDRESS_POINT_ID =

ap.ADDRESS_POINT_ID

left outer join dbo.BUILDING_GROUP bg on bg.BUILDING_GROUP_ID = b.BUILDING_GROUP_ID

left outer join dbo.THOROUGHFARE th1 on th1.THOROUGHFARE_ID = b.PRIMARY_THOROUGHFARE_ID

left outer join dbo.THOROUGHFARE th2 on th2.THOROUGHFARE_ID = b.SECONDARY_THOROUGHFARE_ID

left outer join dbo.LOCALITY l1 on l1.LOCALITY_ID = bgeo.PRIMARY_LOCALITY_ID

left outer join dbo.LOCALITY l2 on l2.LOCALITY_ID = bgeo.SECONDARY_LOCALITY_ID

left outer join dbo.POST_TOWN pt on pt.POST_TOWN_ID = bgeo.POST_TOWN_ID

left outer join dbo.COUNTY c on c.COUNTY_ID = bgeo.POST_COUNTY_ID -- exclude buildings with no delivery points

where not b.status = 2

-- exclude Unassigned Eircodes and ap.eircode is not null

f_get_address_cols function used for Geographic Address view is detailed in Appendix 2.

Contact Information

Contact our Customer Account Management team if you would like more information on the products offered by Eircode, email [email protected]

Your Comments On This Guide

If you have any comments on this Eircode Address Database Product Guide we would be pleased to hear from you. Email your feedback to [email protected] and type ‘ECAD Product Guide enquiry’ in the subject line.

Disclaimer

Eircode is a trademark/brand of the Postcode Management Licence Holder (PMLH) operated by Capita Business Support Services (Ireland) Limited. This guide and the information contained in it are provided ‘as is’. Eircode, PMLH or Capita Business Support Services(Ireland) does not represent or warrant that this guide will be error-free or will meet any particular criteria of performance or quality.

Except as expressly set out in this document no representations, warranties or conditions are given or assumed by Eircode, PMLH or Capita Business Support Services(Ireland) or any other person in respect of any information or assistance which they provide or have provided, directly or indirectly to you. And any such representations, warranties or conditions are excluded to the fullest extent permitted by applicable law.

Eircode, PMLH or Capita Business Support Services (Ireland) excludes all liability for any loss rising out of or relating to the use of this guide or the information in it, whether direct, indirect, consequential or special or howsoever arising.

Use of the Eircode Address Database and documentation is subject to a separate Licence Agreement and as such must be used in accordance with its terms and conditions.

The reader must be aware of their own data protection obligations when using Eircode in conjunction with customer data. When using Eircode the user must ensure they are fully compliant with the relevant legislations regarding data protection both in their direct use and any use via third parties.