The following requirements should be taken into account when implementing historisation:
The following is common to both approaches:
The repository approach developed by the Kanton of Solothurn (SO!GIS) allows data to be checked out, revised externally (potentially in completely different software) and to be checked in again. Alternatively, one can just import data from external production systems and store the data in a central, historised respository. The advantage is that data consistency is always guaranteed.
updatelayer() - see download below - first runs the following checks before doing the updates:
After the checks ran successfully the actual data synchronsation can take place. In a first step, the script finds all records that exist in the old repository table, but not in the new table to be checked in. These records are archived as deleted in the repository table. As a second step the script finds all records that are present in the new table, but not in the repository. Those records are retrieved into the repository and marked as new inserts.
For the live historisation approach developed by the City of Uster (based on existing work in PostgreSQL, e.g. TimeTravel by Varlena), we first need to import two trigger functions written in pl/Perl. You need to load the two trigger functions
update_timegis() listed in the download section below. They are best imported into the "public" schema. Next you have to do the following 6 steps, which can be automated by the script
add_history(), also available below:
1. Create additional columns required for the historisation (see above)
2. Create a delete rule on the master repository table
3. Create insert and update row-level before triggers for insert and update that call the two functions
4. Create indizes on the master table, specifically for create_date and archive_date
5. Create a view representing the current data state
6. Create rules (insert, update and delete) to enable data manipulation on the current view
FOSSGIS 2009 article "[Historisierung von PostGIS-Daten als Grundlage zur Langzeitarchivierung von Geodaten http://www.kappasys.ch/pgtools/pghistory/postgis_historisierung_fossgis2009_neumann_duester.pdf" (german) explaining the historisation principles.
Goal: synchronise external data source with historised repository. Syntax: updatelayer(new_schema.new_table,historised_schema.historised_table)
updatelayer(), pl/PgSQL, by Horst Duester, Kanton Solothurn
insert_timegis() trigger function
insert_timegis(), pl/Perl, by Andreas Neumann, City of Uster
update_timegis() trigger function
update_timegis(), pl/Perl, by Andreas Neumann, City of Uster
Goal: add necessary columns, triggers, rules, indizes and views for historised datasets. Syntax: add_history('name_schema','name_table','name_schema_view','name_view')
add_history(), pl/Perl, by Andreas Neumann, City of Uster
Goal: historised dataset to experiment with. Please create schema "test", as this SQL script creates a table and view in the schema "test". It also assumes that the above listed trigger functions "insert_timegis()" and "update_timegis()" are present. Assumes utf-8 encoding.
historised_table_example.sql, by Andreas Neumann, City of Uster