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

About the Postgres export issue:  The underlying geotools data store implementation defaults to a numeric feature id, so you’ll run into issues when there is a non-numeric path to a feature.  One workaround for this is to create the table in Postgres before exporting to it (or modify the one that was created by the failed export), but use a text type for the "fid" column instead of an integer.  You must also make sure the user has write access to the table in order to export to it.

Let me know if I can clarify anything else.

Thanks,
- JD


On Oct 18, 2017, at 1:01 PM, Dave Blasby <dblasby@xxxxxxxxxxxxxxxx> wrote:

Hi, Markus,

I'm not sure that the PostGIS error is a GeoGig error - I think its more likely a GeoTools PostGIS datastore issue.  However, it might be considered more of a "feature, not a bug."  Its possible that we can fix it on the GeoGig end by creating a features with a "geogig_fid" column that would be written to the DB.   You can ask on the GeoTools list - I'm not sure if there's any control over the FID column type.  The code in question is here;

I haven't really looked at format-patch -- thats a good suggestion.  I'm not sure what format it actually uses, but I expect it would be fine.  You'll have to write something that reads the patch and then executes the appropriate INSERT/DELETE/UPDATE sql commands.

Details of the patch format are here;

You can also see the FormatPatch and CreatePatchOp.

Hope that helps,
Dave


On Wed, Oct 18, 2017 at 12:03 AM, <Markus.Hesse@xxxxxxxxxxxxxx> wrote:
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

<Mail Attachment.png><Mail Attachment.png><Mail Attachment.png><Mail Attachment.png><Mail Attachment.png>



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_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
_______________________________________________
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

_______________________________________________
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

Attachment: signature.asc
Description: Message signed with OpenPGP using GPGMail

Back to the top