3

I have a geopackage (from the 'input' app) which is being inserted into a postgres/postGIS table via an ogr2ogr script on a daily basis:

ogr2ogr -append -f "PostgreSQL" "PG:user=xxxx host=xxxx password=xxxx dbname=xxxx" "path\Invoer.gpkg" -nln invoer --config OGR_TRUNCATE YES

The geopackage has one column (fid) that has been set to auto increment in QGIS/input on each new record made. With the above script it completely copies the table/layer including the auto-increment field default to a PostgreSQL table. The problem is that with every update the fid field recounts from the last fid in the previous (unupdated) table.

How do I change this script so that it copies the fid as a number data field that does NOT include the increment default value?

6
  • Could you drop and create a new table or do you need to use the truncate option for example because of views and triggers that depend on the "invoer" table?
    – user30184
    Aug 11 at 10:06
  • You need to reset your sequence that is used to generate the fid
    – Ian Turton
    Aug 11 at 10:54
  • @user30184 Yes, I need to truncate because of triggers and foreign keys. Aug 11 at 11:15
  • And you aim is to have the same feature ids that you have in GeoPackage into PostGIS in a stable and reliable way?
    – user30184
    Aug 11 at 11:31
  • @user30184 Yes! That is exactly what my aim is! The GeoPackage fid needs to stay a sequence but the PostGIS table fid's should be a copy of those number and not have a sequence. Aug 11 at 11:36
5

I suggest to store the geopackage fid as a separate attribute into PostGIS. You can add a UNIQUE constraint to the attribute but it should not be needed if you truncate the table anyway each time. The fids in source GeoPackage are guaranteed to be unique.

Check what is the fid field in the GeoPackage:

ogrinfo test.gpkg my_layer
...
FID Column = fid
Geometry Column = geom

Select the fid as a normal attribute with SQL

ogrinfo -sql "select *, fid as gpkg_fid from my_layer" test.gpkg
...
OGRFeature(SELECT):26
  gpkg_fid (Integer64) = 26

Here the OGRFeature: shows the automatically recognized FID and gpkg_fid is the new copy of that. If GDAL for some reason does not find FID Column it should be safe to use rowid as a name of the fid column.

The same SQL can be used with the ogr2ogr command. I used select *,fid because it is simple to write. By using that GDAL actually selects "fid" two times, once to be used as a special feature id field and second time as a normal attribute. If this makes trouble it is possible to select every field explicitly

select fid as gpkg_fid, geom, attribute_1, attribute_2... from...

You can insert this data into PostGIS and let GDAL to create an autoincrementing FID. The value of this FID will grow when you truncate and append new data but the correct GeoPackage fids are always stored into the gpkg_fid field.

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.