7

I have been trying to find an article like http://postgis.net/workshops/postgis-intro/history_tracking.html that explains how I can do the following with a GPKG (ideally in QGIS)

  1. Sync edits made on and offline by multiple users
  2. See which user edited which records without having an auto update column in the table with this info or using http://geogig.org/

I understand that some of this is part of QField Sync but when I try it using QGIS 3.11, it just opens all the tables additional times and I don't see the modified data. I also want to understand how syncing and having an audit trail in a geopackage works as explained in this talk https://media.ccc.de/v/bucharest-262-field-data-collection-strategies-using-qfield-and-qgis#t=743.

I have reviewed http://www.geopackage.org/spec121/ and it mentions syncing but there is no info on how it's practically achieved.

How do I sync edits made offline by multiple users and have an audit trail?

  • 2
    Can you please update your post with a specific question? As it currently stands it is difficult to give an answer. – Matthias Kuhn Oct 29 '19 at 13:00
  • 1
    @Mapperz - I have added in a specific question. – GeorgeC Oct 30 '19 at 0:13
  • @Matthias Kuhn would you be able to help on this now that I've clarified the question? – GeorgeC Nov 5 '19 at 10:17
7
+50

There are multiple questions involved and without a specific project I can only give general hints. It needs a system architect with an understanding of workflows, data structures, business processes and the details of the tools described below to create a reliable system with this.

Offline Synchronization

The QFieldSync plugin takes offline copies of features from any kind of feature source, e.g. a postgis database. This source will have a copy of every feature in the database and create logging tables. Every change on this offline data will also create an additional entry in the log tables if it's done in QGIS or QField. When the data is synchronized back it will replay all these changes on the main data source (e.g. the postis database). This functionality is leveraged by QFieldSync from a QGIS builtin functionality "offline editing" which is also available through the core offline editing plugin.

If something does not work as expected here, it's often most interesting to check the log tables in the geopackage (e.g. using the db manager in QGIS). If they do not exist, something went wrong with creating the offline version. If they do not contain anything, the edits have been done in an environment which did not have offline editing enabled.

Audit trail

When synchronizing the data back into the main database, there needs to be some link to the user who edited this data. There are several possibilities how to accomplish this:

  • Use a field with a default value like @user_full_name. This is updated by QGIS/QField and can optionally be flagged with "update on edit" to fill a "last_changed_by" column. This relies on changes being done in an environment that supports QGIS Expressions and you need to trust your users that they don't fake this information. Furthermore you also get the information who changed this last and not a full history of your data.

  • Use a field on the database and fill it with a trigger. Postgres can update fields on update. See this answer on how to do it for a timestamp and expand this to include CURRENT_USER which is the user which is executing the SQL. This requires that you have setup your database for multiple users and that everyone logs in/runs the synchronisation with his own account. The same restrictions on missing history apply (but can be solved with more triggers)

  • Another possibility is to use audit triggers in your database, this will offer a complete postgres-database based solution.

I don't think the geopackage spec is relevant here, nor do I have experience with the postgis history tracking that is linked in the question.

| improve this answer | |
  • Thanks. I want to try and use GPKG without having to go to a full fledged database. I created a demo qgs project and used qfield sync (I uninstalled and re-installed it). I made the field data point/line/polygon layers that come from a gpkg set as "offline editing". I used qfield on the tablet and then tried to bring the data back in but I get an error saying there's no qgs project in the folder, in earlier attempts it just reloaded all the data. I have packaged the sample data in the following link drive.google.com/file/d/1Xc1ElDQRnYjoL8MTtYJ-e_CMx6wuH-ya/… – GeorgeC Nov 8 '19 at 2:48
  • I am using the dev version of qfield based on the comments in github.com/opengisch/QField/issues/609#issuecomment-549266765 and am using the nightly dev version of QGIS. – GeorgeC Nov 8 '19 at 2:50
  • Which folder of the sample data did you import with QFieldSync? – Matthias Kuhn Nov 9 '19 at 11:29
  • 1
    There must only be one single .qgs project in the folder, there are currently two in there (the one you used for data collection in the field). I admit error reporting could be a bit better in this case. – Matthias Kuhn Nov 10 '19 at 10:46
  • 1
    So I open the main project I ran QFIELD sync PACKAGE from and then select the folder where the field data and project is and then run Synchronize. It then works as expected. – GeorgeC Nov 11 '19 at 1:27

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.