Merge geopackages keeping layer structure

Question

I have a bunch of geopackages for each state, and all have the same layer structure (same name and type). I'd like to merge the state level gpkg into a single gpkg, keeping the same layer structure, i.e. have one layer named munic with all the municipalities form the other layers.

I'm trying to do this using ogrmerge.py but somehow I can't figure out the parameters to use. Is it possible to do what I want with ogrmerge.py? Or should I use some other tool?

Here is what I tried and the results I'm getting:

ogrinfo of the two input layers

ogrinfo sp.gpkg 
INFO: Open of `sp.gpkg'
      using driver `GPKG' successful.
1: munic (Multi Polygon)
2: uf (Multi Polygon)

ogrinfo pr.gpkg 
INFO: Open of `pr.gpkg'
      using driver `GPKG' successful.
1: munic (Multi Polygon)
2: uf (Multi Polygon)

My desired output would be a GPKG with two layers, munic and uf, that contains the data from both pr.gpkg and sp.gpkg files

1. ogrmerge without any parameters - The layers for each state are separated. Not what I want

ogrmerge.py -f GPKG -o br.gpkg pr.gpkg sp.gpkg

ogrinfo br.gpkg 
INFO: Open of `br.gpkg'
      using driver `GPKG' successful.
1: pr_munic (Multi Polygon)
2: pr_uf (Multi Polygon)
3: sp_munic (Multi Polygon)
4: sp_uf (Multi Polygon)

2. with -single parameter. All layers are merged into one. Not what I want

ogrmerge.py -f GPKG -single -o br.gpkg pr.gpkg sp.gpkg

ogrinfo br.gpkg 
INFO: Open of `br.gpkg'
      using driver `GPKG' successful.
1: merged (Multi Polygon)

3. Using layer template option with -single. An empty br.gpkg is created

ogrmerge.py -f GPKG -single -nln {LAYER_NAME} -o br.gpkg pr.gpkg sp.gpkg
ERROR 1: The layer name may not contain special characters or spaces
ERROR 1: Terminating translation prematurely after failed
translation of layer {LAYER_NAME} (use -skipfailures to skip errors)

4. Using layer template WITHOUT -single flag. An empty output is created

ogrmerge.py -f GPKG  -nln {LAYER_NAME} -o br.gpkg pr.gpkg sp.gpkg
ERROR 1: Layer munic already exists, and -append not specified.
        Consider using -append, or -overwrite.
ERROR 1: Terminating translation prematurely after failed
translation of layer munic (use -skipfailures to skip errors)
ERROR 1: sqlite3_exec(CREATE TRIGGER "trigger_insert_feature_count_munic" AFTER INSERT ON "munic" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count + 1 WHERE lower(table_name) = lower('munic'); END;) failed: no such table: main.munic
ERROR 1: sqlite3_exec(CREATE TRIGGER "trigger_delete_feature_count_munic" AFTER DELETE ON "munic" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count - 1 WHERE lower(table_name) = lower('munic'); END;) failed: no such table: main.munic
ERROR 1: sqlite3_exec(CREATE TRIGGER "trigger_insert_feature_count_uf" AFTER INSERT ON "uf" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count + 1 WHERE lower(table_name) = lower('uf'); END;) failed: no such table: main.uf
ERROR 1: sqlite3_exec(CREATE TRIGGER "trigger_delete_feature_count_uf" AFTER DELETE ON "uf" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count - 1 WHERE lower(table_name) = lower('uf'); END;) failed: no such table: main.uf

5. Using layer template with -append. Same error apears with -update

ogrmerge.py -f GPKG  -nln {LAYER_NAME} -append -o br.gpkg pr.gpkg sp.gpkg
ERROR: -f incompatible with -update

Answer 1

You have found a missing feature from the ogrmerge.py Python script. You need to use the -append switch for adding more data into the tables munic and uf but when you start the job the tables do not exist so there is no place to append data.

You must run ogrmerge twice but two times is enough. First create the output GeoPackage from your first input GeoPackage and then append data from the rest of you GeoPackages.

ogrmerge  -f gpkg -o mergetest.gpkg -nln {LAYER_NAME} test1.gpkg
ogrmerge  -append -o mergetest.gpkg -nln {LAYER_NAME} test2.gpkg test3.gpkg