Using the vertical street lookup

MapInfo

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.

   

   PO Browser table with the following columns: Postcode, VS_postcode, UPP, PC_Area. Postcode, PO1 1AU is selected.
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;

  

  SQL select screen showing the selection query above

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

2. Open the table.

   

   PC_VS_join Browser table with the following columns: Postcode, _COL1, UPP, PC_Area, _Object. Postcode, VLS00005 is selected.

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:

   

   Database select window showing selection of POSTCODE data from the CP_Polys table

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).

   

   Append Rows to Table dialog box

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.

  

  CP_Polys Browser table with the following columns: Postcode, VS_Postcode, UPP, PC_Area.

ESRI

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.

   

   Layers context menu with with 'Join and Relates', and 'Relate...' selected.

• The Relate window will open.

  

  Relate dialog window

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.

   

   The Identify screen showing details of the polygon VAB00582entify

PostGIS and QGIS

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);

QGIS steps

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.

   

   QGIS 2.2.0 showing the menu structure.

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.

   

   Create a New PostGIS connection screen in front of the Add PostGIS Table dialog.

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.

   

   Add PostGIS Tables screen with two test schema's highlighted.

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.