These notes outline how to load Building Height Attribute data into a PostgreSQL database using the .csv file. The notes have been prepared using version 2.0 of PostGIS for PostgreSQL 9.3.

Loading instructions

1. Open the PGAdmin tool (this can be found on the Windows Start Menu > PostgreSQL).

2. Either connect to an existing database or create a new database.

3. Open the public schema (although in a production environment, it is advised to use a different schema) and create the table using the following, or similar, script:

create table <Insert table name>( 
OS_TOPO_TOID character varying (20), 
OS_TOPO_TOID_VERSION smallint, 
BHA_ProcessDate date,
TileRef character varying (6), 
AbsHMin real,
AbsH2 real,
AbsHMax real, 
RelH2 real, 
RelHMax real, 
BHA_Conf smallint
);

1. Once the tables have been created, the data can be loaded into the table using the SQL COPY, adding the .csv option as the first line contains a header record.

COPY <Insert table name> FROM ‘<Insert file path to bha.csv>’ DELIMITER ‘,’ CSV HEADER;

Note: The sections within the chevrons require your postGIS table name and the file path to your data (the latter must contain no spaces in the path).

Altering the TOID

The following section details the steps that you can take to reformat the TOID attribute in the Building Height Attribute data you’ve loaded, so that the format matches that of the TOID attribute in your translated OS MasterMap Topography Layer Topographic Area data.

Note: If you have used the gawk application process outlined previously, or if the format of the TOIDs in your translated OS MasterMap Topography Layer data match that in the Building Height Attribute data, you do not require this step.

Two reformatting options are presented below:

Script to remove ‘osgb’ from Building Height Attribute TOIDs:

Recommended for use if your translator removes ‘osgb’ from your OS MasterMap Topography Layer TOIDs but does not add any leading zeroes to shorter 13-digit TOIDs.

Run the following SQL command:

UPDATE <insert table name> SET <insert TOID column name> = trim('osgb' 
from os_topo_toid); 
COMMIT;

Script to remove ‘osgb’ and add ‘000’ to 13-digit Building Height Attribute TOIDs:

Recommended for use if your translator removes ‘osgb’ from your OS MasterMap Topography Layer TOIDs and adds three leading zeroes to shorter 13-digit TOIDs.

Run the following SQL command:

// Some codeUPDATE <insert table name> set <insert TOID column name> = (CASE
WHEN char_length(<insert TOID column name>) = 20 THEN trim('osgb' from <insert TOID column name>)
ELSE ('000' || trim('osgb' from <insert TOID column name>)) END);
COMMIT;

Note: The sections inside the chevrons require your postGIS table name and TOID column name.

The following set of instructions assumes a basic knowledge of Oracle databases and SQLLDR, which is the package that can be used to load Building Height Attribute .csv into the database. Other options are available for loading data into Oracle databases.

If required, copy the data file(s) to an appropriate location. It is worth noting that the file(s) will need to be unzipped and therefore you will need in the region of 1.3 Gb of free space.

Once the data is in an appropriate location, the next stage is to uncompress the *.zip file to .csv. This can be done using a package such as WinZip or 7Zip.

Loading instructions

1. Create the tables using the SQL below.

2. Prior to running the script, the tablespace that the tables are going to reside in needs to be altered from <TablespaceName> to the tablespace that is being worked in.

3. If the table already exists, you will firstly need to drop the table using the following SQL:

DROP TABLE <Insert Table Name> CASCADE CONSTRAINTS;

1. Next you will need to create a new empty table using the following SQL:

CREATE TABLE <Insert Table Name> (
MI_PRINX NUMBER (38, 0),
OS_TOPO_TOID VARCHAR2 (20), 
OS_TOPO_VERSION NUMBER, 
BHA_PROCESSDATE DATE, 
TILEREF VARCHAR2 (6), ABSHMIN FLOAT,
ABSH2 FLOAT, 
ABSHMAX FLOAT, 
RELH2 FLOAT, 
RELHMAX FLOAT,
BHA_CONF NUMBER
);

DROP SEQUENCE BHA_data_S; 
CREATE SEQUENCE BHA_data_S 
START WITH 1
    INCREMENT BY 1;
COMMIT;

1. Create an SQLLDR control file containing the following information:

OPTIONS (SKIP=1, BINDSIZE=20971520, READSIZE=20971520, ROWS=2500, ERRORS=10, 
SILENT = FEEDBACK)

LOAD DATA
CHARACTERSET WE8ISO8859P1
INFILE '<Insert file path to bha.csv>'
    APPEND
    INTO TABLE BHA_data FIELDS TERMINATED BY ","
    OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
        (
    MI_PRINX EXPRESSION " BHA_data_S.NEXTVAL", 
    OS_TOPO_TOID,
    OS_TOPO_VERSION,
    BHA_PROCESSDATE DATE "YYYY-MM-DD", 
    TILEREF,
    ABSHMIN, 
    ABSH2, 
    ABSHMAX, 
    RELH2, 
    RELHMAX, 
    BHA_CONF
) Preface

Note: The sections inside the chevrons require you to input your table name and the file path to your data (the latter must contain no spaces in the file path).

1. Once this file is created, it can be called from a .bat file to run it on the box that holds the database rather than a remote machine. The contents of the .bat file should be similar to the following:

@sqlldr <username>/<password>@<service name> 
control= <name of ctl file created previously> 
Pause

1. ‘Double click’ the .bat file that you’ve just created to load the data into the BHA_DATA table in your database.

Altering the TOID

Note: If you have used the gawk application process outlined previously, or if the format of the TOIDs in your translated OS MasterMap Topography Layer data match that in the Building Height Attribute data, you do not require this step.

Two reformatting options are presented below:

1. Script to remove ‘osgb’ from Building Height Attribute TOIDs:

Recommended for use if your translator removes ‘osgb’ from your OS MasterMap Topography Layer TOIDs but does not add any leading zeroes to shorter 13-digit TOIDs.

Run the following SQL command:

UPDATE <Insert Table Name> set <Insert Table Name>.<insert TOID column name>=substr(<Insert 
Table Name>.<insert TOID column name>,5);

Script to remove ‘osgb’ and add ‘000’ to 13-digit Building Height Attribute TOIDs:

Recommended for use if your translator removes ‘osgb’ from your OS MasterMap Topography Layer TOIDs and adds three leading zeroes to shorter 13-digit TOIDs.

Run the following SQL command:

UPDATE <Insert Table Name> set <Insert Table Name>.<insert TOID column name>= (CASE
WHEN length(os_topo_toid) = 20 then substr(<Insert Table Name>.<insert TOID column name>, 5) ELSE ('000' || substr(<Insert Table Name>.<insert TOID column name>, 5))
END);

Note: The sections inside the chevrons require you to enter your table name and TOID column name.

These notes outline how to load Building Height Attribute data into ArcMap using the .csv file you have received and/or altered. The notes have been prepared using version 10.5 of ArcMap. The instructions will also work for ArcPro and version 10.6.1 of ArcMap.

If you are importing an unmodified version of the .csv file, then you can import this directly into ArcMap via the Add Data button.

If you are importing a .csv with an altered OS_TOPO_TOID attribute, you will need to carry out the steps detailed below before importing.

You can create a schema information file which describes the format of the fields contained within a text file. A schema information file is always named ‘schema.ini’ and is stored within the same directory as the .csv you wish to import into ArcMap.

1. Open a new text editor document and copy the following script into it: [_BHA_Data.csv]

Format=CSVDelimited ColNameHeader=True Col1=OS_TOPO_TOID Text Col2=OS_TOPO_VERSION Long
Col3=BHA_ProcessDate DateTime Col4=TileRef Text
Col5=AbsHMin Double Col6=AbsH2 Double Col7=AbsHMax Double Col8=RelH2 Double Col9=RelHmax Double Col10=BHA_Conf Long

Note: The section of code within the square brackets refers to the file name of the .csv you wish to import. Please alter this line to reflect the file name of your .csv.

1. Save this in the same directory as the .csv you wish to import into ArcMap, naming it schema.ini.

This file will now be read by ArcMap when you import the .csv file into it and will specify the field type for each field within the .csv file. This ensures that the TOID is treated as a text string.

These notes outline how to load Building Height Attribute data into QGIS using the .csv file you have received and/or altered. The notes have been prepared using version 2.14.15 of QGIS Desktop. The instructions will also work for version 3.16 of QGIS.

If you are importing an unaltered .csv file which contains TOIDs in the format they are found in the raw GML, then you can import this file via the Add Delimited Text Layer icon. If you wish to import this .csv file via the Add Vector Layer option, you will have to create a .csvt file as outlined below or all the fields will be imported as ‘String’.

If you are importing a modified version of the .csv file, you must create a .csvt file or the TOID field may be imported as a number.

A .csvt file is the equivalent of a schema.ini file for use in QGIS. It specifies the field types of a text file you are importing into QGIS.

It must be named the same as the .csv file it is referring to and be stored in the same directory as the .csv file you wish to import into QGIS.

1. Please open a new text editor document and copy the following script into it: "String","Integer","Date","String","Real","Real","Real","Real","Real","Integer"

2. Save this in the same directory as the .csv file you wish to import into QGIS with the same file name as the .csv file and with the file extension .csvt.

3. You can now import the .csv file into QGIS by selecting the Add Vector Layer or Add Delimited Text Layer options in QGIS; this file will now be read by QGIS during the import process and will specify the field type for each field within the .csv file.

These notes outline how to load Building Height Attribute data into MapInfo using the .csv file you have received and/or altered. The notes have been prepared using version 16.0.0 of MapInfo (64-bit version).

How you have altered the TOID field in the .csv file will affect the methodology employed when importing the .csv file into MapInfo. If you have retained the raw GML TOID format or added leading zeroes into your TOID field, then MapInfo will automatically pick this up as a character field, so it can be added to MapInfo in the following process:

1. Launch MapInfo.

2. Navigate to the Open dialog box either by using the icon or by clicking File > Open.

3. Select Comma Delimited CSV (*.csv) from the Files of type drop-down, then navigate to where the .csv file is stored.

4. Select the .csv file you wish to open and click Open.

5. The Open dialog box will close, and a Comma Delimited CSV Information dialog box will appear. Ensure the Use first line for column titles check box is ticked, then click OK to close the dialog box and open the .csv file.

The TOID field should be formatted as a text string and should include leading zeroes.

If you have removed ‘osgb’ but not added any leading zeroes, please follow the steps outlined above. You should see that the TOIDs are formatted as numbers and contain decimal places. If you then navigate to the directory where you have stored the .csv file, you should see that MapInfo has created a .TAB file with the same name as the .csv file. When this .TAB file is opened within a text editor, something similar to the text below should be displayed:

Note: The field types may vary depending on previous processing. The section of code within the chevrons refers to the file name of the Building Height Attribute .csv.

!table
!version 300
!charset WindowsLatin1
Definition Table
File "<_Name of Tile.csv>"
Type ASCII Delimiter "," Titles Charset "WindowsLatin1" Fields 10
OS_TOPO_TOID Integer ; OS_TOPO_VERSION Smallint ; BHA_ProcessDate Char (10) ; TileRef Char (6) ;
AbsHMin Float ; AbsH2 Float ; AbsHMax Float ; RelH2 Float ; RelHMax Float ; BHA_Conf Smallint ;

As you can see here, the field OS_TOPO_TOID has a data type of ‘Integer’.

1. Manually change this in the text editor to Char (20) ; to specify this field type as text, then save the .TAB file before closing.

1. Close the original .csv file displayed in MapInfo by selecting File > Close Table.

1. Open the .TAB file that you just edited. The TOID field should now be formatted correctly, and you can use this .TAB file to join to the OS MasterMap Topography Layer data holdings.

Note: If you re-open the .csv file in MapInfo and choose to overwrite the table, the TOID field will be formatted as a number again. Instead, you should use the .TAB file rather than the .csv file.

Now you have loaded the .csv file, you will need to export it as a .dbf (dBASE DBF) file and create an index on the TOID column in the table. To do this, follow these steps:

1. In the ribbon toolbar, select the Table tab and then select Export. Navigate to where you wish to save the .dbf. Ensure the Save as type is set to dBASE DBF (*.dbf).

2. Select the Table tab in the ribbon menu, then select Table and from that drop-down, select modify structure, which opens the Modify table structure dialog box.

3. Ensure you have opened the table structure for the Building Height Attribute data because this is the data we want to create an index on. For the column of data containing the TOID value, tick Indexed box. Apply this index by clicking OK.

These notes outline how to join Building Height Attribute data to OS MasterMap Topography Layer in ArcMap. The notes have been prepared using version 10.5 of ArcMap.

In the procedure described below, it is assumed that the user has converted OS MasterMap Topography Layer from the .gz supply into an ESRI shapefile format using translation software. It is important to ensure that any pre-processing of Building Height Attribute data is completed before commencing an ESRI ArcGIS session.

1. Launch ArcMap and load the Building Height Attribute .csv file and the OS MasterMap Topography Layer shapefile.

2. Right-click on the Topographic Area Layer in the table of contents, navigate to Joins and Relates and select the Join… option as shown in the following screenshot:

1. In the Join Data dialog box that opens, select the .csv file you want to join to the OS MasterMap Topography Layer data from the drop-down under option 2 in the list.

2. For options 1 and 3 listed in the Join Data dialog box, select the TOID column in each dataset to specify this as the common attribute between the data that you wish to join on.

3. In the Join Options section of the Join Data dialog box, you can choose to keep all records or keep only matching records; this will mean that you will only have the buildings with heights left after the join.

4. Select OK to carry out the join – it is better not to choose the Validate Join option as this requires a longer processing time. When you open the attribute table of the layer you have joined the .csv file to, you should see the Building Height Attribute columns at the end of the attribute table. You can use the 'Select by attribute tool' to display all the buildings in the attribute table to inspect the Building Height Attribute data more easily in ArcMap. If you experience long processing times when querying this join, please export the file and then carry out these queries.

5. Lastly, in order to save this join to the layer, close the attribute table and right-click the layer in the table of contents. Select Data > Export Data to save a copy of this shapefile with the Building Height Attribute data joined to it. Please be aware that large volumes of data may increase the processing time of this step.

Alternative method to speed up the join process

If you are finding that the join process is taking a long time, you can try the alternative method outlined below which will speed up the join.

Before loading the .csv file into the GIS, you could use ArcCatalog to export the .csv table into a Geodatabase (single). When doing this, you will have the option to edit the format of the field properties for the TOID data. This will allow you to add an attribute index to the table. Once the index has been added, the join process should be quicker. The notes below outline these steps fully:

1. Navigate to the .csv file in ArcCatalog.

2. Right-hand click on file and select the drop-down Export, then choose To Geodatabase (single). This will open up the Table-to-Table dialog box.

3. Select an output location, which needs to be within a file geodatabase, and create a name for the new table you are going to create.

4. Finally, in the Field Map (optional) window, right-click on the TOID field and select the Properties option, which will open the Output Field Properties dialog box.

5. In the Properties box, there will be a row called ‘Length’ with a number. This number has to be less than 80 to allow you to add an attribute index later in the process. As TOIDs are never more than 20 characters in length, this number has to be greater than 20.

6. Click OK to close the window.

7. To complete the export, select OK.

8. Open up ArcGIS and navigate to the Arc Toolbox.

9. Select Data Management Tools, then choose Indexes from the drop-down and open the Add Attribute Index tool.

10. Select the table in the File Geodatabase you have just created. In the ‘Fields to Index’ window, tick the box next to the row which contains the TOID.

11. Select OK to carry out this operation.

12. Once the index has been added, carry out the instructions at the beginning of the section to join the Building Height Attribute data to your OS MasterMap Topography Layer data.

These notes outline how to join Building Height Attribute data to OS MasterMap Topography Layer in QGIS. The notes have been prepared using version 2.14.15 of QGIS Desktop. The instructions will also work for version 3.16 of QGIS.

1. Launch QGIS and load the .csv file and the OS MasterMap Topography Layer data.

2. Right-click the Topographic Area Layer and select Properties from the options list.

3. Click the Joins tab within the Layer Properties dialog box.

4. Press the green plus icon at the bottom of this dialog box, and in the Add vector join dialog box that opens, select the .csv file as the Join layer.

1. For the Join field and Target Field options in the Add vector join dialog box, select the TOID column in each dataset to specify this as the common attribute between the data you wish to join on.

2. Select OK, then close the Layer Properties dialog box.

3. When you open the attribute table of the Topographic Area Layer, you should see the Building Height Attribute columns at the end of the attribute table. You can use the Select features by expression tool to display all the buildings in the attribute table to inspect the Building Height Attribute data more easily in QGIS.

1. Lastly, in order to save this join to the layer, close the attribute table and right-click the layer in the table of contents. Select Save as from the options list to save a copy of the layer with the Building Height Attribute data joined to it.

These notes outline how to join Building Height Attribute data to OS MasterMap Topography Layer in MapInfo. The notes have been prepared using version 12.0.2 of MapInfo.

1. Launch MapInfo and load the .dbf file and the OS MasterMap Topography Layer .TAB file.

2. Select the SQL Select button on the ribbon toolbar, and choose the SQL Select option which opens the SQL Select dialog box. Leave the ‘*’ (asterisk symbol) in the Select Columns box to preserve all fields in both tables.

3. From the Tables drop-down, select the OS MasterMap Topography Layer data and the .dbf file you wish to join. This will populate these tables within the 'from Tables' box.

1. Click within the 'where Condition' box, then select from the Columns drop-down the TOID field from the OS MasterMap Topography Layer data. This will populate this field within the 'where Condition' box.

2. Enter an equals sign after this column name and then use the ‘Columns’ drop-down to select the TOID field from the .dbf file which will populate this field after the equals sign in the ‘where Condition’ box. This action will have written the SQL query to join these tables via the TOID columns; the SQL query should look similar to that shown in the preceding screenshot.

3. In the ‘into Table Named’ box, enter the name you wish to call the new table containing both the OS MasterMap Topography Layer data and .dbf file data.

4. Click OK to close the SQL Select dialog box.

5. A new table will open containing the joined records.
6. Select the Files drop-down from the top toolbar, then choose the Save Query option to save this join.

If you close the query table and open the version you have saved, a map view of the join will open in a window browser. Please be aware that as you are dealing with large files, these steps may take some time to process.