Re: [geogig-dev] Sync changes from GeoGig repository back to Postgis

[Dave Blasby <dblasby@xxxxxxxxxxxxxxxx>]

Hi, Markus,

Glad to see that you are using GeoGig - sorry it isn't doing quite what you are expecting.

The "pg import" and "pg export" commands are quite simple - they are used to load (import) a dataset into GeoGig and to create a new database table with geogig data in it (export).

The export command is meant to create a new table and put the data in - like exporting a brand new shapefile.  It wasn't designed to maintain the original dataset over time.

However, you can use GeoGig to support this.  In general, you'd get a diff from GeoGig and then "apply" that diff to the PostGIS table via INSERT/DELETE/UPDATE sql commands.  If you don't allow users to update the PostGIS table outside of applying controlled diffs from GeoGig then the process isn't very complex.  It gets much more complex if you allow users to makes changes to the PostGIS table. 

The QGIS plugin uses a GeoPKG file that is maintained by the QGIS plugin - you can look into this for more details.  It tracks local changes to the GeoPKG file (the "-i" option puts in audit tables) so it can provide sophisticated functionality.  However, if multiple people are allowed write access to the postgis database, you'll likely want to make some changes to the process so writes do not occur while the database is being "maintained" by GeoGig.  Also, be careful with FIDs - you can see the GeoPKG file maintains FIDs separately.  

Let me know if this helps.  If you do not allow write access to the PostGIS database, then having GeoGig keep it up-to-date isn't very complex; but there's still quite a bit of "little" things to do.

Thanks,

Dave

On Mon, Oct 16, 2017 at 1:40 AM, <Markus.Hesse@xxxxxxxxxxxxxx> wrote:

Hello,

I'm new on geogig, so I hope this is the right place to ask this question.

I have a postgis DB and want to do changes with QGIS in a version controlled environment. So I tried the following:
- Export data from Postgis to Geogig repository
- Load data via Geogis plugin to QGIS
- Do some changes in QGIS
- Sync to Geogig repository (via Geogig plugin).

That runs more or less. But now I want to sync back the changes to postgis, so that postgis remains the final place to store the data.

Question: Is that actually the right way?

I tried to sync to postgres by
geogig pg export --host $GG_HOST --port $GG_PORT --schema $GG_SCHEMA --database $GG_DATABASE --user $GG_USER --password $GG_PASSWORD -o test test

The table in question is names "test". The environment variables ( $GG_HOST) are set as needed.
This fails with the messages "Can't write to the selected table"

The log in .geogig/log/geogig.log is:
2017-10-16 10:13:06,865 ERROR [main] o.l.g.c.GeogigCLI [GeogigCLI.java:390] Can't write to the selected table
org.locationtech.geogig.cli.CommandFailedException: Can't write to the selected table
        at org.locationtech.geogig.geotools.cli.DataStoreExport.exportInternal(DataStoreExport.java:143) ~[geogig-geotools-1.2-SNAPSHOT.jar:1.2-SNAPSHOT
        at org.locationtech.geogig.geotools.cli.DataStoreExport.runInternal(DataStoreExport.java:99) ~[geogig-geotools-1.2-SNAPSHOT.jar:1.2-SNAPSHOT]
        at org.locationtech.geogig.cli.AbstractCommand.run(AbstractCommand.java:68) ~[geogig-cli-1.2-SNAPSHOT.jar:1.2-SNAPSHOT]
        at org.locationtech.geogig.cli.GeogigCLI.executeInternal(GeogigCLI.java:532) ~[geogig-cli-1.2-SNAPSHOT.jar:1.2-SNAPSHOT]
        at org.locationtech.geogig.cli.GeogigCLI.execute(GeogigCLI.java:368) ~[geogig-cli-1.2-SNAPSHOT.jar:1.2-SNAPSHOT]
        at org.locationtech.geogig.cli.app.CLI.run(CLI.java:95) [geogig-cli-app-1.2-SNAPSHOT.jar:1.2-SNAPSHOT]
        at org.locationtech.geogig.cli.app.CLI.main(CLI.java:113) [geogig-cli-app-1.2-SNAPSHOT.jar:1.2-SNAPSHOT]


Additionally I tried to export in a new table in postgis:
geogig pg export --host $GG_HOST --port $GG_PORT --schema $GG_SCHEMA --database $GG_DATABASE --user $GG_USER --password $GG_PASSWORD -o test test2

Now the message is "Could not export. Error:UNABLE_TO_ADD"
The table has been created in postgres, but it is empty. Furthermore it contains the additional field "fid", so the structure is no more identical to the initial table. Maybe that is the reason, the export to "test" failed.

The log in .geogig/log/geogig.log is:
2017-10-16 10:35:11,300 ERROR [main] o.l.g.c.GeogigCLI [GeogigCLI.java:390] Could not export. Error:UNABLE_TO_ADD
java.lang.IllegalArgumentException: Value fid--80be629_15f0bd64ebd_-7ece illegal for type java.lang.Integer
        at org.geotools.jdbc.JDBCDataStore.decodeFID(JDBCDataStore.java:2208) ~[gt-jdbc-17.1.jar:na]
        at org.geotools.jdbc.KeysFetcher$Existing.getNextValues(KeysFetcher.java:232) ~[gt-jdbc-17.1.jar:na]
        at org.geotools.jdbc.KeysFetcher.setKeyValues(KeysFetcher.java:102) ~[gt-jdbc-17.1.jar:na]
        at org.geotools.jdbc.JDBCDataStore.insertSQL(JDBCDataStore.java:4078) ~[gt-jdbc-17.1.jar:na]
        at org.geotools.jdbc.JDBCDataStore.insertNonPS(JDBCDataStore.java:1868) ~[gt-jdbc-17.1.jar:na]
        at org.geotools.jdbc.JDBCDataStore.insert(JDBCDataStore.java:1706) ~[gt-jdbc-17.1.jar:na]
        at org.geotools.jdbc.JDBCInsertFeatureWriter.flush(JDBCInsertFeatureWriter.java:128) ~[gt-jdbc-17.1.jar:na]
        at org.geotools.jdbc.JDBCInsertFeatureWriter.write(JDBCInsertFeatureWriter.java:100) ~[gt-jdbc-17.1.jar:na]
        at org.geotools.data.InProcessLockingManager$1.write(InProcessLockingManager.java:337) ~[gt-main-17.1.jar:na]
        at org.geotools.data.store.ContentFeatureStore.addFeature(ContentFeatureStore.java:308) ~[gt-data-17.1.jar:na]
        at org.geotools.data.store.ContentFeatureStore.addFeatures(ContentFeatureStore.java:259) ~[gt-data-17.1.jar:na]
        at org.locationtech.geogig.geotools.plumbing.ExportOp._call(ExportOp.java:194) ~[geogig-geotools-1.2-SNAPSHOT.jar:1.2-SNAPSHOT]
        at org.locationtech.geogig.geotools.plumbing.ExportOp._call(ExportOp.java:82) ~[geogig-geotools-1.2-SNAPSHOT.jar:1.2-SNAPSHOT]
        at org.locationtech.geogig.repository.AbstractGeoGigOp.call(AbstractGeoGigOp.java:154) ~[geogig-api-1.2-SNAPSHOT.jar:1.2-SNAPSHOT]
        at org.locationtech.geogig.geotools.cli.DataStoreExport.exportInternal(DataStoreExport.java:167) ~[geogig-geotools-1.2-SNAPSHOT.jar:1.2-SNAPSHOT]
        at org.locationtech.geogig.geotools.cli.DataStoreExport.runInternal(DataStoreExport.java:99) ~[geogig-geotools-1.2-SNAPSHOT.jar:1.2-SNAPSHOT]
        at org.locationtech.geogig.cli.AbstractCommand.run(AbstractCommand.java:68) ~[geogig-cli-1.2-SNAPSHOT.jar:1.2-SNAPSHOT]
        at org.locationtech.geogig.cli.GeogigCLI.executeInternal(GeogigCLI.java:532) ~[geogig-cli-1.2-SNAPSHOT.jar:1.2-SNAPSHOT]
        at org.locationtech.geogig.cli.GeogigCLI.execute(GeogigCLI.java:368) ~[geogig-cli-1.2-SNAPSHOT.jar:1.2-SNAPSHOT]
        at org.locationtech.geogig.cli.app.CLI.run(CLI.java:95) [geogig-cli-app-1.2-SNAPSHOT.jar:1.2-SNAPSHOT]
        at org.locationtech.geogig.cli.app.CLI.main(CLI.java:113) [geogig-cli-app-1.2-SNAPSHOT.jar:1.2-SNAPSHOT]


Any ideas?




Mit freundlichen Grüßen / Kind regards

Markus Heße
Software Architekt – Projektleiter /
Software Architect – Project Manager

Mettenmeier GmbH
Klingenderstr. 10 - 14
33100 Paderborn, Germany

T  +49 5251 150-363
F  +49 5251 150-366
M  +49 173 2474123
E  markus.hesse@xxxxxxxxxxxxxx
I   www.mettenmeier.de

_______________________________________________________

Mettenmeier GmbH
Sitz der Gesellschaft: 33100 Paderborn
Geschaeftsfuehrung: Ulrich Mettenmeier
Handelsregister: Amtsgericht Paderborn B 1114
USt.-ID: DE 811 324 124
Steuer-Nr.: 339/5873/3133
_______________________________________________________

_______________________________________________
geogig-dev mailing list
geogig-dev@xxxxxxxxxxxxxxxx
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.locationtech.org/mailman/listinfo/geogig-dev