Working with GPKG data

The following instructions are based on ArcGIS Pro version 2.7.1. It is recommended that you have a basic understanding of ArcGIS Pro tools and terminology before following these instructions.

Note: The file must have the file extension .gpkg (not case sensitive) to recognise it as an OGC GKPG. Make sure the AddressBase Premium GeoPackage is stored in a suitable location and the file is unzipped.

1. Launch ArcGIS Pro.

2. To establish a connection to the folder where the GKPG file is stored, navigate to the Insert tab and click Add Folder. Find and select the folder, then click OK.
3. Navigate to the View tab and open the Catalog Pane. In the pane that appears, find the now-connected folder where the GKPG is stored, then expand the GKPG to show the available layers.
4. Right-click the main.blpu features class and click Add To Current Map (or Add To New if a map frame has not yet been created for this project). Repeat this step for all of the layers in the GKPG.
5. Note that the other tables can be added to the map frame, but they will only act as standalone tables as they have no spatial element to visualise. The BLPU and Streets tables will be visible in the current Map view as they contain geometry. Once all of the tables have been added to the map frame, it should look similar to the following screenshot:
6. These standalone tables can be related to the spatial tables in order to give them a spatial component. The following relates are required:

   • BLPU (ID21_BLPU_Records):

     • UPRN – Application Cross Reference (ID23_XREF_Records) UPRN

     • UPRN – LPI (ID24_LPI_Records) UPRN

     • UPRN – Delivery Point Address (ID28_DPA_Records) UPRN

     • UPRN – Organisation (ID31_Org_Records) UPRN

     • UPRN – Classification (ID32_Class_Records) UPRN

   • LPI (ID24_LPI_Records):

     • USRN – Street (ID11_Street_Records) USRN

   • Street (ID11_Street_Records):

     • USRN – Street Descriptor (ID15_StreetDesc_Records) USRN

7. To perform a relate, right-click on the main.blpu feature class, then click Joins and Relates > Add Relate.
8. In the Add Relate dialog box, change the parameters as below:

   • Layer Name or Table View: The table the relate is being added to (for example, main.blpu).

   • Relate Table: The table the relate is being taken from (for example, main.application.xref).

   • Input Relate Field and Output Relate Field: The field to relate the two tables on (for example, uprn).

   • Relate Name: Something simple that reflects the tables being related (for example, blpu to application_xref).
9. Once the parameters are set, click OK.

10. Repeat steps 7, 8 and 9 for all the relates listed in step 6. You may wish to confirm the relates have been successful by right-clicking on a layer and navigating to Properties > Relates.
11. Once the data has been loaded into ArcGIS, you may wish to display more relevant information in the Explore tool than the Esri-defined Object ID. To change this, use the following instructions:

    • Click on the layer that you wish to change the label field of.

    • Select the Labeling tab (under Feature Layer).

    • Change the Field to the desired field (for example, UPRN).

Note - The following instructions are based on ArcGIS Desktop version 10.

Note: The file must have the file extension .gpkg (not case sensitive) to recognise it as an OGC GKPG. Make sure the AddressBase Premium GeoPackage is stored in a suitable location and the file is unzipped.

1. Start ArcCatalog as a separate program, or within ArcMap if you are using version 10.

2. To establish a connection to the folder where the GKPG file is stored, right-click on Folder Connections > Connect To Folder, then navigate to the folder containing the data. Select that folder and click OK.
3. Navigate to the newly connected folder where the GKPG is stored. Open the GKPG to view its contents.

4. Drag the data with spatial elements (that is, main.street and main.blpu, which are designated by the point symbol) into the Map view. From here, they can be visualised.
5. Add the standalone tables to the map. The tables have no spatial element so cannot be visualised, but they can be related to the data with a spatial element. Select the standalone tables and drag them into the Map view. When prompted, select ID as the Unique Identifier Field(s) before clicking Finish. Note - The ‘Toggle Contents Panel’ can be used to open a window where you can select multiple tables at once.
6. You need to create the following joins/relates between the tables, as stated in AddressBase Premium structure in the technical specification.

   • BLPU (ID21_BLPU_Records):

     • UPRN – Application Cross Reference (ID23_XREF_Records) UPRN

     • UPRN – LPI (ID24_LPI_Records) UPRN

     • UPRN – Delivery Point Address (ID28_DPA_Records) UPRN

     • UPRN – Organisation (ID31_Org_Records) UPRN

     • UPRN – Classification (ID32_Class_Records) UPRN

   • LPI (ID24_LPI_Records):

     • USRN – Street (ID11_Street_Records) USRN

   • Street (ID11_Street_Records):

     • USRN – Street Descriptor (ID15_StreetDesc_Records) USRN

7. To create the joins / relates between tables, select the Source button in the left-hand navigation window and right-click on the first table that you wish to relate to another. To create the relevant relates, use the following instructions:

   • Click Joins and Relates > Relate...
   • From the first dropdown menu, select the attribute from the first table that will be used to create the relate between the two tables. (Apply the relationships as listed in step 5.)

   • From the second dropdown, select the table that is going to be related to. (Apply the relationships as listed in step 5.)

   • From the third dropdown, select the attribute from the table that is being related to. (Apply the relationships as listed in step 5.)

   • In the fourth box, input a relevant name for the relate (for example, BLPU_to_LPI).
   • Click OK.

   • Repeat this process for all of the joins / relates listed in step 5.

8. Once the data has been loaded into ArcMap, you may wish to display more relevant information in the Info tool than the Esri-defined Object ID. To change this, use the following instructions:

   • Double-click on the spatial dataset that you wish to change the Display Expression of.

   • Select the Display tab.

   • Change the Field to the desired field (for example, UPRN).

   • Click OK.

Note - These instructions were created using QGIS version 3.14. Other versions of QGIS can be used, from version 2.10.1 onward.

1. Launch QGIS.

2. Navigate to the GKPG location in the QGIS Browser panel. If the GKPG is not listed when this container is expanded, then right-click on the GeoPackage location and select New Connection.

3. In the File Explorer window that launches, navigate to the location where you saved the AddressBase Premium GKPG and select it.

4. In the QGIS Browser panel, click on the drop-down arrow next to your AddressBase Premium GKPG to view the contents of the GKPG file. Drag the data with spatial elements (i.e. main.street and main.blpu, which are designated by the point symbol) into the map. From here, they can be visualised.
5. Next, add the standalone tables to the map. These tables have no spatial element so cannot be visualised, but they can be related to the data with a spatial element.

6. You need to create the following joins/relates between the tables, as stated in in AddressBase Premium structure in the technical specification:

   • BLPU (ID21_BLPU_Records):

     • UPRN – Application Cross Reference (ID23_XREF_Records) UPRN

     • UPRN – LPI (ID24_LPI_Records) UPRN

     • UPRN – Delivery Point Address (ID28_DPA_Records) UPRN

     • UPRN – Organisation (ID31_Org_Records) UPRN

     • UPRN – Classification (ID32_Class_Records) UPRN

   • LPI (ID24_LPI_Records):

     • USRN – Street (ID11_Street_Records) USRN

   • Street (ID11_Street_Records):

     • USRN – Street Descriptor (ID15_StreetDesc_Records) USRN

7. To create the joins / relates between the tables, right-click on your BLPU layer and select Properties...
8. Go to the Joins tab found on the left-hand side.

9. Click the green plus button in the bottom left-hand corner.

10. Select your Join layer. For this example, it is Application Cross reference (application_xref).

11. Select the Join field. For all BLPU links, this will be the UPRN (see the joins listed in step 4 above).

12. The Target field will also be the UPRN for this example.
13. Click OK. You should now have a join listed in your Joins window.
14. Click OK to return to your main QGIS mapping screen.

15. If you now select one of your BLPU records in the main mapping window, you will see the BLPU attributes and the relevant Application Cross References for that record.
16. You can now repeat steps 7 to 14 for all the additional joins (listed in step 6) you wish to make.

Note - The following step-by-step instructions are based on PostgreSQL version 12.3 and assume that you have set-up your database with the PostGIS spatial extension.

Requirements:

• A development platform for PostgreSQL (for example, pgAdmin or dBeaver)

• A PostgreSQL database

• PostGIS extension

• GDAL extension

• Access to a Command Prompt or similar

• A basic understanding of database terminology

Instructions

Note - Make sure the AddressBase Premium GKPG is stored in a suitable location and the file is unzipped.

1. Open Command Prompt by clicking the Windows Start button in the bottom left-hand side of the screen and typing cmd into the search bar.
2. The Command Prompt will appear.

3. Change the directory of the Command Prompt App if necessary. The directory needs to point to the folder where the GKPG is stored. For this example, the GKPG to be loaded into PostgreSQL is stored in a C:\Temp folder.

4. In the Command Prompt, type cd followed by the directory of the location of the GKPG. Press Enter on the keyboard. This will change the directory.
5. Enter the following command:

   Copy

   ogr2ogr -progress -gt 65000 -f PostgreSQL "PG:user=<username> password=<password> dbname=<database> host=<host>" <data_name>.gpkg

   Where: <username>, <password>, <database> and <host> can be found within the subsequent database. <data_name> is the AddressBase Premium GKPG file, for example, AddressBasePremium_AOI_2021-04-12_001_gpkg.gpkg. Example:

   Copy

   ogr2ogr -progress -gt 65000 -f PostgreSQL "PG:user=postgres password=PG123 dbname=osdata host=localhost" AddressBasePremium_AOI_2021-04-12_001_gpkg.gpkg

6. Open your chosen development platform (for example, dBeaver) by going to the Windows Start button and selecting your chosen software.

7. In your Database Navigator, move to the database you loaded data into and expand the schema. Using the code above, the GKPG should be in your default schema.

8. The GKPG will now appear as new tables / a new table in the schema nominated as default.

9. Once loaded, you may want to add Primary and Foreign Keys to the data. These can only be added on columns where the data values are unique. Where there are no unique data values, an index may be added which will aid searching. For the BLPU table, the UPRN provides a unique value.