Working with CSV data

This is not an extensive list of the applications AddressBase Core can be used in; many other GIS software applications and/or databases can be used to load the product.

Ordnance Survey does not recommend specific suppliers or software products, as the most appropriate system will depend on many factors, for example, the amount of data being taken, resources available within the organisation, the existing and planned information technology infrastructure and the applications that AddressBase products can be used for.

Loading data into GIS applications

This section provides step-by-step instructions on how to load the CSV format of AddressBase Core into commonly-used GIS software.

ArcGIS Pro

Note - These instructions are based on ArcGIS Pro version 2.3.3. More recent releases should perform in a similar manner.

1. Create a new blank map project in ArcGIS Pro and select an appropriate map backdrop to use as context against which to display the data. When you create a new project, ArcGIS Pro creates a new File Geodatabase into which data can be loaded.

2. Select an area in the Map which displays the backdrop of the area of the country of interest.

   • On the right-hand side of the window, open the Catalog pane.

   • Expand the folder structure to show the new file geodatabase created with the project. In this example, the database is called AddressBase_Core.gbd.

     

     ArcGIS Pro UI showing AddressBase_Core.gbd in the Catalog pane
3. Right-click on the file geodatabase and in the context menu and select Import table. A new Geoprocessing window will open.

4. In the Geoprocessing window fill in the fields marked with a red asterisk:

   • Input Rows: Navigate to the location of the AddressBase Core .csv file.

   • Output Name: Provide a name for the output standalone table.

   • Click Run.

     

     ArcGIS Pro Geoprocessing window

     The application will run a script to load the CSV file into the file geodatabase.

5. A green tick box message will appear the process is complete and a new standalone table will display in the left pane.

   

   ArcGIS Pro showing Standalone Tables in the left pane
6. To view the contents of the standalone table, right-click on the new table and select Open. The contents of the file will be displayed in a new browsing window.

   

   ArcGIS Pro showing standalone tables in new browsing window
7. To display the contents of the table against the map backdrop, you need to display the XY data in the table:

   • Right-click the standalone table (AB_Core in our example) in the left pane and select Display XY Data. A new Geoprocessing window will display.

   • Select the X Field (normally easting) and Y Field (normally northing).

   • Select the British_National_Grid option from the coordinate system dropdown list.

   • Click Run.

     

     ArcGIS Pro Geoprocessing window

     The process will now run, and a green tick box message will appear when complete.

   The AddressBase Core data will now display against your chosen mapping backdrop, as shown in the screenshot below. In the left pane, a new feature class, named NAMEDAB_CoreXYTableToPoint1 will display.

   

   ArcGIS Pro UI showing AdressBase Core data in the left pane and Map area

   You can now interrogate and style the data as you prefer.

ArcMap

Note - These instructions are based on ArcMap version 10.5.1. More recent releases should perform in a similar manner.

1. Open ArcCatalog and create a new file geodatabase into which the AddressBase Core data will be loaded.

2. Name the file geodatabase save it in a suitable location. In our example, the file geodatabase is ABCore_ArcMap.gdb

   

   ArcCatalog showing new file geodatabase
3. Right-click the new file geodatabase and select import table (single).

4. In the dialog box:

   • Input Rows: Enter details of the CSV table being loaded.

   • Output Table: Enter the output table it will be called within the file geodatabase.

   • Field Map: Leave this as default to add all the fields within the CSV file.

   • Click OK to proceed. When populated, the dialog box will look similar to the screenshot below.

     

     ArcMap Table to Table dialog

     When the process is complete, a green tick window will appear.

5. In ArcCatalog, the CSV file will have been added to the file geodatabase

   

   ArcCatalog showing new table added to the file geodatabase

Creating the address location geometries

To display the data in the CSV file against a mapping backdrop, you need to create a new feature class using the XY coordinates from the table.

1. Right-click on the new table and select Create Feature Class > From XY table.

2. In the Create Feature Class From XY Table dialog:

   

   ArcMap Create Feature Class From XY Table dialog

   • X Field: Select easting.

   • Y Field: Select northing.

   • Coordinate System of Input Coordinates: Select British National Grid and click OK.

     

     ArcMap Spatial Reference Properties dialog
   • Output: Click the folder icon:

     • Save as Type: Select File and Personal Geodatabase feature class.

     • Name: Give the output a name

     • Click Save.

       

       ArcMap Saving Data dialog
   • Click OK to close the the Create Feature Class From XY Table dialog.

3. In ArcCatalog, a new feature class will have been created as containing point features.

   

   ArcCatalog showing new feature class

Displaying the address location

1. In ArcMap, open a suitable backdrop map to overlay the data against. This can be from locally held data or using one of several available WMS or WMTS mapping services, some of which are free to use. Carry out the following steps to achieve this.

   

   ArcMap UI showing backdrop map

2. Click Add Data.

   

   ArcMap Add Data button
3. Navigate to the folder that contains the geodatabase file, select the XY feature class and then click Add.

   

   ArcMap Add Data dialog
4. The AddressBase Core data is now displays and is ready for use in ArcMap.

   

   ArcMap UI showing AddressBase Core data in map area

QGIS

Note - These instructions are based on QGIS version 3.10.5. More recent releases should perform in a similar manner.

1. Launch QGIS with a blank new project.

2. Add a backdrop map. You can use either a local data holding or an online mapping service from one of several providers, including Ordnance Survey. This example uses the OS Maps API light background as a contextual map.

   

   QGIS UI showing OS Maps API light background as a contextual map

   Make sure that the coordinate reference system is set to EPSG 27700 (British National Grid).

   

   QGIS EPSG 27700 (British National Grid) coordinate reference system option
3. Click the comma icon (Add Delimited Text Layer) in the Manage Layers toolbar on the left. To activate this toolbar, go to View > Toolbars > Manage Layers toolbar and make sure that Manage Layers Toolbar option is selected.

   

   QGIS Add Delimited Text Layer option in left side toolbar
4. In Data Source Manager | Delimited Text window:

   • File name: Select the AddressBase Core data CSV file.

   • First record has field names: Make sure this option is selected. In this instance, the first record in the data in this instance has field names.

   • X field: Select easting.

   • Y field: Select northing.

   • Click Add.

     

     QGIS Data Source Manager | Delimited Text window
5. The data will now load into QGIS and you click Close can close the window. You can now interrogate and style it differently, if required, using the tools in QGIS.

   

   QGIS UI showing AddressBase Core data on the background map

Adding a spatial index to the data

You can improve rendering performance by adding a spatial index to the data.

1. Right-click on the AB_Core entry in the Layers panel and select Properties. Click on the source tab on the left. The following window will appear.

2. Open the Source tab and click Create Spatial Index. The index will be built, improving the rendering of the data for use.

   

   Layer Properties window showing Create Spatial Index option
3. Close the window when the process is complete.

MapInfo Professional

Note - These instructions are based on MapInfo Professional version 16.0.4. More recent releases should perform in a similar manner.

1. Launch MapInfo Professional.

2. Load a suitable backdrop map to provide context for the AB_Core data. This can be map data held locally or from a wide variety of online sources, some of which are free to use. This example uses the OS Maps API light backdrop map, with the coordinate reference system set to EPSG 27700 (British National Grid).

   

   MapInfo Professional UI showing OS Maps API light backdrop map
3. Select the Table tab in the top menu and select Open > Table. You may have set up a quick launch button to open tables at the very top of the window.

   

   MapInfo Professional UI showing Table tab
4. In the Open dialog:

   • Select the folder where the AB_Core data is stored.

   • Files of type: Change this to Comma delimited CSV (*.csv).

   • Preferred View: Leave this as Automatic.

   • Click Open.

     

     MapInfo Professional Open dialog
5. In the Comma Delimited CSV Information dialog:

   • Delimiter: Leave this as a comma.

   • File Character Set: Leave this as Unicode UTF-8.

   • Use First Line for Column Titles: Select this option. The AB_Core data already contains the header information within the CSV file.

   • Click OK.

     

     MapInfo Professional Comma Delimited CSV Information dialog
6. The AB_Core data will now be loaded into MapInfo Professional in a table browser window and will appear in the list of loaded windows in the left pane.

   

   MapInfo Professional showing AB_Core table

Displaying the address location

To display the data against the backdrop map, you need to geocode the table. In other words, the XY coordinates need to be specified.

1. In the Spatial tab in the top menu select Create Points.

   

   MapInfo Professional UI showing Spatial > Create Points option
2. In the Create Points dialog:

   • Symbology: Select a symbol. You can use various symbols from a library of appropriate symbols loaded with MapInfo.

   • Projection: Select British National Grid.

   • Get X Coordinates from: Select easting.

   • Get Y Coordinates from: Select northing.

   • Click OK .

     

     MapInfo Professional Create Points dialog
3. In the Table tab in the top menu click Open > Table. You can also use a short cut if one is defined.

4. In the Open dialog:

   1. Select the newly created table containing XY coordinates.

   2. Preferred View: Select Current Mapper

   3. Click Open.

      

      MapInfo Professional Open dialog

   The AB_Core data now displays against the chosen mapping backdrop. You can now interrogate the data using the tools available in MapInfo Professional.

   

   MapInfo Professional
5. Save the workspace in MapInfo Professional for future use.

CadCorp Map Modeller

Note - These instructions are based on CadCorp Map Modeller version 9.

1. Launch CadCorp Map Modeller.

2. Add a suitable backdrop map for use as a contextual backdrop:

   • Open a blank map and ensure that the default coordinate reference system is set to British National Grid.

   • Select Add Overlay.

     

     CadCorp UI highlighting Add Overlay button
   • In Overlay Types, select Web > Tile Dataset, and then select Next.

     

     CadCorp Overlay Types dialog
   • In Tile Dataset, expand Ordnance Survey (GB) > OS Open Zoomstack, select one of the available styles and then click Finish.

     

     CadCorp Tile Dataset dialog

     The backdrop map will load. For this option, an internet connection is required. You could use a locally held overlay of data as an alternative option.

3. To add the CSV data:

   • Select Add Overlay.

     

     CadCorp UI highlighting Add Overlay button and displaying backdrop map
   • In Overlay Types, select Databases > View Points, and then click Next.

     

     CadCorp Overlay Types dialog

   • In Database Types, select Comma Separated File and then click Next.

     

     CadCorp Database Types dialog
   • In File Browser, select the CSV file and then click Next.

     

     CadCorp File Browser dialog

     The next set of windows configure the data being loaded so that it displays properly.

   • In Database file, make sure the Keep first line option is selected and then click Next.

     

     CadCorp Database file dialog
   • In File format, make sure that the First Row Contains Field Names option is checked and the Text Qualifier dialog is set to a comma, and then click Next.

     

     CadCorp File format dialog
   • In Database columns, click Next. This window does not require any configuration.

     

     CadCorp Database columns dialog
   • In Recordset, click the CSV file in the left pane, using the arrow click all the columns that appear in the right pane, and then click Next. This loads all the data fields.

     

     CadCorp Recordset dialog
   • In View Points, set the X Field to easting and the Y Field dropdown to northing, and then click More Properties… to set the coordinate reference system.

     

     CadCorp View Points dialog
   • Change the coordinates to OSGB 1936.British National Grid and then click OK .

     

     CadCorp View Points dialog
   • Click Finish.

     

     CadCorp View Points dialog

Displaying the address location

You may not see the AB_Core data displayed against the backdrop mapping in the main map window. This is because the default styling may have set the data to display in a form which is too small. To change this, follow these instructions.

1. Right click the AB_Core entry in the Maps pane and select Properties.

   

   CadCorp UI highlighting AB_Core entry in Maps panel
2. In Overlays > Styles:

   • Symbol: Change the type from a dot to some sort of circle.

   • Point: Change the point size so that it moves closer to a value of 0 but is still a negative number, for example -0.05. Experiment to see what is most useful for your purpose.

   • Click Apply and then OK to close the window.

     

     CadCorp Overlays dialog

   The data should now load into the Map window and, depending on the map backdrop used, will look similar to the following example.

   

   CadCorp UI showing AddressBase Core data in the Map area

   Save the project as an SWD document in CadCorp for later use. The data is now ready to be interrogated using the tools available in CadCorp.

Loading AddressBase Core into a database

Considerations

• BIGINT/NUMBER: The ESRI products, ArcMap, ArcGIS Desktop and ArcGIS Server, do not support the BIGINT/NUMBER data type as an Object ID. Bear this in mind if the expectation is to use this data type directly with these products. As an alternative method to facilitate using ESRI software, you can store this data as a string and add a new Serial ID to act as the Object ID.

• Column length: If you are loading AddressBase Core data directly into a database, you may need to increase the column length to accommodate language characters such as ^. Some databases treat this as an additional character and therefore if you define the column length according to our specification there is a chance the load may fail. Please bear in mind such adjustments may be required depending on the database you use to load the data.

• UPRN deletions: It is important to note if a UPRN is deleted and then reinserted, this does not compromise the integrity of the UPRN and its use as a primary key. If a delete is issued for a UPRN, this does not mean it will not reappear in subsequent supplies. There are several reasons this may happen:

  • The record has moved in location more than once, moving it out of your area of interest (AOI; therefore the record is deleted) but then back into your AOI in the future. This could also occur if you alter your AOI.

  • A record has failed data validation upon a change being made. This can result dependent on the change being made in the record being deleted and then reintroduced when the error is fixed by the data supplier.

  If a UPRN is deleted, it will not be reallocated to a different property and it therefore remains the unique identifier for a property.

The following sections describes how to load AddressBase Core into two popular databases.

PostgreSQL

Warning - In PostgreSQL version 11 or above, there is currently a glitch which does not allow a bulk load using a CSV. If this affects you, please follow the steps in Loading data into GIS applications use to load the data into a GIS package and then retrospectively add it to your database.

Note - These instructions are based on PostgreSQL version 10 and assume that you have set-up your database with the PostGIS spatial extension.

Note:

• Where angle brackets (<>) are used, you should be replace the entire string with your content. For example:

  COPY <table_name> FROM 'C:\Address\AddressBaseCore_FULL_2020-05-25_001.csv ' DELIMITER ',' CSV HEADER;

  Becomes:

  COPY addressbase_core FROM 'C:\Address\AddressBaseCore_FULL_2020-05-25_001.csv ' DELIMITER ',' CSV HEADER;

• If referencing a schema, <schema_name>, can be placed in front of any <table_name>, for example:

  COPY ab_core.addressbase_core FROM 'C:\Address\AddressBaseCore_FULL_2020-05-25_001.csv ' DELIMITER ',' CSV HEADER;

These steps describe how to load AddressBase Core into a PostgreSQL database using the CSV files.

1. Open the PGAdmin tool You can find this in Windows Start > PostgreSQL.

2. Either connect to an existing database or create a new database. It is recommended that the encoding is set to UTF-8.

3. Open the public schema and create the tables using the following steps: In a production environment you should use a different schema.

   • Open the SQL query tool.

   • Download PostgreSQL_AddressBase_Core_CreateTable.sql from the AddressBase_Core folder at https://github.com/OrdnanceSurvey/AddressBase/tree/master/Loading_Scripts/PostgreSQL.

   • Open this SQL file in a text editor copy and paste the content into the SQL query tool within PostgreSQL. The script creates a table called addressbase_core; you can choose a different naming convention.

4. As each table is created, load the data into the table using SQL COPY. Adding the CSV option as the first line contains a header record for each table. The path and file name may need to be changed to reflect your data set-up:

   COPY <table_name> FROM 'C:\Address\AddressBaseCore_FULL_2020-05-25_001.csv ' DELIMITER ',' CSV HEADER;

5. Once loaded, you can add Primary Keys to the data: Primary Keys can only be added on columns where the data values are unique. The UPRN, UDPRN, USRN and TOID provide the only unique value in AddressBase Core. Where there are no unique data values, you can add an index to aid searching. To add Primary Keys:

   • Right-click on the table name and select Properties.

   • Select the Constraints tab.

   • Click the + (plus) symbol to add a new primary key.

   • Click the edit icon.

   • Enter a name for the key under the General tab (for example, Key1).

   • Under the Definition tab, select UPRN or any other unique value from the dropdown under Columns.

   • Click Save.

   To index the data:

   • Right-click on the table name and select Create > Index.

   • Under the General tab, enter a name (for example, Idx1).

   • Under Definition > Columns, click the + (plus) symbol.

   • Select the UPRN, for example, or any other unique value.

   • Click Save.

Converting coordinates to geometry

A PostGIS extension is required to create geometries. The AddressBase products contain both British National Grid (BNG) and ETRS89 coordinates. The following SQL shows how to create a column for BNG, but you can adapt the SQL to utilise the ETRS89 data.

1. Add a geometry column called geom to make the data usable in a GIS:

   SELECT AddGeometryColumn ('<schema_name>', '<table_name>', 'geom', 27700, 'POINT', 2);

2. Load the data into your new geometry column:

   UPDATE <schema_name>.<table_name> SET geom = ST_GeomFromText('POINT(' || easting || ' ' || northing || ') ', 27700 )

   This sets the geom column in the BLPU table to equal the values from the easting and northing columns, with the spatial reference defined as 27700.

3. Create a spatial index (for example, idx_abcore_geom) on the data using:

   CREATE INDEX <index_name> ON <schema_name>. <table_name> USING gist(geom)

   This adds the index name idx_abcore_geom to the same table on the geom column.

Microsoft SQL Server

Note – There are many ways to load AddressBase products into Microsoft SQL Server; this is just one suggested method for guidance.

1. Open SQL Server Management Studio (SSMS).

2. Right-click on the database you are loading into and select Properties.

3. Select Options on the left-hand side.

4. Expand the dropdown box for Recovery Model and select BULK-LOGGED. This minimises the logfile size. Default logging for Microsoft SQL Server can cause logfiles to grow over 20GB which can cause loading issues.

5. Open SQL Server Management Studio (SSMS) and right-click your database from the left-hand panel.

6. Navigate to Tasks and click Import Data. This opens the SQL Server Import and Export Wizard.

7. Click Next.

8. On the next screen, change your Data Source to Flat File Source.

9. Use the Browse button to navigate to and select your CSV file. If you cannot see your files, ensure the bottom right dropdown box has CSV files (*.csv) selected.

10. Click Open.

11. Ensure Column names in the first data row is selected.

12. Check that the Text Qualifier is set to a double quote (“). This is to make sure the quotations in the raw data supply are removed upon loading but that the data remains intact.

13. On the left-hand side of this screen, select Columns and check that the column delimiter is set to Comma.

14. On the left-hand side of the screen, select Advanced.

15. For each column of data you are loading, you need to specify a Data Type. The Microsoft SQL Server loader defaults each column to a String. The correct Data Types for each column are given in the AddressBase Core technical specification.

16. Once you have changed the column types to match the correct Data Types listed in the technical specification, click Next.

17. Check that your table is going to be imported into the correct database, then click Next.

18. On this screen, you can edit the default table name that Microsoft SQL Server has chosen by clicking in the destination box. For example, for AddressBase Core renaming to [dbo].[ADDRESSBASE_CORE]

19. Select Edit Mappings in the bottom right-hand corner.

20. In the new window, deselect the checkbox against the UPRN column, which needs to be the Primary Key of the table. Click OK once the Primary Key alterations have been completed.

21. Click Next. On this screen, check that the Source column and Destination columns are correct.

22. Click Next. A summary of your import will appear. Click Finish to continue.

23. A report will be generated as your data is imported. Success should appear at the top once complete.

24. You may need to right-click on your database and click Refresh to see your new table listed.

Setting Primary Keys

To create a Primary Key field you can run an SQL statement, such as the following example for AddressBase Core. Note - the columns you are creating these constraints on cannot be null or allowed to be null.

alter table dbo.ADDRESSBASE_CORE add primary key ([UPRN]);

Creating the point geometry

You can also create point geometry using the Eastings and Northings or the Latitude and Longitude coordinate values. To do this run the following SQL statement:

alter table dbo.ADDRESSBASE_CORE
add geometry_column as geometry::Point([easting],[northing], 27700);

Note – This uses British National Grid coordinates, with 27700 representing the spatial reference of the data. To use the Latitude and Longitude coordinate, the spatial reference should be set to 4258 for ETRS89.