1

I'm using ogr2ogr to write a SQL query from PostGIS to a GeoPackage in a linux shell. Query by Query to many GPKG:

ogr2ogr -f "GPKG" point.gpkg PG:"host=localhost dbname=data user=user password=*******" -sql "select * from point-features"
ogr2ogr -f "GPKG" line.gpkg PG:"host=localhost dbname=data user=user password=*******" -sql "select * from line-features"
ogr2ogr -f "GPKG" polygon.gpkg PG:"host=localhost dbname=data user=user password=*******" -sql "select * from polygon-features"

I would like to write multiple layers from queries into one GeoPackage, but I cannot find a documentation to this.

Like:

ogr2ogr -f "GPKG" pointlinepolygon.gpkg pointlayer linelayer polygonlayer PG:"host=localhost dbname=data user=user password=*******" -sql "select * from pointlinepolygon-features"

Would be happy about support.

4

You have different SQL statements, so you can't do one single line.

You can do mutiple statements like this:

ogr2ogr -f "GPKG" your_gpkg.gpkg PG:"host=localhost dbname=etienne user=postgres password=foo" -nln points -sql "SELECT * FROM points"
ogr2ogr -f "GPKG" your_gpkg.gpkg PG:"host=localhost dbname=etienne user=postgres password=foo" -nln lines -update  -sql "SELECT * FROM lines"

This will give you 2 layers in the Geopackage:

ogrinfo your_gpkg.gpkg 
INFO: Open of `your_gpkg.gpkg'
      using driver `GPKG' successful.
1: points
2: lines

The -nln is for specifying the layer name. They need to be different. https://gdal.org/programs/ogr2ogr.html#cmdoption-ogr2ogr-nln

And -update is to open the layer in update mode. https://gdal.org/programs/ogr2ogr.html#cmdoption-ogr2ogr-update

Edited, I first used -append and it worked. But as suggested in comment, I have updated the answer to use -update which is designed for this.

  • 1
    If you read the document carefully append does a different thing Append to existing layer instead of creating new. I would say that the option that is needed is update gdal.org/programs/ogr2ogr.html#cmdoption-ogr2ogr-update Open existing output datasource in update mode rather than trying to create a new one. Did your second command really work for you? – user30184 Jul 5 at 18:37
  • You are correct. update would be better indeed. And yes the second command really works. The OGRInfo that I showed is real. I also tried in QGIS, it's works. I will update my answer, even if it works. – etrimaille Jul 5 at 19:50
  • I did a test myself and you are right. I would have thought that without -update ogr2ogr would delete the old "your_gpkg.gpkg" database and create a new one, so that finally there would be a database with just "lines" layer. I get two layers also into plain sqlite and spatialite outputs so -append seems fire also -update. – user30184 Jul 5 at 19:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.