Creating a single-line or multi-line address
Last updated
Last updated
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.
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).
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.
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.
| Delivery Point Address component | Type |
|---|---|
DEPARTMENT_NAME | Character |
ORGANISATION_NAME | Character |
SUB_BUILDING_NAME | Character |
BUILDING_NAME | Character |
BUILDING_NUMBER | Integer |
PO_BOX_NUMBER | Integer |
DEPENDENT_THOROUGHFARE (or WELSH_DEPENDENT_THOROUGHFARE) | Character |
THOROUGHFARE (or WELSH_THOROUGHFARE) | Character |
DOUBLE_DEPENDENT_LOCALITY (or WELSH_DOUBLE_DEPENDENT_LOCALITY) | Character |
DEPENDENT_LOCALITY (or WELSH_DEPENDENT_LOCALITY) | Character |
POST_TOWN (or WELSH_POST_TOWN) | Character |
POSTCODE | Character |
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:
| Delivery Point Address component | Example |
|---|---|
DEPARTMENT_NAME | CUSTOMER SERVICE DEPARTMENT |
ORGANISATION_NAME | JW SIMPSON LTD. |
SUB_BUILDING_NAME | UNIT 3 |
BUILDING_NAME | THE OLD FORGE |
BUILDING_NUMBER | 7 |
PO_BOX_NUMBER | |
DEPENDENT_THOROUGHFARE | RICHMOND TERRACE |
THOROUGHFARE | MAIN STREET |
DOUBLE_DEPENDENT_LOCALITY | HOOK |
DEPENDENT_LOCALITY | WARSASH |
POST_TOWN | SOUTHAMPTON |
POSTCODE | SO99 9ZZ |
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:
Delivery Point Address component | Data content | Formatted output |
ORGANISATION_NAME | ‘JWS CONSULTING’ | JWS CONSULTING |
PO_BOX_NUMBER | 5422 | PO BOX 5422 |
THOROUGHFARE | ‘HIGH STREET’ | HIGH STREET |
POST_TOWN | ‘SPRINGFIELD’ | SPRINGFIELD |
POSTCODE | ‘SP77 0SF’ | SP77 0SF |
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:
Delivery Point Address component | Data content | Formatted output |
DEPARTMENT_NAME | null | |
ORGANISATION_NAME | ‘TM MOTORS’ | TM MOTORS |
SUB_BUILDING_NAME | null | |
BUILDING_NAME | ‘THE OLD BARN’ | THE OLD BARN |
BUILDING_NUMBER | 0 (or null) | |
PO_BOX_NUMBER | 0 (or null) | |
DEPENDENT_THOROUGHFARE | null | |
THOROUGHFARE | ‘HORSHAM LANE’ | HORSHAM LANE |
DOUBLE_DEPENDENT_LOCALITY | null | |
DEPENDENT_LOCALITY | null | |
POST_TOWN | ‘HORSHAM’ | HORSHAM |
POSTCODE | ‘RH12 1EQ’ | RH12 1EQ |
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.
Examples of SQL logic to create a single-line Delivery Point Address are on our GitHub repository, which incorporates the following elements:
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.
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.
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.
For a full description of PAOs and SAOs, and the complete set of AddressBase fields, please refer to the relevant AddressBase Premium Technical Specification.
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.
| Table | Geographic Address Component |
|---|---|
Organisation | ORGANISATION |
LPI | SAO_TEXT |
LPI | SAO_START_NUMBER |
LPI | SAO_START_SUFFIX |
LPI | SAO_END_NUMBER |
LPI | SAO_END_SUFFIX |
LPI | PAO_TEXT |
LPI | PAO_START_NUMBER |
LPI | PAO_START_SUFFIX |
LPI | PAO_END_NUMBER |
LPI | PAO_END_SUFFIX |
Street Descriptor | STREET_DESCRIPTION |
Street Descriptor | LOCALITY |
Street Descriptor | TOWN_NAME |
Street Descriptor | ADMINISTRATIVE_AREA* |
BLPU | POSTCODE_LOCATOR |
*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.
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).
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:
| Attribute | Example 1 | Example 2 | Example 3 | Example 4 |
|---|---|---|---|---|
PAO_START_NUMBER | 1 | 1 | 1 | 1 |
PAO_START_SUFFIX | A | A | ||
PAO_END_NUMBER | 5 | 5 | ||
PAO_END_SUFFIX | C | |||
Rendered PAO range | 1 | 1A | 1-5 | 1A-5C |
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:
| Attribute | Example 1 | Example 2 | Example 3 | Example 4 |
|---|---|---|---|---|
PAO (number string) | 1 | 1A | 1A | |
PAO (text) | Rose Cottage | Rose Cottage | ||
Rendered PAO (showing street name location) | 1 <street> | 1A <street> | Rose Cottage, 1A <street> | Rose Cottage, <street> |
For primary addressable objects (PAOs), there will always be either a text entry or a numeric/range entry or both. This is not the case for SAOs, which may be entirely absent for a given address.
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:
Administrative area not included | 34, CROW LANE, RAMSBOTTOM, BL0 9BR |
Administrative area included (BURY) | 34, CROW LANE, RAMSBOTTOM, BURY, BL0 9BR |
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:
| Geographic Address Component | Example |
|---|---|
ORGANISATION | JW SIMPSON LTD |
SAO_TEXT | THE ANNEXE |
SAO (number / range string)* | 1A |
PAO_TEXT | THE OLD MILL |
PAO (number / range string)* | 7–9 |
STREET_DESCRIPTION | MAIN STREET |
LOCALITY | HOOK |
TOWN_NAME | WARSASH |
ADMINISTRATIVE_AREA | SOUTHAMPTON |
POSTCODE_LOCATOR | SO99 9ZZ |
*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.
| Delivery Point Address Component | Data content | Formatted output |
|---|---|---|
PAO_TEXT | ‘HIGHBURY HOUSE’ | HIGHBURY HOUSE |
STREET_DESCRIPTION | ‘HIGH STREET’ | HIGH STREET |
TOWN_NAME | ‘SOUTHAMPTON’ | SOUTHAMPTON |
ADMINISTRATIVE_AREA | ‘SOUTHAMPTON’ | |
POSTCODE_LOCATOR | ‘SO77 0SF’ | SO77 0SF |
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.
| Delivery Point Address Component | Data content | Formatted output |
|---|---|---|
ORGANISATION | ‘TM MOTORS’ | TM MOTORS |
SAO_TEXT | null | |
SAO (number / range string)* | null | |
PAO_TEXT | ‘THE OLD BARN’ | THE OLD BARN |
PAO (number / range string)* | ‘1’ | 1 |
STREET_DESCRIPTION | ‘HORSHAM LANE’ | HORSHAM LANE |
LOCALITY_NAME | null | |
TOWN_NAME | ‘HORSHAM’ | HORSHAM |
ADMINISTRATIVE_AREA | ‘HORSHAM’ | * Duplicate name omitted |
POSTCODE_LOCATOR | ‘RH12 1EQ’ | ‘RH12 1EQ’ |
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.
Example SQL logic to create a single-line Geographic Address can be found on our GitHub repository, which incorporates the following elements:
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).
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
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.
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?”.
The table below offers guidance on what status filters should be considered. Please see the AddressBase Premium technical specification on our website for more information about each of these attributes.
| Status attributes | Table | Use | Values |
|---|---|---|---|
LOGICAL_STATUS | BLPU | Describes where a land or property unit is in its lifecycle. | 1 = Approved 6 = Provisional 8 = Historical |
LOGICAL_STATUS | LPI | Describes where an address is in its lifecycle. | 1 = Approved 3 = Alternative 6 = Provisional 8 = Historical |
BLPU_STATE_CO DE (optional) | BLPU | Informs the user what physical state the land or property is in (for example, ‘under construction’, ‘in use’, ‘demolished’). | 1 = Under construction 2 = In use 2 = Unoccupied 4 = No longer existing 6 = Planning permission granted Null= Unknown or N/A |
RPC_CODE | BLPU | To ascertain how accurate the coordinate is. Use in conjunction with the postcode_locator field to understand the accuracy of the address’ position. | 1 = Visual centre 2 = General internal point 3 = SW corner of 100m grid ref 4 = Start of referenced street 5 = Postcode unit point 9 = Centre of Local Authority area |
ADDRESSBASE_ POSTAL | BLPU | This field can be used to limit your records based on whether they are capable of receiving mail or not. | D = A record which is linked to PAF C = A record which is postal and has a parent linked to PAF L = A record identified as postal via Local Authority information N = Not a postal address |
LANGUAGE | LPI STREET_DESCRIPTOR | This information can be used to limit your records based on the language. | ENG = English CYM = Welsh GAE = Gaelic |
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).
