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

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

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.

Classification	Record 1	Record 2	Classification	Updated record	Record 2	Classification	COU record
Record identifier	32	32	Record identifier	32	32	Record identifier	32
Change type	I	I	Change type	U	I	Change type	U
Pro order	922371	922372	Pro order	922500	922372	Pro order	922500
UPRN	100062645004	100062645004	UPRN	100062645004	100062645004	UPRN	100062645004
Class key	1715C000002050	1715C802457028	Class key	1715C000002881	1715C802457028	Class key	1715C000002881
Classification code	U	CS	Classification code	CR08	CS	Classification code	CR08
Class scheme	AddressBase Premium classification scheme	VOA Primary Description	Class scheme	AddressBase Premium classification scheme	VOA Primary Description	Class scheme	AddressBase Premium classification scheme
Scheme version	1.0.0	1.0.0	Scheme version	1.0.0	1.0.0	Scheme version	1.0.0
Start date	2011-12-01	2010-03-16	Start date	2011-12-01	2010-01-16	Start date	2011-12-01
End date	N/A	N/A	End date	N/A	N/A	End date	N/A
Last update	2011-12-01	2010-08-12	Last update date	2013-05-04	2010-08-12	Last update	2013-05-04
Entry date	2011-12-01	2010-03-16	Entry date	2011-12-01	2010-03-16	Entry date	2011-12-01

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:

Classification	Archive record
Record identifier	32
Change type	I
Pro order	706838
UPRN	116000665
Class key	9055C000081107
Classification code	CL10RE
Class scheme	AddressBase Premium classification scheme
Scheme version	1.0.0
Start date	2011-12-01
End date	N/A
Last update date	2011-12-01
Entry date	2011-12-01

When the updated or deleted records are moved into an archive table, the end date may not always be populated, as seen in Table 4. If this is the case, users may wish to consider adding an end_date (which could be based on the epoch date that it was archived) as shown in Table 5. Adding an end date to an updated or deleted record will enable querying for a particular timeframe.

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

Classification	Archive record
Record identifier	32
Change type	I
Pro order	706838
UPRN	116000665
Class key	9055C000081107
Classification code	CL10RE
Class scheme	AddressBase Premium classification scheme
Scheme version	1.0.0
Start date	2011-12-01
End date	2013-05-04
Last update date	2011-12-01
Entry date	2011-12-01

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.

LPI	Record	COU Record
Record identifier	24	24
Change type	I	U
Pro order	478857	478857
UPRN	100000527208	100000527208
LPI key	4520L000005174	4520L000005174
Language	ENG	ENG
Logical status	1	8
Start date	‘2001-03-23’	‘2001-03-23’
End date		‘2013-04-24’
Last update date	‘2010-05-21’	‘2013-04-24’
Entry date	‘2001-03-23’	‘2001-03-23’
SAO start number
SAO start suffix
SAO end number
SAO end suffix
SAO text
PAO start number
PAO start suffix
PAO end number
PAO end suffix
PAO text	‘SITE OF FORMER MISER NETHAULERS’	‘FORMER SITE OF MISER NETHAULERS’
USRN	36815950	36815950
USRN match indicator	1	1
Area name
Level
Official flag

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’;