3

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)
  1. 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)
  1. 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)
  1. 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
  1. 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
2
  • 1
    I would try something like ogr2ogr -f gpkg -update -append -nln munic -sql "select * from munic" sp.gpkg pr.gpkg.
    – user30184
    Mar 12, 2020 at 12:55
  • Thanks, that does work for two GPKGs at a time, for each layer. The example I posted was considering just two files but for my real application, I have up to 300 GPKGs with 5 layers in each. I can resolve this by doing a loop. Of course, the simpler method would be to get ogrmerge.py to work. But meanwhile, I'll just loop through the whole thing.
    – Daniel
    Mar 12, 2020 at 18:19

1 Answer 1

0

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
1
  • Thanks. I ended up using ogr2ogr and looping through the files and layers. Not an elegant solution but it worked. But I'll try your approach next time I revisit the code.
    – Daniel
    Mar 13, 2020 at 17:53

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.