Working with COU data

Introduction to COU

AddressBase Premium and AddressBase Premium Islands are complex relational datasets that are used by a variety of customers who use a variety of methods and software to manage the data. Some of the software solutions take a considerable length of time to load and manage the data. A change-only update (COU) is a simple and effective way to keep data holdings up to date without spending considerable time loading and managing a full supply every time the data is refreshed.

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 to manage a COU supply of AddressBase Premium data.

Note - If you receive a tile supply, you will receive Change Chunks. This means if a record within your tile has changed, then all of the records in that tile will be provided to you as inserts, and no updates or deletes will be issued. This is not applicable for AddressBase Premium Islands as a tile supply is not available for that product.

Types of change within a COU

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

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

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

3. 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 outlines how to implement the AddressBase Premium COU within a database. It also shows the necessary primary keys needed to implement the COU for each relational table.

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 outlines how to implement the AddressBase Premium COU within a database, shows the necessary primary keys needed to implement the COU for each relational table, and how to archive existing records.

Applying COU to tables

Changes to the BLPU table

Within the Basic Land and Property Unit (BLPU) table, there will not be any 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 abp_blpu
GROUP BY uprn
HAVING ( COUNT(uprn) > 1 );

This query should return 0 rows, and this confirms there are no duplicates. As there are no duplicate records, the UPRN can be used 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 delete

DELETE FROM abp_blpu WHERE uprn IN (SELECT distinct uprn FROM abp_blpu_cou WHERE change_type!= ‘I’);

Insert the new updated records and the newly inserted records

INSERT INTO abp_blpu SELECT * FROM abp_blpu_cou WHERE change_type != ‘D’;

Some of the COU records that are change type ‘U’ (updates) may change the Logical Status Code from ‘1’ to ‘8’, meaning that this address has become ‘Historical’. This means that the BLPU table intrinsically archives historical records.

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 and populate it with records that are being Updated and Deleted from the live BLPU table.

The following command creates an archive table of the records that are being updated and deleted from the existing BLPU table: If this table already exists, you can simply use INSERT INTO rather than CREATE TABLE

CREATE TABLE abp_blpu_archive AS SELECT * FROM abp_blpu WHERE uprn IN (SELECT distinct uprn FROM abp_blpu_cou WHERE change_type != ‘I’);

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

DELETE FROM abp_blpu WHERE uprn IN (SELECT distinct uprn FROM abp_blpu_cou WHERE change_type!= ‘I’);

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

INSERT INTO abp_blpu SELECT * FROM abp_blpu_cou WHERE change_type != ‘D’;

Changes to the Classification table

Because there is a one-to-many relationship between the BLPU table and the Classification table, there can be records in the Classification table that share a UPRN. To apply COU to the correct record, users should use the Class_Key to ensure that the correct classification record is updated.

The table below provides examples of using the Class_Key to apply a COU to one of two classification records that share a UPRN in a Classification table.

The example in classification code table above illustrates a scenario when a user would need to choose between two classification records that have the same UPRN. In this case, the Class_Key has been used to apply the COU to Record 2.

To achieve this outcome (without archiving the ‘old’ Record 2), we can use the following SQL commands to apply the COU:

Initially delete the existing records that are being updated and deleted:

DELETE FROM abp_classification WHERE class_key IN (SELECT distinct class_key FROM abp_ classification_cou WHERE change_type != ‘I’);

Insert the new update records and the new insert records:

INSERT INTO abp_classification SELECT * FROM abp_classification_cou WHERE change_type != ‘D’;

One thing you may want to consider is keeping an archive of the updated and deleted classification records. For example, this might be useful to understand when an address has changed use from residential to commercial.

To achieve this outcome for change types ‘U’ (updates) or ‘D’ (deletes) (with archiving), we can use the following SQL commands to apply the COU:

The following command creates an archive table of the records that are being updated and deleted from the existing Classification table. If this table already exists, you can simply use INSERT INTO rather than CREATE TABLE:

CREATE TABLE abp_classification_archive AS SELECT * FROM abp_classification WHERE class_key IN (SELECT distinct class_key FROM abp_classification_cou WHERE change_type != ‘I’);

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

DELETE FROM abp_classification WHERE class_key IN (SELECT distinct class_key FROM aabp_ classification_cou WHERE change_type != ‘I’);

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

INSERT INTO abp_classification SELECT * FROM abp_classification_cou WHERE change_type != ‘D’;

The following table shows classification and archive record details:

The following table shows classification and archive record with an end date details:

Changes to the Organisation table

The numerous one-to-many relationships between the BLPU table and the Organisation table mean there can be records in the Organisation table that share a UPRN. To apply COU to the correct record, we should use the Org_Key to ensure that the correct classification record is updated.

To apply the COU to the Organisation table (without archiving), the following code can be used:

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

DELETE FROM abp_organisation WHERE org_key IN (SELECT distinct org_key FROM abp_organisation_ cou WHERE change_type != ‘I’);

Insert the new updated records and the newly inserted records:

INSERT INTO abp_organisation SELECT * FROM abp_organisation_cou WHERE change_type != ‘D’;

As with the Classification table, the changes in Organisation name may be useful to keep as archives as doing so will allow a business to find previous organisations and understand when name changes were made.

To apply the COU to the Organisation table (with archiving), the following code can be used:

The following command creates an archive table of the records that are being updated and deleted from the existing Organisation table. If this table already exists, you can simply use INSERT INTO rather than CREATE TABLE:

CREATE TABLE abp_organisation_archive AS SELECT * FROM abp_organisation WHERE org_key IN (SELECT distinct org_key FROM abp_organisation_cou WHERE change_type != ‘I’);

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

DELETE FROM abp_organisation WHERE org_key IN (SELECT distinct org_key FROM aabp_organisation_ cou WHERE change_type != ‘I’);

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

INSERT INTO abp_organisation SELECT * FROM abp_organisation_cou WHERE change_type != ‘D’;

Changes to the Delivery Point Address table

Within the Delivery Point Address table, there will not be any records with the same Unique Delivery Point Reference Number (UDPRN). This can be tested by checking the number of records that have the same UDPRN. The following SQL code would notify you of any duplicates:

SELECT udprn, COUNT(udprn) AS NumOccurrences FROM abp_delivery_point GROUP BY udprn
HAVING (COUNT(udprn) > 1 );

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

To apply the COU to the Delivery Point Address table (without archiving), the following code can be used:

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

DELETE FROM abp_delivery_point WHERE udprn IN (SELECT distinct udprn FROM abp_delivery_point_cou WHERE change_type != ‘I’);

Insert the new updated records and the new inserted records:

INSERT INTO abp_delivery_point SELECT * FROM abp_delivery_point_cou WHERE change_type != ‘D’;

The Delivery Point Address table does not have the ability to hold historical records as it is the current view of the Royal Mail Delivery Point Address File (PAF). Therefore, in order to capture the historical records, you will need to create an archive table that is populated when records are either deleted or updated. The following code will create the archive records:

To apply the COU to the Delivery Point Address table (with archiving), the following code can be used:

The following command creates an archive table of the records that are being updated and deleted from the existing Delivery Point Address table. If this table already exists, you can simply use INSERT INTO rather than CREATE TABLE:

CREATE TABLE abp_delivery_point_archive AS SELECT * FROM abp_delivery_point WHERE udprn IN (SELECT distinct udprn FROM abp_delivery_point_cou
WHERE change_type != ‘I’);

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

DELETE FROM abp_delivery_point WHERE udprn IN (SELECT distinct udprn FROM abp_delivery_point_cou WHERE change_type != ‘I’);

The following command then inserts the new insert records and the new updated records into the Delivery Point Address table:

INSERT INTO abp_delivery_point SELECT * FROM abp_delivery_point_cou WHERE change_type != ‘D’;

Changes to the Land and Property Identifier table

The numerous one-to-many relationships between the BLPU table and the Land and Property Identifier (LPI) table mean there can be records in the LPI table that share a UPRN. To apply the COU to the correct record, we should use the LPI_Key to ensure that the correct classification record is updated.

To apply the COU to the LPI table (without archiving), the following code can be used:

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

DELETE FROM abp_lpi WHERE lpi_key IN (SELECT distinct lpi_key FROM abp_lpi_cou WHERE change_type != ‘I’);

Insert the new updated records and the new inserted records:

INSERT INTO abp_lpi SELECT * FROM abp_lpi_cou WHERE change_type != ‘D’;

As with the BLPU table, some of the COU records that are change type ‘U’ (updates) may change the Logical Status Code from ‘1’ to ‘8’, meaning that this address has become ‘historical’. This means that the LPI table intrinsically archives the historical record.

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 and populate it with records that are being updated and deleted from the live LPI table.

To apply the COU to the LPI table (with archiving), the following code can be used:

The following command creates an archive table of the records that are being updated and deleted from the existing LPI table. If this table already exists, you can simply use INSERT INTO rather than CREATE TABLE:

CREATE TABLE abp_lpi_archive AS SELECT * FROM abp_lpi WHERE lpi_key IN (SELECT distinct lpi_key FROM abp_ lpi_cou WHERE change_type != ‘I’);

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

DELETE FROM abp_lpi WHERE lpi_key IN (SELECT distinct lpi_key FROM abp_lpi_cou WHERE change_type != ‘I’);

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

INSERT INTO abp_lpi SELECT * FROM abp_lpi_cou WHERE change_type != ‘D’;

The following table shows an original LPI record next to a COU record. In this example, the record is being made historical (logical status code: 8) and therefore has a populated end date attribute.

Changes to the Street table

Within the Street table, there will not be any records with the same Unique Street Reference Number (USRN). This can be tested by checking the number of records that have the same USRN. The following SQL code would notify you of any duplicates:

SELECT usrn, COUNT(usrn) AS NumOccurrences FROM abp_street
GROUP BY usrn
HAVING ( COUNT(usrn) > 1 );

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

To apply the COU to the Street table (without archiving), the following code can be used:

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

DELETE FROM abp_street WHERE usrn IN (SELECT distinct usrn FROM abp_street_cou WHERE change_type != ‘I’);

Insert the new updated records and the new inserted records:

INSERT INTO abp_street SELECT * FROM abp_street_cou WHERE change_type != ‘D’;

The Street table does not have the ability to hold historical records as it does not have a Logical Status Code attribute. Therefore, to capture the historical records, you will need to create an archive table that is populated when records are either deleted or updated.

To apply the COU to the Street table (with archiving), the following code can be used:

The following command creates an archive table of the records that are being updated and deleted from the existing Street table. If this table already exists, you can simply use INSERT INTO rather than CREATE TABLE:

CREATE TABLE abp_street_archive AS SELECT * FROM abp_street WHERE usrn IN (SELECT distinct usrn FROM abp_ street_cou WHERE change_type != ‘I’);

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

DELETE FROM abp_street WHERE usrn IN (SELECT distinct usrn FROM abp_street_cou WHERE change_type != ‘I’);

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

INSERT INTO abp_street SELECT * FROM abp_street_cou WHERE change_type != ‘D’;

Changes to the Street Descriptor table

Within the Street Descriptor table, there will not be any records with the same USRN and the same language. This is called a compound key, rather than having a single column as a Primary Key. This can be tested by checking the number of records that have the same USRN. The following SQL code will notify you of any duplicates:

SELECT usrn, language, COUNT(usrn) AS NumOccurrences FROM abp_street_descriptor
GROUP BY usrn, language HAVING ( COUNT(usrn) > 1 );

This query should return 0 rows, and this confirms there are no duplicates using the compound key. As there are no duplicate records, we can therefore use the USRN and LANGUAGE to apply the COU.

To apply the COU to the LPI table (without archiving), the following code can be used:

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

DELETE FROM abp_street_descriptor WHERE EXISTS (SELECT 1 FROM abp_street_descriptor_cou WHERE abp_street_descriptor_cou.usrn = abp_street_descriptor.usrn AND abp_street_descriptor_cou.language = abp_street_descriptor.language
AND abp_street_descriptor_cou.change_type != ‘I’ ) 

Insert the new updated records and the new inserted records:

INSERT INTO abp_street_descriptor SELECT * FROM abp_street_descriptor_cou WHERE change_type != ‘D’;

The Street Descriptor table does not have the ability to hold historical records as it does not have a Logical Status Code attribute. Therefore, to capture the historical records, you will need to create an archive table that is populated when records are either deleted or updated.

To apply the COU to the Street Descriptor table (with archiving), the following code can be used:

The following command creates an archive table of the records that are being updated and deleted from the existing Street table. If this table already exists, you can simply use INSERT INTO rather than CREATE TABLE:

CREATE TABLE abp_street_descriptor_archive AS SELECT * FROM abp_street_descriptor WHERE EXISTS (SELECT 1 FROM abp_street_descriptor_cou
WHERE abp_street_descriptor_cou.usrn = abp_street_descriptor.usrn AND abp_street_descriptor_cou.language = abp_street_descriptor.language AND abp_street_descriptor_cou.change_type != ‘I’ )

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

DELETE FROM abp_street_descriptor WHERE usrn IN (SELECT distinct usrn FROM abp_street_descriptor_cou WHERE change_type != ‘I’);

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

INSERT INTO abp_street_descriptor SELECT * FROM abp_street_descriptor_cou WHERE change_type != ‘D’;

Changes to the Cross Reference table

Within the Cross Reference table, there will not be any records with the same XREF_KEY. This can be tested by checking the number of records that have the same XREF_KEY. The following SQL code will notify you of any duplicates:

SELECT XREF_KEY, COUNT(XREF_KEY) AS NumOccurences FROM ABP_XREF
GROUP BY XREF_KEY
HAVING (COUNT(XREF_KEY) > 1);

The query above should return 0 rows and therefore confirms that there are no duplicates. As there are no duplicates, we can therefore use the XREF_KEY to apply the COU.

To apply the COU to the Cross Reference Table (without archiving), the following code can be used:

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

DELETE FROM ABP_XREF WHERE XREF_KEY IN (SELECT distinct XREF_KEY FROM ABP_XREF_COU WHERE CHANGE_TYPE != ‘I’);

Insert the new records and the updated records:

INSERT INTO ABP_XREF SELECT * FROM ABP_XREF_COU WHERE CHANGE_TYPE != ‘D’;

The Cross Reference table does not have the ability to hold historical records as it does not have a logical status code attribute. Therefore, to capture the historical records, you will need to create an archive table which is populated when records are either deleted or updated.

To apply the COU to the Cross Reference table (with archiving), the following code can be used:

The following command creates an archive table of records which are being updated and deleted from the existing Cross Reference table. If this table already exists, you can simply use INSERT INTO rather than CREATE TABLE:

CREATE TABLE ABP_XREF_ARCHIVE AS SELECT * FROM ABP_REF WHERE XREF_KEY IN (SELECT DISTINCT XREF_KEY FROM ABP_XREF_COU WHERE CHANGE_TYPE != ‘I’);

The following command then deletes the records from the existing table that are either updates or deletes:

DELETE FROM ABP_XREF WHERE XREF_KEY IN (SELECT DISTINCT XREF_KEY FROM ABP_XREF_COU WHERE CHANGE_TYPE != ‘I’);

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

INSERT INTO ABP_XREF SELECT * FROM ABP_XREF_COU WHERE CHANGE_TYPE != ‘D’;