Loading GeoPackage into a database

The following page provides instructions on how to load GeoPackage datasets into PostgreSQL.

Loading a GeoPackage into PostgreSQL (via GDAL / Command Prompt)

Requirements:

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

• A PostgreSQL database

• PostGIS extension

• GDAL

• Access to a Command Prompt or similar

• A GeoPackage dataset

There are various ways of loading a GeoPackage into PostgreSQL. The following step-by-step instructions run through using GDAL / Command Prompt to do this.

1. Open your chosen development platform, for example, dBeaver (this can be found by going to Windows Start Menu > PostGreSQL).

2. Either connect to an existing database or create a new database. It is recommended that the encoding is set to UTF-8. We will return to the PostgreSQL development platform later.

3. Open the Command Prompt by clicking the Start button in the bottom left-hand side of the screen and typing cmd into the search bar.

1. The Command Prompt will appear.

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

3. In the Command Prompt, type cd followed by the directory of the location of the GeoPackage. Press Enter on the keyboard. This will change the directory.

1. Enter the command:

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

username, password, database and host can all be found within the subsequent database.

coordinate_system is the EPSG of the data to be loaded, for example, to load into British National Grid, the EPSG is 27700.

data_name is the name of the GeoPackage to be loaded. Example:

// Some codeogr2ogr -progress -gt 65000 -f PostgreSQL "PG:user=postgres password=PG123 dbname=osdata host=localhost" -a_srs EPSG:27700 Greenspace.gpkg

1. Open your chosen development platform (for example, dBeaver) by going to Start > dBeaver.

2. Under Database Navigator, move to the database you loaded data into and expand the schema. Using the code above, the GeoPackage should be in your default schema (shown in bold).

3. The GeoPackage will now appear as new tables / a new table in the schema nominated as default. Nominate a schema by adding the following text to the command noted in step 7: active schema=<schema>

schema can be found within the subsequent database.

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