GDAL

Using GDAL to load a GeoPackage into a database

GDAL is a translator library for raster and vector geospatial data formats that is released under an X/MIT style Open Source License by the Open Source Geospatial Foundation. It comes with a variety of useful command line utilities for data translation and processing. The following section covers the loading of GeoPackage datasets into a PostgreSQL database using the ETL tool GDAL. The process will be similar for other databases such as Oracle and SQL Server, as well as converting to other data formats.

Requirements

• A PostgreSQL database with PostGIS extension enabled

• GDAL version 1.11.0 or above (with access to a command line interface to use it)

• A GeoPackage dataset

Instructions

1

Use the ogrinfo program to list information about the GeoPackage

ogrinfo <PATH_TO_GEOPACKAGE>

Example:

ogrinfo wtr_ntwk_waterlinkset.gpkg

ogrinfo output

INFO: Open of `C:\wtr_ntwk_waterlinkset.gpkg'
      using driver `GPKG' successful.
1: wtr_ntwk_waterlinkset
2: wtr_ntwk_waterlinkset_wtrlinkref (None)

Without any arguments supplied, ogrinfo will return the layers contained within the GeoPackage.

2

Use ‘Summary Only’ (-so) and ‘List all features of all layers’ (-al) arguments to view summary information about the layers within the GeoPackage

ogrinfo<PATH_TO_GEOPACKAGE>-so -al

Example:

ogrinfo wtr_ntwk_waterlinkset.gpkg -so -al

ogrinfo detailed output

INFO: Open of `C:\wtr_ntwk_waterlinkset.gpkg'
      using driver `GPKG' successful.
Layer name: wtr_ntwk_waterlinkset
Geometry: Unknown (any)
Feature Count: 116
Extent: (209562.134000, 79229.510000) - (341688.834000, 853194.875000)
Layer SRS WKT:
PROJCRS["OSGB 1936 / British National Grid",
[...]

Combined, these arguments will provide summary information about all the layers within the GeoPackage, including projection, schema, feature count and extents.

3

Load the GeoPackage into a PostgreSQL database using the ogr2ogr program

The arguments below will load all layers from the source GeoPackage into the specified target schema in the database:

ogr2ogr -f PostgreSQL "PG:user=<USERNAME>password=<PASSWORD> dbname=<DATABASENAME>host=<HOST>port=<PORTNUMBER>active_schema=<TARGETSCHEMA>" <PATHTOGEOPACKAGE>

<USERNAME>, <PASSWORD>, <DATABASENAME>, <HOST>, <PORTNUMBER> are the connection details of the target PostgreSQL database.

<TARGETSCHEMA> is the schema in the database that the layers should be loaded into. If this doesn’t exist or if it is omitted, they will be loaded into the default schema, the default is usually the ‘public’ schema.

ogr2ogr -f PostgreSQL "PG:user=example_user password=example_password dbname=postgres host=localhost port=5432 active_schema=example_schema" wtr_ntwk_waterlinkset.gpkg

This will create twp tables in the example_schema schema:

schemaname     | tablename
---------------+---------------------------------
example_schema | wtr_ntwk_waterlinkset
example_schema | wtr_ntwk_waterlinkset_wtrlinkref

Different loading options (including renaming tables, reprojecting the data, etc.) can be found on the PostgreSQL / PostGIS — GDAL documentation page.