GDAL

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

You can interrogate a GeoPackage dataset using the ogrinfo program which lists information about it. At a basic level it will return the layers contained within it:

ogrinfo <PATH_TO_GEOPACKAGE>

ogrinfo wtr_ntwk_waterlinkset.gpkg

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

Using the ‘Summary Only’ (-so) and ‘List all features of all layers’ (-al) arguments you can view summary information about all the layers within the GeoPackage, including projection, schema, feature count and extents:

ogrinfo<PATH_TO_GEOPACKAGE>-so -al

ogrinfo wtr_ntwk_waterlinkset.gpkg -so -al

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",
[...]

The GeoPackage can be loaded into a PostgreSQL database using the ogr2ogr program, the 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

Will create two 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.