| Re: [geogig-dev] Antwort: Re: Sync changes from GeoGig repository back to Postgis |
Hi Dave,
thank you for your fast answer.
On isue on export to postgres:
What is the next step? Shall I create an issue in https://github.com/locationtech/geogig/issues?
On merging changes between geogig an postgres:
I like your idea on the branches for changes from QGIS and postgis. This way we can use the abilities of geogig to merge changes and identify conflicts.For solving the conflicts, I hope I can use the QGIS plugin. There the use should be able to visualise the conflict and take a decision, to solve the conflict in one way or another.
On sync postgis:
Here I am still a bit lost. I just tried the diff command on the prompt. That results in very interesting information, but I don't see, how to apply that to the postgis DB.
In the documentation I found something about "geogig format-patch " and "geogig apply", but that can only apply to a geogig repository as far as I understood.
Maybe you can give me some more details on your step "d) create a diff and apply it to PostGIS".
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
Von: Dave Blasby <dblasby@xxxxxxxxxxxxxxxx>
An: GeoGig project <geogig-dev@xxxxxxxxxxxxxxxx>,
Datum: 17.10.2017 23:46
Betreff: Re: [geogig-dev] Sync changes from GeoGig repository back to Postgis
Gesendet von: geogig-dev-bounces@locationtech.org
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_0ABF73E8083176C4002FAE9FC12581 <_4_BB.png> 0ABF75EC083176C4002FAE9FC12581 <_4_BB.png> 0ABF77F0083176C4002FAE9FC12581 <_4_BB.png> 0ABF79F4083176C4002FAE9FC12581 <_4_BB.png> 0ABF7BF8083176C4002FAE9FC12581 BB.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
_______________________________________________ https://dev.locationtech.org/
geogig-dev mailing list
geogig-dev@xxxxxxxxxxxxxxxx
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
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
