Supply options

DVD supply for an area of interest

When you receive an order via hard media (DVD), the following files will be supplied if the supply is not a Managed Great Britain Set (MGBS):

• Data

• Doc

• Order_Details.txt

Within the Data directory, data files will be found in their compressed format.

A text file called Label Information can be found within the Doc directory. This file is a replica of the information on the DVD should you need to reproduce or reprint this.

The Order_Details.txt provides information about the supply, including supply type, format, dates of order, currency and change. It also contains a list of all the zipped folders contained in the Data directory.

DVD supply of Managed Great Britain Sets

The following files will be supplied when you receive an order of a Managed Great Britain Set (MGBS) via hard media (DVD):

• Data

• Doc

• Order_Details.txt

Within the Data directory, data files will be found in their compressed format.

A text file called Label Information can be found within the Doc directory. This file is a replica of the information on the DVD should you need to reproduce or reprint this.

The Order_Details.txt file provides information about the supply, including supply type, format, dates of order, currency and change. It also contains a list of all the zipped folders contained in the Data directory.

Secure File Transfer Protocol

Download

The AddressBase Premium and AddressBase Premium Islands products are available as online downloads for customers who have signed up to the Public Sector Plan on the OS Data Hub. From October 2021, Premium Plan members will be able to download AddressBase Premium and AddressBase Premium Islands data from the OS Data Hub. Data can be downloaded in various formats.

File naming

The data is supplied as chunked files that cover your selected area. These files are named according to the convention shown in the following sub-sections.

When you open your data, you will see a series of zip folders:

Non-geographic chunks

For a MGBS supply of CSV and GML, you will see a single zip folder when you receive your download data. On opening the data link folder, you will see a series of separate unzipped files.

For example:

• AddressBasePremium_FULL_2011-07-29_001_csv.zip (full supply of CSV) or

• AddressBasePremium_COU_2011-07-29_001_gml.zip (COU supply of GML)

For a MGBS supply of GeoPackage, you will see a single zip folder when you receive your download data. On opening the data link folder, you will see a single unzipped file.

For example:

• AddressBasePremium_FULL_2011-07-29_001_gpkg.zip (full supply of GKPG)

Geographic chunks

When you receive your geo-chunked download data for CSV and GML, you will see a series of zipped folders on opening the data.

For example:

• AddressBasePremium_FULL_2011-07-29_TQ2020_csv.zip (full supply of CSV) or

• AddressBasePremium_COU_2011-07-29_TQ2020_gml.zip (COU supply of GML)

When you receive your geo-chunked download data for GKPG, you will see a single zipped folder on opening the data.

For example:

• AddressBasePremium_FULL_2011-07-29_001_gpkg.zip (full supply of GKPG)

Unzipping the data

The GML, GKPG and CSV data are supplied in a compressed form (ZIP). Some software can access these files directly, while other software will require it to be unzipped.

To unzip the zipped data files (.zip extension), you can use an unzipping utility included with most modern operating systems. Alternatively, open-source zipping/unzipping software can be downloaded from the Internet such as 7-Zip.

When the files are unzipped, they are ready for use and will appear as follows:

Non-geographic chunks

• AddressBasePremium_FULL_2020-02-11_001.csv

• AddressBasePremium_ISL_FULL_2020-02-18_001.gml

• AddressBasePremium_FULL_2020-02-18_001.gpkg

Geographic chunks

• AddressBasePremium_2011-07-29_NC4040.csv

For GKPG supply, one GKPG will be supplied which will contain all the tiles:

• AddressBasePremium_FULL_2020-02-18_001.gpkg

AddressBase Premium data is supplied as comma-separated values (CSV), Geography Markup Language (GML) and GeoPackage (GKPG).

This guide shows you how to get started with AddressBase Premium and includes the following sections:

• Prerequisites

• Data supply

• Working with CSV data

• Working with GPKG data

• Working with GML data

• Working with COU data

• Creating a single-line or multi-line address

• Searching for addresses

System requirements

AddressBase Premium is an addressing gazetteer offering full property life cycle information that can be used within GIS and database systems. For details of Ordnance Survey’s licensed partners, who can incorporate the AddressBase products into their systems, please .

Ordnance Survey does not recommend specific suppliers or software products as the most appropriate system will depend on many factors, for example, the amount of data being taken, resources available within the organisation, the existing and planned information technology infrastructure, and the applications that AddressBase products can be used for.

However, as a minimum, the following elements will be required in any system:

• A means of reading the data, either in its native format, or by translating it into a file format or for storage in a database.

• A means of storing and distributing the data, perhaps in a database or through a web-based service.

• A way of visualising and querying the data, typically a GIS.

Backup provision of the products

You are advised to copy the supplied data to a backup medium.

Typical data volumes

For reading purposes, it is recommended to store the data on a single hard disc. This will speed up the ability of your computer to read the data.

The table below lists the unzipped file sizes for the GB full supply of the two products.

Accessing GKPG data via GIS software

This section provides step-by-step instructions on how to access the GKPG format of AddressBase Premium products via commonly used GIS software, including ArcGIS Pro, ArcGIS Desktop, and QGIS.

GKPG (.gpkg) is an open, non-proprietary, platform-independent and standards-based data format for geographic information systems (GIS), as defined by the Open Geospatial Consortium (OGC). It is designed to be a lightweight format that can contain large amounts of varied and complex data in a single, easy to distribute and ready to use file. GKPG is natively supported by numerous software applications.

Loading GKPG into a database

The following sub-section provides step-by-step instructions on how to load the AddressBase Premium GKPG into PostgreSQL using GDAL / Command Prompt.

A common requirement for customers using the AddressBase products is to search for properties using full or partial addresses. Address searches may return a large number of addresses, a short list of possibilities, a single match or no results, depending on the search criteria.

There are many methods of implementing an address search, from free text queries through to structured address component searches. This section will step through two such approaches that may be used when working with AddressBase Premium products: free text search and structured component search.

Free text search

One type of search implementation involves a single ‘search engine’ style text box, into which a user can type all or some of an address. For example:

In this scenario, the user can choose to type anything in Find address, which may be just one component of an address (for example, a postcode, street name or building name), several parts of an address (for example, street name + town name, house name + postcode, etc.) or even (rarely) a complete address.

There may or may not be commas between search items, or they may have been entered with or without capitalised letters, etc. In short, with this search method, there is no structure to the user input and the search methodology must be designed with this in mind.

Structured component search

The other common type of implementation for address searches involves entering search criteria in a structured way (for example, with a different text box for each major address component).

This method guides the user to enter known components of an address and also creates a predictable user input structure around which to build a search function. While generally simpler to use and implement, it can be less user-friendly, particularly in cases where it is not obvious which box to type an address component into; for example, is Richmond Terrace a building name or a street?

The following sub-sections suggest how to implement the two search methods described above. Both methods should be used alongside the instructions on formatting single address labels given in Section 11.

Understanding the different addresses available

The table below outlines what these addresses are and how to access them in the products. It provides a breakdown of the location and definition of Delivery Point addresses and the four categories of Geographic Addresses available in AddressBase Premium products.

The search operation

An address search operation typically requires two stages of interaction from a user, and several processing steps from the underlying IT system. These steps are summarised in the following diagram:

The second user interaction can be omitted if there is only one result returned from the query. In almost all cases, there should be an option to ‘search again’ at the second and third stages in case no results are returned, or if none of the options shown is the required address.

Of course, different applications require different approaches; however, the general principles of the above process apply in all cases where an address is searched for based on user-entered criteria.

Generating a search query from structured user input

Within an interface that accepts structured user input for an address search, it is necessary to ‘map’ the fields presented to the user with those found within the AddressBase Premium products. In particular, any query will need to test multiple fields for a given input and will need to combine result sets from the two different address formats (Delivery Point Address and Geographic Address) in order to produce the most complete result set.

Generally, a search form will describe a simplified view of an address in order to keep the user interface tidy and intuitive. Users may be given a set of text boxes to fill in, generally including building name, building number, street name, locality name, town name and postcode. The relationships between some common search fields and the fields found in AddressBase Premium and AddressBase Premium Islands are as follows:

The table below shows the relationships between some common search fields and the fields found in AddressBase Premium and AddressBase Premium Islands.

The above mapping is an example only and it is possible to break down the search fields differently, in which case a different mapping would be required. The important thing is to consider all possibilities for how data might be recorded. For example, a business name can sometimes appear as an organisation name or a building/PAO name, depending on circumstances, so both must be checked when creating a search query.

Numbers need to be handled very carefully due to the presence of suffixes and ranges. There are two options for structuring the search input in these cases:

1. A single ‘number’ box can be used (as shown in the table above), which will then require some string manipulation to split the input into the appropriate numeric range and suffix components in order to search the Geographic Addresses; or

2. Four boxes can be provided for each number (start number, start suffix, end number and end suffix), which would then need to be combined into an appropriate string to search the Delivery Point Addresses.

Structuring the query for a structured address search

The basic rules to follow when generating a search query from structured input are as follows:

• Ignore any search boxes that are not filled in with values.

• Where a value is entered, assume that a match on at least one of the mapped fields is essential.

In SQL query terms, this means that each search term should generate a sub-query that searches each of the mapped fields (using OR), and that these sub-queries should then be combined together (using AND) into a single search query. The following SQL code illustrates this (for the Delivery Point Address search only) where a street, locality and town name have been entered by the user:

On top of this, for a complete query the two different types of addresses should be queried separately (Geographic and Delivery Point Addresses), and the two result sets should be amalgamated into a single set using a UNION. The following example builds upon the previous example to include Geographic Addresses as well as Delivery Point Addresses:

The geographic query requires four joins between the BLPU, LPI, Street Descriptor and Organisation tables in order to access all the fields required to build an address.

The SQL UNION operator will combine the two result sets, discarding any exact duplicates (retaining the exact duplicates requires the use of UNION ALL, but that is not desirable in this example).

The resulting output from this query will be a set of search results: formatted addresses along with their UPRN. Exact duplicates will be omitted, but all ‘variations’ of the same address will be outputted (one row for each variation, with the same UPRN repeated more than once potentially). It may be wise to also return the ‘logical status’ and/or ‘postal address flag’ values against each to enable further filtering (that is, to include or exclude historical addresses for example, or to restrict the results to postal addresses only).

Supporting case-insensitive queries and partial matches

A flaw in the above examples is the use of equality operators. In practice, because people do not tend to be consistent with the capitalisation of letters, the SQL ‘LIKE’ operator might work better. Depending on the nature of the application, a ‘%’ wildcard could be appended to the end of each search term to allow only the first few letters of an address component to be entered. For example:

Alternatively, if exact matches are required but case sensitivity is not, then the UPPER() or LOWER() SQL functions can be used on each side of the equals sign in comparisons (a solution that should work in all databases):

Finally, to combine all of the approaches, the following would work for maximum flexibility:

Generating a search query from unstructured user input

When offering a ‘search engine’ style search feature with just a single text box to enter search terms, a wholly different approach is required. No assumptions can be made about the order, format or style of the user input, and the data will need to be ‘indexed’ in a way that facilitates searches of this type.

Creating a search index for addresses

Search engine style searches are likely to require the creation of an additional index/lookup table for addresses. Such a table is likely to consist of just two main columns: a key value (UPRN) and a formatted address string. Additional columns may be required to allow filtering of results (such as the ‘logical status’ values, which would allow the results to be filtered on ‘approved’, ‘provisional’ and ‘historical’ statuses, for example).

The table below shows a possible address index table structure:

Note how the addresses have been formatted as a single text string with a single space between each word (although leaving commas in would do no harm). All forms of each address (both PAF and Geographic, current and historical, approved and alternative) have been added to the index, so there can be several rows with the same UPRN. To speed up complex searching, an appropriate index could be added to the Address Text field, such as a full text search index.

Structuring the query for an unstructured address search

Once a suitable search index is in place, the query itself can be put together. The basic idea is to split the user input into search terms by removing commas, double spaces and other unnecessary whitespace, and then splitting the user input at each single space, as follows:

User input: 4, High Street, Westville, wv17 Capitalised, with commas and double-spaces removed: 4 HIGH STREET WESTVILLE WV17

Split into separate search terms:

• 4

• HIGH

• STREET

• WESTVILLE

• WV17

Once the user input has been pre-processed into separate search terms, a query can be generated. The key assumption in this example will be that ALL search terms must be matched against the index table to be considered as a result. This implies a query where each value is matched using an ‘AND’ operator. In order to search the whole index, the ‘LIKE’ operator will need to be used along with a ‘%’ wildcard on either side of the search text. A suitable search query for the above example would be as follows:

This query would return all rows from the index table that contain all of the search terms, along with the appropriate UPRNs.

The table below shows how the index table would be used in the above example to return relevant results:

This result set can then be presented to the user, who can select the most appropriate record, which can then be retrieved in full using the UPRN.

Of course, in a practical implementation, the above query would need to be dynamically generated, with a separate condition added for each search term. This example is quite a strict search query that requires all search terms to be present. Many layers of complexity could be added to allow partial and ‘fuzzy’ matches, and to return confidence scores for example, but such enhancements are beyond the scope of this section.

Summary: Searching for addresses

This section introduces implementing address search functionality using AddressBase Premium products. The main points are summarised below:

• A user front-end for an address search may contain a single, search engine style text box or multiple text boxes representing different parts of an address.

• A typical address search function takes place in three stages:

  • A user enters search text.

  • A query is run, returning a set of possible matches.

  • The user selects the address of interest, and the full record is then returned.

• With a structured search interface, the addresses can be queried directly by mapping the various address fields to the text boxes supplied.

• For an unstructured (single text box) interface, it is necessary to create an index table with fully formatted address strings against each UPRN. Queries can then be run against this index table by splitting the user input into individual search terms and requiring them all to be present.

• It is possible to filter results by status, for example, ‘approved’, ‘alternative’ and ‘historical’, as well as ‘postal’ or ‘non- postal’, etc.

• Any search function should search all forms of an address (both Geographic and Delivery Point Addresses).

• Careful consideration should be given to the use of ‘fuzzy’ search algorithms (such as using wildcard or sound-alike searches).

Loading GML

This section provides step-by-step instructions on how to load the Geography Markup Language (GML) format of AddressBase Premium products into FME and a database.

GML is an XML dialect which can be used to model geographic features. It was designed by the Open Geospatial Consortium (OGC) as a means for people to share information regardless of the applications or technology that they use.

In the first instance, GML was used to overcome the differences between different GIS applications by providing a neutral file format as an alternative to proprietary formats. Because it is independent of applications, it can also be moved between databases or other types of application, which allows a wider application than just GIS data transfer.

Loading GML via a translator

Several organisations provide a loader which will translate AddressBase Premium from GML and insert the data into a database or a GIS. Due to the relational nature of AddressBase Premium, GML will not load straight into most GIS applications, meaning an external translator would be helpful to most users. If you would like to load AddressBase Premium in GML format into a GIS, please contact your vendor for more details.

For more information on Ordnance Survey Partners who provide GML loaders, please visit the .

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.

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:

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:

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:

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:

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:

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:

Delivery Point Address vs Geographic Address

The AddressBase Premium products contain a variety of data fields which allow a user to construct, for a given addressable object, different forms of an address dependent on how the address is to be used.

There are two types of address contained in the AddressBase products:

• Delivery Point Address

• Geographic Address

These two address types come from different sources and are matched together by GeoPlace.

The Delivery Point Address is sourced from Royal Mail’s Postcode Address File (PAF), which is a non- geocoded list of addresses. These addresses are used primarily as a ‘mailing list’ for postal purposes.

Geographic Addresses are maintained by contributing Local Authorities. The structure of a Geographic Address is based on the British Standard BS7666. These addresses are used to provide an accurate geographic locator for an object to aid, for example, service delivery, asset management, or command and control operations. They also represent the legal form of addresses as created under street naming and numbering legislation.

High-level data model

The AddressBase Premium data model accommodates both the Delivery Point Address and the Geographic Address by linking them using the unique property reference number (UPRN) as the key.

It is important to note the cardinality differences that the Geographic and Delivery Point Address components have with the Basic Land and Property Unit (BLPU):

• The relationship between the Delivery Point Address and the BLPU is 0..1 – 1.

• This means that the Delivery Point Address is an optional component, so a Delivery Point Address will only be created when it has been matched to the Geographic Address. Moreover, only one Delivery Point Address can be matched to a BLPU.

• The relationship between the Land and Property Identifier (LPI) and the BLPU is 1..* – 1.

• This means that the LPI component is mandatory; therefore, at least one LPI must exist for each BLPU. Moreover, there can be more than one LPI linked to a single BLPU.

Together, these differences mean that there are more Geographic Addresses in the product than there are Delivery Point Addresses, because:

• Not every BLPU has a Delivery Point (postal) Address, only those that have been matched to the Royal Mail PAF database.

• A single BLPU can have only one Delivery Point Address.

• A single BLPU can have more than one Geographic Address (because alternative and historical addresses are available in AddressBase Premium).

Background to single-line and multi-line address labels

A common requirement for customers using the AddressBase products is to build a single address label from core address elements.

There are two types of address label: single line and multi-line. The simplest label is a full address on a single line, with different elements separated by commas and spaces. This type of label is suited for displaying a full address within a tabular display, such as within an on-screen data grid or spreadsheet, or where a single-line printed address is most appropriate (such as within the text, header or footer of a letter), for example:

ROSE COTTAGE, 5 MAIN STREET, ADDRESSVILLE, LONDON, SE99 9EX

The second type of formatted address is a multi-line address label. These labels are most often used on envelopes or at the tops of letters, where different parts of an address are separated onto different lines, for example:

ROSE COTTAGE 5 MAIN STREET ADDRESSVILLE LONDON SE99 9EX

The following sections outline a methodology for structuring and layering a single address label using AddressBase Premium. The rules outlined are suggestions only and can be used for visual display of full addresses. It is strongly recommended that address components are stored in the format in which they are provided in order to allow maximum flexibility of use and derived value.

Delivery Point Address

A Delivery Point Address contains information sourced from Royal Mail (PAF). Stringent rules are used to match these addresses to the Geographic Address and assign a common UPRN to link addresses from the two addressing sources together in the data model.

To construct a single address label based purely on the Royal Mail PAF address fields, the following attributes listed in Table 7 can be used to build a Delivery Point Address label.

The following table details the Delivery Point Address components.

These address components are listed in the correct order in which they should appear on an address label. There may be a business need to replace the thoroughfare, locality and post_town attributes with the Welsh equivalent (listed in Table 7). The following examples will use the English version of these attributes.

It should be noted that most of the PAF fields are optional and may contain null values (or zero, in the cases of BUILDING NUMBER and PO BOX NUMBER). In these cases, those fields should be omitted.

The following (entirely fictional) example shows all of the PAF fields filled in (apart from the PO BOX NUMBER) and illustrates how these fields should be ordered in a single address label:

In cases where a PO BOX NUMBER is present, it will only be described in the data as an integer. In order to properly format these addresses when generating an address label, these integers should be prefixed with the text ‘PO BOX’, as shown in the following example:

Where null or empty string values exist (for character fields) or zeros or nulls (for integer fields), those fields should be entirely omitted from the output. However, the order in which the fields should be concatenated always remains the same, as shown in the following example:

Building a single-line Delivery Point Address

Building a single-line, formatted address for a Delivery Point is relatively straightforward. All the fields should be checked in the order shown previously in the tables above, and those that have values should be concatenated together into a single line. Generally, address components should be separated by a comma followed by a single space (‘, ’), although sometimes only a space is used between a building number and a thoroughfare name. You can use your preference.

• The SQL operator for concatenating text is a double pipe (‘||’).

• CASE blocks have been used to test each of the fields for null values before concatenating its contents (along with a suitable separator: either ‘, ‘ or ‘ ‘).

• The field names and table names used are illustrative and may vary between databases.

• Depending on the database schema and data loading method used, it may be necessary to test some fields for empty strings (‘’) or zero values (for integer fields) instead of, or as well as, testing for NULLs.

• If you are using PostGres (PostGIS), it might be beneficial to substitute the ‘IS NOT NULL’ with != ‘’. This should improve the overall appearance of the output.

Building a multi-line Delivery Point Address

Splitting a Delivery Point Address into multiple lines is more complicated. There are several rules to consider in order to avoid having very short lines (for example, just a building number) or very long lines within the formatted address. A summary of these rules is as follows:

• Generally, if there is a building number, it should appear on the same line as the thoroughfare (or dependent thoroughfare). If there is no thoroughfare information, the building number should appear on the same line as the first locality line.

• In cases where building numbers have been placed in the building name field due to the presence of a letter suffix (for example, ‘11A’) or a number range separator (for example, ‘3-5’), these should be detected and placed on the same line as the thoroughfare (or on the first locality line if no thoroughfare is present).

• In most other cases, the building name, if present, should appear on a separate line above the thoroughfare name or dependent thoroughfare or locality line if no thoroughfare is present.

• Similar tests should be applied to the SUB_BUILDING_NAME field: if this field contains a number, a number with a suffix or a numeric range, it should precede the building name on the same line. In most other cases, it should appear on a separate line above the building name.

Geographic Address (Local Authority Address)

The structure of a Geographic Address is based on the British Standard BS7666 and is split into a number of components. This means that in order to construct a complete address label, for example, on an envelope, database form or GIS display, the components need to be constructed according to a set of rules.

Within the AddressBase products, the core property-level address information is stored within the Primary Addressable Object (PAO) and Secondary Addressable Object (SAO) fields of the LPI table. The additional attribution required to build a full address label is maintained in the BLPU (postcode_locator), ORGANISATION (organisation) and STREET_DESCRIPTOR (street_description, locality_name, town_name, administrative_area) tables.

Constructing a single address label from the Geographic Address fields

To construct a single address label based purely on the BS7666 address fields, the following attributes listed in the table below should be used to build a Geographic Address label.

The following table details the Geographic Address components.

*ADMINISTRATIVE_AREA is optional because it is common for this field to be the same as the TOWN_NAME. Sometimes, however, this field will help users construct a more complete address.

These address components are listed in the correct order in which they should appear on an address label. There may be a business need to build the address using the alternate language for SAO_TEXT, PAO_TEXT and Street Descriptor entries. This can be achieved by filtering on the language field of the LPI and Street Descriptor tables. The same order as above would be applicable.

Linking address components

The LPI table includes the PAO and SAO fields. However, in order to obtain the rest of the address, it is necessary to join the LPI table to the Street Descriptor table to pick up the street name, locality and town information (using the USRN as the key), and also to the Organisation and BLPU tables (using the UPRN as the key) to pick up the organisation names and postcodes, respectively.

The diagram below shows the links that need to be made in order to build a full Geographic Address from the different BS7666 components in AddressBase Premium.

Using the LPI table as a starting point, the remaining address components can be picked up using table joins to the other tables on UPRNs and USRNs. Note that there can be more than one LPI for each UPRN, so if only one address is required per BLPU, the LPI with logical_status = 1 (approved) should be selected (there can be only one approved LPI per BLPU).

Rendering SAOs and PAOs

When building a single address label, it may be necessary to concatenate the various SAO fields and PAO fields together respectively. These fields contain any property names, numbers, number ranges or suffixes that apply to an address.

A PAO number / range string should be constructed from the PAO_START_NUMBER, PAO_START_SUFFIX, PAO_ END_NUMBER and PAO_END_SUFFIX fields, as illustrated in the following table:

Similarly, a SAO number / range string should be constructed from the SAO_START_NUMBER, SAO_START_ SUFFIX, SAO_END_NUMBER and SAO_END_SUFFIX fields.

In addition to the numeric range fields described above, there are also PAO_text and SAO_text fields. These fields may be populated instead of, or as well as, the numeric range fields. In both cases, if both text and a numeric range string are present, the text should appear before the numeric range in any formatted address, as shown in the following table:

Street description, town, locality and administrative area names

The street description and administrative area names are always present, while the locality name and town name may be empty.

The ADMINISTRATIVE_AREA field always contains a value; however, this value will not always enhance an address, but in some cases it will. In particular, check that it is not the same as the value in the TOWN_NAME field, as is often the case. The following table shows an example where the administrative area name (in this case, BURY) has been included and excluded from a single-line address:

In other cases, the administrative area name will simply contain the local authority name, which would not traditionally form part of a single or multi-line address but can be included to add additional information to an address label. Its inclusion is largely down to business requirements or personal preference; however, it may also be useful to 'de-duplicate' some Geographic Addresses.

The following (entirely fictional) example shows all of the BS7666 Geographic Address fields filled in and illustrates how they should be ordered in a single address label.

The table below details the Geographic Address formatting:

*The number/range strings are built from the relevant PAO / SAO start_number, start_suffix, end_number and end_suffix fields, as described above, and formatted as character strings.

Where an administrative area matches the town name, it should always be omitted.

Where null or empty string values exist (for character fields) or zeros or nulls (for integer fields), those fields should be entirely omitted from the output; however, the order in which the fields should be concatenated always remains the same.

Building a single-line Geographic Address

Building a single-line, formatted address for a Geographic Address is slightly more complicated than for a Delivery Point Address due to the need to pre-format the SAO and PAO number/range strings and join tables together. However, once this is done, the process is largely the same as before: the calculated fields should be checked in the order shown previously in the table above, and those that have values should be concatenated together into a single line. Generally, address components should be separated by a comma followed by a single space (‘, ’), although sometimes only a space is used between a PAO number/range string and a street description. This is down to personal preference.

• The SQL operator for concatenating text is a double pipe (‘||’).

• CASE blocks have been used to test each of the fields for null values before concatenating its contents (along with a suitable separator – either ‘, ‘ or ‘ ‘).

• The field names and table names used are illustrative and may vary between databases.

• Depending on the database schema and data loading method used, it may be necessary to test some fields for empty strings (‘’) or zero values (for integer fields) instead of, or as well as, testing for NULLs.

• If you want no duplicate UPRNs to be returned an additional DISTINCT line needs to read DISTINCT(l.UPRN).

Building a multi-line Geographic Address

Splitting a Geographic Address into multiple lines is more complex. As with Delivery Point Addresses, there are several rules to consider in order to avoid having very short lines (for example, just a building number) or very long lines within the formatted address.

A summary of these rules is as follows:

• Generally, if there is a PAO number/range string, it should appear on the same line as the Street Description. 11A MAIN STREET

• If there is a PAO_text value, it should always appear on the line above the Street Name (or on the line above the <PAO number string> + <Street Name> where there is a PAO number/range). PAO_text only ROSE COTTAGE, MAIN STREET PAO_text and PAO number or range ROSE COTTAGE, 11A MAIN STREET

• If there is a SAO_text value, it should appear on a separate line above the PAO_text line (or the PAO number/range + street line where there is no PAO_text value). SAO_text value only, with PAO_text value only THE ANNEXE, ROSE COURT,

  MAIN STREET SAO_text value only, with PAO number/range only THE ANNEXE, 11A MAIN STREET

• If there is a SAO number/range value, it should be inserted either on the same line as the PAO_text (if there is a PAO_text value), or on the same line as the PAO number/range + Street Name (if there is only a PAO number/range value and no PAO_text value). If there are both PAO_text and a PAO number/range, then the SAO number/range should appear on the same line as the PAO_text, and the PAO number/range should appear on the street line. SAO number/range value only, and PAO_text value only 1A ROSE COURT, MAIN STREET SAO number/range value only, and PAO number/range value only 1-3, 11A MAIN STREET SAO number/range value only, and both PAO_text and PAO number/range values 1A ROSE COURT,

  11A MAIN STREET

• If there is a SAO_text value, it should always appear on its own line. SAO_text value only with PAO_text only THE ANNEXE, ROSE COTTAGE, MAIN STREET SAO_text and SAO number/range and PAO_text and PAO number/range WARDEN’S FLAT, 1A ROSE COURT,

  11A MAIN STREET

• If there is an Organisation Name, it should always appear alone as the top line of the address. Organisation Name along with all PAO + SAO fields COTTAGE INDUSTRY LTD, THE ANNEXE, 1A ROSE COURT, 11A MAIN STREET

• The Locality (if present) should appear on a separate line beneath the Street Description, followed by the Town Name on the line below it. If there is no Locality, the Town Name should appear alone on the line beneath the Street Description. Locality and Town Name present [first part of address, formatted as described above] MAIN STREET,

  HIGHFIELD,

  SOUTHAMPTON Town Name only [first part of address, formatted as described above] HIGH STREET,

  SOUTHAMPTON

• If the Administrative Area name is required and it is not a duplicate of the Town Name, it can optionally be included on a separate line beneath the Town Name. Administrative Area name included [first part of address, formatted as described above] MAIN STREET, WINDSOR, ROYAL BOROUGH OF WINDSOR AND MAIDENHEAD

• Finally, the Postcode Locator should be inserted on the final line of the address. With Postcode_Locator on final line [first part of address, formatted as described above] HIGH STREET, MILTON, ML99 0WW

Advice on creating mailing lists using AddressBase Premium products

It's possible to create mailing lists using the AddressBase Premium and AddressBase Premium Islands products (you can also create them using AddressBase Plus and AddressBase Plus Islands). Given that the AddressBase Premium products contain two different types of address, a decision needs to be made on whether to use the Geographic or Delivery Point Addresses, or a mixture.

The following two options should be considered:

• Use Delivery Point Addresses whenever they are available, and when they are not, use a Geographic Address.

• Use Geographic Addresses in all cases.

Depending on business requirements, in some user interfaces, it may be worth considering displaying both forms of an address, since this will provide the maximum information available about a given UPRN.

‘Mixing and matching’ components from the two different forms of address into a single address label is not recommended as this is likely to cause confusion in some instances.

Address status

When building your query to extract a mailing list, it is important that you consider filtering your results based on the address status and type. The status of an address is often something that needs to be considered when working with address data. Questions need to be answered before AddressBase Premium can be used effectively, such as “Is the addressable object in planning, being constructed, current, demolished or accurately positioned?”.

Other filters available in AddressBase Premium products for use in addressing labels

AddressBase Premium is a rich addressing dataset that contains a wealth of other attributes that could be used in conjunction with address labels. For example:

• Classification can be used to target certain types of property.

• OS MasterMap Topography TOID cross references can be used to link address labels to Topographic objects and viewed in a GIS for Great Britain (AddressBase Premium Islands does not has OS MasterMap Topography TOID cross references).

Preparing the CSV data

The AddressBase Premium CSV and AddressBase Premium Islands CSV are provided with records for various tables incorporated into a single CSV. Depending on the size of the area of interest (AOI) or if you require a full supply of GB or Islands data, there may be multiple CSV files supplied. These CSV files need to be split into individual records and tables.

There are multiple methods for splitting AddressBase Premium CSV files by the record identifiers. The following sub-sections step you through using either Gawk or Python for splitting the data and appending the header file.

Loading CSV into GIS software

This section provides step-by-step instructions on how to load the CSV format of AddressBase Premium products into commonly used GIS software, including ArcGIS Pro, ArcGIS Desktop, MapInfo, QGIS and CadCorp SIS Desktop.

It is assumed that you will have followed the steps in before you attempt to load the data. If this pre-processing is not carried out, there may be errors with loading.

Loading CSV into a database

This section provides step-by-step instructions on how to load the CSV format of AddressBase Premium products into some commonly used databases, including PostgreSQL, Oracle and Microsoft SQL Server databases.

Considerations

Software dependencies

It should be noted that ArcMap, ArcGIS Desktop and ArcGIS Server software do not support the BIGINT/NUMBER data type as an Object ID. Bear this in mind if the expectation is to use this data type directly with these ESRI products. An alternative method to facilitate using ESRI software is to store this data as a string and add a new Serial ID to act as the Object ID.

If you are loading AddressBase data directly into a database, you may need to increase the column length to accommodate language characters such as '^'. Some databases treat this as an additional character and, therefore, if you define the column length according to our specification, there is a chance the load may fail. Please bear in mind such adjustments may be required depending on the database you use to load the data.

UPRN deletions

It is important to note that Primary Keys on all tables (for example, UPRN on the BLPU table) are valid upon a data load. If a Delete is issued for a Primary Key, this doesn’t mean that Primary Key will not reappear in subsequent supplies.

There are a number of reasons this may happen:

• The record has moved in location more than once, moving it out of your AOI (therefore, the record is deleted) but then back into your AOI in the future. This would also occur if you altered your AOI.

• A record has failed data validation upon a change being made. This can result dependent on the change being made in the record being deleted and then reintroduced when the error is fixed by the data supplier.

If a unique property reference number (UPRN) is deleted, it will not be reallocated to a different property, and it therefore remains the unique identifier for a property.

Loading CSV data instructions