Importing data into a database

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.