Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [geogig-dev] Sync changes from GeoGig repository back to Postgis
Hi, markus,

I took a deeper looker look at what you were doing -- I think i misunderstood you.

I'm able to reproduce what you're seeing.  The issue is that its creating a table with an integer fid column - and they trying to put string-fids in it.  That's an issue!  I think this is an issue in the PostGIS GeoTools datastore (I havent looked in detail).


There isn't a lot of support for exporting machine-readable diffs - the QGIS plugin uses the web-api "export-diff" command to build a geopkg with the diff information in it.  I'm not sure I'd recommend that as the best approach (its, also, not available via the CLI).  We have been thinking of coming up with a "change set" file format to deal with this type of information.

The main issue with allowing people to update the PostGIS table is that you need to be able to track what change's they've made.  You can do this with audit triggers on the database, or you can can do a brute-force diff using GeoGig to do a feature-by-feature compare to see what's changed.  

Here's a change example,

PostGIS:  in state A
import to GeoGig - in state A

Make change to PostGIS -- now in state B (A->B)
Make changes to GeoGig -- now in stage C (A->C)

Now, how do you reconcile this?  There's lots of way to do it, but here's one;

a) create a branch in geogig, based on A
b) move PostGIS changes to the branch (A->B)
c) merge changes from geogig (A->C) onto the branch
d) create a diff and apply it to PostGIS

If there are conflicts, this gets a lot more complex.  Also, if people are modifying the database while you are doing this maintenance you will mess things up.

There are other ways to do this (depending on your situation), but they are also quite complex...

Cheers,
Dave


On Tue, Oct 17, 2017 at 1:30 PM, <Markus.Hesse@xxxxxxxxxxxxxx> wrote:
Hallo Dave,

Thank you for the answer. 
As mentioned im my initial mail, export to Postgres did not work for me at all, regardless if overwriting an existing table or creating a new one. So I ask myself,  is there an error in geogig or do I something wrong. 

The diff command sounds interesting. Can you give me some more details on that? 

On write access to Postgres: I’m afraid, I cannot disallow writing directly to Postgres. I thought, I can do a geogig export to apply any changes from Postgres to geogig. I know, that this will take the same time like an initial full export, but I hope, that will be acceptable. What do you mean with “quite a bit of "little" thing”? Can you give me some keywords about that?

Am 17.10.2017 um 21:54 schrieb 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

<_4_0ABF73E8083176C4002FAE9FC12581BB.png><_4_0ABF75EC083176C4002FAE9FC12581BB.png><_4_0ABF77F0083176C4002FAE9FC12581BB.png><_4_0ABF79F4083176C4002FAE9FC12581BB.png><_4_0ABF7BF8083176C4002FAE9FC12581BB.png> 

_______________________________________________________

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

_______________________________________________________

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

Back to the top