0

I have successfully exported more than 11.000 PostGIS views to geopackages. I use the GDAL command line for this with ogr2ogr along with GNU parallel for parallel processing. However, for some reason, there are about 60 views that are problematic and raise errors.

The bash command:

#!/bin/bash

tiles=('tile_1' 'tile_2' 'tile_3' etc..)

process_tile() {
  local tile=$1
  echo "Processing ${tile}..."
  ogr2ogr -f "GPKG" "exports_DT/${tile}_2023.gpkg" PG:"dbname='db' host='host' port='5432' user='user' password='password' active_schema='tin' sslmode='require'" -sql "SELECT geom FROM tin.${tile}" -geomfield "geom" -a_srs "EPSG:7415" -nlt "polygonz" nln "tin" -overwrite
  echo "Done with ${tile}"
}

export -f process_tile


parallel --jobs 10 process_tile ::: "${tiles[@]}"

The error these few views raise:

ERROR 1: sqlite3_exec(CREATE TRIGGER "trigger_insert_feature_count_tin" AFTER INSERT ON "tin" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count + 1 WHERE lower(table_name) = lower('tin'); END;) failed: no such table: main.tin

I do not understand this and why it's raising this only on these views. It is correct in that there's not table by that name, because they're all views. I also have no need for these tables that count the processed tiles nor the triggers that are being created eacht time. I have no idea where this is comming from.

1

1 Answer 1

3

The error comes from the SQLite (GeoPackage) side. It is the GeoPackage that requires the triggers. However, in this case the trigger that gives the error is not defined by the GeoPackage standard but it is an additional trigger that GDAL likes to create. You can have a new try by disabling it https://gdal.org/drivers/vector/gpkg.html#dataset-creation-options

ADD_GPKG_OGR_CONTENTS=[YES/NO]: Defaults to YES. Defines whether to add a gpkg_ogr_contents table to keep feature count, and associated triggers.

Re-run by adding -dsco ADD_GPKG_OGR_CONTENTS=NO. However, the real error is probably occurred already before. The trigger cannot be created because the GeoPackage table "tin" does not exist due to some error when converting the result of the SQL query from the PostGIS view into GeoPackage. Adding --debug on into your ogr2ogr command may give some more information. Otherwise you must study what is special when the creation of "tin" fails. My guess is that there is no geometry and the query SELECT geom FROM tin.${tile} returns an empty result set.

2
  • Thank you! You were correct in identifying that the issue occurred prior to the creation of the trigger, and it wasn't related to missing geometry. I resolved the problem by exporting the problematic data to a local PostgreSQL database. From there, I encountered no further issues. I remain unsure of the exact cause of the problem, but it may have been related to our PostgreSQL server. Jun 20, 2023 at 12:18
  • In some cases I have had same kind of problems if the view or the SQL query used directly in the -sql parameter returns duplicate ID values, which are then tried to use as primary key in the GeoPackage table.
    – user30184
    Jun 20, 2023 at 12:37

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.