Working with CSV data

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

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

     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.

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

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

     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

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:

   • X Field: Select easting.

   • Y Field: Select northing.

   • Coordinate System of Input Coordinates: Select British National Grid and click OK.
   • Output: Click the folder icon:

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

     • Name: Give the output a name

     • Click Save.
   • 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.

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.

2. Click Add Data.
3. Navigate to the folder that contains the geodatabase file, select the XY feature class and then click Add.
4. The AddressBase Core data is now displays and is ready for use in ArcMap.

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.

   Make sure that the coordinate reference system is set to EPSG 27700 (British National Grid).
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.
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.
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.

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.
3. Close the window when the process is complete.

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

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

   The AB_Core data now displays against the chosen mapping backdrop. You can now interrogate the data using the tools available in MapInfo Professional.
5. Save the workspace in MapInfo Professional for future use.

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.
   • In Overlay Types, select Web > Tile Dataset, and then select Next.
   • In Tile Dataset, expand Ordnance Survey (GB) > OS Open Zoomstack, select one of the available styles and then click Finish.

     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.
   • In Overlay Types, select Databases > View Points, and then click Next.

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

     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.
   • 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.
   • In Database columns, click Next. This window does not require any configuration.
   • 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.
   • 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.
   • Change the coordinates to OSGB 1936.British National Grid and then click OK .
   • Click Finish.

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

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

   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.

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

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

  Becomes:

  Copy

  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

  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

   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:

   Copy

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

2. Load the data into your new geometry column:

   Copy

   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:

   Copy

   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.

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.