[GIS] Version editing for Postgresql 9.2.4 / postGIS 2

editingpostgisqgisversioning

Background:
I am "building" a GIS with PostgreSQL 9.2.4 / PostGIS 2 as the database and QGIS 1.8.0 as the geographic application. There will be multiple users accessing, editing and viewing the data at the same time.

Question:

Is version editing automatically enforced in PostgreSQL 9.2.4 / PostGIS 2?
Is it possible to enable/enforce version editing and if yes how?

I have had a look at pgVersion for PostgreSQL 9.2.4 / PostGIS 2 and the QGIS pgVersion plugin. However, if I understand correctly (reading the help files and testing it), pgVersion only allows editing of cell values of existing columns and also no new geographic features can be added.

Best Answer

Yes it seems that PostgreSQL/PostGIS does allow multiple users to edit and view the same data at the same time automatically, whether it is viewed or edited in PostgreSQL/PostGIS or QGIS. However, if not properly managed this, apparently, may result in overlapping and/or corrupt features.

Here are the links from which I made my conclusions:

Please note that the question and answers in the above links were asked/answered about 5 years ago, which mean they point to earlier program versions. However, I cannot imagine the newer versions of PostgreSQL/PostGIS not supporting multi-user editing (might even have been enhanced).

Update: For those using QGIS 1.8.0, under the tab 'Table' in DB Manager there is a Versioning option. However, I keep getting an error that states 'language "SQL" does not exist.'. Any suggestions on how to bypass or fix this error? Does anyone else have the same problem/error?

Related Solutions

[GIS] How to uninstall PostgreSQL Server on localhost and reinstall with new user and password

The postgres user is normally hidden under Windows 7. You can delete it only in the Command Window with

net user postgres /delete

The postgres service can be deleted with sc delete postgresql-8.3 (or whatever version you are running).

For more details on how to uninstall postgres completely, see http://forums.enterprisedb.com/posts/list/1437.page

There are some registry entries to be removed manually.

[GIS] Using ArcGIS and QGIS together in multi-user editing environment

Since you would "prefer everyone on ArcGIS, using ArcSDE, but ... don't have the budget" you may be interested in "GISquirrel" http://www.gisquirrel.com which gives multi-user editing of PostGIS tables in ArcMap (at Basic licence level). This may give you what you want for all users, but if you still need QGIS in the mix then you are quite right to consider the effects of editing with mixed clients. The main and obvious advice is that you should always be prepared to recover from adverse incidents in your database, such as users accidentally deleting records, etc, and the more mission-critical your data then more fine-grained you want your recover capabilities. You might also want regular reporting of the vital statistics of your tables - number of rows, number of new/edit/deleted feature, etc to help identify any issues.
GISquirrel works differently from both ArcGIS+SDE and QGIS, though the overall effect is similar to QGIS in that edits are visible to all users immediately on saving.
GISquirrel checks out selected features for editing, and (optionally) can write values into the main table that “lock” the features and show who is editing them. The lock is only seen as a feature lock by GISquirrel though – other applications would just see a value in a field, though they could be customised to respect it.
On saving edits, they are written back to the main table. Only the changed/new/deleted features are altered in the main table.
GISquirrel also (optionally) manages feature-level metadata, showing who last edited each feature and when, which you wouldn’t get with direct QGIS editing (unless you used triggers and individual database logins).
I cannot foresee any problems with editing a PostGIS table in both (GISquirrel+ArcMap) and QGIS, so long as (as you said) you are not worried about potential editing clashes (where the later save would "win"). But we would recommend doing your own testing to verify this in your environment if you do intend to edit with mixed clients.

Best Answer

Related Solutions

[GIS] How to uninstall PostgreSQL Server on localhost and reinstall with new user and password

[GIS] Using ArcGIS and QGIS together in multi-user editing environment

Related Question