Working with tiled data

Tiled orders allow you to select a small region of Great Britain (GB), rather than having to order the whole of GB. Ordering tiles, as opposed to full GB coverage, reduces the amount of data supplied. This reduces data storage size and the time it takes to process the data.

AddressBase Core tiles can be downloaded through Ordnance Survey’s download service. See Downloading a dataset for full details about accessing this service. To order tiles:

1. Access the OS orders site and select AddressBase Core – 5km DOWNLOAD.

2. Select either a self-drawn polygon, a rectangle or a postcode/place. These options are available in the toolbar above the map. Once you have selected your area of interest (AOI), you can proceed to place your order.

Dependent on whether you are a PSGA customer or an OS Partner, the tiles are supplied as either GKPG or CSV.

You are provided with all 5km tiles that intersect the drawn polygon. The diagram below shows what is supplied when completing a tile order. An area of interest is drawn (blue lines) and a tile is provided for anywhere the drawn area of interest intersects (green lines).

For a GPKG download, you receive tiled data incorporated into one single GKPG file. For CSV tile supplies, individual 5km tiles are supplied for each tile which intersects your AOI.

The diagram below illustrates the format, coverage and supply type of AddressBase Core, specifically highlighting the tile supply options.

Each supply type is covered in the sections that follow.

GPKG

Both GB coverage and tiled supply of GKPG is produced as a full supply only. Both product variants result in you receiving one consolidated GPKG file for your order.

To use this in your GIS software, please refer to Getting started with AddressBase Core > GPKG data, which provides instructions to load GPKG into ArcGIS Pro, ArcMap, QGIS, MapInfo Professional and CadCorp Map Modeller.

CSV

Tiled orders are also an option for CSV supplies of AddressBase Core. The CSV is divided into 5km tiles with an individual CSV produced for each tile. This is the case for both full supply and COU. Each CSV is also be provided with a header, which needs to be considered if you want to consolidate multiple individual tiles into one single file.

One use case for taking tiled coverage is if you only need a small subset of addresses within GB, as this provides you with a smaller data holding than taking all of GB .

If you are planning on using the tile supplies for COU purposes, you need to understand the data structure of the full supply and COU files you will download.

If you select to receive COU tile supplies, if any record within a selected 5km tile is changed between two releases of data (each week), you will receive the entire 5km tile in your next supply containing all of the latest live records for that tile.

For example, as demonstrated in the table below, the original full supply contains the address 17 THE BROADS, PAMPHILL, WIMBORNE, BH21 4DR, and the next COU data does not . However, the full supply does include an additional record, 37 THE BADGERS, PAMPHILL, BH21 4SH. This means that the 17 THE BROADS address has been deleted between the two releases, and 37 THE BADGERS is a new address.

To implement COU, we recommend that you process the full supply and the COU data files into a common structure to be used in conjunction with SQL. You can do this by appending a tilename column onto the full supply and COU data when the CSVs are merged, as shown in the table below.

AddressBaseCore_COU_2020-01-30_SU2075.csv.zip

Ordnance Survey provides FME Workbench and NodeJS scripts to help to pre-process the data as described in the sections that follow. You can access these scripts in our GitHub repository:

• FME workbench

• NodeJS

A README file with usage instructions is provided for both scripts.

The data, either full supply or COU, should now be combined in one CSV file, under one header, with an appended tilename column. Once the data has been pre-processed, it can be loaded into any database. The next sub-sections will detail the different loads of full supply and COU.

Full supply

Once the full supply CSV has been pre-processed, the COPY load method can be used to load the data into your primary database table:

COPY addressbase_core FROM ‘PATH TO PROCESSED FULL SUPPLY ' DELIMITER ',' CSV HEADER;

In this example, addressbase_core is the name of the table. You may use a different name. To use this table in a geospatial setting, geometry columns will need to be inserted using the following SQL:

SELECT AddGeometryColumn (<schema_name>, 'addressbase_core', <column_name>, 27700, 'POINT', 2);
COMMIT;
UPDATE addressbase_core SET geom = ST_GeomFromText('POINT(' || easting || ' ' || northing || ') ', 27700 )
COMMIT;

COU supply

A similar method can be used to insert the COU data into a database. Similarly, addressbase_core_cou is the name given to the database table. You may use a different name.

COPY addressbase_core_cou FROM ‘PATH TO PROCESSED COU CSV ' DELIMITER ',' CSV HEADER;
SELECT AddGeometryColumn (<schema_name>, 'addressbase_core_cou', <column_name>, 27700, 'POINT', 2);
COMMIT;
UPDATE addressbase_core_cou SET geom = ST_GeomFromText('POINT(' || easting || ' ' || northing || ') ', 27700 );
COMMIT;

Application of COU to the primary table

1. If you are applying COU to your primary table, you should now have two tables in your selected database:

   • A primary table which contains some out-of-date data.

   • A COU table with the live data.

2. To update your primary table, there are two basic steps to follow using SQL commands:

   • Delete all rows that contain tilenames that match the tilenames of the incoming COU.

   • Insert all rows from the COU table to the primary table.

The following SQL commands will complete the application of COU into the primary table:

1. Index the tilename column on to the primary table:

   Copy

   CREATE INDEX ON addressbase_core (tilename);

2. Index the tilename column on to the COU table: CREATE INDEX ON addressbase_core_cou (tilename);

   Copy

   CREATE INDEX ON addressbase_core_cou (tilename);

3. Delete from the primary table any records which have a tilename that matches a tilename from your COU table:

   Copy

   DELETE FROM addressbase_core WHERE tilename IN (SELECT DISTINCT(tilename) FROM addressbase_core_cou);
   COMMIT;

4. Insert the live COU data into the primary table:

   Copy

   INSERT INTO addressbase_core_cou SELECT * FROM abcore.abcore_cou; COMMIT;