Sample ECAD Queries

The sample database queries below demonstrate some commonly requested address queries. The SQL is for a SQL Server database or similar relational database.

2.4.1 Querying Towns by County Show all the town in a given county:

-- all post towns in Wexford

SELECT C.NAME as county, PT.NAME as post_town FROM BUILDING b

INNER JOIN POST_TOWN PT ON PT.POST_TOWN_ID = b.POST_TOWN_ID INNER JOIN COUNTY C ON b.COUNTY_ID = C.COUNTY_ID

WHERE C.NAME = 'WEXFORD' GROUP BY C.NAME, PT.NAME ORDER BY POST_TOWN

Determine number of buildings in each town in a given county:

-- number of buildings in each post town in Wexford

SELECT C.NAME as county, PT.NAME as post_town, COUNT(*) as building_count FROM BUILDING b

INNER JOIN POST_TOWN PT ON PT.POST_TOWN_ID = b.POST_TOWN_ID INNER JOIN COUNTY C ON b.COUNTY_ID = C.COUNTY_ID

WHERE C.NAME = 'WEXFORD' GROUP BY C.NAME, PT.NAME ORDER BY POST_TOWN

2.4.2 Querying Industrial Estates in a County Show industrial estates in Dublin:

SELECT DISTINCT l.NAME as Industrial_Estate FROM BUILDING b

INNER JOIN COUNTY c ON b.COUNTY_ID = c.COUNTY_ID

INNER JOIN LOCALITY l ON (b.PRIMARY_LOCALITY_ID = l.LOCALITY_ID OR b.SECONDARY_LOCALITY_ID = l.LOCALITY_ID) WHERE c.NAME = 'DUBLIN'

AND l.ADDRESS_TYPE_ID = 1080 -- Industrial Estate UNION

SELECT DISTINCT b.NAME as Industrial_Estate FROM BUILDING b

INNER JOIN COUNTY c ON b.COUNTY_ID = c.COUNTY_ID WHERE c.NAME = 'DUBLIN'

AND b.ADDRESS_TYPE_ID = 1080 -- Industrial Estate UNION

SELECT DISTINCT bg.NAME as Industrial_Estate FROM BUILDING b

INNER JOIN COUNTY c ON b.COUNTY_ID = c.COUNTY_ID

INNER JOIN BUILDING_GROUP bg ON (b.BUILDING_GROUP_ID = bg.BUILDING_GROUP_ID) WHERE c.NAME = 'DUBLIN'

AND bg.ADDRESS_TYPE_ID = 1080 -- Industrial Estate

2.4.3 Querying Thoroughfares in a Town Show roads in Bantry:

SELECT DISTINCT T.THOROUGHFARE_ID , T.NAME

FROM (SELECT POST_TOWN_ID FROM POST_TOWN WHERE NAME = 'BANTRY') PT INNER JOIN BUILDING B ON B.POST_TOWN_ID = PT.POST_TOWN_ID

INNER JOIN THOROUGHFARE T ON (T.THOROUGHFARE_ID = B.PRIMARY_THOROUGHFARE_ID OR T.THOROUGHFARE_ID = B.SECONDARY_THOROUGHFARE_ID) LEFT OUTER JOIN LOCALITY LOC1 ON (LOC1.LOCALITY_ID = B.PRIMARY_LOCALITY_ID)

LEFT OUTER JOIN LOCALITY LOC2 ON (LOC2.LOCALITY_ID = B.SECONDARY_LOCALITY_ID) WHERE (LOC1.LOCALITY_ID IS NULL OR LOC1.ADDRESS_TYPE_ID NOT IN (3400,3600,3800)) and (LOC2.LOCALITY_ID IS NULL OR LOC2.ADDRESS_TYPE_ID NOT IN (3400,3600,3800)) GROUP BY T.THOROUGHFARE_ID, T.NAME, LOC1.LOCALITY_ID, LOC2.LOCALITY_ID

2.4.4 Postal Address View

The Postal Address View is a view of data from the ECAD address tables in the ECAD database that replicates the ECAD Postal Address table.

The extract requires two primary steps:

1. get the address data from the tables

2. construct address lines 1 to 8 from the address data

Construction of the eight columns for address lines 1 to 8 requires conditional logic and is therefore best suited to a function or other programming construct in either SQL or any other programming language.

The following sub-sections describe an example of how a Postal Address View might be constructed on a relational database, in this case a SQL Server database. Comments are embedded in the SQL for clarification: The SQL is contained in Appendix 3.

Postal Address Line View

The nine address lines are constructed from the following address elements:

 Department

 Organisation

 Sub Building

 Building Number

 Building Group

 Primary Thoroughfare

 Secondary Thoroughfare

 Primary Locality

 Secondary Locality

 Post Town

 County

 Eircode

The Irish language version of the Postal Address is to be produced in the same manner as the English version simply by replacing references to any NAME column to NAME_IRISH.

The exception is the COUNTY table where NAME_IRISH_CO is used.

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.

The construction of the eight address lines is conditional upon the content of these elements and so typically the conditional logic is put in a function. Each value present appears on the next available address line, except for sub building which may prefix another element, and building number which always prefixes the next available address line after building name.

Having constructed the eight address line rows, they are “pivoted” to convert them from rows into columns which can then be appended to each row in the view, as described above. The example SQL function (f_get_address_cols) gets the address lines (f_get_address_lines) and pivots them into 7 columns. See Appendix 3 for details.

3. Data Provision and Updates

Updates are available from Eircode on a quarterly basis. These are available for secure download via the Eircode website www.eircode.ie