MapInfo software enables a separate browser to be viewed that contains all the postcodes related to a vertical street.

To view the vertical street lookup in MapInfo, open the file in a browser.

To join the Code_Point Polygons table and the vstreet_lookup table follow these instructions:

1. Save copies of the main Code-Point Polygons and Vertical Street Lookup tables to work on; for example, CP_Polys.TAB and vstreet_lookup.TAB. Open the new copies of the datasets in MapInfo.
2. Create a new column, of Character (8) type, in the Code-Point Polygons table (CP_Polys.TAB) as column 2. Give it a meaningful name; for example.

3. VS_Postcode (go to Table > Maintenance > Table Structure).

4. Save the table.

Create geometry for the Vertical Street Lookup table (vstreet_lookup.TAB) by joining it to the main Code-Point Polygons table using the following SQL Select query:

• SELECT CP_Polys.POSTCODE, vstreet_lookup.COL1, CP_Polys.UPRN, CP_Polys.AREA_CP, CP_Polys.obj FROM CP_Polys INNER JOIN vstreet_lookup ON CP_Polys.POSTCODE = vstreet_lookup.COL2;

1. Save a copy of the results of the selection as PC_VS_join.TAB.

2. Open the table.

1. Delete the vertical streets from the main Code-Point Polygons table, CP_Polys.TAB – use Query > Select… to search for postcode 'Like “v%” and delete:

1. Save, then pack the Code-Point Polygons table.

1. Go into Table > Maintenance > Pack Table and select Pack Both Types of Data.

1. Append the Postcode – Vertical Street join table, PC_VS_join, to the main Code-Point Polygons table, CP_Polys, to create a list of all postcodes including vertical streets (Table > Append Rows to Table).

2. Save the CP_Polys table.

• If you browse the CP_Polys table now, the rows with a ‘standard’ postcode will have a blank in the second field (VS_Postcode), but the rows that have a vertical street postcode in the Postcode (first) column should have a ‘standard’ postcode in the second field.

ESRI software enables a relationship to be created between two tables. By relating the postcode area and vertical street lookup, it is possible to identify the postcodes related to a vertical street by using the Identify tool.

1. Load the postcode area Shapefile and relevant vertical street lookup file into ESRI ArcMap.

NOTE: it may be necessary to remove the underscores in the vertical street file name for it to load successfully into ArcMap.

1. Right-click on the postcode area Shapefile, go to Joins and Relates and click on Relate.

• The Relate window will open.

1. In option 1, click on the drop-down arrow and select ‘POSTCODE’.

1. In option 2, select the vertical street lookup file.

1. In option 3, select Col2 or the vertical street ID column (this depends how you have named your columns).

1. In option 4, choose a suitable name for the relate.

1. Click OK.

1. When using the Identify tool in ESRI ArcMap, click on a vertical street polygon and expand the + signs on the left to see all the postcodes related to that vertical street polygon.

PostGIS and QGIS can be used in conjunction with each other to create a vertical street lookup table, which can be visualised and interrogated within the QGIS GIS environment alongside the spatially enabled Code-Point postcode area polygons.

PostGIS steps

1. Load both the postcode area polygons and vertical street text file into PostGIS in their own separate tables within the same schema.

2. You will now create a new vertical street lookup table within PostGIS that contains the geometry of the vertical streets, obtained from the postcode area polygons table. Because you will be using the postcode columns from both the postcode area polygons table and the vertical street table you must ensure the postcode columns are named differently. The following SQL can be used to alter the name of the postcode column within the vertical street table:

alter table verticalstreets rename column postcode to vs_postcode;

1. Once you have ensured that all the columns in the two tables are named differently you can run the following script, which will create a new table which will contain all the vertical streets and their associated geometry:

create table vertical_street_lookup as select * from codepoint c, verticalstreets v where c.postcode=v.v_id;

1. This SQL creates a table by joining the vertical street IDs in the vertical street table with those IDs held in the postcode column of the postcode area polygons. The resulting table will effectively hold two columns of identical vertical street IDs, one from the vertical street table itself and one from the postcode column of the postcode area polygons table. The following script can be used to drop one of these columns, removing this duplicated information:

alter table vertical_street_lookup drop column postcode;

1. The postcode area polygons table can be altered to create a flag that alerts you to which postcodes form part of a vertical street. If you do not wish to create this flag, move onto the QGIS steps.

2. First you need to create a Boolean column within the postcode area polygons table to hold this information. Execute the following SQL to achieve this:

alter table codepoint add column vs boolean;

1. This column then can be updated with a value of ‘true’ where a postcode in the postcode area polygons table forms part of a vertical street. The following SQL can be used to do this:

update codepoint

set vs = 'True' where postcode in (select c.postcode from vertical_street_lookup v, codepoint c where v.vs_postcode=c.postcode or c.postcode=v.v_id);

Once you have created a new vertical street lookup table with a geometry column as outlined in the steps above, this can be visualised within QGIS using the following steps:

1. Connect to PostGIS by selecting the Add PostGIS Layers button.

2. This will bring up an Add PostGIS Table(s) dialogue box. If you have not already connected to your PostGIS database select the New option and fill in the Create New PostGIS Connection dialogue box.

3. Once you have added your connection details, select the Test Connect option to ensure the details are correct. When your connection is successful, click OK to save the connection, it should then appear in the connections drop-down. Select Connect and the schemas within your database will be populated. Expand the schema you are using and select both the postcode area polygons table and the vertical street lookup table you have created. Add them to QGIS by selecting Add at the bottom of the dialogue box. You will be prompted to select a coordinate reference system to use from a list which can be filtered by SRID.

4. Once your coordinate system has been selected the two tables should be displayed spatially in QGIS. If you have chosen to add the extra Boolean column to the postcode area polygons table this flag will alert you to when a postcode is part of a vertical street. The vertical street lookup table can then be used to display all the postcodes contained within the vertical street.

1. Open the CSV file with Excel so that the data is displayed in columns.

2. In a new blank column, click in the first cell of the new column (excluding the row column names).

3. In the function line, enter in the following function command where A1 is the column containing the postcode: =TRIM(LEFT(A1,LEN(A1)-3))&" "&RIGHT(A1,3)

This should now produce a column containing postcodes with a single space. For polygon data, extend this formula downwards, stopping when you reach any vertical street references; for example, those beginning with ‘V’.

The same method can be employed in Microsoft Access, using an update query rather than the function line. The functions listed above are the same for Access and Excel.

The process within MapInfo is the same regardless of whether the original supply is in TAB or MID/MIF, as both get imported to MapInfo and opened as a .TAB file.

1. Create a new column, of Character (8) type, in the Code-Point Polygons table (CP_Polys.TAB) as column 3. Give it a meaningful name like ‘SS_Postcode’ (go to Table > Maintenance > Table Structure). Save the table.
2. Click on Query, Select – this brings up the Select Query dialogue box. Ensure that the Code-Point Polygons table, CP_Polys, is being selected from and enter the following expression in the that Satisfy field:

POSTCODE not like “V%”

1. Change the Store Results in Table field to read ‘UpdateThese1’. Click the OK button to apply the query.
2. The next step is to update the SS_Postcode attribute. Click Table and then the Update column tab. This opens the following dialogue box:
3. Ensure that the Table to Update field has ‘UpdateThese1’ as its value. From the Column to Update drop-down menu, select the previously-added column; for example, ‘SS_Postcode’, making sure the Get Value from Table is the same table as in the Table to Update field.

4. Click the Assist button. This opens the following Expression dialog box:

1. In the Type an expression box, key in the following function command:

RTrim$(Left$(<insert column name of original postcodes>,(Len<insert column name of original postcodes> (<insert column name of original postcodes>e)-3)))+" "+Right$(<insert column name of original postcodes>,3)

1. Click the OK button to apply the update.

This process must then be re-run for the postcodes that were originally vertical streets also:

1. Change the Store Results in Table field to read ‘UpdateThese2’. Click the OK button to apply the query.

POSTCODE like “V%”

• Click on Query > Select – this brings up the Select Query dialogue box. Ensure that the Code-Point Polygons table, CP_Polys, is being selected from and enter the following expression in the that Satisfy field:
• The next step is to update the SS_Postcode attribute. Click Table and then the Update column tab. This opens the following dialogue box:

  1. Ensure that the Table to Update field has ‘UpdateThese2’ as its value. From the Column to Update drop-down menu, select the previously-added column; for example, ‘SS_Postcode’, making sure the Get Value from Table is the same table as in the Table to Update field.

  2. Click the Assist button and in the Type an expression box, key in the following function command:

  3. RTrim$(Left$(<insert column name of original postcodes>,(Len(<insert column name of original postcodes>)-3)))+" "+Right$(<insert column name of original postcodes>,3)

  4. Click the OK button to apply the update.

If you now browse the Code-Point Polygons table you should see that the SS_Postcode column is fully populated with single-space postcodes for both ‘standard’ postcodes and vertical street postcodes. You can choose to delete the VS_Postcode column at this point, as the new SS_Postcode contains all the single- spaced postcodes for all features.

These steps assume that the data has already been imported into ArcGIS and that the user has the correct permissions to edit the Shapefile. It is advised to try this method on a copy of the original data, and not the only copy of the data, in case of error.

Once the Shapefile is open, the required new fields can be added.

1. Right-click on the layer in the Table of Contents (down the left-hand side of the map window) and open the Open Attribute Table.

The first step is to add a new column to hold the newly formatted postcode.

1. Click on the Options button then click on Add Field.

• This opens the following dialogue box:

  1. Key in an appropriate name, for example, ‘NewPC’, in the Name box and change the Type box on the drop-down menu to ‘text’; also change the Field Properties Precision/Length to ‘8’.

  2. Click OK and the field is added.

  The next stage is to ensure that only postcode units are updated and not any vertical street references that might also be in the table.

  1. Click on Selection, Selection By Attributes.

  This brings up the Select By Attributes dialogue box:

1. Ensure that the correct Layer is being selected from, in this example, ‘L’.

1. Ensure that the Method field you have chosen is ‘Create a new selection’.

1. Then, enter the following expression in the query field at the bottom of the dialogue box:

“POSTCODE” NOT LIKE ‘V%’

1. Click the OK button to apply the query. This selects all the postcodes that are not vertical streets from the attribute table.

The final stage is to update the new column:

1. To populate the fields, the table must be made editable.

2. Return to the map window. Do not close the attribute table as it will be required later.

3. Click on the Editor drop-down selection =▼.

• If this toolbar is not already loaded, then right-click on an empty part of the grey area on the map window and all the available toolbars will be listed. Simply click on the Editor toolbar and it will be loaded to the toolbar.

1. Ensure that the correct Layer is being selected from, in this example, ‘L’.

1. Ensure that the Method field you have chosen is ‘Create a new selection’.

1. Then, enter the following expression in the query field at the bottom of the dialogue box:

1. In the bottom dialog box, enter the following function command:

RTrim(Left([<insert column name of original postcodes>],(Len([<insert column name of original postcodes>])-3)))+" "+Right([<insert column name of original postcodes>],3)

1. Ensure that the lower-left tick box Calculate selected records only is checked.

1. Click OK to update the column. This will update your new column with a single-space postcode.

1. Finally, go back to the map window, click on the Editor toolbar and select Stop editing. It will prompt to save the edits. Click Yes.

These steps assume that the data has already been imported into QGIS and that the user has the correct permissions to edit the Shapefile. It is advised to try this method on a copy of the original data, and not the only copy of the data, in case of error.

Once the Shapefile has been opened in QGIS it can be edited and a new field with the reformatted postcodes added.

1. Right-click on the layer you wish to edit in the Layers window and select Open Attribute Table.

A new column must be created to hold the newly formatted postcodes.

1. Select the Toggle Editing Mode button in the top left-hand corner of the attribute table. This will activate a range of other buttons in the top bar. Select the New Column button in the top right-hand corner of the attribute table.

2. The following dialogue box will open, prompting you to set the name and parameters of the field. Ensure you set the field type as Text (String) and set the width to 8 characters long as shown below.

1. When the parameters have been set click OK. The extra field will be created and will contain null values. To populate this field, select the Open Field Calculator button in the top right-hand corner of the attribute table. The following dialogue box will be opened.

1. Click the checkbox Update existing field and then choose the field you have created from the drop-down underneath. In the Expression box copy and paste the following code: trim(concat( left( "<insert column name of original postcodes>" , length( "<insert column name of original postcodes>" )-3) ,' ', right( "<insert column name of original postcodes>" , 3) ) )

   See image below for example.

2. After selecting OK, the dialogue box will close, and the new column will be updated with single-spaced postcodes.