4. Appendices
1.7 Eircode Address Database (ECAD)
The ECAF contains the base reference data for over 2 million Irish addresses and is a prerequisite to the purchase of the ECAD. The ECAD contains additional data for each address. For ease of use the two products are delivered in a relational database. The ECAD contains no software; the data within it must be processed for use in IT applications.
The ECAD is comprised of a number of database tables. The tables are divided into a number of categories based on the role they perform within the database. The categories are:
Address – Contains the information that is used to make up an address
Alias – Provides information on address alias information
Lookup – Contains lookup information that has been normalised into separate tables
Information – Holds non address information related to the Address Tables
History – Provides detail of retired address records and their related Eircodes
The tables in the ECAD are described in more detail below (detailed table definition for each table is available in Appendix 1.)
1.7.1 Address Tables
The following is the list of tables that contain the address information stored in the ECAD:
Address Group Address Table
Premises Tables ORGANISATION
SUB_ADDRESS_POINT ADDRESS_POINT BUILDINGS
Thoroughfare Tables BUILDING_GROUP THOROUGHFARE Locality Tables LOCALITY
POST_TOWN COUNTY
Address POSTAL_ADDRESS
GEOGRAPHIC_ADDRESS
The address information stored in these tables is stored in upper case only for English and Irish versions of the address element. Provision has been made to receive Official Irish language versions (Type 3 and 4) in the future, which uses mixed case for Irish versions of the address where required. Address elements always have an associated address type that is stored in the ADDRESS_TYPE Lookup Table.
Address
The Entity Relationship Diagram for Address Tables is shown below, and the tables are described in the following sections.
BUILDING
A record in the BUILDING table generally refers to a permanent physical building, however some records may refer to other entities, e.g. a halting site bay. The BUILDING table is the central hub of the Address Tables, defining the thoroughfare and locality elements of a building. A locality may cross county boundaries, or a thoroughfare may cross multiple post towns, therefore the post town or county information resides solely at the building level.
ADDRESS TYPE Description Single Occupancy
Residential Building
This type of building contains one residential address.
Multi Occupancy Residential Building
This type of building contains multiple residential addresses.
Single Occupancy Non-Residential Building
This type of building contains one non-residential address (business, club or other organisation).
Multi Occupancy Non-Residential Building
This type of building contains multiple non-residential addresses (business, club or other organisation).
Multi Occupancy Mixed Use Building
This type of building contains multiple residential and non-residential addresses.
Buildings can also have a more specific address types such as a Hospital, School, Shopping Centre, etc.
BUILDING_GEOGRAPHIC
There is a one-to-one relationship between the BUILDING_GEOGRAPHIC table and the BUILDING table, joining on BUILDING_ID. The BUILDING_GEOGRAPHIC effectively contains replacement values for the BUILDING table if you wish to create GEOGRAPHIC addresses rather than POSTAL addresses.
ADDRESS_POINT
An address point record exists for every unique address within a building. A standard residential property will have one address point. Apartment buildings and multi-unit commercial buildings will have one address point for every unique address within the building.
Eircodes are assigned at the address point level and reside in the ADDRESS_POINT table.
There are a number of address points that do not have an associated Eircode. The primary reason is some buildings do not receive a direct delivery of mail by the Universal Service Provider. In these cases mail is generally delivered centrally and distributed to each building by internal mail processes, e.g. a University Campus.
For an Eircode to be assigned, a building must receive a direct delivery of post, have a Routing Key, and a verified coordinate. When the BUILDING table STATUS field has a value of 2 this indicates the building does not get a direct delivery of post. Buildings without a Routing Key have STATUS = 3. If a building has a STATUS = 1 but does not have a verified coordinate then no Eircode is assigned. Only addresses with Eircodes are included in the Postal Address table. It is expected, but not guaranteed, that addresses in a STATUS=1 or 3 building that do not receive an Eircode in the release they are introduced would be updated in the next release with the required information to assign an Eircode.
Address points can be one of the following address types:
ADDRESS TYPE Description
Residential Address Point This type of address point has one residential addresses associated with it.
Non-Residential Address Point This type of address point has one or more non-residential address (business, club or other organisation) associated with it.
Mixed Address Point This is a special case where the residential and non residential addresses in the building are essentially the same address. The typical example is a farm house on an active farm.
It is important to note that this is a special case. In general a building with both residential and non-residential addresses (e.g. an apartment over a shop) will receive two address points, one commercial and one residential, and hence two Eircodes.
Buildings can contain multiple address points of type Residential and/or Non-Residential.
SUB_ADDRESS_POINT
A sub address point is an address within a building that has no underlying address information.
This information indicates that there are multiple flats within a building without discrete addresses (e.g. without Flat 1, Flat 2, etc. indicators). These records are inserted into the SUB_ADDRESS_POINT table to indicate their existence. The address point associated with the records is generally of type Residential Address Point, but can also be associated with type Non-Residential Address Point. The SUB_ADDRESS_POINT records are never used to generate a Postal Address and can be ignored for most purposes.
If a building had one postal address and 5 sub address points, there would be five records in table SUB_ADDRESS_POINT and each would have the ADDRESS_POINT_ID of the parent address point, ie. The one associated with the postal address.
ORGANISATION
The ORGANISATION table contains a record for every non-residential address in the ECAD.
The organisation name is present unless the address is vacant or the organisation name is unknown in the case of organisations associated with Non-Residential Address Point. For Mixed Address Points, there is always a reference in the Organisation table and the organisation name is always null.
BUILDING_GROUP
A building group is a collection of buildings with a collective name, located on or near the same thoroughfare.
Building groups can be one of the following generic address types:
ADDRESS TYPE Description
Residential Building Group This type of building group contains buildings with residential addresses only.
Non-Residential Building Group This type of building group contains buildings with non-residential addresses (business, club or other organisation) only.
Mixed Building Group This type of building group contains buildings with residential and non-residential addresses.
Building groups can also have a more specific address type such as a Hospital, School, Shopping Centre, etc.
THOROUGHFARE
Thoroughfares generally refer to the street, road, avenue, etc. on which a building resides.
The BUILDINGS table has a reference to both PRIMARY and SECONDARY thoroughfares (if populated), which both reference this THOROUGHFARE table.
LOCALITY
A locality refers to areas, districts, towns, etc. and is generally one of the following address types:
ADDRESS TYPE Description
Rural Locality This is generally a townland.
Industrial Estate Industrial Estate, Industrial Park, Business Campus, etc.
Shopping District Shopping Centre.
Housing Estate Residential Housing Estate.
Village Based on Census 2011 population < 1,500.
Town Based on Census 2011 population > 1,500.
Urban Area Wholly within a village/town/city e.g.
Rathmines.
Suburban Locality This is an area that is both rural and urban, as it is both a townland, and also an area name applied to houses in a town, as the town has extended partially into the townland.
The BUILDINGS table has a reference to PRIMARY and SECONDARY localities (if populated), which all reference this LOCALITY table.
All records in the POST_TOWN table are duplicated in the LOCALITY table, where the LOCALITY_ID in the LOCALITY table is equal to the POST_TOWN_ID in the POST_TOWN table. These LOCALITY records have one of the following address types:
ADDRESS TYPE Description
Village Based on Census 2011 population < 1,500 Town Based on Census 2011 population > 1,500
Postal District Dublin 1 to 24
City Dublin, Cork, Limerick, Galway or Waterford
POST_TOWN
The POST_TOWN table contains a record for every post town in the ECAD. The post town is a significant element of the Postal Address, however it is not always populated in an address.
The official post office guide, Eolaí an Phoist1, describes post towns in the following manner;
“A provincial postal address may include the name of a town or village several miles distant, with which the addressee has little or no connection, and, in some places, especially if this residence happens to be near a county boundary, the name of the neighbouring county instead of the county in which he actually resides. The explanation is that the main mail despatches have to be sent for more detailed sub division to certain centres known as POST TOWNS, chosen because of their accessibility and convenience.”
The POST TOWN record always has an address type of post town.
COUNTY
The COUNTY table contains a row for each of the 26 counties in Ireland.
A COUNTY record always has an address type of County.
1 Ireland. Eolaí an phoist: Post office guide. Dublin
POSTAL_ADDRESS
The POSTAL ADDRESS Table contains a row for every Postal Address in the ECAD. It contains the following fields:
POSTAL_ADDRESS field
An ECAD_ID referred to in the POSTAL_ADDRESS table is either the ORGANISATION_ID if it is not Null, or the ADDRESS_POINT_ID which is never Null. ECAD_ID is simply a generic name that covers all of the primary keys in the address tables (ORGANISATION_ID,
ADDRESS_POINT_ID, BUILDING_ID, BUILDING_GROUP_ID, etc.) and allows joining to the various _INFO table.
You don’t join the ECAD_ID in the POSTAL_ADDRESS table to any of the _INFO tables, you use the appropriate ID. For example to get coordinates you join to the SPATIAL_INFO table on POSTAL_ADDRESS.BUILDING_ID = SPATIAL_INFO.ECAD_ID.
The individual address elements (i.e. BUILDING_ID, THOROUGHFARE_ID, etc.) make up the address lines. Some address elements are combined for an address line (e.g. Building Number and Thoroughfare Name). Address creation rules have been applied to create Address Line 1-8 in both English and Irish. The final line in every Postal Address in the table is the Eircode. For inbound international mail the country name IRELAND should be appended as the last line of the Postal Address.
The NUA field contains a Y value when the address is a non-unique address, and N when it is a unique address.
The GAELTACHT field contains a Y value when the address is in a Gaeltacht area, and N when it is isn’t.
The ADDRESS_REFERENCE is the An Post GeoDirectory address reference identifier used by the Universal Service Provider.
GEOGRAPHIC_ADDRESS
The GEOGRAPHIC ADDRESS Table contains a row for every Geographic Address in the ECAD. It contains the following fields:
GEOGRAPHIC_ADDRESS field
ADDR_LINE_5
ECAD_ID is simply a generic name that covers all of the primary keys in the address tables (ORGANISATION_ID, ADDRESS_POINT_ID, BUILDING_ID, BUILDING_GROUP_ID, etc.) and allows joining to the various _INFO table.
You don’t join the ECAD_ID in the GEOGRAPHIC _ADDRESS table to any of the _INFO tables, you use the appropriate ID. For example to get coordinates you join to the SPATIAL_INFO table on POSTAL_ADDRESS.BUILDING_ID = SPATIAL_INFO.ECAD_ID.
The individual address elements (i.e. BUILDING_ID, THOROUGHFARE_ID, etc.) make up the address lines. Some address elements are combined for an address line (e.g. Building Number and Thoroughfare Name). Address creation rules have been applied to create Address Line 1-8 in both English and Irish. The final line in every Geographic Address in the table is the Eircode.
The NUA field contains a Y value when the address is a non-unique address, and N when it is a unique address.
The GAELTACHT field contains a Y value when the address is in a Gaeltacht area, and N when it is isn’t.
The ADDRESS_REFERENCE is the An Post GeoDirectory address reference identifier used by the Universal Service Provider.
1.7.2 Lookup Tables
The Lookup Tables are used to tie together the Address Tables. They have been created when the Address Tables were normalised. The Lookup Tables are as follows:
ADDRESS_TYPE
BUILDING_TYPE
DESCRIPTOR
GEOGRAPHIC
QUALIFIER
IRISH_VERIFICATION
ROUTING_KEY
GEODIRECTORY_LOOKUP ADDRESS_TYPE
This table contains one record for each Address Type. Address Types categorise addresses.
The address type is a field in every Address Table and thus allows for addresses to be consistently queried.
For example a BUILDING, a BUILDING GROUP or a LOCALITY may all have an Address Type of “Industrial Estate”. The Address Type of a BUILDING has a bearing on what other information is available for an address. For example if a building has an address type of SINGLE OCCUPANCY RESIDENTIAL BUILDING this means that there will be no entries in the ORGANISATION table for that building. All Address Tables contain an ADDRESS_TYPE_ID that references the ADDRESS_TYPE table.
BUILDING_TYPE
The BUILDING_TYPE table contains one record for each building type, e.g. Detached, Terraced, etc. The BUILDING_INFO table contains a BUILDING_TYPE_ID that references the BUILDING_TYPE table.
DESCRIPTOR
This table contains one record for each Descriptor. A Descriptor is the part of a thoroughfare or building group address such as the examples below.
NAME Irish Name
AVENUE ASCAILL
GROVE GARRÁN
STREET SRÁID
ROAD BÓTHAR
GEOGRAPHIC
This table contains one record for each Geographic. A geographic element is North, South, East or West, which can be appended or prepended to an address element.
Name Irish Name
EAST THOIR
NORTH THUAIDH
SOUTH THEAS
WEST THIAR
QUALIFIER
This table contains one record for each Qualifier. Qualifiers are appended of prepended to address elements, and are listed in the table below.
NAME Irish Name
EXTENSION SÍNEADH
GREAT MHÓR
LITTLE BHEAG
LOWER ÍOCHTARACH
MIDDLE LÁIR
UPPER UACHTARACH
For the BUILDING_GROUP, THOROUGHFARE and LOCALITY tables the DESCRIPTOR_ID, QUALIFIER_ID and GEOGRAPHIC_ID fields are deconstructed from the NAME and NAME_IRISH fields. The tables also contain a variation of the following two fields (THOROUGHFARES table used as an example).
THOROUGHFARE_NAME
The element of NAME that remains after Descriptor, Qualifier and Geographic elements have been removed.
THOROUGHFARE_NAME_IRISH
Same as THOROUGHFARE_NAME but using NAME_IRISH.
THOROUGHFARE Example 1 Example 2
NAME ABBEY STREET
MIDDLE O'CONNELL STREET
LOWER
NAME_IRISH SRÁID NA
MAINISTREACH LÁIR
SRÁID UÍ CHONAILL ÍOCHTARACH
THOROUGHFARE_NAME ABBEY O'CONNELL
THOROUGHFARE_NAME_IRISH NA MAINISTREACH UÍ CHONAILL
As the NAME field contains these elements in a variety of order permutations, it is not possible to re-create the NAME or NAME_IRISH fields in their original order. Please note that the aim of THOROUGHFARE_NAME_IRISH field is to assist parsing of address elements only, not to create syntactically correct Irish, as evidenced by the examples above.
IRISH_VERIFICATION
All Address Tables contain a field called NAME, this contains the English language version for the address element. Each table also contains a NAME_IRISH field which contains the equivalent address element in Irish.
Each Address Table also contains an IRISH_VERIFICATION_ID field. This field denotes the validation status of the Irish language address element. This information is stored in the IRISH_VERIFICATION table as displayed;
IRISH_VERIFICATION_ID VALIDATION_LIFECYCLE
0 NO IRISH - ENGLISH USED
1 EIRCODE TRANSLATION2
2 UNOFFICAL TRANSLATION
3 OFFICIAL IRISH LANGUAGE VERSION
4 LEGAL OFFICIAL IRISH LANGUAGE VERSION
The Irish Validation lifecycle gives the developer the option of choosing the Irish language version where the validation level is appropriately high. Irish language versions of placenames are determined by The Placenames Branch (Department of Arts, Heritage and the Gaeltacht) and given legal status by means of a placenames order signed by the Minister of State at the Department of Arts, Heritage and the Gaeltacht. They are published on www.logainm.ie, developed by Fiontar, DCU. This research is generally undertaken on a County by County basis, with translations moving from official Irish language versions of placenames to legal status when the entire County has been completed and the appropriate Government order enacted.
It is planned that the official Irish language version provided by The Placenames Branch will replace the current unofficial translations provided by GeoDirectory.
ROUTING_KEY
The Routing Key is the first part of an Eircode and is consistently three characters long.
Routing Keys are defined by the Universal Service Provider for postal services which reflect the sortation requirements of the mail distribution network. The Routing Key format is Letter-Number-Number with the single exception of D6W for the Dublin 6W postal district. The existing Dublin Postal Districts 1 to 24 have been retained as D01 to D24. The Routing Key
2 In a small number of cases Eircode has generated a translation without recourse to official sources. These generally apply to simple lookup table entries (e.g. East, West, etc.). Eircode plan to have these translations verified, but do not expect the values to change.
letters and number assigned to the remainder of the 139 Routing Keys are not directly linked to counties, towns or any other geographic boundaries. A Routing Key will be shared by many properties in an area. Routing keys are fixed once assigned to an Eircode and do not change.
The BUILDING table contains a ROUTING_KEY_ID column that links to the ROUTING_KEY table which contains 139 Routing Key Names (e.g. D01, Y14, etc.).
GEODIRECTORY_LOOKUP
For users of GeoDirectory, this table provides a lookup to determine the relevant GeoDirectory GEO_ID for each Eircode ECAD_ID as illustrated by the sample rows below.
GEODIRECTORY_LOOKU P_ID
ECAD_ID GEO_ID ECAD_TABLE GEO_TABLE
1 10005481
BUILDING BUILDINGS
Similarly, you can also look up the relevant ECAD_ID if you have an existing GeoDirectory GEO_ID. If you have a GeoDirectory ADDRESS_POINT_ID this will point to either an ECAD ADDRESS_POINT_ID or an ORGANISATION_ID which will both allow you to determine the Eircode for the GeoDirectory ADDRESS_POINT_ID.
If you have an existing GeoDirectory BUILDING_ID this will point to either an ECAD ADDRESS_POINT_ID or a BUILDING_ID or both. If the Building has only one Address Point (and therefore only one Eircode) we also provide the ADDRESS_POINT_ID reference to allow you to determine the Eircode. If the Building has more than one Address Point (and thus we can’t tell you the Eircode for the building as it has many) then we only provide the ECAD BUILDING_ID.
1.7.3 Information Tables
The information tables provide extra information that adds value to the addresses.
INFORMATION TABLES SPATIAL_INFO
BUILDING_INFO ORGANISATION_INFO ADMINISTRATIVE_INFO POSTAL_INFO
SPATIAL_INFO
The SPATIAL_INFO table contains spatial data. This data includes map coordinates of
The SPATIAL_INFO table contains spatial data. This data includes map coordinates of