Mosaic displays authority information, such as Islington, for all addresses retrived via AddressBase. Additionally, for addresses within the Local Authority, we display a ward code, for example Tollington.

The accuracy of this information depends on having the appropriate reference data entries. We supply this information in the form of a small program, which loads the required entries into the REFERENCE_DATA table.

This utility loads ward codes for most authorities. There are instructions at the end of this document to remove the ward codes that are not required locally.

Installation instructions

Request the installation files

Before you begin, you'll need the installation files. Simply raise a new case and reference this article, and we'll securely send the files to you.

Windows installation

Once you receive your files, you can start the installation process.

Copy the wardcodes.zip to a location on your server.
Unzip the file, then change to the bin directory within wardcodes.
```
cd wardcodes/bin
```
Then run the program.
```
wardcodes.bat
```

Linux and Solaris installation

For Linux and Solaris, follow these steps.

Use an unzip utility on your files, then change to the bin directory within wardcodes.
```
cd wardcodes/bin
```
Execute permission may need to be set using the chmod command.
```
chmod u+x wardcodes

./wardcodes
```

The wardcodes utility asks for details required to connect to the database and then populate the REFERENCE_DATA table in the Mosaic schema or database with ward code and authority information.

Graphics enabled environments

If you're running in a graphics enabled environment, it displays the following dialogue boxes. This is an example from a Linux console.

[oracle@prod-app bin]$ ./wardcodes

Please enter the db type SQLServer/Oracle:
Oracle

Please enter the ORACLE_SID or SERVICE_NAME.
mosprod

Please enter the database host name or IP address.
prod-db

Please enter the database port number.
1542

Please enter the mosaic database username.
fw

Please enter the mosaic database password:


count: 5000
count: 10000
Updated 11736 ward code records.
Updated 416 paf authority records.

Remove unwanted codes

After loading the new ward codes and authorities, you'll be left with two sets of PAF_AUTHORITY and WARD_CODES entries:

The old style codes e.g. ISLINGTON/AUGM
The new style codes e.g. E09000019/E05000380

We recommend migrating existing addresses which use the old-style codes to use the new GSS codes - contact the Service Desk for advice on this. To remove the old reference data please follow the steps below:

Migrate Addresses

To migrate addresses, just raise a new case and we’ll securely send the files to you directly. Once you’ve received the files, follow the steps below:

Copy the MigrateAddresses.zip to a location on your server.
Unzip the file. Once unzipped change to the MigrateAddresses directory.

Run on Windows

To run the program on Windows simply type:

cd MigrateAddresses
Migrate.bat

Run on Linux and Solaris

To run the program on Linux/Solaris simply type:

./Migrate.sh

The program will ask for details required to connect to the database and then update the ADDRESSES table in the Mosaic schema or database.

If you are running in a graphics enabled environment, dialogue boxes will be displayed.

Here's a sample run in a Linux console-only environment. These questions are the same for Windows.

[oracle@prod-app MigrateAddress]$ ./Migrate.sh

Please enter the db type SQLServer/Oracle:
Oracle

Please enter the ORACLE_SID or SERVICE_NAME.
mosprod

Please enter the database host name or IP address.
prod-db

Please enter the database port number.
1542

Please enter the mosaic database username.
fw

Please enter the mosaic database password:

Rollback? Enter Y if you want to revert data changes. (This option causes the data to be rolled back to GSS ward codes.)

Remove old style authorities

Prior to AddressBase the authorities were stored using the upper case of the description as the code, for example, ISLINGTON = Islington.

With the new GSS format ward codes loaded these entries are redundant.

You can remove the old entries using the following SQL.

Oracle

DELETE FROM reference_data WHERE ref_domain = 'PAF_AUTHORITY' AND NOT REGEXP_LIKE(ref_code,'^[A-Z][0-9]{8}');

SQL Server

DELETE FROM reference_data WHERE ref_domain = 'PAF_AUTHORITY' AND ref_code NOT LIKE '[A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Remove old style ward codes

The older PAF dataset used a different format for ward codes. These used a four letter code, for example, AUGN for Tollington.

With the new GSS format ward codes loaded these entries are redundant.

You can remove the old entries using the following SQL.

Oracle

DELETE FROM reference_data WHERE ref_domain = 'WARD_CODES' AND REGEXP_LIKE(ref_code,'[A-Z]{4}');

SQL Server

DELETE FROM reference_data WHERE ref_domain = 'WARD_CODES' AND ref_code LIKE '[A-Z][A-Z][A-Z][A-Z]'

Ex-Local Authority ward codes

The import process loads all the ward codes in the country however Mosaic only displays ward codes for in-Local Authority addresses.

To remove ward codes for addresses outside your Local Authority area, follow these simple steps.

Confirm you have entries in REFERENCE_DATA data for LOCAL_PAF_AUTHORITY.
Confirm the entries use the new GSS-style code. For example E09000019/Islington.
Delete the ex-Local Authority codes using the following SQL for both Oracle and SQL Server.

DELETE FROM reference_data rd WHERE rd.ref_domain = 'WARD_CODES' AND parent_code NOT IN (SELECT ref_code FROM
 reference_data WHERE ref_domain='LOCAL_PAF_AUTHORITY')

AddressBase Epoch update - Oracle only

AddressBase Epoch update - SQL Server only

Incorrect Local Authority area allocated to postcodes on boundary

Local Authority code displays rather than the Local Authority description

Looked After Codes sent to the NHS for CP-IS