PostgreSQL-GeoPackage

This repository holds scripts used to evaluated the suitability of PostgreSQL to serve as alternative to SQLite for a container of raster tiles as specified in the GeoPackage standard [OGC 12-128r11].

Usage

Follow the Vagrant instructions for a clean environment and connect to it:

vagrant ssh
cd PostgreSQL-GeoPackage/

Create a PostgreSQL database and load the GeoPackage schema into the PostgreSQL-GeoPackage:

createdb -E UTF8 -U gpkg gpkg
psql -U gpkg gpkg -f gpkg-pg_init.sql

Load a SQLite GeoPackage into the PostgreSQL-GeoPackage, dump it again, and validate the result of the round-trip:

./gpkg-pg_loadpkg.py Sample-GeoPackage_Sentinel-2_Vienna_Austria.gpkg "dbname='gpkg' user='gpkg'"
sqlite3 Sample-GeoPackage_Sentinel-2_Vienna_Austria.gpkg .dump > before
rm Sample-GeoPackage_Sentinel-2_Vienna_Austria.gpkg
./gpkg-pg_dump.py "dbname='gpkg' user='gpkg'" Sample-GeoPackage_Sentinel-2_Vienna_Austria
sqlite3 Sample-GeoPackage_Sentinel-2_Vienna_Austria.gpkg .dump > after
diff before after

Dump a spatial subset of the PostgreSQL-GeoPackage and validate it by visual comparison to a GDAL generated subset:

gdal_translate -of GPKG -srcwin 768 768 256 256 Sample-GeoPackage_Sentinel-2_Vienna_Austria.gpkg Sample-GeoPackage_Sentinel-2_Vienna_Austria_subset_gdal.gpkg
rm Sample-GeoPackage_Sentinel-2_Vienna_Austria.gpkg
./gpkg-pg_dump.py "dbname='gpkg' user='gpkg'" Sample-GeoPackage_Sentinel-2_Vienna_Austria -srcwin 3 3 1 1

Finally, drop the PostgreSQL-GeoPackage:

./gpkg-pg_drop.py "dbname='gpkg' user='gpkg'" Sample-GeoPackage_Sentinel-2_Vienna_Austria

Acknowledgment

The sample SQLite GeoPackage was created from Sentinel-2 data using GDAL.

Legal notice: Contains modified Copernicus Sentinel data [2016]