12

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.

Improve this question

1 Answer 1

Reset to default
15

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.

Improve this answer
3
  • 4
    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
    Commented Jul 5, 2019 at 18:37
  • 1
    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
    Commented Jul 5, 2019 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
    Commented Jul 5, 2019 at 19:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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