One of the challenges of data collection projects is consolidating all the data a central database, such as Postgres/PostGIS. Using PostGIS as a live survey layer is not usually recommended:
A more cumbersome way around this, is to convert your tables from PostGIS to a file based GIS layer (e.g. GeoPackage) and take the files with you to the field. This will create a new problem: keeping all the tables (from multiple surveyors) and the PostGIS table up-to-date.
During a survey to assess water access for villages in Limpopo province, South Africa, our friends at Kartoza have commissioned us to extend the Mergin service to support PostGIS. The Mergin service already supports file-based data synchronisation. The aim was to bridge the gap between the Mergin service and PostGIS so that the changes from Mergin immediately appear on PostGIS and vice versa.
To facilitate that, we further developed the Geodiff library to support Postgres driver. In addition, we developed mergin-db-sync tool to sync the tables from Postgres database with the Mergin service. The mergin-db-sync tool runs as a service (daemon) that keeps an eye on a particular Mergin project, and if there is a new version of the project, it will fetch the most recent changes and apply them to database tables in PostgreSQL. It works also the other way around at the same time: it looks for changes in the configured PostgreSQL schema and upload them in a new version of the Mergin project if any changes were detected. This service can be easily started on the local Intranet (where the PostgreSQL database is run) and therefore it does not need any adjustments to the firewall to allow access to the local database from public Internet.
The above diagram details how Postgres/PostGIS synchronisation works with the Mergin service via the DB-Sync tool.
From the surveyors’ point of view, the extra set up to sync with the Postgres/PostGIS does not affect their workflow. In fact, mergin-db-sync tool acts as another client syncing data to the Mergin project, therefore it is possible to see all the changes in the project log originated from mergin-db-sync tool.
The tool is available on GitHub with a permissive open source license (MIT). At this point it supports PostgreSQL, but the mechanism is fairly generic and support for other database engines may be added in the future without great effort. All the heavy lifting is done by the Geodiff library which has been significantly enhanced during the development of mergin-db-sync tool.
If you have any issues or feedback to enhance the tool, you can file a ticket on the project repo.
If you’d like to set up DB-Sync tool with your Mergin survey projects, you can contact us on firstname.lastname@example.org