Working with COU data

All AddressBase products are available as a full supply or a Change Only Update (COU).

A COU means you will only be supplied with the features which have changed since your last supply.

The following sub-sections provide guidance on how you could potentially manage a COU supply of AddressBase Core using PostgreSQL.

• Where angle brackets (<>) are used, you should be replace the entire string with your content. For example:

  COPY <table_name> FROM 'C:\Address\ AddressBaseCore_COU_2020-06-03_001.csv' DELIMITER ',' CSV HEADER;

  Becomes:

  COPY addressbase_core_cou FROM 'C:\Address\ AddressBaseCore_COU_2020-06-03_001.csv' DELIMITER ',' CSV HEADER;

• If referencing a schema, <schema_name>, can be placed in front of any <table_name>, for example:

  COPY ab_core.addressbase_core_cou FROM 'C:\Address\ AddressBaseCore_COU_2020-06- 03_001.csv' DELIMITER ',' CSV HEADER;

Types of change

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

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

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

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

High-level COU implementation model

High-level COU implementation model diagram

High-level COU implementation model with archiving

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

High-level COU implementation model with archiving diagram

Applying COU to tables

Within AddressBase Core there will be no records with the same UPRN. You can test this by checking the number of records that have the same UPRN. The following SQL query notifies you of any duplicates.

SELECT uprn, COUNT(uprn) AS NumOccurrences FROM <table_name>
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.

With archiving

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 Core table.

The following commands create an archive table of the records that are being updated and deleted from the existing table, for example, addressbase_core_archive.

1. Use the steps in Working with CSV data > Loading AddressBase Core into a database to load the COU CSV into a new or existing table, for example, addressbase_core_cou.

2. If this table already exists, you can use INSERT INTO <abcore_archive_table_name>, rather than CREATE TABLE <abcore_archive_table_name> AS.

   CREATE TABLE <abcore_archive_table_name> AS SELECT * FROM <master_abcore_table_name> WHERE uprn IN (SELECT uprn FROM <abcore_cou_table_name> WHERE change_code != 'I');

3. The following commands deletes the records from the existing table, which are either updates or deletions.

   DELETE FROM <master_abcore_table_name>
   WHERE uprn IN (SELECT uprn FROM <abcore_cou_table_name> WHERE change_code != 'I');

4. The following command inserts the new insert records and the new updated records into the live BLPU table.

   INSERT INTO <master_abcore_table_name>
   SELECT * FROM <abcore_cou_table_name> WHERE change_code != 'D';

5. You can check that this has been successful by re-checking that there are no duplicate UPRNs.

Without archiving

If there is no requirement for archiving, follow these steps:

1. Use the steps in Working with CSV data > Loading AddressBase Core into a database to load the COU CSV into a new or existing table, for example, addressbase_core_cou.

2. Now, delete the existing records that will be updated, for example, Updates and Deletions.

   DELETE FROM <master_abcore_table_name>
   WHERE uprn IN (SELECT uprn FROM <abcore_cou_table_name> WHERE change_code != 'I');

3. Insert the new updated records.

   INSERT INTO <master_abcore_table_name>
   SELECT * FROM <abcore_cou_table_name> WHERE change_code != 'D';

4. You can check that this has been successful by re-checking that there are no duplicate UPRNs.