Working with COU data
Last updated
Last updated
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.
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 area of interest (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.
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.
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.
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:
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
Insert the new updated records and the newly inserted records
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
The following command then deletes the records from the existing table which are either updates or deletions:
The following command then inserts the new insert records and the new updated records into the live BLPU 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:
Insert the new update records and the new insert records:
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:
The following command then deletes the records from the existing table that are either updates or deletions:
The following command then inserts the new insert records and the new updated records into the Classification table:
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 |
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:
Insert the new updated records and the newly inserted records:
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
:
The following command then deletes the records from the existing table that are either updates or deletions:
The following command then inserts the new insert records and the new updated records into the Organisation 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:
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:
Insert the new updated records and the new inserted records:
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
:
The following command then deletes the records from the existing table that are either updates or deletions:
The following command then inserts the new insert records and the new updated records into the Delivery Point Address 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:
Insert the new updated records and the new inserted records:
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
:
The following command then deletes the records from the existing table which are either updates or deletions:
The following command then inserts the new insert records and the new updated records into the LPI table:
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 |
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:
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:
Insert the new updated records and the new inserted records:
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
:
The following command then deletes the records from the existing table that are either updates or deletions:
#The following command then inserts the new insert records and the new updated records into the Street 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:
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:
Insert the new updated records and the new inserted records:
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
:
The following command then deletes the records from the existing table that are either updates or deletions:
The following command then inserts the new insert records and the new updated records into the Street 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:
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:
Insert the new records and the updated records:
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
:
The following command then deletes the records from the existing table that are either updates or deletes:
The following command then inserts the new insert records and the updated records: