Is it is possible to convert query on postgresql database to geopackage file

Question

I am working on postgresql database with postgis. I was generate csv file from the database which includes geo information but I need now to generate geopackage file instead. I searched on this but I did not found any tools making something like that directly. I know I can use gdal to convert from csv to geopackage file but I do not need to make that. I need to generate the geopackage file direct from the database. Can anyone help me in that?

Thanks

Answer 1

gdal support also generate geopackage file from the table/view direct using command like the following:

ogr2ogr -f "GPKG" mynewfilename.gpkg \
PG:"host=localhost user=postgres dbname=postgres password=mypassword" "mytablename"

Answer 2

In theory: yes, it could be done. But in reality: it's almost impossible.

The geopackage file format is based on the SQLite one's. In PostgreSQL you can connect to different databases (in case of SQLite: to files) through foreign data wrappers. There is an SQLite foreign data wrapper, but it's only read-only. There is also a JDBC wrapper, which can support SQLite. But even if you can manage to write a new SQLite database file from PostgreSQL, you will need to study geopackage's internal format -- with just a "Coming soon" Implementation Guide (as of writing).