Working with COU data

All the AddressBase products are available as a full supply or a COU. A COU means you will only be supplied with the features which have changed since your last supply. The following sections provide guidance on how you could potentially manage a COU supply of AddressBase and AddressBase Plus data.

If you receive a tile supply, you will receive Change Chunks. This means if a record within your tile has changed, all of the records in that tile will be provided to you as inserts, and no updates or deletes will be issued.

Tiles are only available for GB supplies, so this does not apply to AddressBase Plus Islands.

Types of change

At a high-level, there are three types of change found within a COU:

• Deletes (CHANGE_TYPE ‘D’) are objects that have ceased to exist in your AOI since the last product refresh.

• Inserts (CHANGE_TYPE ‘I’) are objects that have been newly inserted into your AOI since the last product refresh.

• Updates (CHANGE_TYPE ‘U’) are objects that have been updated in your AOI since the last product refresh.

High-level COU implementation model

The diagram below shows how to implement an AddressBase, AddressBase Plus and AddressBase Plus Islands COU within a database.

High-level COU implementation model

High-level COU implementation model – with archiving

Before a COU is applied, there may be a business requirement to archive existing address records. The table below shows how to implement archiving with an AddressBase COU within a database.

High-level COU implementation model on how to create archive tables and apply a COU

Applying COU to tables

Within AddressBase and AddressBase Plus there will be no records with the same UPRN. This can be tested by checking the number of records that have the same UPRN. The following SQL code would notify you of any duplicates:

SELECT uprn, COUNT(uprn) AS NumOccurrences FROM addressbase_plus
GROUP BY uprn
HAVING ( COUNT(uprn) > 1 );

This query should return 0 rows, and this confirms that there are no duplicates. As there are no duplicate records, we can use the UPRN to apply the COU.

Once confirmed, the following steps can be taken to apply the COU (without archiving):

Initially delete the existing records that will be updated and deleted:

DELETE FROM addressbaseplus WHERE uprn IN (SELECT uprn FROM addressbaseplus_cou WHERE change_type != 'I');

Insert the new updated records and the new inserted records:

INSERT INTO addressbaseplus SELECT * FROM addressbaseplus _cou WHERE change_type != 'D';

Where there is a business requirement to keep the records that are being Updated and Deleted in a separate archive table, the following SQL will create an Archive Table. It will populate with records that are being Updated and Deleted from the live AddressBase or AddressBase Plus table.

The following command creates an archive table of the records that are being updated and deleted from the existing table.

If this table already exists, you can simply use INSERT INTO rather than CREATE TABLE.

CREATE TABLE addressbaseplus_archive AS SELECT * FROM addressbaseplus
WHERE uprn IN (SELECT uprn FROM addressbaseplus_cou WHERE change_type != 'I');

The following command then deletes the records from the existing table, which are either updates or deletions:

DELETE FROM addressbaseplus
WHERE uprn IN (SELECT uprn FROM addressbaseplus_cou WHERE change_type!= 'I');

The following command then inserts the new insert records and the new updated records into the live table:

INSERT INTO addressbaseplus SELECT * FROM addressbaseplus_cou WHERE change_type != 'D';